PostgreSQL分区表实战：大表水平拆分与查询优化

小编 • • 数据库

PostgreSQL的表分区功能可以将大表在物理存储层面拆分为多个子表，查询时通过分区裁剪减少扫描范围。对于日志、交易记录等持续增长的大表，分区是控制单表数据量、提升查询性能的有效手段。

声明式分区与分区策略

PostgreSQL 10开始支持声明式分区（Declarative Partitioning），相比传统的继承分区，语法更简洁，优化器支持也更完善。分区策略包括Range分区、List分区和Hash分区三种。

-- Range分区：按时间范围分区，最常用于日志类数据
CREATE TABLE access_log (
    id          BIGSERIAL,
    user_id     INTEGER NOT NULL,
    request_url TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    created_at  TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- 按月创建分区
CREATE TABLE access_log_202601
    PARTITION OF access_log
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE access_log_202602
    PARTITION OF access_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE access_log_202603
    PARTITION OF access_log
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 创建默认分区，捕获未匹配任何分区的数据
CREATE TABLE access_log_default
    PARTITION OF access_log DEFAULT;

-- List分区：按离散值分区，适合按区域、类别拆分
CREATE TABLE order_data (
    id          BIGSERIAL,
    order_no    TEXT NOT NULL,
    region      TEXT NOT NULL,
    amount      NUMERIC(12,2),
    created_at  TIMESTAMP NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE order_data_north
    PARTITION OF order_data
    FOR VALUES IN ('beijing', 'tianjin', 'hebei');

CREATE TABLE order_data_south
    PARTITION OF order_data
    FOR VALUES IN ('guangdong', 'fujian', 'hainan');

-- Hash分区：均匀分布数据，适合无法按范围或列表划分的场景
CREATE TABLE user_data (
    id          BIGSERIAL,
    user_code   TEXT NOT NULL,
    user_name   TEXT NOT NULL,
    extra       JSONB
) PARTITION BY HASH (user_code);

CREATE TABLE user_data_0 PARTITION OF user_data
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_data_1 PARTITION OF user_data
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_data_2 PARTITION OF user_data
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_data_3 PARTITION OF user_data
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

分区裁剪与查询计划分析

分区表的核心价值在于分区裁剪（Partition Pruning）：查询条件中包含分区键时，优化器自动跳过不相关的分区，减少IO扫描量。通过EXPLAIN可以验证分区裁剪是否生效。

-- 查询特定月份的数据，触发分区裁剪
EXPLAIN ANALYZE SELECT * FROM access_log
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';

-- 执行计划应只扫描 access_log_202602 分区
-- "Append" 节点下只有 access_log_202602
-- 如果出现所有分区都被扫描，说明分区裁剪未生效

-- 常见导致裁剪失效的原因：分区键上使用了函数
-- 错误：对分区键做函数转换，无法裁剪
SELECT * FROM access_log
WHERE DATE(created_at) = '2026-02-15';
-- 不会触发分区裁剪，会扫描全部分区

-- 正确：直接比较分区键值
SELECT * FROM access_log
WHERE created_at >= '2026-02-15' AND created_at < '2026-02-16';
-- 正确触发分区裁剪

-- 验证分区裁剪效果
EXPLAIN SELECT * FROM access_log
WHERE created_at >= '2026-02-15' AND created_at < '2026-02-16';
-- Subplans Removed: 2  (裁剪掉了2个分区)

自动化分区管理

时间范围分区需要定期创建新分区，手工维护容易遗漏导致数据写入默认分区。通过pg_partman扩展可以实现自动创建和删除分区。

-- 安装pg_partman扩展
CREATE EXTENSION pg_partman;

-- 创建分区配置：按月自动分区，预创建3个月的分区
SELECT partman.create_parent(
    p_parent_table := 'public.access_log',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := 'monthly',
    p_premake := 3  -- 预创建未来3个月的分区
);

-- 设置自动删除旧分区：保留12个月数据
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false  -- true=仅解除关联，false=删除分区
WHERE parent_table = 'public.access_log';

-- 设置定时任务自动执行维护（使用pg_cron扩展）
CREATE EXTENSION pg_cron;

-- 每天凌晨1点执行分区维护
SELECT cron.schedule(
    'partman-maintenance',
    '0 1 * * *',
    $$SELECT partman.run_maintenance_proc()$$
);

-- 查看已创建的分区配置
SELECT parent_table, control, partition_interval,
       premake, retention
FROM partman.part_config;

分区表上的索引策略

分区表支持在父表上创建索引，PostgreSQL会自动在所有子分区上创建对应索引。也可以在单个分区上创建独立索引，针对特定分区的查询做定向优化。

-- 在父表上创建索引，自动传播到所有分区
CREATE INDEX idx_access_log_user_id
    ON access_log (user_id);

CREATE INDEX idx_access_log_created_at
    ON access_log (created_at DESC);

-- 复合索引：分区键放最前面，配合分区裁剪效果最佳
CREATE INDEX idx_access_log_user_time
    ON access_log (created_at, user_id);

-- 单独为热分区创建针对性索引
CREATE INDEX idx_access_log_202603_status
    ON access_log_202603 (status_code)
    WHERE status_code >= 400;  -- 部分索引，只索引异常请求

-- 查看分区索引状态
SELECT
    schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename LIKE 'access_log%'
ORDER BY tablename, indexname;

-- 索引使用情况统计
SELECT
    schemaname, relname, indexrelname,
    idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname LIKE 'access_log%'
ORDER BY idx_scan DESC;

数据迁移到分区表

已存在的普通大表迁移到分区表需要分步骤执行。直接建分区表再导入数据是最安全的方案，但停机时间较长。通过分批迁移的方式可以实现在线迁移。

-- 方案：使用CTE分批迁移旧表数据
-- 第1步：创建分区表（如上）

-- 第2步：分批迁移数据，避免长事务
DO $$
DECLARE
    start_date DATE := '2026-01-01';
    end_date DATE := '2026-04-01';
    batch_start DATE;
    batch_end DATE;
    rows_moved INTEGER;
BEGIN
    batch_start := start_date;
    WHILE batch_start < end_date LOOP
        batch_end := batch_start + INTERVAL '1 day';

        -- 使用CTE先删后插，确保幂等
        WITH moved AS (
            DELETE FROM old_access_log
            WHERE created_at >= batch_start
              AND created_at < batch_end
            RETURNING *
        )
        INSERT INTO access_log
        SELECT * FROM moved;

        GET DIAGNOSTICS rows_moved = ROW_COUNT;
        RAISE NOTICE 'Migrated %: % rows', batch_start, rows_moved;

        -- 批次间暂停，减少对在线业务影响
        PERFORM pg_sleep(0.1);
        batch_start := batch_end;
    END LOOP;
END $$;

-- 第3步：验证数据一致性
SELECT 'old' AS source, COUNT(*) FROM old_access_log
UNION ALL
SELECT 'new', COUNT(*) FROM access_log;

-- 第4步：确认无误后删除旧表
DROP TABLE old_access_log;

数据备份恢复在分区表场景下有天然优势——可以按分区单独备份和恢复。对于月度分区表，每月只需要备份当前月和上月分区，历史分区在确认不再变更后可以跳过备份。SQL查询优化中，分区表的统计信息是独立收集的，每个分区的数据分布可能不同，执行计划可能因分区而异。NoSQL选型应用场景中，如果数据量增长可控且有明确的时间或分类维度，PostgreSQL分区表比迁移到MongoDB等文档数据库更经济实用。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/postgresql-fen-qu-biao-shi-zhan-da-biao-shui-ping-chai-fen/

赞 (0)
小编小编
0
上一篇 11小时前
下一篇 11小时前

相关推荐