在PostgreSQL 10中引入了声明式分区时,这是向前迈出的一大步。 但是,新的重要功能在PostgreSQL 10中不起作用,而现在在PostgreSQL 11中已解决。其中之一是本地分区索引。 为了更容易理解,我们从PostgreSQL 10中的示例开始。
postgresql 10:
postgres=# select version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit (1 row) postgres=# create table part ( a int, list varchar(5) ) partition by list (list); CREATE TABLE postgres=# create table part_1 partition of part for values in ('beer'); CREATE TABLE postgres=# create table part_2 partition of part for values in ('wine'); CREATE TABLE postgres=# \d+ part Table "public.part" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+------------- a | integer | | | | plain | | list | character varying(5) | | | | extended | | Partition key: LIST (list) Partitions: part_1 FOR VALUES IN ('beer'), part_2 FOR VALUES IN ('wine') postgres=#
在ppstgresql 10中,如果在分区表上创建索引,会发生什么呢?
postgres=# create index idx_test on part (a); ERROR: cannot create index on partitioned table "part" postgres=#
无法创建索引。但是可以在分区上创建索引:
postgres=# create index idx_test_1 on part_1 (a); CREATE INDEX postgres=# create index idx_test_2 on part_2 (a); CREATE INDEX postgres=#
再来看看postgresql 11中是什么样子的。
postgres=# create table part ( a int, list varchar(5) ) partition by list (list); CREATE TABLE postgres=# create table part_1 partition of part for values in ('beer'); CREATE TABLE postgres=# create table part_2 partition of part for values in ('wine'); CREATE TABLE postgres=# \d+ part Table "public.part" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+------------- a | integer | | | | plain | | list | character varying(5) | | | | extended | | Partition key: LIST (list) Partitions: part_1 FOR VALUES IN ('beer'), part_2 FOR VALUES IN ('wine') postgres=#
再次尝试在分区表上创建索引:
postgres=# create index idx_test on part (a); CREATE INDEX postgres=# \d+ part Table "public.part" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+------------- a | integer | | | | plain | | list | character varying(5) | | | | extended | | Partition key: LIST (list) Indexes: "idx_test" btree (a) Partitions: part_1 FOR VALUES IN ('beer'), part_2 FOR VALUES IN ('wine') postgres=# \d+ part_1 Table "public.part_1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+------------- a | integer | | | | plain | | list | character varying(5) | | | | extended | | Partition of: part FOR VALUES IN ('beer') Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5))) Indexes: "part_1_a_idx" btree (a) postgres=# \d+ part_2 Table "public.part_2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+------------- a | integer | | | | plain | | list | character varying(5) | | | | extended | | Partition of: part FOR VALUES IN ('wine') Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5))) Indexes: "part_2_a_idx" btree (a) postgres=#
在postgresql 11中,分区表上创建的索引被级联到了分区上。
此外,在postgresql10中,如果想对分区表添加主键约束,会失败:
postgres=# alter table part add constraint part_pk primary key(a,list); ERROR: primary key constraints are not supported on partitioned tables LINE 1: alter table part add constraint part_pk primary key(a,list); ^ postgres=#
不过,却可以在分区上添加主键约束:
postgres=# alter table part_1 add constraint part1_pk primary key(a,list); ALTER TABLE postgres=# alter table part_2 add constraint part2_pk primary key(a,list); ALTER TABLE postgres=#
在postgresql11中,可以直接在分区表上添加主键约束,添加成功后,会自动添加到分区上:
postgres=# alter table part add constraint part_pk primary key(a,list); ALTER TABLE postgres=# \d+ part_1 Table "public.part_1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+------------- a | integer | | not null | | plain | | list | character varying(5) | | not null | | extended | | Partition of: part FOR VALUES IN ('beer') Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5))) Indexes: "part_1_pkey" PRIMARY KEY, btree (a, list) "part_1_a_idx" btree (a) postgres=# \d+ part_2 Table "public.part_2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+------------- a | integer | | not null | | plain | | list | character varying(5) | | not null | | extended | | Partition of: part FOR VALUES IN ('wine') Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5))) Indexes: "part_2_pkey" PRIMARY KEY, btree (a, list) "part_2_a_idx" btree (a) postgres=# \d+ part Table "public.part" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------------------+-----------+----------+---------+----------+--------------+------------- a | integer | | not null | | plain | | list | character varying(5) | | not null | | extended | | Partition key: LIST (list) Indexes: "part_pk" PRIMARY KEY, btree (a, list) "idx_test" btree (a) Partitions: part_1 FOR VALUES IN ('beer'), part_2 FOR VALUES IN ('wine') postgres=#
当然也可以像10那样,直接在分区上添加主键约束。
不过,在分区表上添加的主键约束,必须包含分区列:
postgres=# alter table part add constraint part_pk primary key(a); ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "part" lacks column "list" which is part of the partition key. postgres=#