PostgreSQL傳統(tǒng)的分區(qū)方法，使用約束來區(qū)分不同的分區(qū)存儲(chǔ)數(shù)據(jù)（配置constraint_exclusion = partition），執(zhí)行選擇/刪除/更新時(shí)執(zhí)行計(jì)劃根據(jù)約束和查詢條件排除不需要的查詢的分區(qū)表。調(diào)用COPY或插入數(shù)據(jù)時(shí)使用插入或規(guī)則，將數(shù)據(jù)插入對(duì)應(yīng)的分區(qū)表。

• pg_pathman使用ProcessUtility_hook鉤子來處理分區(qū)表的COPY查詢。

• RuntimeAppend（重寫Append計(jì)劃節(jié)點(diǎn)）

• RuntimeMergeAppend（重寫MergeAppend計(jì)劃節(jié)點(diǎn)）

• PartitionFilter（INSERT觸發(fā)器的直接替代）

• 目前支持range，hash分區(qū)。

• 支持自動(dòng)分區(qū)管理（通過函數(shù)接口創(chuàng)建分區(qū)，自動(dòng)將主表數(shù)據(jù)遷移到分區(qū)表），或手工分區(qū)管理（通過函數(shù)實(shí)現(xiàn)，將現(xiàn)有的表綁定到分區(qū)表，或者從分區(qū)表分割） 。

• 支持的分區(qū)分區(qū)類型包括int，float，date，以及其他常用類型，包括自定義的域。

• 通過CUSTOM SCAN實(shí)現(xiàn)了有效的分區(qū)表JOIN，子查詢過濾分區(qū)。

• 使用RuntimeAppend和RuntimeMergeAppend自定義計(jì)劃節(jié)點(diǎn)實(shí)現(xiàn)了動(dòng)態(tài)分區(qū)選擇。

• PartitionFilter HOOK，實(shí)現(xiàn)就地插入，代替?zhèn)鹘y(tǒng)的插入觸發(fā)器或插入規(guī)則。

• 支持自動(dòng)添加分區(qū)。目前僅支持范圍分區(qū)表。

• 支持從/到直接讀取或?qū)懭敕謪^(qū)表的復(fù)制，提高效率。

• 支持分區(qū)分區(qū)的更新，需要添加替換，如果不需要更新分區(qū)分區(qū)，則不建議添加此轉(zhuǎn)換器，會(huì)產(chǎn)生一定的性能影響。

• 允許用戶自定義定義函數(shù)，在創(chuàng)建分區(qū)時(shí)會(huì)自動(dòng)觸發(fā)。

• 非插入式創(chuàng)建分區(qū)表，以及后臺(tái)自動(dòng)將主表數(shù)據(jù)遷移到分區(qū)表，非插入式。

• 支持FDW，通過配置參數(shù)pg_pathman.insert_into_fdw =（禁用| postgres | any_fdw）支持postgres_fdw或任意fdw(外部分區(qū))

• 支持GUC參數(shù)配置，注意通過使用了HOOK，如果其他插件也使用了相同的HOOK，需要將pg_pathman放在后面注冊(cè)，如pg_stat_statements。

shared_preload_libraries =pg_stat_statements，pg_pathman

• 插入優(yōu)化，使用PartitionFilter替換關(guān)系，替換初始化的方式。效率提高非常明顯。

• 查詢優(yōu)化，分區(qū)定義加載在內(nèi)存中，使用二進(jìn)制搜索和哈希搜索對(duì)應(yīng)范圍與哈希分區(qū)表，使用RuntimeAppend和RuntimeMerge附加自定義計(jì)劃節(jié)點(diǎn)以在運(yùn)行時(shí)選擇分區(qū)；

• 同時(shí)運(yùn)行時(shí)過濾，支持子查詢。傳統(tǒng)的約束法不支持子查詢過濾。

1. 安裝部署

unzip pg_pathman-master.zip

cd pg_pathman-master

make USE_PGXS=1

make USE_PGXS=1 install

alter system set shared_preload_libraries=pg_stat_statements,pg_pathman,telepg_monitor;

$pg_ctl restart -m fast

c ksl

postgres=# create extension pg_pathman;

CREATE EXTENSION



ksl=# dx

                                                    List of installed extensions

    Name | Version |   Schema | Description 

