PostgreSQL分区表实践与思考

1、技术创新，变革未来斗鱼 POSTGRESQL分区表实践与思考1分区表场景与意义2pg各版本分区表实现3生产环境中pg分区实现4分区表实践与问题01 分区表场景与意义分区表的业务需求 数据库业务数据配置表：数据量小，易管理与优化状态表：数据量大，性能问题流水表：有明显的时间变化，每天数据量都很大，定期归档统计表： 加载后数据不变，数据量很大，需统计分析性能问题，历史数据归档问题，空间问题 分区表 OLTP需要用到分区表，OLAP中分区表是大多数表的存在状态定义：把一个大的物理表分成若干个小的物理表，并使得这些小物理表在逻辑上可 以被当成一张表来使用。主表/父表/Master Table主表是创建

2、字表的模板，是一个正常的普通表，但正常情况下，主表并不存在任何数据子表/分区表/Chlid Table/Partition Table子表继承并属于一个主表，与主表是一对多的关系，子表中存储所有的数据分区表意义性能提升特定场景，查询更新转为分区操作提升性能历史数据历史数据归档和清理，通过drop分区实现，非常高效存储拆分单个表只能在一个位置，分区表冷热分离分区表的意义分区表的定义表名字段约束索引主表和分区表关系实现分区字段和分区策略范围分区 列 表 分 区 哈希分区pg各版本分区表实现02pg官方分区表的演进 Postgresql 官方分区表功能的演进传统分区表 postgresql 9.x及

3、之前的版本内置分区表 postgresql 10版本开始支持增强分区表 postgresql 11版本中分区表功能的增强高性能分区表postgresql 12版本中，分区表性能提升主表和分区表需分别创建，各自定义，数据不能自动关联主表和分区表关系：通过继承和触发器实现 实现方式：定义父表定义子表： 用 inherits 创建分区表定义子表约束 ：约束数据对应分区的规则创建子表索引 ：子表不会继承父表的索引创建分区插入、修改、删除函数和触发器启用分区查询参数：设置 constraint_exclusion 参数传统分区表 pg 9.5 之前版本传统分区表 pg 9.5 之前版本CREATE TA

4、BLE pg_9_tab( id serial,uid int4,username varchar,create_time bigint); CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time);CREATE TABLE pg_9_tab_p_hisotry(CHECK ( create_time = 1569859200 and create_time = 1572537600 and create_time = 1575129600 and create_time 1577808000 ) ) INHER

5、ITS(pg_9_tab);CREATE INDEX idx_pg_9_tab_p_hisotry_ctime ON pg_9_tab_p_hisotry USING btree (create_time); CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time); CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time); CREATE INDEX idx_

6、pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time);CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger() RETURNS triggerLANGUAGE plpgsql AS $function$BEGIN END;$function$;CREATE TRIGGER insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_inser

7、t_trigger();传统分区表 pg 9.5 之前版本内置分区表（斗鱼线上目前使用的分区表之一）Postgresql 10提供了内置分区表，不需要预先在父表上定义触发器， 对父表的DML操作也会自动路由到相应分区。仅支持范围分区和列表分区。Pg 10内置分区表创建步骤：创建主表：指定分区键和分区策略创建分区表：指定分区表的主表和分区键的取值范围，范围不能有重叠在分区表上创建索引：分区键一般必须创建索引，其他列根据实际场景选择是 否场景索引内置分区表 pg 10 支持版本CREATE TABLE pg_10_tab_p_hisotry PARTITION OF pg_10_tab FOR V

8、AlUES FROM (1546272000) TO (1569859200); CREATE TABLE pg_10_tab_p_201910 PARTITION OF pg_10_tab FOR VAlUES FROM (1569859200) TO (1572537600); CREATE TABLE pg_10_tab_p_201911 PARTITION OF pg_10_tab FOR VAlUES FROM (1572537600) TO (1575129600); CREATE TABLE pg_10_tab_p_201912 PARTITION OF pg_10_tab FO

9、R VAlUES FROM (1575129600) TO (1577808000);CREATE INDEX idx_pg_10_tab_p_hisotry_ctime ON pg_10_tab_p_hisotry USING btree (create_time); CREATE INDEX idx_pg_10_tab_p_201910_ctime ON pg_10_tab_p_201910 USING btree (create_time); CREATE INDEX idx_pg_10_tab_p_201911_ctime ON pg_10_tab_p_201911 USING btr

10、ee (create_time); CREATE INDEX idx_pg_10_tab_p_201912_ctime ON pg_10_tab_p_201912 USING btree (create_time);INSERT INTO pg_10_tab(uid,username,create_time)SELECT round(1000*random(),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);SELECT count(*) FROM pg_10_tab; SELECT count(*

11、) FROM ONLY pg_10_tab;dt+ pg_10_tab*内置分区表 pg 10 支持版本CREATE TABLE pg_10_tab( id serial, uid int4, username varchar, create_time bigint ) PARTITION BY RANGE(create_time); CREATE INDEX idx_pg_10_tab_ctime ON pg_10_tab USING btree (create_time); #不能执行内置分区表 pg 10 支持版本分区表可以继承主表的字段、索引信息支持hash分区，rang/list 支

