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

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

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

4、E TABLE 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)INHERITS(pg_9_ta

5、b);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_pg_9_tab_p_201912

6、_ctime ON pg_9_tab_p_201912 USING btree(create_time);CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()RETURNS trigger LANGUAGE plpgsqlAS$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_insert_trigger();传统分区表 pg

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

8、t)PARTITION BY RANGE(create_time);CREATE INDEX idx_pg_10_tab_ctime ON pg_10_tab USING btree(create_time);#不能执行CREATE TABLE pg_10_tab_p_hisotry PARTITION OF pg_10_tab FOR VAlUES FROM(1546272000)TO(1569859200);CREATE TABLE pg_10_tab_p_201910 PARTITION OF pg_10_tab FOR VAlUES FROM(1569859200)TO(1572537

9、600);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 FOR 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 IND

10、EX 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 btree(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_t

11、ime)SELECT round(1000*random(),chr(int4(random()*26)+65),generate_series(1568476800,1576339200,360);SELECT count(*)FROM pg_10_tab;SELECT count(*)FROM ONLY pg_10_tab;dt+pg_10_tab*内置分区表 pg 10 支持版本内置分区表 pg 10 支持版本功能增强：分区表可以继承主表的字段、索引信息支持hash分区，rang/list 支持默认分区，多级分区分区索引增强：分区主表可以创建主键，可以创建外键，DML增强：可以分区间移动

12、行，可以路由到外部分区，性能方面：数据搜索访问优化，提升分区检索性能Pg 10内置分区表创建步骤：1.创建主表：指定分区键和分区策略，创建相关索引2.创建分区表：指定分区表的主表和分区键的取值范围，范围不能有重叠增强分区表 pg 11 支持版本CREATE TABLE pg_11_tab(id serial,uid int4,username varchar,create_time bigint)PARTITION BY RANGE(create_time);CREATE INDEX idx_pg_11_tab_ctime ON pg_11_tab USING btree(create_tim

13、e);#可以执行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 VAlUES 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 PA

14、RTITION OF pg_11_tab FOR VAlUES FROM(1575129600)TO(1577808000);CREATE INDEX idx_pg_11_tab_p_hisotry_ctime ON pg_11_tab_p_hisotry 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_t

15、ab_p_201911 USING btree(create_time);CREATE INDEX idx_pg_11_tab_p_201912_ctime ON pg_11_tab_p_201912 USING btree(create_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_

16、tab;SELECT count(*)FROM ONLY pg_11_tab;dt+pg_11_tab*增强分区表 pg 11 支持版本增强分区表 pg 11 支持版本高性能分区表功能方面：增加更多查询函数，便于快速查看分区表状态性能方面：分区表的性能得到大幅提升，尤其是在分区表数量非常多时，DML性能提升更加明显高性能分区表 pg 12 支持版本CREATE TABLE pg_12_tab(id serial,uid int4,username varchar,create_time bigint)PARTITION BY RANGE(create_time);CREATE INDEX id

17、x_pg_12_tab_ctime ON pg_11_tab USING btree(create_time);#可以执行CREATE TABLE pg_12_tab_p_hisotry PARTITION 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(1572537

18、600)TO(1575129600);CREATE TABLE pg_12_tab_p_201912 PARTITION OF pg_12_tab FOR VAlUES FROM(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 coun

19、t(*)FROM ONLY pg_12_tab;dt+pg_12_tab*高性能分区表 pg 12 支持版本高性能分区表 pg 12 支持版本pg分区表各版本主要功能对比对比项对比项Pg 9.x及之前及之前Pg 10.xPg 11.xPg 12.x分区特点传统分区内置分区加强分区高性能分区数据路由自建路由触发器内置分区内置分区内置分区分区表索引创建子表逐个单建子表逐个单建子表继承父表子表继承父表父表全局主键不支持不支持支持支持分区策略Range,listRange,listRange,list,hashRange,list,hash默认分区不支持不支持支持支持Update跨分区不支持不支持支持

20、支持分区性能差较差范围策略优化大量dml优化维护难易度难较难容易容易生产环境中分区实现03分区表的管理方式 使用 pg_pathman 管理 postgresql 分区表 greenplum 分区表的实现与管理生产环境分区表管理方式优势：Pg第三方插件，分区表性能更高、便于管理和维护原理：PostgreSQL传统的分区方法，使用约束来区分不同分区存放的数据(配置constraint_exclusion=partition)，执行select/delete/update时执行计划根据约束和查询条件排除不需要查询的分区表。调用COPY或插入数据时使用触发器或规则，将数据插入对应的分区表。传统的做法

21、，无论是查询还是插入，对性能的影响都较大。pg_pathman与传统的继承分区表做法有一个不同的地方，分区的定义存放在一张元数据表中，表的信息会cache在内存中，同时使用HOOK来实现RELATION的替换，所以效率非常高。用 pg_pathman 管理pg分区表实现：pg_pathman 使用函数来维护分区表，并且创建了一些视图，可以查看分区表的状态。分区表的定义则存在一张表中，定义数据缓存在内存中。四个管理表：1.pathman_config-main config storage2.pathman_config_params-optional parameters3.pathman_c

22、oncurrent_part_tasks-currently running partitioning workers4.pathman_partition_list-list of all existing partitions用 pg_pathman 管理pg分区表范围分区实现步骤：1 创建主表2 插入测试数据3 创建分区 调用 create_range_partitions 函数创建分区，指定起始值、间隔、分区个数4 迁移数据到分区表中 调用 partition_table_concurrently 函数非阻塞迁移数据 stop_concurrent_part_task 停止迁移任务 p

23、athman_concurrent_part_tasks 查看迁移任务的进行过程5 确认迁移状态是否完成 select count(*)from only dba_schema.journal;6 禁用主表，确认执行计划 explain select*from dba_schema.journal where dt=2016-10-25 00:00:00:timestamp;具体实验过程，参见：http:/ pg_pathman 管理pg分区表用 pg_pathman 管理pg分区表greenplum 分区表管理方式分区表+分布式存储定义主表后，所有操作会自动运用到分区表中1 创建主表，指定分

24、布键2 指定分区键，分区策略，创建分区表分区键是否是 int类型，实际性能差距较大Greenplum 管理分区表Greenplum 管理分区表分区表实践与问题041 斗鱼pg分区表环境：混合云IDC：postgresql 分区表 混合云云环境：postgresql分区表 混合云云环境：greenplum分区表 2 斗鱼pg分区字段类型：以按照时间的range范围分区为主3 斗鱼pg分区表类型：postgresql 10 内置分区表 pg_pathman 插件维护分区表 greenplum 分布+分区表Pg分区表实际生产环境说明Pg分区表架构1 分区后性能降低问题 业务表修改为分区表后，查询不走分区键，性能会下降很多 所有的查询都需要包含分区键，分区键兼顾性能与归档 如果表不是非常大，使用分区表性能降低太多，可不用分区表2 数据传输问题 开发数据同步传输通道 DTS数据同步，不同版本传输性能的不同3 使用规范问题 所有有关pg的操作，都建立在pg体系结构之上 按照pg的设计和特点来使用pgPg分区表实际生产环境问题