分區管理：PostgreSQL 宣告式分割表的完整維運指南 | PostgreSQL

2026/07/03
postgresql partitioning pg-partman performance
分區管理：PostgreSQL 宣告式分割表的完整維運指南 | PostgreSQL

分割表（Partitioned Table） 是 PostgreSQL 處理大規模資料集的核心手段。自 PG10 引入宣告式分割（Declarative Partitioning）以來，透過 RANGE、LIST、HASH 三種策略將大表拆分為多個小分割，搭配 Partition Pruning 讓查詢只掃描必要的分割，是兼顧效能與維護性的最佳方案。本篇涵蓋分割表從建立、日常管理、pg_partman 自動化到大表線上遷移的完整維運指南。

分割表架構概覽

分割表的核心概念：

  • 父表（Parent Table）：定義分割鍵（Partition Key）與分割策略，本身不儲存資料
  • 子分割（Child Partition）：實際儲存資料的表，繼承父表結構
  • 分割策略：RANGE（時間/數值範圍）、LIST（離散值）、HASH（雜湊均散）
  • 多層分割（Sub-partitioning）：子分割可再進一步分割
-- 典型的時間範圍分割表
CREATE TABLE orders (
    order_id    BIGINT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL,
    customer_id BIGINT,
    amount      NUMERIC(12,2)
) PARTITION BY RANGE (created_at);

-- 建立季度分割
CREATE TABLE orders_2026q1
    PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

三種分割策略

RANGE 分割

最常見，適合時序資料（日誌、訂單、事件）：

-- 月份分割
CREATE TABLE orders_2026_06
    PARTITION OF orders
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- 可指定儲存參數和表空間
CREATE TABLE orders_2026_07
    PARTITION OF orders
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01')
    WITH (fillfactor = 90)
    TABLESPACE fast_ssd;

LIST 分割

適合地區、類別等離散值：

CREATE TABLE orders_by_region (
    order_id BIGINT, region TEXT, amount NUMERIC
) PARTITION BY LIST (region);

CREATE TABLE orders_asia
    PARTITION OF orders_by_region
    FOR VALUES IN ('TW', 'JP', 'KR', 'SG', 'HK');

CREATE TABLE orders_europe
    PARTITION OF orders_by_region
    FOR VALUES IN ('DE', 'FR', 'GB', 'IT', 'ES');

HASH 分割

適合沒有明確範圍、需要均等分散的場景：

CREATE TABLE orders_by_hash (
    order_id BIGINT, data JSONB
) PARTITION BY HASH (order_id);

CREATE TABLE orders_hash_0
    PARTITION OF orders_by_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_hash_1
    PARTITION OF orders_by_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- ... 以此類推

Partition 生命週期管理

ATTACH PARTITION（附加分割）

將現有資料表附加為分割，常用於大量資料的線上載入：

-- 1. 建立獨立表並載入資料
CREATE TABLE orders_2026_07_staging (LIKE orders INCLUDING ALL);
\COPY orders_2026_07_staging FROM '/data/orders_2026_07.csv' CSV HEADER;

-- 2. 建立約束以加速 ATTACH 驗證（PostgreSQL 跳過逐行驗證）
ALTER TABLE orders_2026_07_staging
    ADD CONSTRAINT orders_2026_07_check
    CHECK (created_at >= '2026-07-01' AND created_at < '2026-08-01')
    NOT VALID;

-- 3. 驗證約束（低峰期執行，不鎖表）
ALTER TABLE orders_2026_07_staging
    VALIDATE CONSTRAINT orders_2026_07_check;

-- 4. 附加為分割（有 NOT VALID 約束時，ATTACH 更快）
ALTER TABLE orders
    ATTACH PARTITION orders_2026_07_staging
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

DETACH PARTITION（卸載分割）

卸載分割用於歸檔或維護，不刪除資料：

-- 傳統卸載（需要短暫的 ACCESS EXCLUSIVE 鎖）
ALTER TABLE orders DETACH PARTITION orders_2024_01;

-- CONCURRENTLY 選項（PG14+）：不阻塞讀寫
ALTER TABLE orders DETACH PARTITION orders_2024_01 CONCURRENTLY;

-- 卸載後的歸檔選項
-- 選項一：移至歸檔 Schema
ALTER TABLE orders_2024_01 SET SCHEMA archive;

-- 選項二：匯出後刪除
\COPY orders_2024_01 TO '/archive/orders_2024_01.csv' CSV HEADER;
DROP TABLE orders_2024_01;

-- 選項三：移至冷儲存表空間
ALTER TABLE orders_2024_01 SET TABLESPACE cold_storage;

DETACH CONCURRENTLY 的運作機制：先在系統目錄標記「即將卸載」→ 等待所有現有交易完成 → 完成卸載，全程不持有長時間排他鎖。

Partition Pruning

