PostgreSQL 12: 分区表DML性能大辐提升

PostgreSQL 的分区表个人认为是 PostgreSQL 比较薄弱的环节,从 10 版本开始 PostgreSQL 的分区表在功能方面得到完善,到了 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虚拟机。

环境准备

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

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

创建分区(16个分区),如下:

1
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 分区的分区,如下:

1
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

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

1
2
3
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);

测试场景

场景一、基于分区键查询

1
SELECT * FROM userinfo WHERE userid= $;

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

1
SELECT * FROM userinfo WHERE username= $;

场景三、插入一条记录

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

测试汇总

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

版本 基于分区键查询-p16 基于非分区键查询-p16 插入单条记录-p16 基于分区键查询-p2048 基于非分区键查询-p2048 插入单条记录-p2048
PostgreSQL 11 0.040 ms 1.192 ms 0.473 ms 40.403 ms 137.06 ms 5.008 ms
PostgreSQL 12 0.043 ms 0.254 ms 0.408 ms 0.578 ms 29.77 ms 0.446 ms

根据本文的测试场景,可以得出如下结论:

  • 对于Hash分区表,基于分区键查询场景,当分区数量很大时,性能较11版本有指数级提升,本例 p2048 分区表提升 69.9倍。
  • 对于Hash分区表,基于非分区键查询场景,性能较11版本提升明显,本例 p16分区表提升 4.7 倍, p2048分区表提升 4.6 倍。
  • 对于Hash分区表,插入单条记录场景,当分区数量很大时,性能较11版本提升明显, 本例 p2048 分区表提升 11.2 倍。

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

参考

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

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

PostgreSQL实战
感谢支持!
0%