PostgreSQL 11 新特性之分区索引

作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
文章目录

        分区自动索引
        分区表唯一约束

在 PostgreSQL 10 中,分区上的索引需要基于各个分区手动创建,而不能基于分区的父表创建索引。PostgreSQL 11 可以基于分区表创建索引。分区表上的索引并不会创建一个物理上的索引,而是为每个分区上的索引创建一个模板。
分区自动索引

如果在分区表上创建了一个索引,PostgreSQL 自动为每个分区创建具有相同属性的索引。

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

CREATE TABLE measurement_y2018 PARTITION OF measurement
    FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
CREATE TABLE measurement_y2019 PARTITION OF measurement
    FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');

CREATE INDEX idx_measurement_peaktemp ON measurement(peaktemp);

\d measurement
              Table "public.measurement"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 city_id   | integer |           | not null |
 logdate   | date    |           | not null |
 peaktemp  | integer |           |          |
 unitsales | integer |           |          |
Partition key: RANGE (logdate)
Indexes:
    "idx_measurement_peaktemp" btree (peaktemp)
Number of partitions: 2 (Use \d+ to list them.)

measurement 表上创建了一个索引 idx_measurement_peaktemp,因此该表上的分区也会自动创建相应的索引。

\d measurement_y2018
           Table "public.measurement_y2018"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 city_id   | integer |           | not null |
 logdate   | date    |           | not null |
 peaktemp  | integer |           |          |
 unitsales | integer |           |          |
Partition of: measurement FOR VALUES FROM ('2018-01-01') TO ('2019-01-01')
Indexes:
    "measurement_y2018_peaktemp_idx" btree (peaktemp)

自动创建的索引,名称按照 “{partition name}_{column name}_idx” 的模式定义。多个字段的复合索引使用下划线(_)连接字段名称。如果索引名称已经存在,在名称的最后添加一个数字。如果名称过长,使用缩写。

随后新增的分区或者通过 ATTACH PARTITION 挂载的分区都会自动创建相应的索引。

CREATE TABLE measurement_y2020 (LIKE measurement);
ALTER TABLE measurement ATTACH PARTITION measurement_y2020
  FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

\d measurement_y2020
           Table "public.measurement_y2020"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 city_id   | integer |           | not null |
 logdate   | date    |           | not null |
 peaktemp  | integer |           |          |
 unitsales | integer |           |          |
Partition of: measurement FOR VALUES FROM ('2020-01-01') TO ('2021-01-01')
Indexes:
    "measurement_y2020_peaktemp_idx" btree (peaktemp)

    
自动创建的索引不能单独删除,可以通过分区表统一删除。

DROP INDEX measurement_y2020_peaktemp_idx;
ERROR:  cannot drop index measurement_y2020_peaktemp_idx because index idx_measurement_peaktemp requires it
HINT:  You can drop index idx_measurement_peaktemp instead.

DROP INDEX idx_measurement_peaktemp;

   
分区表唯一约束

对于 PostgreSQL 10,只能基于分区创建唯一约束(PRIMARY KEY 和 UNIQUE KEY),而不能针对分区的父表创建唯一约束。PostgreSQL 11 支持分区表上的唯一约束。

CREATE TABLE rtable(c1 INT, c2 VARCHAR(10)) PARTITION BY RANGE(c1);
ALTER TABLE rtable ADD CONSTRAINT pk_rtable PRIMARY KEY(c1);
\d rtable
                      Table "public.rtable"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | character varying(10) |           |          |
Partition key: RANGE (c1)
Indexes:
    "pk_rtable" PRIMARY KEY, btree (c1)
Number of partitions: 0

   
添加分区或者加载(ATTACH)分区时自动创建相应的主键:

CREATE TABLE rtable100 PARTITION OF rtable FOR VALUES FROM (1) TO (100);
\d rtable100
                    Table "public.rtable100"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | character varying(10) |           |          |
Partition of: rtable FOR VALUES FROM (1) TO (100)
Indexes:
    "rtable100_pkey" PRIMARY KEY, btree (c1)

    
如果在分区表上创建了唯一约束,无法再创建基于外部表(FOREIGN TABLE)的分区。因为无法为外部表创建唯一约束。

CREATE FOREIGN TABLE rtable200 PARTITION OF rtable FOR VALUES FROM (101) TO (200) SERVER remote1;
ERROR:  cannot create index on foreign table "rtable200"

 
主键约束或唯一约束必须包含分区字段。这样才能确保整个分区表内的唯一性,因为每个分区上的唯一约束只维护自身的唯一性。

CREATE TABLE rtable1(c1 INT, c2 VARCHAR(10)) PARTITION BY RANGE(c1);
ALTER TABLE rtable1 ADD CONSTRAINT pk_table1 PRIMARY KEY(c2);
ERROR:  insufficient columns in PRIMARY KEY constraint definition
DETAIL:  PRIMARY KEY constraint on table "rtable1" lacks column "c1" which is part of the partition key.

新的索引修改语句 ALTER INDEX ATTACH PARTITION 可以将分区上的已有索引挂载到分区表上的索引。