：pg_partman（分區管理器）

步驟一：安裝外掛程式並建立父表

首先，在您的資料庫中安裝pg_partman外掛程式，然後建立一個用於分區的父表。

-- 1. 在當前資料庫中啟用 pg_partman 擴充
CREATE EXTENSION pg_partman;

-- 2. 建立一個父表，用於儲存日誌資料
-- PARTITION BY RANGE (created_at) 指定了該表將作為分區表，並使用 created_at 列進行定界分割
CREATE TABLE public.log_table (
    id bigint GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL,
    message text
) PARTITION BY RANGE (created_at);

步驟二：初始化分區集

調用create_parent()函數，將log_table註冊到pg_partman的管理體系中，並定義分區規則。

-- 調用 create_parent 函數初始化分區
-- public.log_table: 要管理的父表
-- created_at: 分區鍵列
-- '1 day': 分區間隔，表示每天建立一個新分區
SELECT create_parent(
    p_parent_table := 'public.log_table',
    p_control := 'created_at',
    p_interval := '1 day'
);

執行成功後，pg_partman會自動建立當前及未來幾天的分區。您可以通過\d+ public.log_table命令查看已建立的分區。

步驟三：插入測試資料

向父表log_table中插入一些資料，資料會根據created_at的值自動路由到對應的分區中。

INSERT INTO public.log_table (created_at, message)
SELECT generate_series(
     now() - interval '4 days',
     now(),
     interval '1 hour'
 ),
 'This is a log message.';

步驟四：配置保留原則並驗證清理

設定資料保留原則，讓pg_partman自動清理到期資料。將配置log_table只保留最近2天的資料，並自動刪除（DROP）到期的分區。

-- 1. 更新 part_config 表，為 log_table 設定保留原則
UPDATE part_config
SET
    retention = '2 days',             -- 保留周期為2天
    retention_keep_table = false      -- 不保留表結構，直接 DROP 到期的分區
WHERE parent_table = 'public.log_table';

-- 2. 手動執行維護任務，觸發保留原則
CALL run_maintenance_proc();

-- 3. 驗證：再次檢查分區列表，確認2天前的分區已被刪除
SELECT partition_schemaname, partition_tablename FROM show_partitions('public.log_table', 'DESC');

至此，您已成功配置了一個按天分區並自動清理的日誌表。

情境一：按天分區的日誌表（含自動清理）

此情境適用於日誌、審計記錄、物聯網（IoT）時序資料等增長迅速且有明確生命週期的資料。

業務情境

按天對audit_logs表進行分區，資料保留30天，到期資料自動歸檔到另一個模式，而不是直接刪除。

操作步驟

1. 建立父表和初始化分區集：

   -- 建立父表
   CREATE TABLE public.audit_logs (
       log_id uuid NOT NULL,
       event_time timestamptz NOT NULL,
       user_id text,
       details jsonb,
       PRIMARY KEY (event_time, log_id)
   ) PARTITION BY RANGE (event_time);
   
   -- 初始化分區集，按天分區
   SELECT create_parent(
       p_parent_table := 'public.audit_logs',
       p_control := 'event_time',
       p_interval := '1 day'
   );

2. 配置保留與歸檔策略：將超過30天的舊分區表移動到archive模式下，以便後續進行離線分析或備份。

   -- 建立用于歸檔的模式
   CREATE SCHEMA IF NOT EXISTS archive;
   
   -- 更新配置，設定保留原則
   UPDATE part_config
   SET
       retention = '30 days',          -- 保留30天
       retention_schema = 'archive'    -- 到期後移動到 archive 模式
   WHERE parent_table = 'public.audit_logs';

   當run_maintenance()運行時，任何完全早於30天前的分區表（例如audit_logs_p2023_10_01）將被從主要磁碟分割集中分離，並將其模式從public更改為archive，變為archive.audit_logs_p2023_10_01。

情境二：按ID分區的業務表

此情境適用於使用者表、商品表等以數字ID為主鍵，並希望通過分區將冷熱資料分離的業務。

