第1页
PostgreSQL 表分区实践分享
许中清 @Huawei jonathan.shv@gmail.com
许中清 @Huawei jonathan.shv@gmail.com
第2页
• 表分区是RDBMS最重要的特性之一 • PG用继承表实现“表分区” • PG实现原生表分区：三种方案 • 实践中的关键问题
第3页
表分区
分而治之：表分区就是把一张大表的数据根据一定策略拆分成若干个子表
CREATE TABLE op_log(
log_no
bigint,
creation_time date,
created_by text,
content
text)
PARTITION BY RANGE(creation_time)
( PARTITION p1 VALUES LESS THAN (DATE'2014-07-01'),
PARTITION p2 VALUES LESS THAN (DATE'2015-01-01'),
PARTITION p3 VALUES LESS THAN (DATE'2015-07-01')，
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
分而治之：表分区就是把一张大表的数据根据一定策略拆分成若干个子表
CREATE TABLE op_log(
log_no
bigint,
creation_time date,
created_by text,
content
text)
PARTITION BY RANGE(creation_time)
( PARTITION p1 VALUES LESS THAN (DATE'2014-07-01'),
PARTITION p2 VALUES LESS THAN (DATE'2015-01-01'),
PARTITION p3 VALUES LESS THAN (DATE'2015-07-01')，
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
第4页
分而治之：解决复杂问题的方法论
• 提高数据聚集度（按分区键） • 利于多核能力，并行处理，提
升分析类业务性能
性能
易管理
• 表的维护从操作一个大表变 成操作多个子表
• 冷热数据差异化管理操作。
可用性
• 表的维护与读写产生冲突
• 表分区缩短维护时间窗，提供 可用性
• 提高数据聚集度（按分区键） • 利于多核能力，并行处理，提
升分析类业务性能
性能
易管理
• 表的维护从操作一个大表变 成操作多个子表
• 冷热数据差异化管理操作。
可用性
• 表的维护与读写产生冲突
• 表分区缩短维护时间窗，提供 可用性
第5页
重要：表分区特性广泛应用
• 在数据仓库领域，表分区是最常使用的特性之一
• 在数据仓库领域，表分区是最常使用的特性之一
第6页
• 表分区是RDBMS最重要的特性之一 • PG用继承表实现“表分区” • PG实现原生表分区：三种方案 • 实践中的关键问题
第7页
PG如何处理继承表
INSERT：子表不继承
CREATE TABLE join_table(c1 int,c2 int); CREATE TABLE parent (c1 int,c2 int); CREATE TABLE child1(c3 int) inherits (parent); CREATE TABLE child2(c4 int) inherits (parent);
INSERT INTO parent VALUES(10,10);
SELECT：子表在底层展开
SELECT * FROM parent p, join_table j WHERE p.c2=j.c2;
INSERT：子表不继承
CREATE TABLE join_table(c1 int,c2 int); CREATE TABLE parent (c1 int,c2 int); CREATE TABLE child1(c3 int) inherits (parent); CREATE TABLE child2(c4 int) inherits (parent);
INSERT INTO parent VALUES(10,10);
SELECT：子表在底层展开
SELECT * FROM parent p, join_table j WHERE p.c2=j.c2;
第8页
PG如何处理继承表(2)
UPDATE/DELETE: 执行计划从顶层展开所有子表
UPDATE parent p SET c1=j.c1 FROM join_table j WHERE p.c2=j.c2;
UPDATE/DELETE: 执行计划从顶层展开所有子表
UPDATE parent p SET c1=j.c1 FROM join_table j WHERE p.c2=j.c2;
第9页
PG使用表继承可实现表分区
op_log
创建表分区四步法
log_no creation_time created_by content
① 创建主表：CREATE TABLE op_log(…);
op_log_2012
log_no creation_time created_by content
op_log_2013
< DATE'2013-01-01'
log_no creation_time created_by content
DATE'2013-01-01'
< DATE'2014-01-01'
op_log_2014
log_no creation_time created_by content
DATE'2014-01-01'
< DATE'2015-01-01'
②创建子表：
CREATE TABLE op_log_2013() INHERITS(op_log);
③增加子表约束：
ALTER TABLE op_log_2013 ADD CONSTRAINT log_range CHECK (creation_time>DATE’2013-01-01’ AND creation_time<DATE’2014-01-01’);
④
指定路由机制： （Trigger/Rule） CREATE TRIGGER router_to_2013 BEFORE INSERT ON log_range FOR EACH ROW EXECUTE PROCEDURE …;
partition_range.sql
op_log
创建表分区四步法
log_no creation_time created_by content
① 创建主表：CREATE TABLE op_log(…);
op_log_2012
log_no creation_time created_by content
op_log_2013
< DATE'2013-01-01'
log_no creation_time created_by content
DATE'2013-01-01'
< DATE'2014-01-01'
op_log_2014
log_no creation_time created_by content
DATE'2014-01-01'
< DATE'2015-01-01'
②创建子表：
CREATE TABLE op_log_2013() INHERITS(op_log);
③增加子表约束：
ALTER TABLE op_log_2013 ADD CONSTRAINT log_range CHECK (creation_time>DATE’2013-01-01’ AND creation_time<DATE’2014-01-01’);
④
指定路由机制： （Trigger/Rule） CREATE TRIGGER router_to_2013 BEFORE INSERT ON log_range FOR EACH ROW EXECUTE PROCEDURE …;
partition_range.sql
第10页
PG“表分区”性能(INSERT)
在批量插入的场景下，trigger对性能影响很大
ms 25000 20000 15000 10000
5000 0
20987 9875
循环插入
203 批量插入
通过主表插入(Trigger) 直接插入子表
测试环境：5个子表，插入10K条数据，插入的所有数据都在一个分区内 每个tuple的大小16 byte
在批量插入的场景下，trigger对性能影响很大
ms 25000 20000 15000 10000
5000 0
20987 9875
循环插入
203 批量插入
通过主表插入(Trigger) 直接插入子表
测试环境：5个子表，插入10K条数据，插入的所有数据都在一个分区内 每个tuple的大小16 byte
第11页
PG“表分区”性能(SELECT)
SELECT生成执行计划的时间 与 子表数量成线性关系
1000m0 s 9000 8000 7000 6000 5000 4000 3000 2000 1000 0 010460768
1828 940700 875
3750 1875
5422 2687
全表扫描 范围查询 点查找
子表数量 全表扫描 范围查询 点查询
5 16 16
50 100 500
1k
2k
3k
5k
46 78 900 1,828 3,750 5,422 9,547
47 78 500 937 1,900 2,750 5,203
470 875 1,875 2,687 4,797
原因：
对主表的查询会展开成N+1条类似的查询语句。(N为主表的子表数量) 相当于要把同一条SQL语句(主表替换成子表)优化N+1遍
SELECT生成执行计划的时间 与 子表数量成线性关系
1000m0 s 9000 8000 7000 6000 5000 4000 3000 2000 1000 0 010460768
1828 940700 875
3750 1875
5422 2687
全表扫描 范围查询 点查找
子表数量 全表扫描 范围查询 点查询
5 16 16
50 100 500
1k
2k
3k
5k
46 78 900 1,828 3,750 5,422 9,547
47 78 500 937 1,900 2,750 5,203
470 875 1,875 2,687 4,797
原因：
对主表的查询会展开成N+1条类似的查询语句。(N为主表的子表数量) 相当于要把同一条SQL语句(主表替换成子表)优化N+1遍
第12页
PG“表分区”：管理不易
1. 创建分区表： （四步法）
1. 步骤复杂(全手工) 2. 易出错
2. 创建索引：
3. 创建N+1个索引
3. 分区表的管理：
4. 增加子表，三步。并需要修改主表路 由机制
1. 创建分区表： （四步法）
1. 步骤复杂(全手工) 2. 易出错
2. 创建索引：
3. 创建N+1个索引
3. 分区表的管理：
4. 增加子表，三步。并需要修改主表路 由机制
第13页
• 表分区是RDBMS最重要的特性之一 • PG用继承表实现“表分区” • PG实现原生表分区：三种方案 • 实践中的关键问题
第14页
改造PG：原生支持表分区
性能
1. 最大分区数量 2. CURD的性能 3. 复杂查询的时延 4. 批量业务的性能
设计原则
满足业务需求
1. 表分区策略 2. 性能基线 3. 分区管理功能
内核侵入最小
1. 保持PG原生架 构，风险可控
2. 侵入内核过多会 导致稳定周期长
性能
1. 最大分区数量 2. CURD的性能 3. 复杂查询的时延 4. 批量业务的性能
设计原则
满足业务需求
1. 表分区策略 2. 性能基线 3. 分区管理功能
内核侵入最小
1. 保持PG原生架 构，风险可控
2. 侵入内核过多会 导致稳定周期长
第15页
改造PG-方案1：在上包一层
1. 父表是一张没有物理属性的虚拟表
pg_index
pg_attribute
2. 增加pg_partition保存分区信息
3. 每个子表也是pg_class中一张表
op_log
pg_trigger
op_log_2012
pg_class
op_log_2013
pg_constraint
op_log_2014
pg_partition
pg_statistic
优点： 1.
2. 缺点：
3. 4. 5.
普通表、父表、子表id在同一个id空间。元数据缓存空间、存 储引擎接口无需改动。 改动小，实现相对容易
每一个子表都有一份元数据，冗余度高，系统资源占用高 表模式修改(DDL)需要更新所有子表元数据，失效消息数量剧增 因为以上原因导致对分区数的限制
pg_toast
1. 父表是一张没有物理属性的虚拟表
pg_index
pg_attribute
2. 增加pg_partition保存分区信息
3. 每个子表也是pg_class中一张表
op_log
pg_trigger
op_log_2012
pg_class
op_log_2013
pg_constraint
op_log_2014
pg_partition
pg_statistic
优点： 1.
2. 缺点：
3. 4. 5.
普通表、父表、子表id在同一个id空间。元数据缓存空间、存 储引擎接口无需改动。 改动小，实现相对容易
每一个子表都有一份元数据，冗余度高，系统资源占用高 表模式修改(DDL)需要更新所有子表元数据，失效消息数量剧增 因为以上原因导致对分区数的限制
pg_toast
第16页
改造PG-方案2：在下加一层分区表的逻辑/物理元数据解耦：
1. 所有子表的逻辑元数据继承父表
pg_index
pg_attribut方e 案2为用当方p前g案_采toast
2. 每个子表独立保存物理元数据
op_log
pg_trigger
op_log_2012
pg_class
op_log_2013
pg_constraint
op_log_2014
pg_partition
pg_statistic
优点： 1. 元数据缓存大大减小：所有子表的逻辑元数据只需要存一份 2. DDL语句修改更高效 3. 子表数量支持的更多
缺点： （复杂度、工作量、内核侵入程度较高） 4. 普通表与子表的id不在一个id空间 5. 元数据缓存机制复杂：子表需要单独的缓存空间 6. 存储引擎接口必须要区分普通表与分区表，并新增处理分支
1. 所有子表的逻辑元数据继承父表
pg_index
pg_attribut方e 案2为用当方p前g案_采toast
2. 每个子表独立保存物理元数据
op_log
pg_trigger
op_log_2012
pg_class
op_log_2013
pg_constraint
op_log_2014
pg_partition
pg_statistic
优点： 1. 元数据缓存大大减小：所有子表的逻辑元数据只需要存一份 2. DDL语句修改更高效 3. 子表数量支持的更多
缺点： （复杂度、工作量、内核侵入程度较高） 4. 普通表与子表的id不在一个id空间 5. 元数据缓存机制复杂：子表需要单独的缓存空间 6. 存储引擎接口必须要区分普通表与分区表，并新增处理分支
第17页
改造PG-方案3：向下拉一层
pg_attribute
所有表的逻辑/物理元数据解耦： 1. 所有表的元数据分逻辑/物理两层，对
应将pg_class拆分成pg_class_logic 和pg_class_physical两层 2. 普通表是只有一个子表的特殊分区表
缺点： 1. 对PG的程序架构和代码要
pg_class_logic
逻辑元数据层 物理元数据层 pg_class_physical
pg_constraint pg_statistic
伤筋动骨的修改
优点： 1. 从逻辑上统一了分区表和普通表。所有的表id都在同一个空间 2. 元数据缓存和失效机制不需要再区分分区表和普通表 3. 存储引擎接口不需要区分分区表和普通表 4. 垃圾回收/统计采样等等都不再需要区分分区表和普通表
pg_trigger pg_toast
pg_attribute
所有表的逻辑/物理元数据解耦： 1. 所有表的元数据分逻辑/物理两层，对
应将pg_class拆分成pg_class_logic 和pg_class_physical两层 2. 普通表是只有一个子表的特殊分区表
缺点： 1. 对PG的程序架构和代码要
pg_class_logic
逻辑元数据层 物理元数据层 pg_class_physical
pg_constraint pg_statistic
伤筋动骨的修改
优点： 1. 从逻辑上统一了分区表和普通表。所有的表id都在同一个空间 2. 元数据缓存和失效机制不需要再区分分区表和普通表 3. 存储引擎接口不需要区分分区表和普通表 4. 垃圾回收/统计采样等等都不再需要区分分区表和普通表
pg_trigger pg_toast
第18页
• 表分区是RDBMS最重要的特性之一 • PG用继承表实现“表分区” • PG实现原生表分区：三种方案 • 实践中的关键问题
第19页
关键问题1：元数据与缓存失效机制
PostgreSQL元数据缓存同步策略
分区表的关键问题
Table
1:1 RelationData
Session1
Session2
Session3
RelationData
PData
元数据表 缓存
进程1
RelationData
PData
元数据表 缓存
进程2
失效消息队列
RelationData
PData
元数据表 缓存
进程3
1. 一个RelationData包含一张表的所有表模式信息 2. 一个Session中同一张表的RelationData实例最多只有一个 3. RelationData是从优化器、执行器、到存储引擎等共用的数据
结构 4. 收到实效消息后RelationData会重建
Parent Table Partition
1:1
1:m 1:1
RelationData PData
1. 子表的数据结构(PData)如何作为 参数在系统中传递(改动最小)？难 道要改所有的函数接口吗？
2. 接收到表模式失效消息时，如何重 建主表和子表的信息？
PostgreSQL元数据缓存同步策略
分区表的关键问题
Table
1:1 RelationData
Session1
Session2
Session3
RelationData
PData
元数据表 缓存
进程1
RelationData
PData
元数据表 缓存
进程2
失效消息队列
RelationData
PData
元数据表 缓存
进程3
1. 一个RelationData包含一张表的所有表模式信息 2. 一个Session中同一张表的RelationData实例最多只有一个 3. RelationData是从优化器、执行器、到存储引擎等共用的数据
结构 4. 收到实效消息后RelationData会重建
Parent Table Partition
1:1
1:m 1:1
RelationData PData
1. 子表的数据结构(PData)如何作为 参数在系统中传递(改动最小)？难 道要改所有的函数接口吗？
2. 接收到表模式失效消息时，如何重 建主表和子表的信息？
第20页
关键问题2：分区表的统计信息
PostgreSQL统计信息：
create table sta_test(c1 int,c2 int); insert into sta_test values(generate_series(1,10),generate_series(100,200)); insert into sta_test values(generate_series(30,40),generate_series(300,400)); insert into sta_test values(generate_series(60,70),generate_series(600,700));
特征值统计信息（c1）：MCV(Most Common Values)
直方图统计信息（c2）：
分区表统计信息的关键问题： 1. 采集时，生成主表的统计信息还是每个子表的统计信息？ 2. 如果使用主表的统计信息，那么Pruning之后的，据此计算的cost偏差 很大(特别是分区键上的cost) 3. 如果使用子表的统计信息，那么如何累加成主表的统计信息？子表太多 时，优化器效率如何保证？
PostgreSQL统计信息：
create table sta_test(c1 int,c2 int); insert into sta_test values(generate_series(1,10),generate_series(100,200)); insert into sta_test values(generate_series(30,40),generate_series(300,400)); insert into sta_test values(generate_series(60,70),generate_series(600,700));
特征值统计信息（c1）：MCV(Most Common Values)
直方图统计信息（c2）：
分区表统计信息的关键问题： 1. 采集时，生成主表的统计信息还是每个子表的统计信息？ 2. 如果使用主表的统计信息，那么Pruning之后的，据此计算的cost偏差 很大(特别是分区键上的cost) 3. 如果使用子表的统计信息，那么如何累加成主表的统计信息？子表太多 时，优化器效率如何保证？
第21页
关键问题3：表级锁与并发
PG表级锁冲突矩阵(简化)
分区表的关键问题
Requested Lock Mode
ACCESS SHARE (SELECT) ROW EXCLUSIVE (I/U/D/)
Current Lock Mode
ACCESS ROW
ACCESS
SHARE EXCLUSIVE EXCLUSIVE
(SELECT) (I/U/D/) (DDL)
Add/Drop Partition 时父表上加什么锁？
session1
session2
SELECT >350
Parent
DROP P2
X Pruning:0011
P1 P2 P3 P4
DROPED
100 200 300 400
X ERROR: P4?
ACCESS EXCLUSIVE (DDL)
X
X
X
注:pg有8个级别的表级锁，常见的3个
INSERT INTO Interval分区表如何避免为同一 个子表创建两个物理文件？
（Interval分区表：向某个子表插入第一条记录 时才会为这个子表创建物理文件）
PG表级锁冲突矩阵(简化)
分区表的关键问题
Requested Lock Mode
ACCESS SHARE (SELECT) ROW EXCLUSIVE (I/U/D/)
Current Lock Mode
ACCESS ROW
ACCESS
SHARE EXCLUSIVE EXCLUSIVE
(SELECT) (I/U/D/) (DDL)
Add/Drop Partition 时父表上加什么锁？
session1
session2
SELECT >350
Parent
DROP P2
X Pruning:0011
P1 P2 P3 P4
DROPED
100 200 300 400
X ERROR: P4?
ACCESS EXCLUSIVE (DDL)
X
X
X
注:pg有8个级别的表级锁，常见的3个
INSERT INTO Interval分区表如何避免为同一 个子表创建两个物理文件？
（Interval分区表：向某个子表插入第一条记录 时才会为这个子表创建物理文件）
第22页
关键问题4：子表剪枝算法
分区键是单列
分区 表：p_table(100,200,300,400,500) 查询: SELECT * FROM ptable WHERE c>150 and (c<250 or c>450)
AND
C>150 01111
OR 11101
C<250 11100
C>450 00001
分区键是多列 分区键: (c1,c2) 值域 P1: (100, 100) {(-∞,100], (-∞,+∞)} P2: (100, 200) {100, [100,200)} P3: (100, 300) {100, [200,300)} P4: (200, 200) {[100,200], (-∞,+∞)} P5: (200, 300) {200, [200,300)} P6: (200, 500) {200, [300,500)} P7: (300, 200) {[200,300], (-∞,+∞)} P8: (300, 400) {[300, [200,400]} 查询: SELECT * FROM ptable
?WHERE c2>350
Pruning结果：P1,P4,P6,P7,P8
分区键是单列
分区 表：p_table(100,200,300,400,500) 查询: SELECT * FROM ptable WHERE c>150 and (c<250 or c>450)
AND
C>150 01111
OR 11101
C<250 11100
C>450 00001
分区键是多列 分区键: (c1,c2) 值域 P1: (100, 100) {(-∞,100], (-∞,+∞)} P2: (100, 200) {100, [100,200)} P3: (100, 300) {100, [200,300)} P4: (200, 200) {[100,200], (-∞,+∞)} P5: (200, 300) {200, [200,300)} P6: (200, 500) {200, [300,500)} P7: (300, 200) {[200,300], (-∞,+∞)} P8: (300, 400) {[300, [200,400]} 查询: SELECT * FROM ptable
?WHERE c2>350
Pruning结果：P1,P4,P6,P7,P8
第23页
① Scan(P1)
关键问题5：算子
Append
Iterator (P1,P2,P3)
Scan(P2)
Scan(P3)
Scan(Pi)
② Iterator
(P1,P2,P3)
Join P,Q分区策略完 全一样
Iterator (Q1,Q2,Q3)
Iterator (J1,J2,J3)
Join (P1,P2,P3) (Q1,Q2,Q3)
Scan(Pi)
Scan(Qi)
Scan(Pi)
Scan(Qi)
关键问题5：算子
Append
Iterator (P1,P2,P3)
Scan(P2)
Scan(P3)
Scan(Pi)
② Iterator
(P1,P2,P3)
Join P,Q分区策略完 全一样
Iterator (Q1,Q2,Q3)
Iterator (J1,J2,J3)
Join (P1,P2,P3) (Q1,Q2,Q3)
Scan(Pi)
Scan(Qi)
Scan(Pi)
Scan(Qi)
第24页
时延的代价
测试环境： 数据量 ： 测试目的：
存储：SAS(15000rps)*2 计算：2P12C 100个子表，每个子表10w条数据，共1000w条数据 主要针对OLTP类场景，验证表分区之后的性能代价
s200
INSERT性能对比
160 142 140
100 80
75 65
55 50
50w（1并发）
200w(50并发)
PG继承表 分区表 PG普通表
SELECT(Index)性能对比
继承表场景下时间太长，中途中止
14 12
12 11.35
6 4
4 3.68
0 10w次/并发50 并发10w次/并发200 并发
PG继承表 分区表 PG普通表
UPDATE性能对比
继承表场景下时间太长，中途中止
40 35.374 35 30.519 30
15 10 6.185 5.554
分区表 PG普通表
1tuple/1sql100sql/1tx50 1并tu发ple/共1s更ql1新sq1l/01wtx条1 并发 共更新10w条
DELETE性能对比
30 25 20
继承表场景下时间太长，大约1200s 24.585
19.098
10 5 3.33 2.95
分区表 PG普通表
1tuple/1sql100sql/1tx501tu并ple发/1s共ql1删sq除l/1t0xw1条并发 共删除10w条
测试环境： 数据量 ： 测试目的：
存储：SAS(15000rps)*2 计算：2P12C 100个子表，每个子表10w条数据，共1000w条数据 主要针对OLTP类场景，验证表分区之后的性能代价
s200
INSERT性能对比
160 142 140
100 80
75 65
55 50
50w（1并发）
200w(50并发)
PG继承表 分区表 PG普通表
SELECT(Index)性能对比
继承表场景下时间太长，中途中止
14 12
12 11.35
6 4
4 3.68
0 10w次/并发50 并发10w次/并发200 并发
PG继承表 分区表 PG普通表
UPDATE性能对比
继承表场景下时间太长，中途中止
40 35.374 35 30.519 30
15 10 6.185 5.554
分区表 PG普通表
1tuple/1sql100sql/1tx50 1并tu发ple/共1s更ql1新sq1l/01wtx条1 并发 共更新10w条
DELETE性能对比
30 25 20
继承表场景下时间太长，大约1200s 24.585
19.098
10 5 3.33 2.95
分区表 PG普通表
1tuple/1sql100sql/1tx501tu并ple发/1s共ql1删sq除l/1t0xw1条并发 共删除10w条
第25页
谢谢