# Partition 維護工具 pg_partman * [pg_partman 官方 GitHub](https://github.com/pgpartman/pg_partman) ## pg_partman 的用途? PostgreSQL 的 Partitioned Table，需要先建立好該資料對應的 Partition 才能 Insert 該筆資料。 過往在 Windows 環境是自行準備 Range Partition 維護機制，架構大致是 1. Config 表定義各 Partitioned Table 的時間切割單位。 2. Stored Procedure 處理未來 Partition 的建立。 3. Stored Procedure 處理逾期 Partition 的刪除或搬移 (Detach/Attach)。 4. 排程定期執行 2、3 項的 Stored Procedure。 pg_partman 的優勢是直接涵括了以上的機制，無需外部的調度工具 (如 pg_cron、pgAgent 等排程工具) 也可完成自動的 Partition 維護。 最近換電腦，趕緊來試試 pg_partman 功能，並預計於下一篇測試 TimeScaleDB 後比較兩者。 本文紀錄 Demo 使用 pg_partman 的過程。 ## 安裝 Docker-compose、Dockerfile 參考[我的 GitHub](https://github.com/michelle0809/PostgresDockerCompose/tree/with-pg_partman)。 啟動 Container 後，連線到 PostgreSQL 即可安裝 pg_partman Extension。 下圖是依照官方範例，建立了 partman Schema，並於該 Schema 安裝 pg_partman Extension。 ![image](https://hackmd.io/_uploads/rknaecEJll.png) 安裝後查看 partman Schema 內容，有以下物件。 <center class="half"> <img src="https://hackmd.io/_uploads/HJYzE9EJxl.png" width="200"/> <img src="https://hackmd.io/_uploads/HJM7N5Nkxx.png" width="200"/> </center> ## 功能 ### 新建 Partitioned Table 1. 依照 PostgreSQL 原生方式建立 Partitioned Table。 ```sql -- create partitioned table create table public.test_partman ( id uuid default gen_random_uuid(), content varchar not null, created_at timestamp with time zone default now(), primary key (id, created_at) ) partition by range (created_at); ``` 2. 使用 pg_partman 的 create_parent 函數設定 Partition 維護。 ```sql -- create partition by pg_partman SELECT partman.create_parent( p_parent_table := 'public.test_partman' -- 父表 , p_control := 'created_at' -- Partition Key , p_interval := '1 day' -- 每個分區的時間長度 , p_type := 'range' -- 支援 range/list partition -- , p_epoch := 'none' -- 若 p_control 是 integer/bigint 欄位，透過此參數設定該數字所代表的意義 (seconds/milliseconds/microseconds/nanoseconds) , p_premake := 7 -- 領先於當前時間的分區數 , p_start_partition := current_date::varchar -- 起始分區的時間/數值 , p_default_table := true -- 是否建立 Default Partition , p_automatic_maintenance := 'on' -- 是否啟動自動維護 -- , p_constraint_cols := NULL -- 約束條件 -- , p_template_table := NULL -- 模板表 , p_jobmon := false -- 是否透過 pg_jobmon 監控 pg_partman 的工作 (我沒安裝故設定 false) -- , p_date_trunc_interval := NULL -- Partition 時間截斷方式 , p_control_not_null := true -- 是否 partition key 不能為 NULL -- , p_time_encoder := NULL -- 若 Partition Key 是非標準的時間格式或非時間類型，則需在此參數提供將 Partition Key 轉換為時間格式的函數 -- , p_time_decoder := NULL -- 若 Partition Key 是非標準的時間格式或非時間類型，則需在此參數提供將時間格式轉換為 Partition Key 的函數 ); ``` 3. 建立後資料表狀況如下圖。 <center class="half"> <img src="https://hackmd.io/_uploads/SkMyd0EJll.png" width="200"/> </center> ### 既有 Normal Table 轉換為 Partitioned Table 依照官方文檔的[說明](https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md#partitioning-an-existing-table)，免不了資料搬移的過程，但官方提供了 offline、online 兩種實作方案。 若可接受 down time 並期望較快的搬移完成可採用 offline 實作方式；反之希望最小 down time 就依照官方的 online 實作方式操作。 #### 測試資料 ```sql -- create normal table CREATE TABLE public.original_table ( id uuid primary key default gen_random_uuid(), content varchar not null, created_at timestamp with time zone default now() ); CREATE INDEX idx_created_at ON public.original_table (created_at); -- insert test data INSERT INTO public.original_table (content, created_at) SELECT concat('content_', i) AS content, now() - i * interval '1 hour' AS created_at FROM generate_series(1, 10000000) AS i; ``` #### offline 方法 1. 重新命名 Existing Normal Table ```sql ALTER TABLE public.original_table RENAME to old_nonpartitioned_table; ``` 2. 重建為 Partitioned Table ```sql SELECT partman.create_parent( p_parent_table := 'public.original_table' -- 父表 , p_control := 'created_at' -- Partition Key , p_interval := '1 month' -- 每個分區的時間長度 , p_type := 'range' -- 支援 range/list partition , p_premake := 7 -- 領先於當前時間的分區數 , p_start_partition := (SELECT min(created_at)::date::varchar FROM public.old_nonpartitioned_table) -- 起始分區的時間/數值 , p_default_table := true -- 是否建立 Default Partition , p_automatic_maintenance := 'on' -- 是否啟動自動維護 , p_jobmon := false -- 是否透過 pg_jobmon 監控 pg_partman 的工作 (我沒安裝故設定 false) , p_control_not_null := true -- 是否 partition key 不能為 NULL ); ``` ![image](https://hackmd.io/_uploads/BJwv9JSkxe.png) 3. 執行 pg_partman 提供的 Procedure 執行資料搬移 ```sql CALL partman.partition_data_proc ( p_parent_table := 'public.original_table' -- 已設定分區的父表 , p_loop_count := 1000000 -- 循環次數 , p_interval := '1 month' -- 每個分區的時間長度 , p_lock_wait := 0 -- 鎖定等待時間 , p_lock_wait_tries := 10 -- 鎖定等待次數 , p_wait := 1 -- 等待時間 , p_order := 'ASC' -- 排序方式 , p_source_table := 'public.old_nonpartitioned_table' -- 來源表 , p_ignored_columns := NULL -- 忽略的欄位 , p_quiet := false -- 若關閉此選項，可在執行過程中看到詳細的資訊 ); ``` ![image](https://hackmd.io/_uploads/SJ7u5kH1gl.png) 4. VACUUM ANALYZE 新表 ```sql VACUUM ANALYZE public.original_table; ``` 5. 檢查搬移結果 ```sql SELECT count(1) FROM public.original_table; ``` <center class="half"> <img src="https://hackmd.io/_uploads/BkZFc1S1xg.png" width="400"/> </center> 6. 刪除舊資料表 ```sql DROP TABLE public.old_nonpartitioned_table; ``` ### 既有 Partitioned Table 改由 pg_partman 維護 直接執行 create_parent，並設定新的分區開始由 pg_partman 維護 (p_start_partition)。 因為既有的分區名稱如果不符合 pg_partman 命名規則，要處理也很麻煩，不如就從新的分區開始接管就好。 ```sql -- existing partitioned table CREATE TABLE public.original_partitioned_table ( id uuid default gen_random_uuid(), content varchar not null, created_at timestamp with time zone default now(), primary key (id, created_at) ) PARTITION BY RANGE (created_at); CREATE TABLE original_partitioned_table_20250422 PARTITION OF public.original_partitioned_table FOR VALUES FROM ('2025-04-22') TO ('2025-04-23'); CREATE TABLE original_partitioned_table_20250423 PARTITION OF public.original_partitioned_table FOR VALUES FROM ('2025-04-23') TO ('2025-04-24'); CREATE TABLE original_partitioned_table_20250424 PARTITION OF public.original_partitioned_table FOR VALUES FROM ('2025-04-24') TO ('2025-04-25'); CREATE TABLE original_partitioned_table_20250425 PARTITION OF public.original_partitioned_table FOR VALUES FROM ('2025-04-25') TO ('2025-04-26'); CREATE TABLE original_partitioned_table_20250426 PARTITION OF public.original_partitioned_table FOR VALUES FROM ('2025-04-26') TO ('2025-04-27'); -- setting partitioned table by pg_partman SELECT partman.create_parent( p_parent_table := 'public.original_partitioned_table' -- 父表 , p_control := 'created_at' -- Partition Key , p_interval := '1 day' -- 每個分區的時間長度 , p_type := 'range' -- 支援 range/list partition -- , p_epoch := 'none' -- 若 p_control 是 integer/bigint 欄位，透過此參數設定該數字所代表的意義 (seconds/milliseconds/microseconds/nanoseconds) , p_premake := 7 -- 領先於當前時間的分區數 , p_start_partition := '2024-04-27'::varchar -- 起始分區的時間/數值 , p_default_table := true -- 是否建立 Default Partition , p_automatic_maintenance := 'on' -- 是否啟動自動維護 -- , p_constraint_cols := NULL -- 約束條件 -- , p_template_table := NULL -- 模板表 , p_jobmon := false -- 是否透過 pg_jobmon 監控 pg_partman 的工作 (我沒安裝故設定 false) -- , p_date_trunc_interval := NULL -- Partition 時間截斷方式 , p_control_not_null := true -- 是否 partition key 不能為 NULL -- , p_time_encoder := NULL -- 若 Partition Key 是非標準的時間格式或非時間類型，則需在此參數提供將 Partition Key 轉換為時間格式的函數 -- , p_time_decoder := NULL -- 若 Partition Key 是非標準的時間格式或非時間類型，則需在此參數提供將時間格式轉換為 Partition Key 的函數 ); ``` ![image](https://hackmd.io/_uploads/HygOlGc1lx.png) ### 分區維護 從 log 可以看到 pg_partman background worker 每日呼叫 run_maintenance_proc() 的紀錄。 從 part_config 表的 maintenance_last_run 欄位也可看到各表上次被維護的時間。 ![image](https://hackmd.io/_uploads/BJGrWM91gg.png) run_maintenance_proc() 會依據該資料時間點加上 pre_make 設定時長來判斷是否有需要新增分區。也就是說若表內資料的時間長期停滯在某一時間點，pg_partman 會判斷不需要新增分區。 >[!Note] **Q: 這機制聽起來很有風險？如果超過 pre_make 所設定的時間都沒有新增資料，會造成我下一次 INSERT 資料失敗？** 其實不會，因為有設定 default 分區。此情況資料會進到 default 分區、下次 background worker 運作就會新增分區了。 若希望無論是否有資料都要建立分區，則到 part_config 將 infinite_time_partitions 選項開啟即可。 ### 逾期資料清理 pg_partman 的 create_parent() 目前沒有提供 retention 參數的設定介面，故需要 UPDATE part_config 做設定。 part_config 表中和 retention 有關的欄位如下: | 欄位名稱 | 用途 | 範例值 | |---------|------|--------| | retention | 定義資料保存時長，需是一個可轉換為 interval 或 bigint 的值 | '30 days' | | retention_schema | 會覆蓋 retention_keep_table 的設定，若有設定則會將逾期分區搬移到 retention_schema 的 schema 下而不會刪除 | NULL | | retention_keep_index | 分離逾期分區時是否要刪除該分區的 index | true | | retention_keep_table | 逾期資料是否要保留，設定 true 則只是 DETACH 但不刪除 | true | 1. 建立 Partitoned Table，設定從一個月前開始建立分區。 ```sql CREATE TABLE public.test_retention ( id uuid default gen_random_uuid(), content varchar not null, created_at timestamp with time zone default now(), primary key (id, created_at) ) PARTITION BY RANGE (created_at); SELECT partman.create_parent( p_parent_table := 'public.test_retention' -- 父表 , p_control := 'created_at' -- Partition Key , p_interval := '1 day' -- 每個分區的時間長度 , p_type := 'range' -- 支援 range/list partition , p_premake := 7 -- 領先於當前時間的分區數 , p_start_partition := (current_date - interval '1 month')::varchar -- 起始分區的時間/數值 , p_default_table := true -- 是否建立 Default Partition , p_automatic_maintenance := 'on' -- 是否啟動自動維護 , p_jobmon := false -- 是否透過 pg_jobmon 監控 pg_partman 的工作 (我沒安裝故設定 false) , p_control_not_null := true -- 是否 partition key 不能為 NULL ); ``` ![image](https://hackmd.io/_uploads/SkGb5Mcyxe.png) 2. 設定 Retention 策略。 ```sql UPDATE partman.part_config SET retention = '7 days' WHERE parent_table = 'public.test_retention'; SELECT retention, retention_schema, retention_keep_index, retention_keep_table, * FROM partman.part_config WHERE parent_table = 'public.test_retention'; ``` ![image](https://hackmd.io/_uploads/H1mc9fqJgg.png) 3. 手動觸發 run_maintence_proc()。 <center class="half"> <img src="https://hackmd.io/_uploads/S1k6qf5kxl.png" width="300"/> </center> 4. 確認資料表下不再有舊分區。 <center class="half"> <img src="https://hackmd.io/_uploads/HkaZoz9Jge.png" width="200"/> </center> 5. 由於 retention_keep_table 維持預設值 true，故這些舊分區只是被 DETACH 後作為一般表留存，而未被刪除。(有需要可以將這些表 ATTACH 到另一張歷史資料備份的父表，不過 pg_partman 的 retention 功能目前沒有這種機制，需要自行建立機制) <center class="half"> <img src="https://hackmd.io/_uploads/SkNPVvjkgl.png" width="200"/> </center> ### 刪除透過 pg_partman 維護的資料表 Drop Table 後還要將 part_config 中該表的設定資料刪除，也建議清理掉該表的 Templete Table。 ![image](https://hackmd.io/_uploads/rJEl2zcJgg.png)