業務情境

對orders表按order_id進行分區，每個分區包含1000萬個ID。

決策建議

p_interval的選擇至關重要。應根據業務ID的增長速率和單個分區的合理大小來估算。如果每天新增100萬訂單，10000000的間隔意味著大約每10天建立一個新分區。

操作步驟

1. 建立父表和初始化分區集：

   -- 建立父表
   CREATE TABLE public.orders (
       order_id bigint NOT NULL,
       customer_id bigint,
       order_date timestamptz,
       amount numeric
   ) PARTITION BY RANGE (order_id);
   
   -- 初始化分區集，按ID定界分割
   -- 注意：p_interval 即使是數字，也必須以文本形式提供
   SELECT create_parent(
       p_parent_table := 'public.orders',
       p_control := 'order_id',
       p_interval := '10000000' -- 每個分區包含1000萬個ID
   );

2. 配置保留原則（可選）：如果舊訂單資料可以被歸檔或刪除，可以配置基於ID的保留原則。

   -- 保留最近的5000萬個訂單資料
   -- 假設當前最大 order_id 是 100,000,000
   -- 該策略會刪除 order_id < 50,000,000 的所有分區
   UPDATE part_config
   SET
       retention = '50000000',
       retention_keep_table = false
   WHERE parent_table = 'public.orders';

情境三：遷移現有大表到分區表

此情境適用於將一個已存在的、資料量巨大的單體表（如TB層級）平滑遷移到pg_partman管理的分區結構。

業務情境

將1 TB大小的sensor_data表遷移為按月分區的分區表，並最小化對線上業務的影響。

核心工具

partition_data_proc()過程。它能以小批量、獨立事務的方式遷移資料，避免了長時間的表鎖和事務積壓。

操作步驟

1. 準備工作：建立父表並初始化分區集：

   -- 1. 將原表重新命名，作為資料來源
   ALTER TABLE public.sensor_data RENAME TO sensor_data_source;
   
   -- 2. 建立一個結構相同的新父表
   CREATE TABLE public.sensor_data (
       reading_id bigserial,
       device_id text,
       ts timestamptz NOT NULL,
       value double precision
   ) PARTITION BY RANGE (ts);
   
   -- 3. 初始化分區集
   SELECT create_parent('public.sensor_data','ts','1 month');

2. 執行資料移轉：使用partition_data_proc將sensor_data_source的資料分批遷移到新的分區表sensor_data中。

   -- 調用資料移轉過程
   -- p_source_table: 指定資料來源表
   -- p_interval: 每個批次處理的資料時間範圍，建議小於或等於分區間隔。此處設為'1 day'，表示每天的資料在一個事務中處理。
   -- p_wait: 每個批次之間的等待時間（秒），用於降低對I/O和CPU的持續壓力。
   CALL partition_data_proc(
       p_parent_table := 'public.sensor_data',
       p_source_table := 'public.sensor_data_source',
       p_interval := '1 day',
       p_wait := 1
   );

   該過程會迴圈執行，直到sensor_data_source表中的所有資料都被遷移完畢。遷移過程中，pg_partman會按需自動建立所需的分區。

3. 收尾工作：遷移完成後，驗證資料一致性，然後可以安全地刪除原資料來源表sensor_data_source。在遷移期間，應用可以繼續向新的分區父表sensor_data寫入新資料。

分區類型與間隔

分區類型

pg_partman基於PostgreSQL的聲明式分區，主要支援以下分區方式：

• 定界分割（Range Partitioning）：適用於連續的資料，如時間戳記或序列ID。這是最常用的分區類型。

• 列表分區（List Partitioning）：僅在基於數位ID分區且間隔為1時支援。

分區間隔（p_interval）

此參數定義了每個子分區所覆蓋的資料範圍。

