PostgreSQL 10 版本虽然支持创建范围分区表和列表分区表，但创建过程依然比较繁琐，需要手工定义子表索引、主键，详见 PostgreSQL10：重量级新特性-支持分区表，PostgreSQL 11 版本得到增强，在父表上创建索引、主键、外键后，子表上将自动创建，本文演示这三种场景。

值得一提的是，11 版本之前 PostgreSQL 的分区表不支持全局主键，虽然可以在父表和子表上分别定义主键，但不支持全局主键，也就是说，父表和子表、子表和子表的主键数据可以重复。

手册上的 Release 说明

Support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on partitioned tables

本文以创建哈希分区表为例进行测试。

测试环境准备

创建分区表并插入测试数据，为后续测试做准备。

创建父表

CREATE TABLE userinfo (
 userid    int4,
 username  character varying(64),
 ctime   timestamp(6) without time zone
) PARTITION BY HASH(userid);

创建子表

CREATE TABLE userinfo_p0 PARTITION OF userinfo FOR VALUES WITH(MODULUS 4, REMAINDER 0);
CREATE TABLE userinfo_p1 PARTITION OF userinfo FOR VALUES WITH(MODULUS 4, REMAINDER 1);
CREATE TABLE userinfo_p2 PARTITION OF userinfo FOR VALUES WITH(MODULUS 4, REMAINDER 2);
CREATE TABLE userinfo_p3 PARTITION OF userinfo FOR VALUES WITH(MODULUS 4, REMAINDER 3);

插入数据

给分区表插入100万数据，如下:

INSERT INTO userinfo(userid,username,ctime) SELECT n, n || '_username',clock_timestamp() FROM generate_series(1,1000000) n;

测试一: 创建主键

在父表上创建主键，如下。

ALTER TABLE userinfo ADD PRIMARY KEY (userid);

在父表上查看，如下。

francs=> \d userinfo
                          Table "francs.userinfo"
  Column  |              Type              | Collation | Nullable | Default
----------+--------------------------------+-----------+----------+---------
 userid   | integer                        |           | not null |
 username | character varying(64)          |           |          |
 ctime    | timestamp(6) without time zone |           |          |
Partition key: HASH (userid)
Indexes:
    "userinfo_pkey" PRIMARY KEY, btree (userid)
Number of partitions: 4 (Use \d+ to list them.)

查看子表，发现子表上也有了主键。

francs=> \d userinfo_p0
                         Table "francs.userinfo_p0"
  Column  |              Type              | Collation | Nullable | Default
----------+--------------------------------+-----------+----------+---------
 userid   | integer                        |           | not null |
 username | character varying(64)          |           |          |
 ctime    | timestamp(6) without time zone |           |          |
Partition of: userinfo FOR VALUES WITH (modulus 4, remainder 0)
Indexes:
    "userinfo_p0_pkey" PRIMARY KEY, btree (userid)

此主键为全局主键，子表间的主键之间不能有重复数据。

测试二: 创建索引

在父表上创建索引，如下

francs=> CREATE INDEX idx_userinfo_username ON userinfo USING BTREE(username);
CREATE INDEX

发现父表和子表上都创建了索引，如下。

francs=> \d userinfo
                          Table "francs.userinfo"
  Column  |              Type              | Collation | Nullable | Default
----------+--------------------------------+-----------+----------+---------
 userid   | integer                        |           | not null |
 username | character varying(64)          |           |          |
 ctime    | timestamp(6) without time zone |           |          |
Partition key: HASH (userid)
Indexes:
    "userinfo_pkey" PRIMARY KEY, btree (userid)
    "idx_userinfo_username" btree (username)
Number of partitions: 4 (Use \d+ to list them.)

francs=> \d userinfo_p1
                         Table "francs.userinfo_p1"
  Column  |              Type              | Collation | Nullable | Default
----------+--------------------------------+-----------+----------+---------
 userid   | integer                        |           | not null |
 username | character varying(64)          |           |          |
 ctime    | timestamp(6) without time zone |           |          |
Partition of: userinfo FOR VALUES WITH (modulus 4, remainder 1)
Indexes:
    "userinfo_p1_pkey" PRIMARY KEY, btree (userid)
    "userinfo_p1_username_idx" btree (username)

测试三: 创建外键

例如两张表，supplier_groups 和 supplier 分别用来存储供应商分组和供应商信, 如下。

CREATE TABLE supplier_groups(
    group_id    int4 PRIMARY KEY,
    group_name  text
);

CREATE TABLE suppliers (
    supplier_id    int4 PRIMARY KEY,
    supplier_name  text,
    group_id       int4 REFERENCES supplier_groups(group_id)
) PARTITION BY HASH (supplier_id);

创建子表

CREATE TABLE suppliers_p0 PARTITION OF suppliers FOR VALUES WITH(MODULUS 4, REMAINDER 0);
CREATE TABLE suppliers_p1 PARTITION OF suppliers FOR VALUES WITH(MODULUS 4, REMAINDER 1);
CREATE TABLE suppliers_p2 PARTITION OF suppliers FOR VALUES WITH(MODULUS 4, REMAINDER 2);
CREATE TABLE suppliers_p3 PARTITION OF suppliers FOR VALUES WITH(MODULUS 4, REMAINDER 3);

查看子表

查看子表，发现子表上也自动创建了外键。

francs=> \d suppliers_p0
               Table "francs.suppliers_p0"
    Column     |  Type   | Collation | Nullable | Default
---------------+---------+-----------+----------+---------
 supplier_id   | integer |           | not null |
 supplier_name | text    |           |          |
 group_id      | integer |           |          |
Partition of: suppliers FOR VALUES WITH (modulus 4, remainder 0)
Indexes:
    "suppliers_p0_pkey" PRIMARY KEY, btree (supplier_id)
Foreign-key constraints:
    "suppliers_group_id_fkey" FOREIGN KEY (group_id) REFERENCES supplier_groups(group_id)

总结

以上演示了 PostgreSQL 11 分区表在父表上创建索引、主键、外键后，子表会自动创建相应索引、主键、外键，相比10版本极大减少了分区表维护工作量。

参考

• PostgreSQL10: 重量级新特性-支持分区表
• PostgreSQL11: 分区表增加哈希分区
• PostgreSQL11: 分区表支持创建主键、外键、索引
• PostgreSQL11: 分区表支持UPDATE分区键
• PostgreSQL11: 分区表增加 Default Partition
• PostgreSQL11: Partitioning Improvements

新书推荐

最后推荐和张文升共同编写的《PostgreSQL实战》，本书基于PostgreSQL 10 编写，共18章，重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等，涵盖大量实战用例!

购买链接：https://item.jd.com/12405774.html