PostgreSQL分區(qū)表實(shí)踐與思考

1、技術(shù)創(chuàng)新，變革未來斗魚 POSTGRESQL分區(qū)表實(shí)踐與思考1分區(qū)表場景與意義2pg各版本分區(qū)表實(shí)現(xiàn)3生產(chǎn)環(huán)境中pg分區(qū)實(shí)現(xiàn)4分區(qū)表實(shí)踐與問題01 分區(qū)表場景與意義分區(qū)表的業(yè)務(wù)需求 數(shù)據(jù)庫業(yè)務(wù)數(shù)據(jù)配置表：數(shù)據(jù)量小，易管理與優(yōu)化狀態(tài)表：數(shù)據(jù)量大，性能問題流水表：有明顯的時間變化，每天數(shù)據(jù)量都很大，定期歸檔統(tǒng)計表： 加載后數(shù)據(jù)不變，數(shù)據(jù)量很大，需統(tǒng)計分析性能問題，歷史數(shù)據(jù)歸檔問題，空間問題 分區(qū)表 OLTP需要用到分區(qū)表，OLAP中分區(qū)表是大多數(shù)表的存在狀態(tài)定義：把一個大的物理表分成若干個小的物理表，并使得這些小物理表在邏輯上可 以被當(dāng)成一張表來使用。主表/父表/Master Table主表是創(chuàng)建

2、字表的模板，是一個正常的普通表，但正常情況下，主表并不存在任何數(shù)據(jù)子表/分區(qū)表/Chlid Table/Partition Table子表繼承并屬于一個主表，與主表是一對多的關(guān)系，子表中存儲所有的數(shù)據(jù)分區(qū)表意義性能提升特定場景，查詢更新轉(zhuǎn)為分區(qū)操作提升性能歷史數(shù)據(jù)歷史數(shù)據(jù)歸檔和清理，通過drop分區(qū)實(shí)現(xiàn)，非常高效存儲拆分單個表只能在一個位置，分區(qū)表冷熱分離分區(qū)表的意義分區(qū)表的定義表名字段約束索引主表和分區(qū)表關(guān)系實(shí)現(xiàn)分區(qū)字段和分區(qū)策略范圍分區(qū) 列 表 分 區(qū) 哈希分區(qū)pg各版本分區(qū)表實(shí)現(xiàn)02pg官方分區(qū)表的演進(jìn) Postgresql 官方分區(qū)表功能的演進(jìn)傳統(tǒng)分區(qū)表 postgresql 9.x及

3、之前的版本內(nèi)置分區(qū)表 postgresql 10版本開始支持增強(qiáng)分區(qū)表 postgresql 11版本中分區(qū)表功能的增強(qiáng)高性能分區(qū)表postgresql 12版本中，分區(qū)表性能提升主表和分區(qū)表需分別創(chuàng)建，各自定義，數(shù)據(jù)不能自動關(guān)聯(lián)主表和分區(qū)表關(guān)系：通過繼承和觸發(fā)器實(shí)現(xiàn) 實(shí)現(xiàn)方式：定義父表定義子表： 用 inherits 創(chuàng)建分區(qū)表定義子表約束 ：約束數(shù)據(jù)對應(yīng)分區(qū)的規(guī)則創(chuàng)建子表索引 ：子表不會繼承父表的索引創(chuàng)建分區(qū)插入、修改、刪除函數(shù)和觸發(fā)器啟用分區(qū)查詢參數(shù)：設(shè)置 constraint_exclusion 參數(shù)傳統(tǒng)分區(qū)表 pg 9.5 之前版本傳統(tǒng)分區(qū)表 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();傳統(tǒng)分區(qū)表 pg 9.5 之前版本內(nèi)置分區(qū)表（斗魚線上目前使用的分區(qū)表之一）Postgresql 10提供了內(nèi)置分區(qū)表，不需要預(yù)先在父表上定義觸發(fā)器， 對父表的DML操作也會自動路由到相應(yīng)分區(qū)。僅支持范圍分區(qū)和列表分區(qū)。Pg 10內(nèi)置分區(qū)表創(chuàng)建步驟：創(chuàng)建主表：指定分區(qū)鍵和分區(qū)策略創(chuàng)建分區(qū)表：指定分區(qū)表的主表和分區(qū)鍵的取值范圍，范圍不能有重疊在分區(qū)表上創(chuàng)建索引：分區(qū)鍵一般必須創(chuàng)建索引，其他列根據(jù)實(shí)際場景選擇是 否場景索引內(nèi)置分區(qū)表 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*內(nèi)置分區(qū)表 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); #不能執(zhí)行內(nèi)置分區(qū)表 pg 10 支持版本分區(qū)表可以繼承主表的字段、索引信息支持hash分區(qū)，rang/list 支