12、持默认分区，多级分区分区索引增强： 分区主表可以创建主键，可以创建外键， DML增强： 可以分区间移动行，可以路由到外部分区，性能方面：数据搜索访问优化，提升分区检索性能Pg 10内置分区表创建步骤：创建主表：指定分区键和分区策略，创建相关索引创建分区表：指定分区表的主表和分区键的取值范围，范围不能有重叠增强分区表 pg 11 支持版本 功能增强：CREATE TABLE pg_11_tab_p_hisotry PARTITION OF pg_11_tab DEFAULT;CREATE TABLE pg_11_tab_p_201910 PARTITION OF pg_11_tab FOR VA

13、lUES FROM (1569859200) TO (1572537600); CREATE TABLE pg_11_tab_p_201911 PARTITION OF pg_11_tab FOR VAlUES FROM (1572537600) TO (1575129600); CREATE TABLE pg_11_tab_p_201912 PARTITION OF pg_11_tab FOR VAlUES FROM (1575129600) TO (1577808000);CREATE INDEX idx_pg_11_tab_p_hisotry_ctime ON pg_11_tab_p_h

14、isotry USING btree (create_time); #不用执行 CREATE INDEX idx_pg_11_tab_p_201910_ctime ON pg_11_tab_p_201910 USING btree (create_time);CREATE INDEX idx_pg_11_tab_p_201911_ctime ON pg_11_tab_p_201911 USING btree (create_time);CREATE INDEX idx_pg_11_tab_p_201912_ctime ON pg_11_tab_p_201912 USING btree (cre

15、ate_time); #不用执行INSERT INTO pg_11_tab(uid,username,create_time)SELECT round(1000*random(),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);SELECT count(*) FROM pg_11_tab; SELECT count(*) FROM ONLY pg_11_tab;dt+ pg_11_tab*增强分区表 pg 11 支持版本CREATE TABLE pg_11_tab( id serial, uid i

16、nt4, username varchar, create_time bigint ) PARTITION BY RANGE(create_time); CREATE INDEX idx_pg_11_tab_ctime ON pg_11_tab USING btree (create_time); #可以执行增强分区表 pg 11 支持版本高性能分区表 pg 12 支持版本 高性能分区表功能方面：增加更多查询函数，便于快速查看分区表状态性能方面：分区表的性能得到大幅提升，尤其是在分区表数量非 常多时，DML性能提升更加明显CREATE TABLE pg_12_tab_p_hisotry PAR

17、TITION OF pg_12_tab DEFAULT;CREATE TABLE pg_12_tab_p_201910 PARTITION OF pg_12_tab FOR VAlUES FROM (1569859200) TO (1572537600); CREATE TABLE pg_12_tab_p_201911 PARTITION OF pg_12_tab FOR VAlUES FROM (1572537600) TO (1575129600); CREATE TABLE pg_12_tab_p_201912 PARTITION OF pg_12_tab FOR VAlUES FROM

18、 (1575129600) TO (1577808000);INSERT INTO pg_12_tab(uid,username,create_time)SELECT round(1000*random(),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);SELECT count(*) FROM pg_12_tab; SELECT count(*) FROM ONLY pg_12_tab;dt+ pg_12_tab*高性能分区表 pg 12 支持版本CREATE TABLE pg_12_tab( i

19、d serial, uid int4, username varchar, create_time bigint ) PARTITION BY RANGE(create_time); CREATE INDEX idx_pg_12_tab_ctime ON pg_11_tab USING btree (create_time); #可以执行高性能分区表 pg 12 支持版本pg分区表各版本主要功能对比对比项Pg 9.x及之前Pg 10.xPg 11.xPg 12.x分区特点传统分区内置分区加强分区高性能分区数据路由自建路由触发器内置分区内置分区内置分区分区表索引创建子表逐个单建子表逐个单建子表继

20、承父表子表继承父表父表全局主键不支持不支持支持支持分区策略Range,listRange,listRange,list,hashRange,list,hash默认分区不支持不支持支持支持Update跨分区不支持不支持支持支持分区性能差较差范围策略优化大量dml优化维护难易度难较难容易容易生产环境中分区实现03生产环境分区表管理方式 分区表的管理方式使用 pg_pathman 管理 postgresql 分区表greenplum 分区表的实现与管理原理：PostgreSQL传统的分区方法，使用约束来区分不同分区存放的数据(配置constraint_exclusion=partition)，执行s

21、elect/delete/update时执行计划根据约 束和查询条件排除不需要查询的分区表。调用COPY或插入数据时使用触发器或规 则，将数据插入对应的分区表。传统的做法，无论是查询还是插入，对性能的影响 都较大。pg_pathman与传统的继承分区表做法有一个不同的地方，分区的定义存放在一张 元数据表中，表的信息会cache在内存中，同时使用HOOK来实现RELATION的替 换，所以效率非常高。用 pg_pathman 管理pg分区表 优势：Pg第三方插件，分区表性能更高、便于管理和维护pg_pathman 使用函数来维护分区表，并且创建了一些视图，可以查看分区表的 状态。分区表的定义则存

22、在一张表中，定义数据缓存在内存中。四个管理表：pathman_config - main config storagepathman_config_params - optional parameterspathman_concurrent_part_tasks - currently running partitioning workerspathman_partition_list - list of all existing partitions用 pg_pathman 管理pg分区表 实现：创建主表插入测试数据创建分区调用 create_range_partitions 函数创建分区，指定起始值、间隔、分区个数迁移数据到分区表中调用 partition_table_concurrently 函数非阻塞迁移数据stop_concurrent_part_task停止迁移任务pathman_concurrent_