Partition Pruning（分割修剪）是查詢規劃器排除不相關分割的最佳化機制，是分割表效能收益的核心來源。

靜態修剪 vs 動態修剪

-- 靜態修剪：規劃階段完成（常數條件）
SELECT * FROM orders WHERE created_at = '2026-02-15';
-- → 只掃描 orders_2026q1

-- 動態修剪（PG11+）：執行階段生效（參數化查詢）
PREPARE get_orders(TIMESTAMPTZ) AS
    SELECT * FROM orders WHERE created_at = $1;

驗證 Pruning 效果

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE created_at BETWEEN '2026-05-01' AND '2026-05-31';

-- 理想的執行計畫：
-- Append
--   -> Seq Scan on orders_2026_05
-- 不應出現其他月份的分割

Pruning 失效的常見原因

-- 錯誤：分割鍵被函數包裝 → 無法 Pruning
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2026;

-- 正確：使用範圍條件
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- 錯誤：date_trunc 也會導致失效
SELECT * FROM orders WHERE date_trunc('month', created_at) = '2026-05-01';

-- 正確：改為範圍查詢
SELECT * FROM orders
WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01';

pg_partman 自動管理

pg_partman 是 PostgreSQL 最廣泛使用的分割表自動管理擴充套件：

安裝與初始化

CREATE EXTENSION pg_partman SCHEMA partman;

-- 按月分割
SELECT partman.create_parent(
    p_parent_table    => 'public.orders',
    p_control         => 'created_at',
    p_type            => 'range',
    p_interval        => 'monthly',
    p_premake         => 4,                -- 預建未來 4 個分割
    p_start_partition => '2026-01-01'
);

-- 按天分割的高寫入量日誌表
SELECT partman.create_parent(
    p_parent_table         => 'public.app_logs',
    p_control              => 'logged_at',
    p_type                 => 'range',
    p_interval             => 'daily',
    p_premake              => 7,           -- 預建 7 天
    p_retention            => '90 days',   -- 保留 90 天
    p_retention_keep_table => false        -- 超過期限直接 DROP
);

排程維護

-- 手動觸發維護（測試用）
SELECT partman.run_maintenance('public.orders');

-- 搭配 pg_cron 自動排程
SELECT cron.schedule(
    'partman-maintenance',
    '0 * * * *',  -- 每小時執行
    $$SELECT partman.run_maintenance(p_analyze := false)$$
);

-- 每天凌晨清理過期分割
SELECT cron.schedule(
    'partman-cleanup',
    '0 2 * * *',
    $$SELECT partman.drop_partition_list('public.orders')$$
);

postgresql.conf 設定（Background Worker 方式）

shared_preload_libraries = 'pg_partman_bgw'
pg_partman_bgw.interval = 3600     -- 每小時執行
pg_partman_bgw.role = 'partman'
pg_partman_bgw.dbname = 'production'

跨 Partition 索引

PG11+ 支援在父表建立分割索引，自動在所有子分割上建立對應的本地索引：

-- 在父表建立索引（自動套用到所有分割）
CREATE INDEX CONCURRENTLY idx_orders_customer
    ON orders (customer_id);

-- 部分索引
CREATE INDEX CONCURRENTLY idx_orders_high_amount
    ON orders (amount DESC)
    WHERE amount > 1000;

在附加現有表為分割前，預先建立索引可加速 ATTACH：

-- staging 表預先建立索引
CREATE INDEX idx_staging_customer
    ON orders_2026_07_staging (customer_id);

-- ATTACH 後，本地索引自動與父表的分割索引關聯
ALTER TABLE orders
    ATTACH PARTITION orders_2026_07_staging
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

唯一約束限制：分割表的唯一約束必須包含分割鍵：

-- 錯誤：不含分割鍵
ALTER TABLE orders ADD CONSTRAINT uq_id UNIQUE (order_id);
-- ERROR: unique constraint must include all partitioning columns

-- 正確：包含分割鍵
ALTER TABLE orders ADD CONSTRAINT uq_id UNIQUE (order_id, created_at);

Partition 與 Vacuum/ANALYZE

分割表的 Vacuum 和 ANALYZE 對每個分割獨立運作：

-- 手動 VACUUM 特定分割
VACUUM ANALYZE orders_2026_05;

-- 對父表執行會自動處理所有分割
VACUUM ANALYZE orders;

-- 為高寫入分割個別調整 autovacuum
ALTER TABLE orders_2026_05 SET (
    autovacuum_vacuum_scale_factor = 0.005
);

-- 為唯讀歷史分割關閉 autovacuum
ALTER TABLE orders_2024_01 SET (
    autovacuum_enabled = false
);

-- 監控各分割的 dead tuple 累積
SELECT tablename, n_dead_tup, n_live_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE tablename LIKE 'orders_%' AND n_dead_tup > 1000
ORDER BY dead_pct DESC;