• 基於時間：接受任何有效interval值，如'1 hour'、'1 day'、'1 week'或'1 month'。

  說明

  • 支援的最小時間間隔為1秒，上限則受限於PostgreSQL所支援的最小和最大時間戳記值。

  • 首次運行create_parent()建立分區集時，在確定要建立的第一個分區時，小於一天的間隔會向下取整。

    • 小於24小時但大於1分鐘的間隔將向下取整到最近的小時。

    • 小於1分鐘的間隔則向下取整到最近的分鐘。

    • 系統會建立足夠多的分區以支援到當前真即時間。這意味著在運行create_parent()時，可能會建立比預期更多的先前分區，而未來分區可能不會全部建立。首次運行run_maintenance()時會修複缺失的未來分區。這是由於需要支援自訂時間間隔所導致的。對於大於或等於24小時的間隔，設定將會如預期一樣進行。

  • 對於等於或大於100年的間隔，外掛程式將使用世紀或千年的實際起始點來確定分區名稱和約束規則。例如，21世紀和第3個千年是從2001年01月01日開始的（不是2000年），這也意味著沒有0年。

• 基於數字/ID：接受一個表示範圍大小的整數，但需以文本形式提供，如'1000000'。

子分區（Sub-partitioning）

子分區允許在已有的分區上建立更細粒度的分區，形成多級分區結構（如按年分區，每年再按月子分區）。其主要用於資料群組織和生命週期管理，例如對一個巨大的年分區進行按月歸檔。對於效能提升，子分區通常效果有限，優先考慮調整頂層分區的間隔。

資料保留原則（Retention）

pg_partman通過run_maintenance()自動執行資料保留原則，管理舊分區的生命週期。核心配置參數如下：

約束排除（Constraint Exclusion）

約束排除是分區表提升查詢效能的關鍵特性。當查詢的WHERE條件可以明確排除某些分區時，查詢最佳化工具會跳過對這些分區的掃描。

pg_partman提供了在非分區鍵列上添加CHECK約束的功能，以增強約束排除的效果。例如，一個按created_at分區的表，如果經常有WHERE device_id = 'A'的查詢，可以為舊分區（資料不再變化）的device_id列添加約束。

核心配置參數如下：

• constraint_cols：一個文本數組，指定要在哪些列上建立CHECK約束。例如'{device_id, user_id}'。

• optimize_constraint：一個整數，定義對多老的分區應用約束。預設值30表示對30個分區間隔之前的舊分區應用約束。

• constraint_valid：在表上添加約束可能會與表中已有的資料產生衝突，也可能導致pg_partman的維護操作耗時較長。可以設定這些約束在建立時是否為不生效。雖然這樣可以讓約束的建立幾乎瞬間完成，但在驗證之前無法使用約束排除。因此，預設情況下約束會被建立為有效（valid）。

模板表與屬性繼承

由於PostgreSQL聲明式分區對某些對象屬性（如非分區鍵上的主鍵/唯一索引、autovacuum設定等）的繼承支援不完善，pg_partman使用模板表機制來確保新建立的分區能正確繼承這些屬性。下表展示了pg_partman是如何管理特定屬性繼承的。如果某個屬性未在表中列出，則表示它通過父表進行管理。

• 屬性：當您調用create_parent()時，pg_partman會自動建立一個名為parent_table_template的表。您對此模板表所做的任何ALTER TABLE修改（如添加索引、更改autovacuum參數）都會在未來建立新分區時被應用。

  說明

  對模板表的修改不會追溯應用於已存在的分區。如需修改，需要手動對舊分區執行 ALTER 操作。

• 許可權和所有權：預設情況下，許可權和所有權不會被繼承。如果通過pg_partman啟用了該功能，請注意這種繼承僅發生在分區建立時，更改後不會自動應用（可通過reapply_privileges()重新應用）。除非你需要直接存取分區，否則通常不需要啟用此功能。如果你需要，可以設定inherit_privileges選項。

  說明

  如果您使用IDENTITY特性來管理序列，則只有在通過父表插入資料時，該特性才支援自動產生新的序列值；而直接向分區插入資料時，該特性不予支援。

自動維護

