引言

一张订单表 5000 万行，两年前建表时没人想过它会这么大：管理后台查一个用户近三个月的订单，索引还健在时 80ms，如今同样的查询变成 1.2 秒；DBA 想给 created_at 建个新索引，CREATE INDEX CONCURRENTLY 跑了三个小时还没完，期间磁盘 IO 打满影响线上；想删掉两年的历史数据，DELETE 跑了一夜没删完，还把表搞出了 40% 的膨胀。单表大了以后，慢的从来不只是查询——索引维护、数据清理、备份恢复、vacuum，每一件事都在恶化。

这些问题的共同解法是分区表：把一张逻辑大表按规则切成多个物理小段，查询只碰需要的段（分区裁剪），删除整段数据变成秒级的 DETACH/DROP，索引和 vacuum 都只发生在小分区上。PostgreSQL 10+ 的声明式分区让这件事的门槛降到了几条 DDL。这篇文章按"选分区类型 → 建按月 RANGE 分区 → 本地索引设计 → 分区裁剪验证 → 自动化维护"的顺序，用完整的 SQL 和 EXPLAIN 前后对比，讲清千万级大表分区的正确姿势——以及哪些坑（跨分区查询反而更慢、主键必须带分区键、唯一索引限制）会让你"分了区反而更慢"。

一、什么时候该分区：先对症，再下药

1.1 大表的四种痛，分区能治哪几种

判断标准：数据有天然的"时间/地域/离散键"边界、且 90% 的查询都会带这个边界条件 → 适合分区；查询五花八门不带统一过滤键 → 先优化索引，分区帮不上忙。

1.2 三种分区类型的选择

选型心法：跟查询条件的形状走。查询总带"某个月份"→ RANGE；总带"某个大区"→ LIST；只有 user_id 且想摊平数据 → HASH（注意 HASH 裁剪只能定位到一组分区，且不便于按时间归档）。订单表的查询天然带时间范围，RANGE 是唯一正确答案。

二、实战第一步：按月 RANGE 分区改造

2.1 新表直接建分区（理想路径）

-- 主表：只存分区规则，不存数据
CREATE TABLE t_order (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id     BIGINT NOT NULL,
    product_id  BIGINT NOT NULL,
    amount      NUMERIC(10,2) NOT NULL,
    status      SMALLINT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)          -- ★ 分区键必须包含在主键/唯一约束里
) PARTITION BY RANGE (created_at);

-- 子分区：按月一段，闭开区间 [from, to)
CREATE TABLE t_order_2026_07 PARTITION OF t_order
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE t_order_2026_08 PARTITION OF t_order
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE t_order_2026_09 PARTITION OF t_order
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- 默认分区兜底：接收不落在任何子分区的数据（防止插入报错）
CREATE TABLE t_order_default PARTITION OF t_order DEFAULT;

两个必须知道的硬规则：

1. 分区键必须包含在主键/唯一约束中——PRIMARY KEY (id) 会直接报错，必须写成 (id, created_at)。这决定了"全局唯一的业务键"要么带分区键、要么用应用层保证唯一、要么重新考虑分区键的选择；
2. 默认分区是双刃剑：有它兜底不会插报错，但建新分区前必须确认默认分区里没有属于新区间的数据，否则 ATTACH 会失败——生产上要么定期检查默认分区，要么干脆不留默认分区、让漏数据立刻暴露。

2.2 存量大表在线改造（3000 万行不停服）

直接改表结构需要锁表重写数据，生产不可行。正确姿势是"新表+双写/回填+切换"：

方案 A（允许短暂停写窗口，最简单，1 亿行约 10~30 分钟）：
  1. 创建分区新表 t_order_new（同结构）
  2. 停写（或只读代理拦截写入请求）
  3. INSERT INTO t_order_new SELECT * FROM t_order   -- 大批量可分月并行灌
  4. 校验行数与抽样数据
  5. 事务内：RENAME t_order → t_order_old，t_order_new → t_order

方案 B（不停服，工程标准做法）：
  1. 建分区新表 t_order_new
  2. 触发器双写：新写入同时进旧表和新分区表
  3. 历史数据按月分批回填（COPY/INSERT 分批，控制 IO）
  4. CDC（Debezium）或对账补偿追赶增量
  5. 低峰期原子 RENAME 切换读，观察后停双写