-------------+---------+------------+-------------------------------------------------------

 pageinspect | 1.7 | public | inspect the contents of database pages at a low level

 pg_pathman | 1.5 | public | Partitioning tool for PostgreSQL

   plpgsql | 1.0  | pg_catalog | PL/pgSQL procedural language

(3 rows)

2. RANGE分區(qū)實(shí)戰(zhàn)舉例

創(chuàng)建需要分區(qū)的主表：

postgres=# create table part_test(id int, info text, 
crt_time timestamp not null); -- 分區(qū)列必須有not null約束 

CREATE TABLE

插入一批測試數(shù)據(jù)，模擬已經(jīng)有數(shù)據(jù)了的主表：

postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id|| hour)::interval from generate_series(1,10000) t(id); 

INSERT 0 10000

postgres=# select * from part_test limit 10; 

 id | info |          crt_time 

----+----------------------------------+----------------------------

  1 | 36fe1adedaa5b848caec4941f87d443a | 20XX-10-25 10:27:13.206713

  2 | c7d7358e196a9180efb4d0a10269c889 | 20XX-10-25 11:27:13.206893

  3 | 005bdb063550579333264b895df5b75e | 20XX-10-25 12:27:13.206904

  4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 20XX-10-25 13:27:13.20691

  5 | 857214d8999348ed3cb0469b520dc8e5 | 20XX-10-25 14:27:13.206916

  6 | 4495875013e96e625afbf2698124ef5b | 20XX-10-25 15:27:13.206921

  7 | 82488cf7e44f87d9b879c70a9ed407d4 | 20XX-10-25 16:27:13.20693

  8 | a0b92547c8f17f79814dfbb12b8694a0 | 20XX-10-25 17:27:13.206936

  9 | 2ca09e0b85042b476fc235e75326b41b | 20XX-10-25 18:27:13.206942

 10 | 7eb762e1ef7dca65faf413f236dff93d | 20XX-10-25 19:27:13.206947

(10 rows)

• 分區(qū)列必須有not null約束;

• 分區(qū)個(gè)數(shù)必須能覆蓋已有的所有記錄.

創(chuàng)建分區(qū)，每個(gè)分區(qū)包含1個(gè)月的跨度數(shù)據(jù)：

postgres=#select create_range_partitions(part_test::regclass, -- 主表OID

 crt_time, -- 分區(qū)列名

 2020-11-05 00:00:00::timestamp, -- 開始值

 interval 1 month, -- 間隔；interval 類型，用于時(shí)間分區(qū)表

 24, -- 分多少個(gè)區(qū)

 false) ; -- 不遷移數(shù)據(jù)

NOTICE:  sequence "part_test_seq" does not exist, skipping

 create_range_partitions 

-------------------------

     24

(1 row)

由于不遷移數(shù)據(jù)，所以數(shù)據(jù)還在主表：

postgres=# select count(*) from only part_test;

 count 

-------

 10000

(1 row)

使用非堵塞式的遷移接口：

postgres=# select partition_table_concurrently(part_test::regclass,

                             10000,

                             1.0);

NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task(part_test);

 partition_table_concurrently 

------------------------------



(1 row)

--查看后臺(tái)的數(shù)據(jù)遷移任務(wù)：

select * from pathman_concurrent_part_tasks;

遷移結(jié)束后，主表數(shù)據(jù)已經(jīng)沒有了，全部在分區(qū)中：

postgres=# select count(*) from only part_test;

 count 

-------

     0

(1 row)

數(shù)據(jù)遷移完成后，建議禁用主表，這樣執(zhí)行計(jì)劃就不會(huì)出現(xiàn)主表了。

postgres=# select set_enable_parent(part_test::regclass, false);

 set_enable_parent 

-------------------

(1 row)

postgres=# explain select * from part_test where crt_time = 2020-11-05 00:00:00::timestamp;

              QUERY PLAN 

-------------------------------------------------------------------------------

 Append (cost=0.00..16.18 rows=1 width=45)

   -> Seq Scan on part_test_1 (cost=0.00..16.18 rows=1 width=45)

         Filter: (crt_time = 2020-11-05 00:00:00::timestamp without time zone)

(3 rows)

• 分區(qū)列必須有not null約束；

• 分區(qū)個(gè)數(shù)必須能覆蓋已有的所有記錄；

• 建議使用非堵塞式遷移接口；

