斗鱼POSTGRESQL分区表实践与思考-企业应用专场（35页）.pdf

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