12、持默認(rèn)分區(qū)，多級分區(qū)分區(qū)索引增強(qiáng)： 分區(qū)主表可以創(chuàng)建主鍵，可以創(chuàng)建外鍵， DML增強(qiáng)： 可以分區(qū)間移動行，可以路由到外部分區(qū)，性能方面：數(shù)據(jù)搜索訪問優(yōu)化，提升分區(qū)檢索性能Pg 10內(nèi)置分區(qū)表創(chuàng)建步驟：創(chuàng)建主表：指定分區(qū)鍵和分區(qū)策略，創(chuàng)建相關(guān)索引創(chuàng)建分區(qū)表：指定分區(qū)表的主表和分區(qū)鍵的取值范圍，范圍不能有重疊增強(qiáng)分區(qū)表 pg 11 支持版本 功能增強(qiáng)：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); #不用執(zhí)行 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); #不用執(zhí)行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*增強(qiáng)分區(qū)表 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); #可以執(zhí)行增強(qiáng)分區(qū)表 pg 11 支持版本高性能分區(qū)表 pg 12 支持版本 高性能分區(qū)表功能方面：增加更多查詢函數(shù)，便于快速查看分區(qū)表狀態(tài)性能方面：分區(qū)表的性能得到大幅提升，尤其是在分區(qū)表數(shù)量非 常多時，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*高性能分區(qū)表 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); #可以執(zhí)行高性能分區(qū)表 pg 12 支持版本pg分區(qū)表各版本主要功能對比對比項(xiàng)Pg 9.x及之前Pg 10.xPg 11.xPg 12.x分區(qū)特點(diǎn)傳統(tǒng)分區(qū)內(nèi)置分區(qū)加強(qiáng)分區(qū)高性能分區(qū)數(shù)據(jù)路由自建路由觸發(fā)器內(nèi)置分區(qū)內(nèi)置分區(qū)內(nèi)置分區(qū)分區(qū)表索引創(chuàng)建子表逐個單建子表逐個單建子表繼

20、承父表子表繼承父表父表全局主鍵不支持不支持支持支持分區(qū)策略Range,listRange,listRange,list,hashRange,list,hash默認(rèn)分區(qū)不支持不支持支持支持Update跨分區(qū)不支持不支持支持支持分區(qū)性能差較差范圍策略優(yōu)化大量dml優(yōu)化維護(hù)難易度難較難容易容易生產(chǎn)環(huán)境中分區(qū)實(shí)現(xiàn)03生產(chǎn)環(huán)境分區(qū)表管理方式 分區(qū)表的管理方式使用 pg_pathman 管理 postgresql 分區(qū)表greenplum 分區(qū)表的實(shí)現(xiàn)與管理原理：PostgreSQL傳統(tǒng)的分區(qū)方法，使用約束來區(qū)分不同分區(qū)存放的數(shù)據(jù)(配置constraint_exclusion=partition)，執(zhí)行s

21、elect/delete/update時執(zhí)行計劃根據(jù)約 束和查詢條件排除不需要查詢的分區(qū)表。調(diào)用COPY或插入數(shù)據(jù)時使用觸發(fā)器或規(guī) 則，將數(shù)據(jù)插入對應(yīng)的分區(qū)表。傳統(tǒng)的做法，無論是查詢還是插入，對性能的影響 都較大。pg_pathman與傳統(tǒng)的繼承分區(qū)表做法有一個不同的地方，分區(qū)的定義存放在一張 元數(shù)據(jù)表中，表的信息會cache在內(nèi)存中，同時使用HOOK來實(shí)現(xiàn)RELATION的替 換，所以效率非常高。用 pg_pathman 管理pg分區(qū)表 優(yōu)勢：Pg第三方插件，分區(qū)表性能更高、便于管理和維護(hù)pg_pathman 使用函數(shù)來維護(hù)分區(qū)表，并且創(chuàng)建了一些視圖，可以查看分區(qū)表的 狀態(tài)。分區(qū)表的定義則存

22、在一張表中，定義數(shù)據(jù)緩存在內(nèi)存中。四個管理表：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分區(qū)表 實(shí)現(xiàn)：創(chuàng)建主表插入測試數(shù)據(jù)創(chuàng)建分區(qū)調(diào)用 create_range_partitions 函數(shù)創(chuàng)建分區(qū)，指定起始值、間隔、分區(qū)個數(shù)遷移數(shù)據(jù)到分區(qū)表中調(diào)用 partition_table_concurrently 函數(shù)非阻塞遷移數(shù)據(jù)stop_concurrent_part_task停止遷移任務(wù)pathman_concurrent_