• 建議數(shù)據(jù)遷移完成后，禁用主表。

1700w數(shù)據(jù)大概遷移了一個(gè)多小時(shí)，如果表有索引可以先刪除索引，數(shù)據(jù)遷移完成后再建索引，因?yàn)樵趧?chuàng)建分區(qū)的時(shí)候，所有的分區(qū)表都會(huì)多帶帶創(chuàng)建索引，這也是不能保證全局唯一的原因。

--禁止自動(dòng)擴(kuò)展分區(qū)：

select set_auto(part_test::regclass, false);

insert into ksl.part_test values (1,test,2023-01-01::timestamp);

--向后添加分區(qū)：

select append_range_partition(part_test::regclass);

--向前添加分區(qū)：

select prepend_range_partition(part_test::regclass);

--停止遷移任務(wù)：

select top_concurrent_part_task(part_test::regclass);

-- 查看后臺(tái)的數(shù)據(jù)遷移任務(wù)：

select * from pathman_concurrent_part_tasks;

-- 分裂范圍分區(qū),數(shù)據(jù)會(huì)自動(dòng)遷移到另一個(gè)分區(qū)：

select split_range_partition(

part_test_1::regclass, -- 分區(qū)oid

      2020-11-25 00:00:00::timestamp, -- 分裂值

      part_test_1_2); -- 分區(qū)表名

--合并范圍分區(qū),目前僅支持范圍分區(qū)。

--指定兩個(gè)需要合并分區(qū)，必須為相鄰分區(qū)：

select merge_range_partitions(part_test_1::regclass, part_test_1_2::regclass) ;

--不相鄰的分區(qū)合并會(huì)報(bào)錯(cuò)

select merge_range_partitions(part_test_2::regclass, part_test_12::regclass) ;

ERROR:  partitions "part_test_2" and "part_test_12" are not adjacent

--合并后，會(huì)刪掉其中一個(gè)分區(qū)表。

--刪除單個(gè)范圍分區(qū)：

drop_range_partition(

partition TEXT, -- 分區(qū)名稱

        delete_data BOOLEAN DEFAULT TRUE) -- 是否刪除分區(qū)數(shù)據(jù)，如果false，表示分區(qū)數(shù)據(jù)遷移到主表。  

--刪除分區(qū)， 數(shù)據(jù)遷移到主表：

select drop_range_partition(part_test_1,false);  

--刪除分區(qū)，分區(qū)數(shù)據(jù)也刪除，不遷移到主表：

select drop_range_partition(part_test_3,true);

drop_partitions(parent REGCLASS,delete_data BOOLEAN DEFAULT FALSE)

-- 刪除所有分區(qū)表，并將數(shù)據(jù)遷移到主表

select drop_partitions(part_test::regclass, false);

--綁定分區(qū)(已有的表加入分區(qū)表)。

--將已有的表，綁定到已有的某個(gè)分區(qū)主表。

--已有的表與主表要保持一致的結(jié)構(gòu)，包括dropped columns。 (查看pg_attribute的一致性)

--如果設(shè)置了回調(diào)函數(shù)，會(huì)觸發(fā)。

--綁定分區(qū)時(shí)，自動(dòng)創(chuàng)建繼承關(guān)系，自動(dòng)創(chuàng)建約束。

attach_range_partition(

relation REGCLASS, -- 主表OID

          partition REGCLASS, -- 分區(qū)表OID

          start_value ANYELEMENT, -- 起始值

          end_value ANYELEMENT) -- 結(jié)束值

create table part_test_1 (like part_test including all);

select attach_range_partition(part_test::regclass, part_test_1::regclass, 2020-11-05 00:00:00::timestamp, 2020-12-05 00:00:00::timestamp);

--將分區(qū)從主表的繼承關(guān)系中刪除, 不刪數(shù)據(jù)，刪除繼承關(guān)系，刪除約束。

detach_range_partition(partition REGCLASS) -- 指定分區(qū)名，轉(zhuǎn)換為普通表 

select detach_range_partition(part_test_2);

-- 更新觸發(fā)器。

--如果分區(qū)字段要被更新，需要?jiǎng)?chuàng)建更新觸發(fā)器，否則不需要。

create_range_update_trigger(parent REGCLASS)

ksl=> select * from part_test_3 limit 10;

  id | info |          crt_time 

