在 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.)@H_301_238@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)@H_301_238@自动创建的索引，名称按照 “{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)@H_301_238@自动创建的索引不能单独删除，可以通过分区表统一删除。
DROP INDEX measurement_y2020_peaktemp_IDx;ERROR: cannot drop index measurement_y2020_peaktemp_IDx because index IDx_measurement_peaktemp requires itHINT: You can drop index IDx_measurement_peaktemp instead.DROP INDEX IDx_measurement_peaktemp;@H_301_238@对于 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@H_301_238@添加分区或者加载（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)@H_301_238@如果在分区表上创建了唯一约束，无法再创建基于外部表（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"@H_301_238@主键约束或唯一约束必须包含分区字段。这样才能确保整个分区表内的唯一性，因为每个分区上的唯一约束只维护自身的唯一性。
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 deFinitionDETAIL: PRIMARY KEY constraint on table "rtable1" lacks column "c1" which is part of the partition key.@H_301_238@新的索引修改语句 ALTER INDEX ATTACH PARTITION 可以将分区上的已有索引挂载到分区表上的索引。
人生本来短暂，你又何必匆匆！点个赞再走吧！
总结以上是内存溢出为你收集整理的PostgreSQL 11 新特性之分区索引全部内容，希望文章能够帮你解决PostgreSQL 11 新特性之分区索引所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）