大表線上遷移

將現有大表遷移至分割表是高風險任務，以下是近乎零停機的線上遷移方案：

遷移流程：

  1. 建立分割表結構（不含資料）
     │
  2. 建立同步 Trigger → 新寫入同步到分割表
     │
  3. 批次遷移歷史資料（分批 INSERT，避免長事務）
     │
  4. 驗證資料完整性（count 比對）
     │
  5. 表名交換（需短暫停機窗口）
     │  BEGIN;
     │  ALTER TABLE orders RENAME TO orders_legacy;
     │  ALTER TABLE orders_partitioned RENAME TO orders;
     │  DROP TRIGGER sync_trigger ON orders_legacy;
     │  COMMIT;
     │
  6. 確認無誤後刪除舊表

批次遷移的關鍵是避免長事務：

DO $$
DECLARE
    batch_size INT := 10000;
    offset_val BIGINT := 0;
    rows_moved INT;
BEGIN
    LOOP
        INSERT INTO orders_partitioned
        SELECT * FROM orders
        ORDER BY order_id
        LIMIT batch_size OFFSET offset_val
        ON CONFLICT DO NOTHING;

        GET DIAGNOSTICS rows_moved = ROW_COUNT;
        EXIT WHEN rows_moved = 0;

        offset_val := offset_val + batch_size;
        RAISE NOTICE '已遷移 % 筆', offset_val;
        PERFORM pg_sleep(0.1);  -- 讓出 CPU，避免影響線上業務
    END LOOP;
END $$;

多層分割（Sub-partitioning）

當分割數量過多時（建議不超過 1000 個），使用多層分割減少單層數量：

-- 先按年分割
CREATE TABLE orders_2026
    PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')
    PARTITION BY RANGE (created_at);

-- 再按月分割
CREATE TABLE orders_2026_01
    PARTITION OF orders_2026
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

效能監控

-- 各分割資料量分佈
SELECT
    child.relname AS partition_name,
    pg_size_pretty(pg_total_relation_size(child.oid)) AS total_size,
    psut.n_live_tup AS live_rows,
    psut.last_autovacuum
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
LEFT JOIN pg_stat_user_tables psut ON psut.relid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

-- 分割邊界查詢
SELECT
    c.relname AS partition_name,
    pg_get_expr(c.relpartbound, c.oid) AS partition_bound
FROM pg_class p
JOIN pg_inherits i ON i.inhparent = p.oid
JOIN pg_class c    ON c.oid = i.inhrelid
WHERE p.relname = 'orders'
ORDER BY c.relname;

-- 熱點分割識別
SELECT relname,
    n_tup_ins + n_tup_upd + n_tup_del AS total_writes
FROM pg_stat_user_tables
WHERE relname LIKE 'orders_%'
ORDER BY total_writes DESC LIMIT 5;

版本演進

版本重要改進
PG 10引入宣告式分割（RANGE、LIST）
PG 11HASH 分割、分割索引、外鍵支援、動態 Pruning
PG 12大量分割時的規劃效能大幅提升
PG 13分割表邏輯複寫（Logical Replication）支援
PG 14DETACH PARTITION CONCURRENTLY、Pruning 效能改善
PG 15MERGE 語句改善
PG 16進一步的分割修剪效能最佳化

常見陷阱

忘記提前建立分割

分割表不接受不符合範圍的資料，未建立分割時 INSERT 直接報錯。務必使用 pg_partman 或 pg_cron 自動預建。

唯一約束必須包含分割鍵

這是 PostgreSQL 分割表的硬限制，設計 Schema 時必須考慮。

大量分割導致規劃時間增加

每個分割都會增加 Planner 的計算成本。建議單一分割表不超過 1000 個分割，超過時改用多層分割。

外鍵效能考量

PG11+ 支援外鍵指向分割表，但效能開銷較大，需謹慎評估。

總結

分割表維運管理 是處理大規模資料集的核心 DBA 技能：

  • 三種策略：RANGE（時序資料）、LIST（離散分類）、HASH（均等分散）
  • 生命週期：CREATE → ATTACH（載入）→ 使用 → DETACH（歸檔）→ DROP（清理）
  • Partition Pruning 是效能收益的核心，WHERE 條件必須直接包含分割鍵
  • pg_partman 自動化預建與清理，搭配 pg_cron 或 BGW 排程
  • 索引策略：父表建立分割索引自動套用到所有分割，唯一約束必須包含分割鍵
  • 大表遷移：觸發器同步 + 批次遷移 + 表名交換，實現近乎零停機
  • 分割數量控制在 1000 以內，超過時使用多層分割

下一篇，我們將深入探討 安全與權限管理——掌握 PostgreSQL 的角色系統、權限體系、Row Level Security 與加密機制。

BenZ Software Developer

熱愛技術的軟體開發者，在這裡分享程式開發經驗與學習筆記。