Linux

Ubuntu 22.04 PostgreSQL 分區表設定

Ubuntu 22.04 PostgreSQL Table Partitioning

分區表概述

PostgreSQL 分區表（Table Partitioning）是一種將大型資料表拆分為多個較小實體分區的技術。透過分區，可以顯著提升查詢效能、簡化資料維護，並優化儲存空間的使用。

分區表的優點

  • 查詢效能提升：查詢時只需掃描相關分區，減少 I/O 操作
  • 維護便利性：可以針對單一分區進行備份、重建索引或刪除
  • 批次刪除效率：透過刪除整個分區來快速移除大量資料
  • 資料歸檔簡化：輕鬆將舊資料分區移至較慢的儲存媒體

分區類型

PostgreSQL 支援三種主要的分區策略：

分區類型說明適用場景
Range依據範圍分區時間序列資料、數值區間
List依據離散值分區地區、類別、狀態碼
Hash依據雜湊值分區平均分散資料

Range 分區

Range 分區適用於具有連續範圍值的資料，最常見的應用是依據日期進行分區。

建立 Range 分區表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 建立主表（父表）
CREATE TABLE orders (
    order_id        SERIAL,
    customer_id     INTEGER NOT NULL,
    order_date      DATE NOT NULL,
    amount          DECIMAL(10,2),
    status          VARCHAR(20)
) PARTITION BY RANGE (order_date);

-- 建立月份分區
CREATE TABLE orders_2025_01 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

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

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

-- 建立預設分區（處理不符合任何分區的資料）
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

為分區建立索引

1
2
3

-- 在父表建立索引，會自動套用到所有分區
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_date ON orders (order_date);

List 分區

List 分區適用於依據離散值進行分類的場景，例如地區或產品類別。

建立 List 分區表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 建立依地區分區的銷售表
CREATE TABLE sales (
    sale_id         SERIAL,
    region          VARCHAR(20) NOT NULL,
    sale_date       DATE NOT NULL,
    product_name    VARCHAR(100),
    quantity        INTEGER,
    price           DECIMAL(10,2)
) PARTITION BY LIST (region);

-- 建立各地區分區
CREATE TABLE sales_north PARTITION OF sales
    FOR VALUES IN ('taipei', 'hsinchu', 'taoyuan');

CREATE TABLE sales_central PARTITION OF sales
    FOR VALUES IN ('taichung', 'changhua', 'nantou');

CREATE TABLE sales_south PARTITION OF sales
    FOR VALUES IN ('kaohsiung', 'tainan', 'pingtung');

CREATE TABLE sales_east PARTITION OF sales
    FOR VALUES IN ('hualien', 'taitung', 'yilan');

Hash 分區

Hash 分區可將資料平均分散到多個分區中，適用於無明顯分區鍵的情況。

建立 Hash 分區表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 建立 Hash 分區表
CREATE TABLE user_sessions (
    session_id      UUID PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    login_time      TIMESTAMP NOT NULL,
    ip_address      INET,
    user_agent      TEXT
) PARTITION BY HASH (user_id);

-- 建立 4 個 Hash 分區
CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

分區管理

新增分區

1
2
3

-- 動態新增新月份分區
CREATE TABLE orders_2025_04 PARTITION OF orders
    FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

分離分區

1
2
3
4
5
6

-- 將分區從父表分離（保留資料）
ALTER TABLE orders DETACH PARTITION orders_2025_01;

-- 分離後可以獨立操作該表
-- 例如：備份、歸檔或刪除
DROP TABLE orders_2025_01;

附加分區

1
2
3

-- 將現有表附加為分區
ALTER TABLE orders ATTACH PARTITION orders_2025_05
    FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');

自動分區維護

可以透過 pg_cron 擴充套件實現自動分區管理。

安裝 pg_cron

1
2

# Ubuntu 22.04 安裝 pg_cron
sudo apt-get install postgresql-14-cron

設定自動建立分區

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 建立自動建立分區的函數
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
    partition_date DATE;
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    -- 計算下個月的日期
    partition_date := DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month');
    partition_name := 'orders_' || TO_CHAR(partition_date, 'YYYY_MM');
    start_date := partition_date;
    end_date := partition_date + INTERVAL '1 month';

    -- 檢查分區是否已存在
    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = partition_name
    ) THEN
        EXECUTE FORMAT(
            'CREATE TABLE %I PARTITION OF orders
             FOR VALUES FROM (%L) TO (%L)',
            partition_name, start_date, end_date
        );
        RAISE NOTICE 'Created partition: %', partition_name;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 使用 pg_cron 排程（每月 25 日執行）
SELECT cron.schedule('create_partition', '0 0 25 * *',
    'SELECT create_monthly_partition()');

查詢優化

啟用分區裁剪

1
2
3
4
5

-- 確認分區裁剪已啟用
SHOW enable_partition_pruning;

-- 如需啟用
SET enable_partition_pruning = on;

查看查詢計畫

1
2
3
4
5

-- 使用 EXPLAIN 驗證分區裁剪效果
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE order_date >= '2025-02-01'
  AND order_date < '2025-03-01';

平行查詢

1
2
3
4

-- 設定平行查詢參數
SET max_parallel_workers_per_gather = 4;
SET parallel_tuple_cost = 0.01;
SET parallel_setup_cost = 100;

效能考量

分區數量建議

  • 分區數量建議控制在 100-1000 個之間
  • 過多分區會增加查詢規劃時間
  • 過少分區則無法發揮分區優勢

分區鍵選擇原則

  1. 選擇查詢中經常使用的欄位
  2. 避免使用經常更新的欄位
  3. 確保資料在分區間分布均勻

監控分區大小

1
2
3
4
5
6
7
8

-- 查看各分區的大小
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
WHERE tablename LIKE 'orders_%'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

統計資訊更新

1
2
3
4
5

-- 定期更新分區統計資訊
ANALYZE orders;

-- 或針對特定分區
ANALYZE orders_2025_01;

參考資料

comments powered by Disqus
© 2020 - 2026 Astroicers Blog
Built with Hugo
Theme Stack designed by Jimmy