-- 方案 A 的数据搬迁示例：按月并行灌入，减轻单事务压力
INSERT INTO t_order_new SELECT * FROM t_order
WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01';
-- 每月一条，可多会话并行执行

三、分区索引：本地索引的正确设计

3.1 PG 分区索引的关键认知：只有本地索引

与 Oracle/MySQL 的全局索引不同，PG 的分区表索引全部是本地索引——在主表上 CREATE INDEX 会自动在每个分区上各建一个（分区级索引），不存在跨分区的全局索引结构。

-- 在主表上建，自动级联到所有现有分区 + 未来新分区（Recommended）
CREATE INDEX idx_order_user_time ON t_order (user_id, created_at DESC);
CREATE INDEX idx_order_status ON t_order (status, created_at);
CREATE INDEX idx_order_attrs_gin ON t_order USING gin (attrs);

这个设计的含义：

• 好处：每个分区索引很小（一个月的数据），索引维护/vacuum/rebuild 粒度小、速度快，热分区的索引整体常驻内存；
• 代价：查询条件不带分区键时，PG 要到每个分区的本地索引各查一遍再合并（Append + BitmapOr），分区数越多开销越大——这是"查询必须带分区键"的根本原因。

3.2 索引列的设计原则

第一列给强选择性等值条件（user_id/status），第二列给分区键或时间：
  (user_id, created_at DESC)   → 查某用户某时间段的订单（最常见）
  (status, created_at)          → 查某状态最近的数据（运营后台）
分区键 created_at 本身不需要单独建索引：
  裁剪靠分区边界完成，分区内的 created_at 范围条件
  由 (user_id, created_at) 或顺序扫描小分区消化
GIN（JSONB）照常建在主表，级联到各分区

3.3 只给热分区建索引（部分分区索引，PG 11+ 的进阶技巧）

历史分区几乎只读、查询模式固定时，可以只给部分分区建某些索引，省空间省维护：

-- 全部订单都需要 user_id 索引（主表级联）
CREATE INDEX idx_order_user_time ON t_order (user_id, created_at DESC);

-- 报表常用的"退款中"状态组合索引，只给近 3 个月的热分区建
CREATE INDEX t_order_2026_07_refund_idx
    ON t_order_2026_07 (status, amount)
    WHERE status = 3;
CREATE INDEX t_order_2026_08_refund_idx
    ON t_order_2026_08 (status, amount)
    WHERE status = 3;

四、分区裁剪：让查询自动只扫相关分区

4.1 EXPLAIN 前后对比（本篇核心证据）

改造前（5000 万行单表，查某用户近三个月订单）：

EXPLAIN ANALYZE
SELECT id, amount, status FROM t_order_old
WHERE user_id = 8821 AND created_at >= '2026-07-01'
  AND created_at < '2026-10-01'
ORDER BY created_at DESC LIMIT 20;

Limit (cost=1.02..184.55 rows=20) (actual time=1180.412..1180.421 rows=20)
  ->  Index Scan using idx_old_user_time on t_order_old
      Index Cond: (user_id = 8821 AND created_at >= ... )
      (actual time=0.098..1180.405 rows=20)
      Buffers: shared hit=812345 read=22190     ← 穿行整表大索引，读 83 万页
Planning Time: 0.3 ms
Execution Time: 1180.512 ms                    ← 1.18 秒

改造后（分区表，同语义查询）：

EXPLAIN ANALYZE
SELECT id, amount, status FROM t_order
WHERE user_id = 8821 AND created_at >= '2026-07-01'
  AND created_at < '2026-10-01'
ORDER BY created_at DESC LIMIT 20;

Limit (actual time=0.215..0.231 rows=20)
  ->  Merge Append
        Sort Key: t_order.created_at DESC
        ->  Index Scan using t_order_2026_07_user_time_idx
              on t_order_2026_07 t_order        ← 只碰 7 月分区
              Index Cond: (user_id = 8821 AND ...)
        ->  Index Scan using t_order_2026_08_user_time_idx
              on t_order_2026_08 t_order        ← 只碰 8 月分区
        ->  Index Scan using t_order_2026_09_user_time_idx
              on t_order_2026_09 t_order        ← 只碰 9 月分区
              （46 个历史分区直接消失在计划里——被裁剪掉了）
