先交代一下背景。前阵子接手一套零售订单库orders 表跑到 2 亿行的时候一个带索引的简单时间范围查询从几十毫秒退化到 3 秒开外。加索引已经没什么明显效果因为要扫的数据块实在太多索引命中率再高也架不住整体数据量膨胀。折腾了两周最后真正解决问题的是把 PostgreSQL 分区表管理这套体系捡起来重新做了表结构改造。这篇文章就把我在这个过程中积累的对分区表的理解、设计方案、日常运维动作和实际踩过的坑整理成一份可落地的经验贴适合正在被大表查询拖垮的后端开发也适合需要长期维护在线事务库的 DBA 参考。先说一个重要结论分区表不是简单的把一张大表拆成几张子表而是一整套从表结构设计、查询裁剪、索引约束到生命周期管理的组合拳。如果只是照猫画虎建几个分区最后该慢还是慢甚至可能比以前更慢。1. 分区表不是把表拆碎想清楚收益边界再动手1.1 单表变慢的本质分区解决的是什么问题很多人以为单表数据量大了之后B-tree 索引就会失效其实不完全对。索引一般是不会失效的但问题出在几个地方。第一是缓存命中率。一张 500 GB 的表即使只查最近一个月的 5 GB 数据数据库也没办法保证这 5 GB 永远留在共享内存里因为其他业务 SQL 会不断把热数据挤出去。每次查询都可能从磁盘重新加载数据块IO 延迟就起来了。第二是 VACUUM 和统计信息的压力。单表几十亿行时一次 autovacuum 可能要跑几个小时死元组的回收速度跟不上产生速度表只增不减查询质量越来越差。第三是索引本身膨胀。大表的索引层级多随机读索引页的成本高更新频繁时索引维护的开销也是一笔隐形账。分区带来的本质变化是把物理存储和管理粒度拆小了。每个分区是一个独立的表可以单独做 VACUUM、REINDEX、归档甚至可以放到不同的表空间里把冷数据挪到慢速磁盘把热数据留在 SSD 上。更重要的是SQL 里的过滤条件能直接排除掉无关分区减少实际扫描的数据量。但要注意分区不是万能药。如果一张表的数据没有明显的自然分区维度比如字典表、配置表强行分区只会让查询计划变复杂、DDL 运维变繁琐。分区最擅长的场景是数据按时间、按租户、按某个枚举维度持续增长并且业务查询往往总是命中其中一小部分数据的场景。1.2 三种分区策略RANGE、LIST 和 HASH 怎么选PostgreSQL 从 10 开始提供声明式分区支持 RANGE、LIST、HASH 三种策略。这个声明式的意思是你只要告诉数据库按哪个列、怎么分区后续插入数据时的路由、查询时的裁剪都由数据库自动处理不需要手写触发器或者规则。分区策略适用场景分区键例子裁剪收益RANGE连续增长的数据按时间或数值区间切分订单时间、事件时间、ID 区间时间范围查询裁剪效果最好LIST枚举值明确按有限维度隔离城市、业务线、状态、租户 ID等值过滤时可以精确命中单个分区HASH没有自然范围键希望数据均匀分散用户 ID、设备 ID、随机 Hash 键只在按分区键等值查询时有收益我在实际项目中的选择习惯是这样的凡是流水、日志、订单这类有时序特征的表无脑优先考虑 RANGE 分区按天或按月切。LIST 分区适合数据被业务天然隔离的场景比如多租户系统但要注意数据倾斜问题——某个租户数据量特别大时这个分区会变成新的大表到时候可能还得在这个分区下再做一次子分区。HASH 分区通常是在写多读少、没有明确时间访问特征的场景救急用的比如会话表、中间表。它能把写入压力分散到多个分区但查询时必须带上分区键否则会扫全部 HASH 分区。1.3 版本下限和生产环境的版本选择建议声明式分区是 PG10 才有的能力所以如果项目还在用 9.x那就只能用老式继承分区模拟建议尽早升级。版本方面我的最低底线是 PostgreSQL 14因为从 14 开始才支持并发 DETACH PARTITION这在运维上是质变。新项目直接上 PG16 或者 PG17无论稳定性还是分区相关的优化都更成熟。有朋友会纠结下载哪个发行版、要不要源码编译我的建议是生产环境优先用官方稳定版本对应的 Linux 发行版软件源或者直接用官方 Docker 镜像省去编译折腾。分区功能本身不需要额外扩展内核支持不用装任何组件。2. 声明式分区替代继承分区的理由和版本演进里值得你升级的细节2.1 老式继承分区为什么我不再推荐PostgreSQL 10 之前没有原生分区能力社区普遍用表继承 约束排除来模拟分区父表定义字段若干子表继承父表每个子表加 CHECK 约束再靠触发器或者规则把插入路由到对应子表。这套方案最大的问题是约定大于配置。约束写得对路由触发器写得对查询优化器还愿意做 constraint exclusion系统才能正常工作。但凡某张子表的 CHECK 约束边界写错一点、触发器漏写一个分支数据就可能落到错误的表里排查起来非常痛苦。而且继承分区的父表查询裁剪依赖constraint_exclusion参数这个参数对没完没了的 SQL 判断成本很高统计信息也很难收集准确。我在 2018 年之前维护过一套用继承分区做的订单表后来迁移到声明式分区之后最大的感受是省心。数据库内核负责路由和裁剪不需要应用层或者触发器参与出错概率大幅下降。2.2 PG10 到 PG14 甚至更新的版本哪些变化真正影响日常运维声明式分区从引入到现在几乎每个版本都在补强。不是每个版本变化都值得你激动但对运维真正有影响的就那么几个点。PG11 加入了 HASH 分区和默认分区。默认分区很重要它是应对数据边界没覆盖住的兜底方案。没有默认分区时一旦插入一条不属于任何分区边界的数据INSERT 直接报错有了默认分区数据先落进去之后你再排查边界问题。PG12 的分区裁剪性能提升非常明显特别是 UPDATE、DELETE 和 JOIN 场景下的裁剪能力。早期版本里 UPDATE 跨全部分区执行是常态12 以后大多数情况下能按条件精确裁剪。PG14 引入了并发 DETACH PARTITION。这是我最看重的一个版本特性。早先要把某个分区摘下来归档ALTER TABLE 会拿比较重的锁业务高峰根本不敢动。14 之后可以用DETACH PARTITION ... CONCURRENTLY在不阻塞读写的情况下完成分区分离。这个能力直接决定了生产环境能不能做在线归档所以我一直把 14 当成运维底线。之后的版本主要是在细节上继续打磨比如逻辑复制、子分区、更细的锁粒度优化等。如果你还在 13 以下分区表的长期运维会特别受限制越早升级越划算。2.3 从旧继承分区迁移到声明式分区的基本思路老实说PostgreSQL 不支持直接把一张继承分区表原地转换成声明式分区表。我这里的做法比较保守新建一张声明式的父表按同样的分区键建好全新的子分区然后分批把数据从旧表搬过去。迁移过程中要注意几个问题。第一分批搬迁时不要直接 INSERT 到父表虽然声明式分区会自动路由但大批量搬迁时逐行路由的开销不小更建议按目标分区直接写入。第二索引和约束在建完数据后再补否则每插一批数据都要维护索引速度慢很多。第三搬迁期间应用要有停写窗口或者用双写方案过渡这一步最容易被低估。如果旧的子表本来就是用 CHECK 约束限定边界的普通表理论上可以通过 ATTACH PARTITION 把它挂到新父表下前提是约束和声明式边界完全匹配。但我在生产上不建议赌这个旧系统里的 CHECK 约束往往写得不规范校验失败反而更麻烦。3. 设计阶段的三个关键决策分区键、粒度、约束错了后面全是坑3.1 分区键的选择直接决定分区裁剪是否有效分区键选不好分区表就只是个形式查询照样扫所有子表。核心原则只有一条业务查询里出现频率最高的过滤条件列才有资格做分区键。以订单表为例绝大多数查询都带时间范围那 order_time 就是天然的分区键。如果某个系统主要按 customer_id 访问数据也可以考虑对 customer_id 做 HASH 分区或者先按 customer_id 做 LIST 分区再按时间做二级分区。更关键的是查询里对分区键的使用方式必须能被优化器识别。比较常见的一个反例是-- 这个条件无法做分区裁剪因为分区键被函数包了一层 SELECT * FROM orders WHERE date_trunc(month, order_time) 2025-01-01;正确的写法是让分区键裸出现直接给边界范围-- 这个写法能精确裁剪到 2025 年1月这一个分区 SELECT * FROM orders WHERE order_time 2025-01-01 AND order_time 2025-02-01;还有一个很容易忽略的坑是隐式类型转换。order_time 如果是 timestamptz你查询时拿一个 date 类型或者字符串直接比较优化器做隐式转换之后可能导致无法裁剪。实际设计中最好统一参数类型避免明明有分区键条件却一个分区都裁不掉的尴尬。验证裁剪是否生效的方法很简单用 EXPLAIN 看计划EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_time 2025-01-01 AND order_time 2025-02-01;如果输出里出现大大的 Subplans Removed: 11 之类的字样说明裁剪生效了。如果 Append 节点下面还是列着一大堆子表扫描那就要回头检查条件写法。3.2 分区粒度不是越细越好数量膨胀会反噬运维分区粒度是设计阶段最容易走极端的地方。有人喜欢按天分区一天一张表结果一年 365 个分区有人喜欢按年分区一年一张表结果查近三个月数据照样扫整个年度分区。这两者都谈不上最优。我的经验是分区数量尽量控制在几百到一两千以内不要轻易上万。每一个分区都是独立的表有自己的元数据、统计信息、锁和 autovacuum 调度。分区太多之后最直观的表现是两个一是执行计划生成时处理的分区元数据变多规划时间上升二是自动清理任务容易出现VACUUM 风暴几十上百个分区同时触发 autovacuum把 IO 打满。具体粒度上时序数据按月是最常见的默认选择。单月数据量如果只有几百 MB按月足够如果单月数据量到了几十 GB按天反而更合适。一个可以参考的区间是单个分区数据量保持在几百 MB 到几 GB 之间并且典型查询覆盖的分区数不要超过三五个。比如业务经常查最近一周的数据按周分区可能比按月更好。HASH 分区的数量设计要更谨慎因为 HASH 分区一旦确定 MODULUS后期想改分区数量基本上等于重建整个表。我一般建议 HASH 分区数选 4、8、16 这种 2 的幂次或者按预估数据量和单分区容量直接留够余量一次到位。3.3 主键和唯一约束的硬限制设计期就要接受声明式分区表上有一个经常让开发团队头疼的限制父表上的主键或者唯一约束必须包含全部分区键列。举个例子orders 按 order_time 分区你没法直接在订单表上建立 order_id 的全局主键。想建唯一约束只能建(order_id, order_time)的联合唯一约束。这个限制的本质原因是分区表的唯一性校验如果只看 order_id数据库得跨所有分区去查才能确认这在分布式架构里成本太高。PostgreSQL 选择了更保守的方案唯一约束必须带上分区键这样每个分区内部就能独立保证唯一性全局唯一性由分区边界自身保证。设计期遇到这个约束我的建议是不要硬刚。业务上如果确实需要 order_id 全局唯一可以把 order_id 的序列生成改成全局唯一 ID比如 UUID 或者雪花 ID同时把联合唯一索引建上。如果必须保留单列主键语义那就得考虑是不是不应该分区或者在旁边放一张非分区的 ID 映射表但这会引入一致性问题能不用尽量不用。另外在父表上执行 CREATE INDEX 会自动传播到所有已有分区也会自动应用到后续新建的分区这个特性省了很多事。但要注意一次性在父表上建索引会对每个分区执行 DDL数据量大的分区会拖慢整条语句。更稳妥的方式是在低峰期执行或者干脆对重点分区逐个建索引避免所有分区同时被锁。4. 增删分区的标准操作从建表到换掉的完整动作拆解4.1 初始建表和提前预创建分区先看声明式分区的基本建表语句CREATE TABLE orders ( order_id bigint NOT NULL, order_time timestamptz NOT NULL, customer_id bigint, status text ) PARTITION BY RANGE (order_time);然后创建第一个分区CREATE TABLE orders_2025_01 PARTITION OF orders FOR VALUES FROM (2025-01-01) TO (2025-02-01);声明式分区的边界遵循左闭右开原则也就是 起始值 AND 结束值。这个边界语义要记清楚否则容易在月末月初的数据归属上出问题。手动一个个建未来几个月的分区太蠢了。我习惯用 generate_series 批量生成 DDL 语句然后执行。比如一次性创建全年按月分区SELECT format( CREATE TABLE orders_%s PARTITION OF orders FOR VALUES FROM (%L) TO (%L);, to_char(gs, YYYY_MM), gs, gs interval 1 month ) FROM generate_series(2025-01-01::date, 2025-12-01::date, interval 1 month) gs;实际使用时根据分区键类型调整边界值的写法。时序系统的通用做法是预创建未来 3 到 6 个月的分区这样即使业务突发插入一个未来的时间点也不会因为找不到分区而报错。4.2 附加、分离和删除分区的正确姿势日常运维里给父表挂上一个新分区用 ATTACH PARTITIONALTER TABLE orders ATTACH PARTITION orders_2025_06 FOR VALUES FROM (2025-06-01) TO (2025-07-01);这里值得提醒的是PostgreSQL 在附加分区时会校验新表数据是否符合边界。如果这个表已经不是空表校验过程中需要扫描数据锁的开销会比想象中大。所以在附加一张大表之前最好确认数据已经完全符合边界并且预留足够的维护窗口。分离分区用 DETACH PARTITION。分离并不删除数据子表变成一个独立存在的普通表这是归档最常用的路径-- PG14 之前会长时间锁表 ALTER TABLE orders DETACH PARTITION orders_2024_12; -- PG14 推荐并发方式不影响业务读写 ALTER TABLE orders DETACH PARTITION orders_2024_12 CONCURRENTLY;删除过期分区则更简单直接但也很危险一个DROP TABLE下去数据就没了。我习惯先确认分区范围和业务保留策略再执行DROP TABLE orders_2024_01;需要注意DROP 父表会级联删除所有子分区这个操作在生产环境几乎不能碰。如果父表误删所有分区数据一起没了恢复极其麻烦。平时做权限控制时最好把父表的 DROP 权限收掉。4.3 让 pg_partman 自动接管分区生命周期人工维护分区在分区数量少的时候还能撑住一旦表多了每天检查有没有未来分区、有没有过期分区会变成一种折磨。我在生产环境里常用 pg_partman 这个扩展来做自动化管理。安装并创建扩展之后调用 create_parent 把目标表交给它CREATE EXTENSION pg_partman; SELECT partman.create_parent( public.orders, order_time, native, monthly );然后通过更新 part_config 配置保留周期比如现场保留最近 12 个月历史分区可以自动归档或删除UPDATE partman.part_config SET retention 12 months, rollback_interval 3 months WHERE parent_table public.orders;最后用运维调度任务定期执行维护函数。可以用 PostgreSQL 里的 pg_cron 扩展也可以用系统 crontab 调用 psqlSELECT partman.run_maintenance(public.orders);跑起来之后新分区的创建、过期分区的保留策略都由 pg_partman 接管不需要再手动盯着月份边界发呆。如果没有条件装扩展退而求其次写一个每月 1 号执行的脚本自动建未来三个月分区、删掉保留期之外的分区也完全可行。5. 裁剪机制与执行计划分区表查询变快的真正来源5.1 分区裁剪在什么时候生效什么时候失效分区表的性能优势全部建立在分区裁剪上。裁剪分为两个阶段一个是生成执行计划时根据条件排除分区另一个是执行阶段根据参数实际排除分区。生效的条件说起来很简单查询条件里包含分区键并且优化器有能力判断这个条件只可能命中某几个分区。但实际项目里翻车的情况特别多。除了前面提到的函数包裹分区键还有像order_time::date current_date这种写法虽然逻辑上等价但优化器不一定能把它转换成安全的边界条件裁剪效果往往不如直接给范围条件可靠。如果分区键是 timestamptz查询条件里最好保持同类型比较。比如-- 明确边界稳定裁剪 WHERE order_time 2025-01-01 00:00:0008 AND order_time 2025-02-01 00:00:0008反过来一条不带分区键条件的查询在分区表上的表现往往比单表更差。因为优化器会把所有分区的数据规划成一个 Append 结果集每个子分区都要扫一遍再合并。这种查询本来在大表上就慢拆完之后只会更慢。所以分区表上线前必须梳理一遍现存 SQL哪些语句强制带上了分区键哪些没有。没有带分区键的查询要么改造要么明确接受全分区扫描的成本。5.2 UPDATE 和 DELETE 的成本经常被低估跨分区移动是隐藏炸弹很多人做分区表只关心 SELECT 查询等到上线才发现 UPDATE 和 DELETE 才是性能黑洞。范围 UPDATE 如果没有带分区键条件和全表扫描没区别而且每个分区都要扫。DELETE 也一样删除过期数据时直接 DROP 分区快得多但如果应用里用 DELETE 逐条删那效率惨不忍睹。更麻烦的是跨分区更新。比如一条订单记录因为某种原因要修改 order_time更新后这条数据不再属于原来的分区。PostgreSQL 会先把旧元组标记删除再把新元组路由到目标分区插入。这个操作本质上是旧行删除 新行插入会同时修改两个分区上的索引还可能触发外键检查和可见性判断。遇到这种情况如果业务能接受最直接的办法是禁止修改分区键。实在需要修改最好用先 DELETE 再 INSERT的方式显式处理避免让数据库在 UPDATE 语义里做隐式跨分区搬移。批量更新分区键更是大忌一旦发生IO 和锁都会飙升业务侧会有明显抖动。5.3 统计信息与 autovacuum分区多了要重新设计运维节奏分区表本质上是一堆独立的小表所以统计信息和清理也是按分区独立进行的。对父表执行 ANALYZE 会递归分析所有分区当分区数量很大时这可能会变成一个耗时很长的全库操作。我通常只对近期发生过大量数据变动的分区执行 ANALYZEANALYZE orders_2025_01;每天批量导入完成之后对当天对应的分区单独做一次 ANALYZE成本低收益直接。autovacuum 方面分区多了以后要特别小心并发清理风暴。几十个分区同时到达 autovacuum 触发阈值数据库会同时启动多个 worker 去清理IO 很容易被打满。建议通过调整每个分区的 autovacuum 阈值或者把批量导入和 autovacuum 的触发错开。对于某些经常更新的热分区也可以单独设置更积极的清理参数。6. 归档、锁与长期维护分区表运行半年后才知道的事6.1 一套稳健的冷数据归档流程分区表上线半年后最舒服的一件事就是归档不再依赖 DELETE。删一张大表里的旧数据即使有索引也要产生大量 WAL 和死元组而 DROP 一个分区是直接把整个数据文件丢掉几乎不产生垃圾更不需要 VACUUM。我惯用的归档流程是这样的先 DETACH 最早一个月分区它变成一个独立表接着把它移动到专门的归档表空间或者归档库如果是再也用不到的数据直接 DROP 或者转存到数仓后 DROP如果需要支持历史查询保留独立表应用侧通过视图或者查询路由把在线父表和归档表合并起来访问。实际操作时有一个细节要注意DETACH 之后独立表仍然保留原表名如果后续还要处理同一月份的数据很容易产生这个表到底还挂不挂在父表下的混淆。我的习惯是分离后立刻把表名改成带_archive后缀的命名方式避免后续脚本误操作。6.2 DDL 的锁行为什么操作必须在低峰期做分区表运维里锁问题是最容易给业务带来惊吓的环节。ATTACH PARTITION 时数据库要检查新分区数据是否满足边界这个过程在大数据量下可能持有较重的锁。所以我的原则是涉及大数据量分区的 ATTACH一律放到低峰期执行预创建空分区则很轻量白天执行问题不大。DETACH 在 PG14 之前同样很重所以前面才反复强调生产环境尽量上 PG14 以上版本。PG14 的并发 DETACH 也要注意它是分两个阶段完成的如果中间因为故障中断Poll 里会留下一个 pending 状态的分离操作需要执行ALTER TABLE orders DETACH PARTITION orders_2024_12 FINALIZE;这个细节很多人不知道第一次遇到时会以为把表搞坏了。其实只要补一个 FINALIZE 就能收尾。6.3 空间膨胀和日常巡检的几个小习惯分区表不会天然免于膨胀特别是数据频繁更新或者大量 DELETE 的分区。膨胀到一定程度及时用 VACUUM FULL 或者 pg_repack 处理单个分区绝对不要对父表直接做整表级 VACUUM FULL。父表本身没有存储数据对父表执行 VACUUM FULL 不仅没有用还可能让所有分区一起被处理长时间持锁风险很大。日常巡检我建议关注三样东西一是每个分区的大小和增长趋势预判未来一个月是否需要新增分区二是最近分区的索引大小是否突然膨胀需要 REINDEX三是默认分区的大小如果默认分区持续变大说明边界设计有问题数据没有正常路由到对应分区要赶紧查边界和分区键。6.4 一张表收下我这些年踩过的主要坑现象根本原因我的解法INSERT 报no partition of relation found数据落到了所有分区边界之外预创建足够未来分区或启用默认分区兜底查询很慢且 EXPLAIN 显示扫描了所有子分区条件里没有分区键或对分区键用了函数/隐式转换改写条件为裸分区键范围比较父表建唯一约束失败声明式分区唯一约束必须包含分区键联合唯一索引业务改用全局唯一 IDATTACH 大表分区时锁表很久数据校验需要扫描全部分区数据提前确认数据边界低峰执行DETACH CONCURRENTLY 之后表状态异常并发分离中途中断进入 pending 状态执行 FINALIZE 收尾默认分区越来越大边界没覆盖住新数据定期检查 default 分区及时补正式分区并把默认分区数据迁走大量分区同时跑 autovacuumIO 飙高各分区清理阈值同时被触发错峰批量导入按分区单独调整 autovacuum 参数分区表用到现在我的体会是它最大的价值不只是查询变快而是让数据生命周期管理变得可预期。再大的表只要分区设计合理、自动化到位归档像切豆腐一样干净利落根本不需要在业务高峰期去赌 DELETE 的性能。如果你正好在规划一张可能膨胀到千万级以上的表我的建议是别犹豫早点把分区键嵌进业务查询习惯里未来你会感谢现在的这个决定。