跳至主要內容
日沐 SunBathe

Appearance

資料表分區 ​

當表資料量達到數億筆時，分區是提升效能的關鍵技術。本篇介紹 PostgreSQL 的分區策略與實作。

一、 為什麼需要分區？ ​

問題 ​

  • 大表查詢慢
  • 索引過大，維護成本高
  • VACUUM 耗時長
  • 備份困難

分區的好處 ​

  1. 查詢優化：Partition Pruning 只掃描相關分區
  2. 維護簡化：可以單獨 VACUUM 或刪除分區
  3. 歸檔方便：舊資料可以整個分區 DETACH
  4. 並行處理：不同分區可並行操作

二、 分區類型 ​

PostgreSQL 10+ 支援原生分區：

類型分區鍵使用場景
Range範圍時間序列資料
List列舉值地區、類別
Hash雜湊均勻分散資料

三、 Range 分區 ​

最常用，按範圍劃分。

建立分區表 ​

sql
-- 父表（不存實際資料）
CREATE TABLE orders (
    id BIGSERIAL,
    customer_id INTEGER,
    amount NUMERIC(10, 2),
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)  -- 分區鍵必須包含在主鍵中
) PARTITION BY RANGE (created_at);

-- 建立分區
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- 預設分區（接收不匹配的資料）
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

自動建立分區 ​

使用 pg_partman 擴充：

sql
CREATE EXTENSION pg_partman;

SELECT create_parent(
    p_parent_table => 'public.orders',
    p_control => 'created_at',
    p_type => 'native',
    p_interval => 'monthly',
    p_premake => 3  -- 預先建立 3 個月
);

四、 List 分區 ​

按列舉值劃分。

sql
CREATE TABLE sales (
    id BIGSERIAL,
    region TEXT NOT NULL,
    amount NUMERIC,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE sales_asia PARTITION OF sales
    FOR VALUES IN ('TW', 'JP', 'KR', 'CN');

CREATE TABLE sales_europe PARTITION OF sales
    FOR VALUES IN ('UK', 'DE', 'FR');

CREATE TABLE sales_americas PARTITION OF sales
    FOR VALUES IN ('US', 'CA', 'BR');

CREATE TABLE sales_others PARTITION OF sales DEFAULT;

五、 Hash 分區 ​

均勻分散資料。

sql
CREATE TABLE logs (
    id BIGSERIAL,
    user_id INTEGER NOT NULL,
    action TEXT,
    created_at TIMESTAMPTZ,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);

-- 建立 4 個分區
CREATE TABLE logs_0 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE logs_1 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE logs_2 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE logs_3 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

六、 多層分區 ​

分區可以巢狀：

sql
-- 第一層：按年份
CREATE TABLE events (
    id BIGSERIAL,
    event_type TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, event_type, created_at)
) PARTITION BY RANGE (created_at);

-- 2024 年的分區，再按類型分
CREATE TABLE events_2024 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    PARTITION BY LIST (event_type);

CREATE TABLE events_2024_click PARTITION OF events_2024
    FOR VALUES IN ('click');
CREATE TABLE events_2024_view PARTITION OF events_2024
    FOR VALUES IN ('view');
CREATE TABLE events_2024_other PARTITION OF events_2024 DEFAULT;

七、 Partition Pruning ​

PostgreSQL 自動跳過不相關的分區：

sql
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';

輸出會顯示只掃描 orders_2024_02，其他分區被剪裁。

確保 Pruning 生效 ​

sql
-- 確認設定開啟
SHOW enable_partition_pruning;  -- 應該是 on

-- 條件必須直接涉及分區鍵
-- ✅ 可以 Prune
SELECT * FROM orders WHERE created_at >= '2024-02-01';

-- ❌ 無法 Prune（函數包裝）
SELECT * FROM orders WHERE DATE(created_at) = '2024-02-01';

八、 分區管理 ​

新增分區 ​