Execution Time: 0.246 ms                    ← 0.25 毫秒，快 4800 倍
Buffers: shared hit=24                      ← 24 页 vs 83 万页

三个数字说明一切：执行时间 1180ms → 0.25ms；缓存页 83 万 → 24；计划里的分区从全部 49 个 → 3 个。这就是分区裁剪（Partition Pruning）：优化器根据 WHERE 里的 created_at 范围直接砍掉无关分区，连打开它们的成本都没有。

4.2 什么写法会"裁剪失效"（比不会分区更常见的坑）

验证裁剪是否生效，看 EXPLAIN 计划里出现了几个分区：只出现相关分区 = 生效；出现 Append 带全部子分区 = 失效，回头检查 WHERE 写法。

4.3 跨分区聚合的收益边界

不带分区键的查询（如全表 count、全年报表）会退化成"所有分区的扫描合并"，分区不会让它变快，但也不会更慢（单分区更小，反而利于并行 Append）。真正的收益边界：按分区键过滤的查询占 90% 以上，分区才有正收益。如果你的报表大多是跨全表的，分区表的价值主要剩维护侧（删除/vacuum），查询侧别抱期望。

五、分区维护：自动建新分区、自动归档旧分区

分区表上线只是开始，不写自动化脚本的分区表半年后一定会出事——新月份到了没有分区，数据全落进默认分区或直接报错。

5.1 提前建未来分区（pg_cron 每天检查）

-- 核心函数：确保 [今天, 今天+N个月] 的分区都存在
CREATE OR REPLACE FUNCTION fn_ensure_month_partitions(
    p_table text, p_ahead_months int DEFAULT 3)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    v_month date;
    v_start date;
    v_end   date;
    v_part  text;
BEGIN
    FOR i IN 0 .. p_ahead_months LOOP
        -- 从当月起逐月生成
        v_month := date_trunc('month', now())::date + make_interval(months => i);
        v_start := v_month;
        v_end   := (v_month + make_interval(months => 1))::date;
        v_part  := p_table || '_' || to_char(v_month, 'YYYY_MM');

        IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = v_part) THEN
            EXECUTE format(
                'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
                v_part, p_table, v_start, v_end);
            RAISE NOTICE 'created partition %', v_part;
        END IF;
    END LOOP;
END $$;

SELECT fn_ensure_month_partitions('t_order', 3);

# pg_cron 每天凌晨自动执行（需安装 pg_cron 扩展）
SELECT cron.schedule('ensure-order-partitions', '0 2 * * *',
    $$SELECT fn_ensure_month_partitions('t_order', 3)$$);

5.2 历史分区归档：DETACH 而不是 DELETE

-- 归档 2024 年 7 月的分区：秒级元数据操作，不删行、不产生膨胀
ALTER TABLE t_order DETACH PARTITION t_order_2024_07;

-- 归档去向二选一：
-- ① 挂到归档表（仍在同库，可继续查）
ALTER TABLE t_order_archive ATTACH PARTITION t_order_2024_07
    FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');
-- ② pg_dump 导出后 DROP（冷存储/对象存储）
pg_dump -t t_order_2024_07 -Fc mydb > order_2024_07.dump
DROP TABLE t_order_2024_07;

DETACH 的三个工程细节：① PG 14+ 支持 DETACH PARTITION ... CONCURRENTLY，不阻塞查询（最终 detach 阶段仍需短暂锁）；② DETACH 前确认没有依赖该分区的外键引用冲突；③ 删除类操作永远用 DETACH/DROP，不要用 DELETE——DELETE 逐行标记+vacuum 回收，5000 万行跑一夜还留下膨胀，DETACH 一瞬间完成且零碎片。

5.3 归档自动化脚本（pg_cron 每月执行）

CREATE OR REPLACE FUNCTION fn_archive_old_partitions(
    p_table text, p_archive_table text, p_keep_months int DEFAULT 24)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    r record;
    v_cutoff date := date_trunc('month', now())::date
                     - make_interval(months => p_keep_months);
