PostgreSQL 声明式分区表实战：3 步让亿级大表查询从 30 秒降到 200 毫秒

痛点

订单表跑了两年，行数突破 3 亿，单表体积 180GB。业务方一查"最近 7 天的订单统计"，PostgreSQL 要全表扫描——即使有索引，B-tree 树高到 5 层，buffer 命中率跌破 60%，查询耗时从毫秒级劣化到 30 秒。VACUUM 跑一次要 4 小时，autovacuum 经常追不上写入速度，dead tuple 堆积引发 bloat。

DBA 的本能反应是"加索引"，但 3 亿行表上 CREATE INDEX 一跑就是 40 分钟，期间写入全阻塞（CONCURRENTLY 也要跑 2 小时以上且消耗大量 IO）。真正的解法不是更多索引，而是把大表拆小——PostgreSQL 声明式分区（Declarative Partitioning）。

方案

PostgreSQL 10+ 原生支持声明式分区，核心思路：按时间（或业务键）把一张逻辑表拆成多个物理子表。查询时优化器通过 Partition Pruning 自动跳过无关分区，只扫目标子表。

关键收益：

指标	分区前（单表 3 亿行）	分区后（月分区）
7 天查询耗时	28-35s	150-250ms
VACUUM 单次耗时	3-4h	5-10min/分区
索引创建耗时	40min+	2-3min/分区
历史数据清理	DELETE + VACUUM 数小时	`DROP` 分区 < 1s

实操步骤

第 1 步：创建分区表结构

-- 创建按月 RANGE 分区的主表
CREATE TABLE orders (
    id          BIGSERIAL,
    user_id     BIGINT NOT NULL,
    amount      NUMERIC(12,2) NOT NULL,
    status      SMALLINT DEFAULT 0,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- 创建月分区（示例：2026 年 7-9 月）
CREATE TABLE orders_2026_07 PARTITION OF orders
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE orders_2026_08 PARTITION OF orders
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE orders_2026_09 PARTITION OF orders
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- 每个分区独立创建索引（自动继承到新分区）
CREATE INDEX ON orders (user_id, created_at);
CREATE INDEX ON orders (status) WHERE status IN (0, 1);

注意：分区范围是 左闭右开 [FROM, TO)，不会重叠。

第 2 步：自动化分区创建（Cron + PL/pgSQL）

手动建分区迟早忘，用函数 + pg_cron 自动创建下个月分区：

CREATE OR REPLACE FUNCTION create_monthly_partition(
    base_table TEXT,
    target_month DATE
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date     DATE;
    end_date       DATE;
BEGIN
    start_date     := date_trunc('month', target_month);
    end_date       := start_date + INTERVAL '1 month';
    partition_name := base_table || '_' || to_char(start_date, 'YYYY_MM');

    -- 检查分区是否已存在
    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = partition_name
    ) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
            partition_name, base_table, start_date, end_date
        );
        RAISE NOTICE '分区 % 创建成功', partition_name;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- pg_cron：每月 25 号自动创建下月分区
SELECT cron.schedule(
    'create-orders-partition',
    '0 3 25 * *',
    $$SELECT create_monthly_partition('orders', now() + INTERVAL '1 month')$$
);

第 3 步：存量数据在线迁移

已有的单表不能直接 ALTER 成分区表。推荐方案：pg_partman 扩展 + 分批迁移：

# 安装 pg_partman
sudo apt install postgresql-16-partman

# 在 postgresql.conf 中加载
shared_preload_libraries = 'pg_partman_bgw'
pg_partman_bgw.dbname = 'mydb'
pg_partman_bgw.interval = 3600  -- 每小时检查一次

-- 1. 重命名原表
ALTER TABLE orders RENAME TO orders_old;

-- 2. 创建分区表（结构同第 1 步）
CREATE TABLE orders ( ... ) PARTITION BY RANGE (created_at);

-- 3. 按月批量迁移，每批 10 万行，避免锁表
DO $$
DECLARE
    batch_size INT := 100000;
    rows_moved INT := 1;
BEGIN
    WHILE rows_moved > 0 LOOP
        WITH moved AS (
            DELETE FROM orders_old
            WHERE ctid IN (
                SELECT ctid FROM orders_old LIMIT batch_size
            )
            RETURNING *
        )
        INSERT INTO orders SELECT * FROM moved;
        GET DIAGNOSTICS rows_moved = ROW_COUNT;
        RAISE NOTICE '迁移 % 行', rows_moved;
        PERFORM pg_sleep(0.5);  -- 降低对线上的冲击
    END LOOP;
END $$;

-- 4. 迁移完成后删除旧表
DROP TABLE orders_old;

更稳的方案：先不删旧表，在应用层做双写，验证一周后再切换。

避坑

坑 1：查询不带分区键，Partition Pruning 失效

-- ❌ 全分区扫描（没有 created_at 条件）
SELECT * FROM orders WHERE user_id = 12345;

-- ✅ 带上分区键，只扫 1 个分区
SELECT * FROM orders
WHERE user_id = 12345 AND created_at >= '2026-08-01' AND created_at < '2026-09-01';

用 EXPLAIN 验证：看到 Seq Scan on orders_2026_08 而不是所有分区，就对了。关键配置：

-- 确保运行时裁剪开启（默认 on，但要确认）
SET enable_partition_pruning = on;

坑 2：跨分区 UNIQUE 约束不支持

PostgreSQL 分区表的 UNIQUE/PRIMARY KEY 必须包含分区键：

-- ❌ 报错：unique constraint must include partition key
ALTER TABLE orders ADD PRIMARY KEY (id);

-- ✅ 正确：主键包含分区键
ALTER TABLE orders ADD PRIMARY KEY (id, created_at);

如果业务需要全局唯一 id，用 BIGSERIAL（sequence 全局唯一）+ 应用层校验，不依赖数据库 UNIQUE 约束。

坑 3：没有 DEFAULT 分区导致数据丢失

插入不匹配任何分区范围的数据会直接报错。务必创建默认分区兜底：

CREATE TABLE orders_default PARTITION OF orders DEFAULT;

然后设监控：当 orders_default 有数据时告警——说明分区创建脚本没跟上。

-- 监控查询：默认分区是否有数据
SELECT count(*) FROM orders_default;

总结

月分区 + RANGE 策略是时序数据（订单、日志、事件）的最优解，按天分区适合日写入量 > 500 万的场景
自动化建分区是刚需，手动建迟早漏，pg_cron 或 pg_partman 二选一
历史数据清理从 DELETE 变成 DROP TABLE，从小时级变成秒级，磁盘空间即时释放
所有查询必须带分区键条件，否则退化为全分区扫描，比不分区还慢
分区数控制在 几百个以内，超过千级分区后 Planning 阶段开销会显著上升

亿级大表不可怕，可怕的是不拆。声明式分区是 PostgreSQL 原生能力，无需额外组件，今天就可以在测试环境跑起来。