作者介绍

谭峰，网名francs，《PostgreSQL实战》作者之一，《PostgreSQL 9 Administration Cookbook》译者之一，PostgreSQL 中文社区委员，致力于PostgreSQL技术分享，博客 https://postgres.fun

PostgreSQL 的分区表个人认为是 PostgreSQL 比较薄弱的环节，老版本的分区表通过继承表和触发器实现，甚至需要逐个对子表进行索引创建，使用上不方便。

10 版本和 11 版本在分区表的功能上进行了大幅完善，但是当分区表分区数量较大时，分区表的DML性能并不好。

PostgreSQL 12 版本的分区表的性能得到了大辐提升，尤其当分区表的分区数量非常多时，DML 性能提升更加明显。

发行说明

Improve performance of many operations on partitioned tables (Amit Langote, David Rowley, Tom Lane, Álvaro Herrera)

Allow tables with thousands of child partitions to be processed efficiently by operations that only affect a small number of partitions.

本文将大致比较 11 版本和 12 版本的分区表 DML 性能，测试环境为1台8核16G虚拟机，操作系统 CentOS 7.4。

环境准备

本文以HASH分区表为例进行测试，创建分区表父表，如下:

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

创建分区（16个分区），如下:

psql -At postgres postgres -c "SELECT 'CREATE TABLE userinfo_' || n || ' PARTITION of userinfo FOR VALUES WITH (MODULUS 16, REMAINDER  ' || n || ');' FROM generate_series(0,15) as n ;" | psql 

本文将分别测试包含 16 分区的分区表和包含 2048 分区的分区表，若创建包含 2048 分区的分区，如下:

psql -At postgres postgres -c "SELECT 'CREATE TABLE userinfo_' || n || ' PARTITION of userinfo FOR VALUES WITH (MODULUS 2048, REMAINDER  ' || n || ');' FROM generate_series(0,2047) as n ;" | psql 

插入数据并创建索引，如下:

INSERT INTO userinfo(userid,username,ctime) SELECT round(100000000*random()), n || '_username',clock_timestamp() FROM generate_series(1,20000000) n;
CREATE INDEX idx_userinfo_userid ON userinfo USING BTREE(userid);
CREATE INDEX idx_userinfo_username ON userinfo USING BTREE(username);

测试场景

场景一、基于分区键查询

SELECT * FROM userinfo WHERE userid= $;

场景二、基于非分区键查询

SELECT * FROM userinfo WHERE username= $;

场景三、插入一条记录

INSERT INTO userinfo(userid,username,ctime) VALUES (11111111,'11111111_username',now());

测试汇总

分别在虚机上的 11 版本和 12 版本对以上三个测试场景测试，测试对象为包含 16 个分区的分区表和包含 2048 分区的分区表，为了便于描述，将包含 16 分区的分区表定义为 p16，将包含 2048 分区表的分区表定义为 p2048，每个场景执行三次取最小执行时间，统计如下:

根据本文的测试场景，测试结果汇总如下:

对于Hash分区表，基于分区键查询场景，当分区数量很大时，性能较11版本有指数级提升，本例 p2048 分区表提升 69.9倍。

对于Hash分区表，基于非分区键查询场景，性能较11版本提升明显，本例 p16分区表提升 4.7 倍， p2048分区表提升 4.6 倍。

对于Hash分区表，插入单条记录场景，当分区数量很大时，性能较11版本提升明显, 本例 p2048 分区表提升 11.2 倍。

以上只是大致对 11 版本和 12 版本的哈希分区表进行性能比较，若想获得更精确的性能数据可以使用 pgbench 工具进行压测。

参考

1、 [PostgreSQL11：分区表支持创建主键、外键、索引]

(https://postgres.fun/20180920155600.html)

2、[PostgreSQL 12 preview - 分区表性能提升百倍]

(https://github.com/digoal/blog/blob/master/201905/20190521_01.md)

阅读源文

https://postgres.fun/20190802150700.html