BEGIN
    -- 找出上界早于保留期的所有子分区
    FOR r IN
        SELECT c.relname AS part_name,
               pg_get_expr(c.relpartbound, c.oid) AS bound
        FROM pg_inherits i
        JOIN pg_class c ON c.oid = i.inhrelid
        JOIN pg_class p ON p.oid = i.inhparent
        WHERE p.relname = p_table
    LOOP
        -- 解析分区上界（形如 FOR VALUES FROM (...) TO ('2024-07-01')）
        IF (regexp_match(r.bound, $$TO \('([0-9-]+)'\)$$))[1] < v_cutoff::text THEN
            EXECUTE format('ALTER TABLE %I DETACH PARTITION %I',
                           p_table, r.part_name);
            EXECUTE format('ALTER TABLE %I ATTACH PARTITION %I FOR VALUES %s',
                           p_archive_table, r.part_name,
                           substr(r.bound, 1));   -- 沿用原边界挂到归档表
            RAISE NOTICE 'archived %', r.part_name;
        END IF;
    END LOOP;
END $$;

SELECT cron.schedule('archive-order-partitions', '0 3 1 * *',
    $$SELECT fn_archive_old_partitions('t_order', 't_order_archive', 24)$$);

5.4 分区表的日常运维清单

六、效果汇总与注意事项

6.1 改造前后对比

6.2 注意事项速查

硬规则：
  主键/唯一约束必须包含分区键
  分区键查询必须裸用（不套函数、不做运算）
  点查尽量带上分区键，否则跨分区索引合并
工程纪律：
  未来分区预建 ≥2 个月（pg_cron 自动化）
  删数据只用 DETACH/DROP，禁用 DELETE
  新分区导入后立即 ANALYZE
  默认分区行数监控告警
规模参考：
  单分区 500 万~2000 万行为宜，超了升级为按天
  分区总数 <1000（几百个以上规划期注意锁与元数据开销）

七、常见问题

7.1 分区表和"按月手工拆表"（order_2026_07 这种）比好在哪？

手工拆表的问题：应用层要自己路由到正确的表、跨月查询要 UNION 所有月份、加一列要改几十张表、没有统一的约束和索引管理。分区表把路由交给优化器（裁剪）、把跨段查询交给 Append（应用仍查 t_order 一个名字）、把 DDL 级联交给主表（改一次全分区生效）。手工拆表能做的分区表都能做，反之不成立——除非你的 ORM/团队完全不能接受分区语法，否则没有理由手工拆。

7.2 为什么我的分区表查询比不分区的单表还慢？

九成是这两种情况：① 查询不带分区键——每个分区的本地索引都要各查一遍再合并，分区越多开销越大（看 EXPLAIN 里 Append 是否包含全部分区）；② 裁剪失效——分区键上套了函数或用了运行时表达式（对照 4.2 表逐项排查）。分区表的收益前提永远是"查询条件与分区键形状匹配"，不匹配时它只是"多了管理的便利、没有查询的收益"。

7.3 主键必须带分区键，业务上 id 全局唯一怎么办？

三个层次解决：① 大多数业务主键是 IDENTITY/序列生成的，序列是全局的，跨分区的 id 天然不重复，主键约束 (id, created_at) 只是在"id+时间"维度上防重，业务唯一性由序列保证——这是最常见也够用的方案；② 必须数据库层强保证全局唯一（如外部传入的订单号），可以加 (order_no, created_at) 唯一约束 + 应用层先查后插 + 定期对账；③ 唯一性要求和分区键彻底冲突的场景（想按 user_id 分区但约束在 order_no 上），说明分区键选错了，回到选型重新评估。

7.4 按月还是按天分区？分区数有没有上限？

经验公式：单分区控制在 500 万~2000 万行。订单日均 50 万行 → 按月 1500 万行正好；日均 500 万行 → 按天。分区总数建议控制在千级以内（几百个问题不大）：分区太多会让规划期开销、锁管理、pg_class 元数据查询都变重，EXPLAIN 时间也会变长。历史分区归档及时的话，活跃分区通常只有几十个，压力不大。

7.5 DETACH 分区时业务会卡吗？归档窗口怎么选？

