PostgreSQL 分区表实战:声明式分区、本地索引与查询裁剪的坑清单
数据量上来后,"按时间分区"是 PG 里最常见的架构决策之一。PG 17/18 时代声明式分区已相当成熟(PG 18 还允许 AFTER 触发器角色语义修正、VACUUM 处理继承子表等变化),但生产事故仍然集中在三处:分区裁剪失效导致全分区扫、本地索引与全局唯一约束的矛盾、以及分区数量膨胀后的规划开销。本文给一套从设计到运维的分区实战手册。
一、选型:RANGE/LIST/HASH 各管什么
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
ts timestamptz NOT NULL,
tenant_id int NOT NULL,
kind text, payload jsonb
) PARTITION BY RANGE (ts);
CREATE TABLE events_2026_11 PARTITION OF events
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
判据:
- 时间 RANGE:日志/事件/订单——配合
DROP PARTITION秒级清理、分区级 vacuum;自动管理用 PG15+ 的CREATE ... PARTITION OF ... DEFAULT+ pg_partman/pg_auto_partition; - HASH:多租户均摊写入(按 tenant_id),解决"热点租户压一个分区"——注意 HASH 数取 2 的幂,扩容要 detach/reattach 重分布,规划一次到位;
- LIST:地区/业务线等枚举维度。
不要为了"看起来先进"给小表分区。经验阈值:单表 <5000 万行且无清理需求 → 不分区(PG18 的 skip scan、并行扫描已经覆盖很多过去需要分区的场景)。
二、查询裁剪:一切收益的前提
-- 裁剪生效:约束表达式与查询谓词可比较
SELECT count(*) FROM events WHERE ts >= '2026-11-01' AND ts < '2026-11-08';
EXPLAIN → Subplans Removed: N -- 或只扫目标分区
-- 裁剪失效的常见写法:
WHERE ts >= now() - interval '7 days' -- stable 函数可裁剪(now 是 stable,OK)
WHERE to_char(ts,'YYYYMM') = '202611' -- 函数包裹 → 全分区扫
WHERE id IN (SELECT ... ) -- 子查询里的时间条件传不进裁剪
三条保命规则:分区键必须出现在 WHERE 且不被函数包裹;ORM/框架生成 SQL 要审计这条(PG 的 JDBC/psql 都支持 enable_partition_pruning,默认 on);跨分区聚合查询接受并行扫描计划而不是强塞时间条件。
三、索引设计:本地索引 + 全局唯一性矛盾
PG 没有真正的"全局二级索引"。唯一约束必须包含分区键——这是分区表最大的架构约束:
-- 想按 code 全局唯一?events(id) PK 必须是 (id, ts),而业务 code 唯一做不到跨分区
-- 解法一(推荐):主数据不分区,只给日志/事件类分区;code 唯一留在主表
-- 解法二:全局唯一性靠"路由表 + 应用层 upsert"或 Redis SETNX 前置挡重
本地索引策略:每个分区独立建同款索引(CREATE INDEX ON events_2026_11 (tenant_id, ts)),配合裁剪后单分区索引扫。PG 17+ 支持 CREATE INDEX ON ONLY parent 的分步并行建索引流程:先各分区建、再 attach,避免大表锁窗口。
BRIN 是时间分区的隐藏利器:按 ts 物理有序的日志表,用 USING brin (ts)(几 KB 索引)替代 B-tree(几 GB),扫描裁剪 + BRIN 块跳过即可——分区 + BRIN 组合下,"按时间查"的成本与单列 B-tree 相当,维护成本低一个数量级。
四、运维面:膨胀的分区数与 detach 流程
- 分区数上限实践值:几百个以内规划开销可忽略;上千后每条查询的 prune 阶段都变慢(
EXPLAIN里 parse/plan time 上升)。按"月分区"通常比"天分区"更平衡; - DETACH/ATTACH:PG 14+
ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY不阻塞读写,attach 校验用NOT VALID+ 后续VALIDATE CONSTRAINT分步做(大表 attach 的约束校验就是锁窗口来源); - 归档流水线:DETACH 旧分区 → 压缩/导出对象存储 → DROP。与冷热分层篇的对象存储路径直接衔接;
- 统计信息:分区表父表无数据,
pg_stat_user_tables按子分区看;autovacuum 参数要逐个分区设(热分区激进、冷分区关)。
ALTER TABLE events DETACH PARTITION events_2025_12 CONCURRENTLY;
-- 导出 → 归档桶 → DROP TABLE events_2025_12;
五、与复制/备份的交互注意
- 逻辑复制对分区表:订阅端发布父表即覆盖全部子分区,但新分区自动同步、旧数据要预灌;DDL(建分区)不同步——发布窗口内禁 DDL 纪律同样适用;
- pgBackRest 物理备份包含全部分区文件,恢复演练按整库做;单分区救急走"detach → 独立表恢复 → attach"路径更快;
- PG 18 注意:VACUUM/ANALYZE 默认处理继承子表(含分区),老脚本里手工循环分区的逻辑要复核,避免双重 vacuum;unlogged 分区表被禁止——临时数据另建非分区 unlogged 表。
六、反模式清单
- 用分区解决"索引太大":先加 BRIN/降列宽/清理,分区是最后手段;
- 给高频点查表按时间分区:点查不带 ts → 全分区扫(除非 id 里编码时间)——业务主键路由优先于时间分区;
- 分区键选低基数 LIST:一个租户占 80% 数据的 LIST 只是把问题集中了;
- 忘建 DEFAULT 分区:插入越界数据直接报错炸写入——要么 DEFAULT 兜底 + 告警,要么提前预建 12 个月;
- 跨分区外键指向分区表:PG 支持但代价高(每分区约束),尽量让 FK 从分区表指向不分区的主数据表。
小结
PG 分区表的收益公式是:裁剪生效才有收益,DROP PARTITION 与 BRIN 才是时间分区的真正红利。设计期先回答"查询是否总带分区键"和"唯一性是否需要全局"两个问题——答不了就别分区;运维期盯三件事:分区数增长曲线、attach/detach 的锁窗口、autovacuum 按分区分级。把"月分区 + DEFAULT 兜底 + BRIN(ts) + DETACH 归档流水线"固化成模板后,分区表就从一次性架构决策变成可长期演进的容量管理工具。
评论(0)
登录 后参与评论。