------+----------------------------------+----------------------------

 1450 | d16ae9fa14aabb821df6692beef610e6 | 2021-01-05 00:33:46.657077

 1451 | b88247d2cb9acb9e98ba472f575f180c | 2021-01-05 01:33:46.657081

 1452 | 344c48262f105e8622099b24d9ed7d8a | 2021-01-05 02:33:46.657086

 1453 | bd6e36744447ab70a1624134de9dbde0 | 2021-01-05 03:33:46.65709

 1454 | 3d8c3470df5dcbb1e5ad68974fabf11a | 2021-01-05 04:33:46.657094

 1455 | 71664d8dcdad66ef2ccd0464cc61279b | 2021-01-05 05:33:46.657098

 1456 | 7f0da1bec230ad34741081a5da79b995 | 2021-01-05 06:33:46.657102

 1457 | 34045bcda2117d5643a54c29febd51b6 | 2021-01-05 07:33:46.657107

 1458 | 103a593f0be11898153cf58d5ca576be | 2021-01-05 08:33:46.657111

 1459 | 16e4d2340014ddfeb195c141c0395474 | 2021-01-05 09:33:46.657117

(10 rows)

--創(chuàng)建更新觸發(fā)器前，如果更新分區(qū)字段后的值跨分區(qū)了，會(huì)報(bào)約束錯(cuò)誤。

ksl=> update part_test set crt_time=2021-11-05 00:33:46.657077 where id=1450;

ERROR: new row for relation "part_test_3" violates check constraint "pathman_part_test_3_check"

DETAIL: Failing row contains (1450, d16ae9fa14aabb821df6692beef610e6, 2021-11-05 00:33:46.657077).

--創(chuàng)建更新觸發(fā)器后，正常。

--永久禁止分區(qū)表pg_pathman插件。

--可以針對(duì)單個(gè)分區(qū)主表禁用pg_pathman：

select disable_pathman_for(part_test);

禁用pg_pathman后，繼承關(guān)系和約束不會(huì)變化，只是pg_pathman不介入custom scan 執(zhí)行計(jì)劃。

禁用pg_pathman后的執(zhí)行計(jì)劃：

postgres=# explain select * from part_test where crt_time=2020-11-25 00:00:00::timestamp;

                QUERY PLAN 

-------------------------------------------------------------------------------

 Append (cost=0.00..16.00 rows=2 width=45)

   -> Seq Scan on part_test (cost=0.00..0.00 rows=1 width=45)

         Filter: (crt_time = 2020-11-25 00:00:00::timestamp without time zone)

   ->  Seq Scan on part_test_1 (cost=0.00..16.00 rows=1 width=45)

         Filter: (crt_time = 2020-11-25 00:00:00::timestamp without time zone)

(5 rows)

disable_pathman_for沒有可逆操作，請(qǐng)慎用。

--全局禁止pg_pathman

與禁用單個(gè)分區(qū)主表不同，全局禁止只需要調(diào)整參數(shù)即可，不需要修改pg_pathman的元數(shù)據(jù)，同時(shí)它是可逆操作。

pg_pathman.enable

$ vi $PGDATA/postgresql.conf

pg_pathman.enable = off


$ pg_ctl reload

• 如果在建初始分區(qū)時(shí)，需要設(shè)置分區(qū)表的表空間，可以設(shè)置會(huì)話或事務(wù)的參數(shù)

  setlocal default_tablespace=tablespacename;

• disable_pathman_for函數(shù)沒有可逆操作，請(qǐng)慎用。

• 不建議關(guān)閉pg_pathman.enable

• 不建議開啟自動(dòng)擴(kuò)展范圍分區(qū)，一個(gè)錯(cuò)誤的分區(qū)值可能導(dǎo)致創(chuàng)建很多分區(qū)。

• 推薦使用set_enable_parent禁用主表。

• 由于pg_pathman使用了customscan接口，所以只支持9.5以及以上版本。

• 傳統(tǒng)哈希分區(qū)需要輸入分區(qū)鍵值的約束條件，才能正確選擇分區(qū)。pg_pathman只要輸入鍵值即可。

文章首發(fā)于2020年12月19日

本文作者：柯詩亮（上海新炬王翦團(tuán)隊(duì)）

本文來源：“IT那活兒”公眾號(hào)