普通 DETACH 需要拿表级锁，会短暂阻塞该分区上的查询（毫秒到秒级，取决于是否有长事务持有锁）。生产建议：① 用 PG 14+ 的 DETACH PARTITION ... CONCURRENTLY，不阻塞读（注意它不能在事务块里执行）；② 安排在低峰执行；③ 执行前 kill 掉 idle in transaction 的长事务，避免锁等待。归档 ATTACH 到归档表同样需要分区边界完全匹配和短暂锁，配合 CONCURRENTLY 版本使用。

7.6 有没有现成的自动分区扩展，不用自己写函数？

有：pg_partman 是最成熟的分区管理扩展，支持按时间/序列自动预建分区、自动归档（retention）、子分区嵌套，配上 pg_cron 就是完整的自动化方案，本文手写的两个函数本质上是 pg_partman 的简化版——生产建议直接用 pg_partman（配置 retention = "24 months"、premake = 3 两行搞定），手写版本适合扩展能力受限的环境（云 RDS 不支持时用应用层调度兜底）。

八、总结

分区实战速查卡

选型：查询形状定分区类型（时间→RANGE / 枚举→LIST / 摊平→HASH）
建表：主键带分区键 (id, created_at)；默认分区慎用+监控行数
索引：主表建自动级联本地索引；(选择性列, 分区键) 组合；
      历史分区可只建部分索引
验证：EXPLAIN 看计划里出现几个分区——只出现相关的才是裁剪生效
      分区键裸用不套函数；点查尽量带分区键
维护：pg_cron 预建未来分区（领先 2~3 月）
      归档用 DETACH CONCURRENTLY + ATTACH/导出，禁 DELETE
      新分区导入后 ANALYZE；生产用 pg_partman + pg_cron
收益边界：带分区键查询 >90% 才有查询侧正收益；
          不带分区键的查询不指望分区提速

一句话

一张 5000 万行的表，慢的从来不只是查询：索引重建要三个小时、删一年历史数据跑一夜还留下 40% 膨胀、vacuum 和备份越来越难——这些痛的共同解法是声明式分区，而它的正确姿势可以用一句话概括：分区键的选择跟查询条件的形状走，索引和裁剪的效果都由这一点决定。订单查询天然带时间范围，就按月 RANGE 切分，主键必须带上分区键写成 (id, created_at)——这是 PG 的硬规则，序列全局递增保证 id 业务唯一，默认分区要么不留、要么监控行数防漏；索引全部是本地索引，在主表上建一次自动级联所有分区，设计成"选择性等值列在前、分区键或时间列在后"的复合形态，历史分区还能只建部分索引省维护成本；查询侧的灵魂是分区裁剪——EXPLAIN 里计划从扫全表的 83 万个缓存页 1.18 秒，变成只碰三个月份分区、24 个页、0.25 毫秒，快 4800 倍的关键不是索引变好了，而是另外 46 个分区根本没进执行计划，前提是分区键在 WHERE 里裸用、不套函数不做运算，点查尽量带上分区键，否则本地索引的跨分区合并会让分区表比单表还慢。维护侧的黄金法则是：未来分区用 pg_cron 或 pg_partman 提前两三个月自动预建，防止月末数据落进默认分区或直接报错；删历史数据永远用 DETACH PARTITION CONCURRENTLY 加 ATTACH 到归档表或导出 DROP，秒级完成、零膨胀，DELETE 逐行标记加 vacuum 回收的方式在分区表里没有存在的理由；新分区灌完数据立即 ANALYZE 让裁剪和行数估算保持准确。最后记住收益边界：90% 以上查询带分区键，分区才有查询侧的正收益，跨全表的报表不会因此变快——分区表解决的是"大表的物理管理问题"，不是"坏 SQL 的优化问题"，两件事别指望同一个方案。

给团队的建议

互动话题：你们的千万级大表是分区、分库分表还是硬扛？分区裁剪失效的坑踩过吗？评论区聊聊你的表规模和方案选择。

参考资料

• PostgreSQL 官方文档：Partitioning（声明式分区）
• PostgreSQL 文档：Partition Pruning（分区裁剪机制）
• PostgreSQL 文档：ALTER TABLE ... DETACH PARTITION（含 CONCURRENTLY）
• pg_partman 官方仓库（分区自动管理扩展）
• pg_cron 官方仓库（库内定时任务）