PostgreSQL 分区

PostgreSQL 分区

1. 概念

   PostgreSQL 分区是将单个大表分成小的、可管理的部分的过程。通过将表分解为更小的部分，可以提高查询性能和数据加载速度，同时也可以简化数据维护和管理。使用分区技术，可以将大型表分成多个小表，每个小表只包含特定的数据子集。

   在 PostgreSQL 中，分区表由一个主表和多个子表组成。主表包含所有子表的元数据，而子表包含实际数据。分区表的查询和 DML 操作与普通表相同，但是在执行操作时，PostgreSQL 会自动将操作路由到正确的子表。

   PostgreSQL 支持三种分区方式：

   1. 范围分区（Range Partitioning）：将表中的数据按照某个范围进行分区，例如按照时间范围或数值范围。例如：

      CREATE TABLE measurement (
          logdate         date not null,
          peaktemp        int,
          unitsales       int
      ) PARTITION BY RANGE (logdate);
      

   2. 列分区（List Partitioning）：将表中的数据按照某个列的值进行分区，例如按照地区或类型进行分区。例如：

      CREATE TABLE cities (
          city_id      bigserial not null,
          name         text not null,
          population   bigint
      ) PARTITION BY LIST (left(lower(name), 1));
      

   3. 哈希分区（Hash Partitioning）：将表中的数据按照哈希值进行分区，这种方式适合于数据分布比较均匀的情况。例如：

      CREATE TABLE orders (
          order_id     bigint not null,
          cust_id      bigint not null,
          status       text
      ) PARTITION BY HASH (order_id);
      

2. PostgreSQL 分区的详细步骤：

   1. 创建父表：在 PostgreSQL 中，您需要创建一个父表来定义分区规则。创建父表时，您需要指定要分区的列和分区方式。以下是创建父表的示例 SQL 语句：

      create table vehicles
      (
          id         varchar(64) not null,
          make       varchar(50),
          model      varchar(50),
          year       integer     not null,
          created_at timestamp default now(),
          constraint vehicles_pkey
              primary key (id, year)
      )
          partition by RANGE (year);
      

      在此示例中，我们创建了一个名为 vehicles 的表，并将其分区方式设置为 RANGE，使用 year 列进行分区。这意味着我们将根据 year 列的值将表拆分为多个子表。

   2. 创建子表：现在，您需要创建子表来存储实际数据。子表必须具有与父表相同的列，并使用父表定义的分区键进行分区。以下是创建子表的示例 SQL 语句：

      -- 包含前面不包含后面[2010,2011)
      CREATE TABLE vehicles_2010 PARTITION OF vehicles FOR VALUES FROM (2010) TO (2011);
      CREATE TABLE vehicles_2011 PARTITION OF vehicles FOR VALUES FROM (2011) TO (2012);
      CREATE TABLE vehicles_2012 PARTITION OF vehicles FOR VALUES FROM (2012) TO (2013);
      

      在此示例中，我们创建了三个子表，分别用于存储 2010 年、2011 年和 2012 年的车辆数据。

   3. 插入数据：现在，您可以向父表中插入数据，而 PostgreSQL 将自动将数据路由到正确的子表中。以下是向父表中插入数据的示例 SQL 语句：

      INSERT INTO vehicles (id, make, model, year)
      VALUES ('01', 'Toyota', 'Corolla', 2010);
      INSERT INTO vehicles (id, make, model, year)
      VALUES ('02', 'Honda', 'Civic', 2011);
      INSERT INTO vehicles (id, make, model, year)
      VALUES ('03', 'Ford', 'F-150', 2012);
      

      在此示例中，我们向父表中插入了三条车辆数据，分别是 2010 年的 Toyota Corolla、2011 年的 Honda Civic 和 2012 年的 Ford F-150。由于我们已经定义了分区规则，因此 PostgreSQL 将自动将这些数据路由到正确的子表中。

   4. 查询数据：现在，你可以像查询普通表一样查询分区表。PostgreSQL 将自动将查询路由到正确的子表中，并返回正确的结果。以下是查询数据的示例 SQL 语句：

      SELECT * FROM vehicles WHERE year = 2010;
      —— 同
      SELECT * FROM vehicles_2010;
      

      在此示例中，我们查询了所有年份为 2010 的车辆，并从 vehicles_2010 子表中返回结果。