sql
CREATE TABLE orders_2024_04 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

卸載分區（保留資料） ​

sql
ALTER TABLE orders DETACH PARTITION orders_2023_01;

-- 現在 orders_2023_01 是獨立的表
-- 可以歸檔、備份或刪除

附加分區 ​

sql
-- 把已存在的表附加為分區
ALTER TABLE orders ATTACH PARTITION orders_2024_05
    FOR VALUES FROM ('2024-05-01') TO ('2024-06-01');

刪除分區 ​

sql
DROP TABLE orders_2023_01;  -- 連資料一起刪除

九、 索引策略 ​

分區表的索引 ​

在父表建立索引，PostgreSQL 會自動為每個分區建立：

sql
-- 在父表建立索引
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- 每個分區都會自動有對應的索引

唯一約束 ​

唯一約束必須包含分區鍵：

sql
-- ❌ 無法建立（email 不是分區鍵的一部分）
CREATE UNIQUE INDEX ON orders(customer_id);

-- ✅ 可以
CREATE UNIQUE INDEX ON orders(customer_id, created_at);

十、 實戰範例 ​

時序資料 ​

sql
-- 系統日誌，按天分區
CREATE TABLE system_logs (
    id BIGSERIAL,
    level TEXT,
    message TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 建立未來 7 天的分區
DO $$
DECLARE
    d DATE;
BEGIN
    FOR d IN SELECT generate_series(CURRENT_DATE, CURRENT_DATE + 7, '1 day')::DATE LOOP
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS system_logs_%s PARTITION OF system_logs FOR VALUES FROM (%L) TO (%L)',
            to_char(d, 'YYYYMMDD'),
            d,
            d + 1
        );
    END LOOP;
END $$;

定時清理舊分區 ​

sql
-- 刪除 30 天前的分區
DO $$
DECLARE
    p RECORD;
BEGIN
    FOR p IN
        SELECT inhrelid::regclass AS partition_name
        FROM pg_inherits
        WHERE inhparent = 'system_logs'::regclass
    LOOP
        -- 解析分區名稱中的日期，如果超過 30 天就刪除
        IF p.partition_name::TEXT ~ '_[0-9]{8}$' THEN
            IF SUBSTRING(p.partition_name::TEXT FROM '_([0-9]{8})$')::DATE < CURRENT_DATE - 30 THEN
                EXECUTE 'DROP TABLE ' || p.partition_name;
            END IF;
        END IF;
    END LOOP;
END $$;

十一、 分區 vs 分表 ​

特性原生分區應用層分表
查詢透明是否（需指定表）
自動 Pruning是否
跨分區查詢自動需要 UNION
維護PostgreSQL 管理應用自行管理

> **優先使用原生分區**

PostgreSQL 10+ 的原生分區已經很成熟，應用層分表只在特殊情況才需要。

十二、 效能考量 ​

分區數量 ​

  • 太少：Pruning 效果有限
  • 太多：計畫時間增加

建議：數十到數百個分區，避免上千個。

分區鍵選擇 ​

  • 查詢最常用的條件
  • 範圍分區選擇單調遞增的欄位（如時間）
  • 避免高基數欄位作為 List 分區鍵

總結 ​

概念說明
Range按範圍分區，適合時間序列
List按列舉值分區，適合類別
Hash均勻分散，適合無明顯模式的資料
Partition Pruning自動跳過不相關的分區
DETACH/ATTACH動態管理分區

分區讓大表管理變得輕鬆，是處理億級資料的必備技術。

進階挑戰 ​

  1. 為一個日誌表設計按月分區策略，並建立自動建立下個月分區的腳本
  2. 使用 pg_partman 擴充自動管理分區的建立和刪除
  3. 比較有無分區時的 EXPLAIN 輸出，觀察分區裁剪效果

延伸閱讀與資源 ​

← 上一章：檢視表與物化檢視 | 返回專題首頁 | 下一章：備份與還原 →