pg_partman的核心維護任務（建立新分區和執行保留原則）由run_maintenance()函數或run_maintenance_proc()過程執行。在PolarDB PostgreSQL版（相容Oracle）中，推薦使用內建的後台背景工作處理序（Background Worker）來自動化此過程，它會根據您設定的間隔自動調用run_maintenance_proc()。

手動維護

在某些情況下，您可能需要手動觸發維護，例如在調試或需要對特定表進行精細控制時。

• CALL run_maintenance_proc(); 推薦使用此過程。它會為part_config中所有automatic_maintenance為on的分區集執行維護，並在每個分區集維護完成後自動認可事務，減少鎖爭用。

• SELECT run_maintenance('public.my_table');如果只想對單個分區集執行維護，可以調用此函數並傳入父表名。

監控與警示

在生產環境中，需監控分區維護是否正常運行，以防因分區未及時建立而導致資料寫入失敗。part_config表中的maintenance_last_run欄位記錄了每個分區集最後一次成功運行維護的時間戳記。這是一個關鍵的監控指標。

-- 查詢所有自動維護的分區集中，超過2倍維護間隔仍未成功啟動並執行
SELECT parent_table
FROM part_config
WHERE
    automatic_maintenance = 'on'
    AND maintenance_last_run < (now() - (SELECT setting::interval * 2 FROM pg_settings WHERE name = 'pg_partman_bgw.interval'));

如果此查詢返回任何行，意味著對應的分區集維護可能已中止，需要立即介入排查。

建立分區

此類函數用於初始化分區集和定義子分區。

create_parent(
    p_parent_table text
    , p_control text
    , p_interval text
    , p_type text DEFAULT 'range'
    , p_epoch text DEFAULT 'none'
    , p_premake int DEFAULT 4
    , p_start_partition text DEFAULT NULL
    , p_default_table boolean DEFAULT true
    , p_automatic_maintenance text DEFAULT 'on'
    , p_constraint_cols text[] DEFAULT NULL
    , p_template_table text DEFAULT NULL
    , p_jobmon boolean DEFAULT true
    , p_date_trunc_interval text DEFAULT NULL
    , p_control_not_null boolean DEFAULT true
    , p_time_encoder text DEFAULT NULL
    , p_time_decoder text DEFAULT NULL
)
RETURNS boolean

create_sub_parent(
    p_top_parent text
    , p_control text
    , p_interval text
    , p_type text DEFAULT 'range'
    , p_default_table boolean DEFAULT true
    , p_declarative_check text DEFAULT NULL
    , p_constraint_cols text[] DEFAULT NULL
    , p_premake int DEFAULT 4
    , p_start_partition text DEFAULT NULL
    , p_epoch text DEFAULT 'none'
    , p_jobmon boolean DEFAULT true
    , p_date_trunc_interval text DEFAULT NULL
    , p_control_not_null boolean DEFAULT true
    , p_time_encoder text DEFAULT NULL
    , p_time_decoder text DEFAULT NULL
)
RETURNS boolean

create_partition_time(
    p_parent_table text
    , p_partition_times timestamptz[]
    , p_start_partition text DEFAULT NULL
)
RETURNS boolean

create_partition_id(
    p_parent_table text
    , p_partition_ids bigint[]
    , p_start_partition text DEFAULT NULL
)
RETURNS boolean

遷移資料

此類函數用於將存量資料或誤入預設分區的資料移轉到正確的分區中。

partition_data_time(
    p_parent_table text
    , p_batch_count int DEFAULT 1
    , p_batch_interval interval DEFAULT NULL
    , p_lock_wait numeric DEFAULT 0
    , p_order text DEFAULT 'ASC'
    , p_analyze boolean DEFAULT true
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
)
RETURNS bigint

partition_data_id(
    p_parent_table text
    , p_batch_count int DEFAULT 1
    , p_batch_interval bigint DEFAULT NULL
    , p_lock_wait numeric DEFAULT 0
    , p_order text DEFAULT 'ASC'
    , p_analyze boolean DEFAULT true
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
)
RETURNS bigint

