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

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

PostgreSQL 12 新特性解读之三| 分区表DML性能大辐提升

原作者：谭峰　　创作时间：2019-10-16 15:31:52+08　　

wangliyun 发布于2019-10-17 08:12:52　　

评论: 1　　

顶: 670　

踩: 687　

### **作者介绍**
#### 谭峰，网名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)

评论:1　　

顶: 670　

踩: 687　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-24 10:38:44+08

主题教育专题组织生活会对照检查材料:https://www.nanss.com/gongzuo/18464.html 厨房对联:https://www.nanss.com/shenghuo/18458.html 旖旎是什么意思:https://www.nanss.com/xuexi/19947.html 儒林外史读书笔记:https://www.nanss.com/xuexi/19624.html 中国民间为什么要吃腊八粥:https://www.nanss.com/wenti/18276.html 美术教师工作总结:https://www.nanss.com/gongzuo/18776.html 苗族的传统节日:https://www.nanss.com/shenghuo/19788.html 茭白的营养价值:https://www.nanss.com/yinshi/19513.html 社会实践感想:https://www.nanss.com/xuexi/19683.html 欠条的正确写法:https://www.nanss.com/shenghuo/19383.html 经典短篇散文:https://www.nanss.com/yuedu/18814.html 西游记读后感100字:https://www.nanss.com/xuexi/19634.html 年货会方案:https://www.nanss.com/gongzuo/18656.html 人际交往技巧:https://www.nanss.com/shenghuo/18596.html gs是什么意思:https://www.nanss.com/wenti/19781.html 屈原怎么死的:https://www.nanss.com/wenti/18277.html 清代名人轶事:https://www.nanss.com/shenghuo/19261.html 毕业论文致谢部分:https://www.nanss.com/xuexi/18811.html 关于家的诗句:https://www.nanss.com/xuexi/19167.html 高中化学教学总结:https://www.nanss.com/gongzuo/18740.html 初五是什么日子:https://www.nanss.com/wenti/19389.html 印度的首都是哪里:https://www.nanss.com/shenghuo/18539.html 变质岩:https://www.nanss.com/jiaju/19464.html 全球人口排名前十位:https://www.nanss.com/shenghuo/19663.html 尊敬老师的名言:https://www.nanss.com/yulu/19256.html 八个字的吉祥话:https://www.nanss.com/yulu/18641.html 水的性质:https://www.nanss.com/jiaju/18392.html 蛇皮果怎么吃:https://www.nanss.com/yinshi/19519.html 检查怎么写:https://www.nanss.com/gongzuo/19149.html 讲座心得:https://www.nanss.com/xuexi/18695.html