PostgreSQL 12 新特性解读之三| 分区表DML性能大辐提升__PostgreSQL 的分区表个人认为是 PostgreSQL 比较薄弱的环节，老版本的分区表通过继承表和触发器实现，甚至需要逐个对子表进行索引创建，使用上不方便。 10 版本和 11 版本在分区表的功能上进行了大幅完善，但是当分区表分区数量较大时，分区表的DML性能并不好。 PostgreSQL 12 版本的分区表的性能得到了大辐提升，尤其当分区表的分区数量非常多时，DML 性能提升更加明显。: 世界上功能最强大的开源数据库...

### **作者介绍**
#### 谭峰，网名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，每个场景执行三次取最小执行时间，统计如下:

![CENTER_PostgreSQL_Community]( /images/news/2019/20191016_1547_微信截图_20191016154559.png)

#### 根据本文的测试场景，测试结果汇总如下:
#### 对于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
![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)