partition_data_proc (
    p_parent_table text
    , p_loop_count int DEFAULT NULL
    , p_interval text DEFAULT NULL
    , p_lock_wait int DEFAULT 0
    , p_lock_wait_tries int DEFAULT 10
    , p_wait int DEFAULT 1
    , p_order text DEFAULT 'ASC'
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
    , p_quiet boolean DEFAULT false
)

維護分區

此類函數用於日常的分區管理、監控和資訊查詢。

run_maintenance(
    p_parent_table text DEFAULT NULL
    , p_analyze boolean DEFAULT false
)
RETURNS void

run_maintenance_proc(
    p_wait int DEFAULT 0
    , p_analyze boolean DEFAULT NULL
)

check_default(
    p_exact_count boolean DEFAULT true
)

show_partitions(
    p_parent_table text
    , p_order text DEFAULT 'ASC'
    , p_include_default boolean DEFAULT false
)
RETURNS TABLE (
    partition_schemaname text
    , partition_tablename text
)

show_partition_name(
    p_parent_table text
    , p_value text
    , OUT partition_schema text
    , OUT partition_table text
    , OUT suffix_timestamp timestamptz
    , OUT suffix_id bigint
    , OUT table_exists boolean
)
RETURNS record

show_partition_info(
    p_child_table text
    , p_partition_interval text DEFAULT NULL
    , p_parent_table text DEFAULT NULL
    , OUT child_start_time timestamptz
    , OUT child_end_time timestamptz
    , OUT child_start_id bigint
    , OUT child_end_id bigint
    , OUT suffix text
)
RETURNS record

銷毀分區

此類函數用於撤銷分區結構或根據保留原則刪除舊分區。

undo_partition(
    p_parent_table text
    , p_target_table text
    , p_loop_count int DEFAULT 1
    , p_batch_interval text DEFAULT NULL
    , p_keep_table boolean DEFAULT true
    , p_lock_wait numeric DEFAULT 0
    , p_ignored_columns text[] DEFAULT NULL
    , p_drop_cascade boolean DEFAULT false
    , OUT partitions_undone int
    , OUT rows_undone bigint)
RETURNS record

undo_partition_proc(
    p_parent_table text
    , p_target_table text DEFAULT NULL
    , p_loop_count int DEFAULT NULL
    , p_interval text DEFAULT NULL
    , p_keep_table boolean DEFAULT true
    , p_lock_wait int DEFAULT 0
    , p_lock_wait_tries int DEFAULT 10
    , p_wait int DEFAULT 1
    , p_ignored_columns text[] DEFAULT NULL
    , p_drop_cascade boolean DEFAULT false
    , p_quiet boolean DEFAULT false
)

drop_partition_time(
    p_parent_table text
    , p_retention interval DEFAULT NULL
    , p_keep_table boolean DEFAULT NULL
    , p_keep_index boolean DEFAULT NULL
    , p_retention_schema text DEFAULT NULL
    , p_reference_timestamp timestamptz DEFAULT CURRENT_TIMESTAMP
)
RETURNS int

drop_partition_id(
    p_parent_table text
    , p_retention bigint DEFAULT NULL
    , p_keep_table boolean DEFAULT NULL
    , p_keep_index boolean DEFAULT NULL
    , p_retention_schema text DEFAULT NULL
)
RETURNS int

part_config

此表是pg_partman的主配置中心，儲存所有頂級父表的分區策略和維護設定。

part_config_sub

此表用於儲存子分區的配置。其結構與part_config基本一致，但有一個關鍵區別：它使用sub_parent列來標識其所屬的上一級父表。

• sub_parent（text）：子分區集的父表名，該表本身是頂級父表的一個分區。

• 其他列：表中所有其他的列（如partition_interval、retention、premake等）的含義與part_config中完全相同，但其範圍是當前子分區集。

您無需在此重複查看所有列的定義。只需理解，當pg_partman維護一個子分區集時，它會從part_config_sub表中尋找以sub_parent為鍵的配置行，並應用該行中定義的策略。