这个标题我相信所有PostgreSQL的运维和老开发看了都会心一笑——不是因为它简单，恰恰是因为这个功能看着入门简单，真正上了生产环境、扛住真实业务流量之后，各种问题才开始浮现。我手上负责的某电商订单系统，订单流水表一度涨到了两千多万行，单表查询从毫秒级退化到秒级，夜间批量任务跑起来能把IO打满，后来花了两周时间把核心流水表改成分区表，才把这口气喘过来。
这篇文章就围绕这个真实改造过程展开。我会讲清楚当时为什么选分区表而不是分库分表，分区方案具体怎么设计，迁移数据时哪些步骤不能省，上线后又踩了哪些坑，以及分区表的日常维护到底怎么做。不写空泛的理论，全程都是可以直接抄作业的SQL、参数和排查思路。
1. 一张日增3万行的订单流水表，是如何一步步“变卡”的
先说一下当时的业务背景。项目是一个标准的分销型电商系统，用户下单后会生成订单主记录和一系列流水记录：支付回调、退款、售后状态变更、风控日志，全部落在同一张名为sim_order_log的表里。
这张表早期的结构其实没什么问题：
CREATE TABLE sim_order_log ( id BIGSERIAL PRIMARY KEY, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, biz_type SMALLINT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), payload JSONB, status SMALLINT NOT NULL DEFAULT 1 ); CREATE INDEX idx_order_log_user_created ON sim_order_log (user_id, created_at DESC);流量没起来之前，一切都很正常，查询走索引，响应在几十毫秒级别。但业务跑了一年半之后，这张表的数据量稳定在了2200万行左右，加上JSONB字段和索引，表空间加索引空间接近6GB。问题开始集中爆发：
- 订单详情页要查某个订单的所有流水，单条SQL本身不复杂，但走
idx_order_log_user_created时索引深度增加，随机IO变多，接口P99从80ms涨到了1.2秒。 - 每天凌晨的归档任务要把超过90天的流水搬到历史库，用的是
DELETE FROM sim_order_log WHERE created_at < now() - interval '90 days'，这条语句高峰期能跑40分钟，期间产生大量WAL日志，还和业务写入抢IO。 - 因为高频的插入和删除，表膨胀明显，autovacuum 经常跑不完，手动 vacuum 又会锁表，导致夜间告警不断。
- 索引维护成本越来越高，每次
REINDEX都是对线上的一轮冲击。
其实到这个时候，团队里讨论过几个方案：一是上分布式数据库，迁移成本太高，得改数据访问层；二是做分库分表，但中间件带来的复杂度对一个小团队并不友好；三是继续扛着，定时清理旧数据。最后还是决定，先用PostgreSQL原生分区表，把这张表内部拆开。原因很简单：大部分慢查询都带有created_at时间条件，而旧数据归档本质是删除时间较早的行，分区表在这两个场景下都有天然优势。
2. 分区方案设计：为什么选范围分区，分区键和粒度怎么定
选型阶段，我们把分区方案的核心问题拆成了三个：分区类型、分区键、分区粒度。
2.1 业务查询模式决定了必须用范围分区
PostgreSQL 从10版本开始原生支持声明式分区，分区类型包括范围分区、列表分区和哈希分区。我们业务上的所有高频查询，几乎都带时间窗口：查某个用户的近期流水、统计某天的支付成功率、跑凌晨归档……
这类“按时间段圈数据”的查询，最适合范围分区。每月的订单流水落入一个独立分区，查询时优化器根据created_at条件直接裁剪掉无关分区，只扫目标分区，这就是分区裁剪的核心收益。
哈希分区当时也考虑过，但它适合按某个键分散写入的场景，比如把数据平均打散到多个物理分区。我们的数据写入本来就是顺序增长的，哈希分区不仅没法优化时间范围查询，还会让“查单个月数据”变成“扫全部哈希分区”，属于反向优化。
列表分区更不用说，业务上没有明显的枚举维度和数据生命周期绑定，直接排除。
2.2 分区键不能拍脑袋，我踩过一次时间格式的坑
分区键的选择上，最稳妥、最贴合业务查询的就是created_at。但这里有一个非常容易被新手忽略的细节：我们表里的created_at是TIMESTAMPTZ，不是TIMESTAMP。
TIMESTAMPTZ存的是带时区的时间，实际存储是以UTC形式保存的。如果分区函数直接写成to_char(created_at, 'YYYYMM')，在时区偏移的影响下，某些凌晨的订单会被划分到前一个分区，读取的时候用本地时间边界去查，就会发生“数据确实存在，但查不到或者落在默认分区”的诡异问题。
我们的做法是统一在分区函数里做时区归一化：
to_char(timezone('UTC', created_at), 'YYYYMM')所有写入和查询的SQL都强制走同一个函数，保证分区判定逻辑一致。后来规范里也定了死规矩：涉及分区键的SQL，不允许直接裸用created_at做比较后再让应用层去拼分区名，统一走触发器或应用层的时间工具类。
2.3 按月分区是“细粒度”和“分区数量”的平衡点
分区粒度上，我们测试过按周、按月、按季度三种方案。
按周分区，一年52个分区，好处是删除旧数据时DROP PARTITION的粒度更细，坏处是分区数量增长快，PG的系统目录和统计信息负担变大，而且很多报表查询是按月统计的，跨分区查询要拼接好几个分区，性能反而不如按月。
按季度分区，分区数量少，但单分区数据量仍然过大，删除一个季度的分区相当于一次性删掉几百万行，灵活性不够。
最后定了按月分区。每个分区约90万到120万行，单个分区文件大小控制在几百MB，索引查询、vacuum、归档都很舒服。预创建策略上，我们每次提前创建未来3个月的分区，避免12月31日晚上跨年时应用还在往12月分区写数据、凌晨任务又去建1月分区的竞态问题。
2.4 声明式分区和“继承式分区”的取舍
说句实话，PG10之前的老项目里大量使用的继承式分区，我也维护过。通过INHERITS创建子表、用触发器把数据分发到各子表、查询时靠constraint exclusion实现裁剪，这套东西能用，但有一个致命弱点：父表上的约束和索引不会自动同步到子表，每次加索引都要手动给所有子表执行一遍，漏一个子表就是一次事故。
PG10以后的声明式分区，父表和子表的关系由内核管理，索引、约束、默认值都可以通过父表统一下发，而且ATTACH/DETACH可以在线操作，不用长时间锁表。所以我们的方案毫不犹豫地用了声明式分区。
3. 分区改造的完整实施步骤：从建父表到回填数据
这一节是全文最“实操”的部分。我把当时执行的步骤按顺序列出来，每一步都附了理由和注意事项，确保你照着做不会中途卡壳。
3.1 第一步：重建为分区父表
我们迁移策略不是ALTER TABLE直接把普通表转成分区表（早期版本不支持这种在线转换），而是新建一张分区父表，然后数据回填，再切换应用。父表定义如下：
CREATE TABLE sim_order_log ( id BIGSERIAL NOT NULL, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, biz_type SMALLINT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), payload JSONB, status SMALLINT NOT NULL DEFAULT 1 ) PARTITION BY RANGE (timezone('UTC', created_at));注意几点：
- 原表的
PRIMARY KEY没有直接搬过来。因为PostgreSQL要求，分区表的唯一约束必须包含分区键。如果把id直接设为唯一约束，而id不是分区键，会直接报错。所以主键改成了(id, created_at)联合约束，这在应用层没有任何影响，因为我们所有的查询都是带时间条件的。 - 分区键用了
timezone('UTC', created_at)表达式。PG11及以下不支持表达式作为分区键，当时我们线上的版本是PG12，所以没问题。如果你还在PG10，需要用created_at TIMESTAMPTZ NOT NULL直接做分区键，但要提前在应用层把时间统一为UTC。
3.2 第二步：建立各个月份分区
我们手工创建了一张模板SQL，每个月自动生成并执行：
CREATE TABLE sim_order_log_202401 PARTITION OF sim_order_log FOR VALUES FROM ('2024-01-01 00:00:00+00') TO ('2024-02-01 00:00:00+00');起点和终点都是UTC时间。这样就保证任何时区的客户端写入，只要底层值在区间内就能落到正确分区。
同时创建了一个默认分区，用来接住因为各种原因没有跑到正确分区去的“流浪数据”：
CREATE TABLE sim_order_log_default PARTITION OF sim_order_log DEFAULT;这个默认分区是我强烈建议创建的，否则一旦有程序没用统一时间函数、或者时间边界计算错误，插入时会出现“no partition of relation found for row”的报错。虽然默认分区会导致数据悄悄堆在不该在的地方，但至少不会阻断写入。配合监控定期检查默认分区行数，能第一时间发现Bug。
3.3 第三步：创建分区自动创建函数
如果每次都要手工建分区，运维会崩溃。我写了一个简单的自动建分区函数，供定时任务调用：
CREATE OR REPLACE FUNCTION auto_create_log_partition(target_date date) RETURNS void AS $$ DECLARE partition_name text; start_date text; end_date text; BEGIN -- 目标日期的下个月 partition_name := 'sim_order_log_' || to_char(target_date, 'YYYYMM'); start_date := to_char(target_date, 'YYYY-MM-01'); end_date := to_char(target_date + interval '1 month', 'YYYY-MM-01'); IF NOT EXISTS ( SELECT 1 FROM pg_class WHERE relname = partition_name ) THEN EXECUTE format( 'CREATE TABLE %I PARTITION OF sim_order_log FOR VALUES FROM (%L) TO (%L)', partition_name, start_date || ' 00:00:00+00', end_date || ' 00:00:00+00' ); END IF; END; $$ LANGUAGE plpgsql;函数逻辑很简单：根据传入日期生成下个月分区名和时间边界，如果表不存在就创建。后面接上pg_cron，每月1号和每月15号各执行一次，预创建未来3个月的分区。
3.4 第四步：回填历史数据
这一步是整个迁移里最磨人的。2200万行数据，如果用一条INSERT INTO sim_order_log SELECT ... FROM 旧表一把梭，会带来两个问题：
- 大事务会锁表，业务写入被迫长时间等待；
- 产生的WAL量巨大，主从同步延迟会拉到分钟级以上。
我们的做法是按天切片，分批回填。每天大概3万行，每批最多更新5万行，然后提交一次。用一个循环脚本控制：
-- 伪代码，实际通过脚本循环执行 SELECT min(created_at), max(created_at) FROM legacy_sim_order_log; -- 对每个日期 d： INSERT INTO sim_order_log (id, order_no, user_id, biz_type, created_at, payload, status) SELECT id, order_no, user_id, biz_type, created_at, payload, status FROM legacy_sim_order_log WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02' ORDER BY id;每批之间间隔1秒到2秒，给主库喘息的时间。同时把归档任务的夜间时间段和回填时间错开。回填过程中我们实时观察主从延迟和IO等待，整体跑了一天半，没有出现长时间锁表问题。
3.5 第五步：统一创建索引
父表创建完后，在父表上统一创建索引，所有现存分区和未来新分区都会自动带上：
CREATE INDEX idx_order_log_user_created ON sim_order_log (user_id, created_at DESC); CREATE INDEX idx_order_log_order_no ON sim_order_log (order_no); CREATE INDEX idx_order_log_biz_type ON sim_order_log (biz_type);这里特别强调一点：CONCURRENTLY关键字不能用在分区父表上。我当时为了不锁写入，试过CREATE INDEX CONCURRENTLY，结果PG直接报错，说不支持在分区父表上并发建索引。解决办法是先建父表索引（短暂锁表，因为当时回填还没开始，影响可控），后续子表如果需要单独加索引，再用CONCURRENTLY落在具体子表上。
3.6 第六步：切换应用写入路径
索引建好、数据回填完成之后，就是应用层切换。我们的做法是：把sim_order_log改名为sim_order_log_legacy，新表命名sim_order_log。应用层代码一行不改，因为表名没变，只是底层变成了分区表。
切换前我特意观察了旧表上的序列（sequence）值：
SELECT last_value, max(id) FROM sim_order_log_legacy_seq, sim_order_log_legacy GROUP BY last_value;要确保新表里的BIGSERIAL序列起点大于旧表最大ID，否则插入时主键冲突。一般直接：
SELECT setval('sim_order_log_id_seq', (SELECT max(id) FROM sim_order_log_legacy));这一步忘了做的话，线上开工第一天就会爆主键冲突。
切换后立刻执行验证SQL：
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM sim_order_log WHERE user_id = 12345 AND created_at >= '2024-01-01' AND created_at < '2024-02-01';看到的结果是：Seq Scan on sim_order_log_202401或Index Scan on idx_order_log_user_created，确认分区裁剪生效，没有走全分区扫描。
4. 改造后的真实收益：三个维度的数据对比
分区表上线后，我记录了两个月的运行数据。这里不说“感觉快了”这种话，直接看数字。
4.1 旧数据归档：从40分钟降到秒级
这是分区表带来的最直接、最夸张的收益。以前归档90天前的数据，靠的是DELETE一条条清理，现在直接：
ALTER TABLE sim_order_log DETACH PARTITION sim_order_log_202312;把整个12月分区分出去，然后在低峰期DROP TABLE或转成归档表。单次操作秒级完成，不产生海量WAL，不会拖垮主库。12月分区大约100万行，对比一下：
| 操作方式 | 耗时 | WAL产生量 | 锁表情况 |
|---|---|---|---|
| DELETE FROM ... WHERE created_at < ... | 40分钟 | 极大，主从延迟升高 | 长时间持有行锁 |
| DETACH + DROP PARTITION | 不到1秒 | 极小 | 短暂锁表 |
从这以后，归档任务从“每天夜间高危操作”变成了“定时分区摘除”，再也没因为清理旧数据出过事故。
4.2 典型查询：P99从1.2秒降到150毫秒
订单详情页要查询某个用户最近半年的流水，SQL条件同时包含user_id和created_at。分区前扫的是一个6GB的单表B树，分区后裁剪到了6个月份分区，实际扫描的数据量只有原来的四分之一左右，P99从1.2s降到150ms。
没带created_at条件的查询呢？我专门测过，性能反而会下降，因为优化器要扫描所有分区，再UNION结果。这类查询在业务里其实很少——几乎所有对流水表的访问接口都带时间范围，但我们还是排查了一遍所有SQL，发现有三条后台统计SQL确实没带时间条件，后来统一在应用层加了默认近30天的时间窗口，才算把隐患清完。
4.3 维护负担：vacuum不再“永远跑不完”
普通单表在2200万行的时候，autovacuum 跑一圈要好几个小时，期间还可能因为事务ID回卷问题产生告警。分区之后，每个分区只有100万行左右，autovacuum 对每个分区的处理时间降到了十几分钟级别，而且分区可以错峰清理，不会出现“一个大表卡住所有vacuum worker”的情况。
这个收益在日常看起来不起眼，但对长跑业务来说，它决定了系统能不能稳定运行一年不出幺蛾子。
5. 上线半年后，我们踩过的五个大坑
前面都是在讲怎么把分区表做对、做出收益。下面这部分是我认为最值得分享的实战排错经验，毕竟纸上谈兵谁都会，真实环境里的坑才见功力。
5.1 默认分区成了垃圾桶，导致新分区无法挂载
上线的第二个月，我们的监控脚本发现，默认分区sim_order_log_default的行数涨到了40万，而最新的业务分区只有20万。这个数据分布明显不正常。
排查过程是这样的：
- 先查默认分区里数据的
created_at范围，发现大部分数据是最近三天的，而且集中在凌晨。 - 去看应用的日志，发现一个定时任务在跑数据对账，它对
sim_order_log执行插入时，时间字段是upstream传来的字符串，但格式是2024-03-01 08:30:00这种没有时区标记的时间。 - 我们的分区函数按UTC转换，而这个字符串被PG默认按数据库时区解析成了本地时间，于是永远差了8小时，凌晨的数据就落到了前一天的分区边界之外，全被默认分区接走了。
更麻烦的是，因为有数据卡在默认分区，我们想给下个月挂新分区时，PG报错：
ERROR: partition constraint would be violated by some row原因很清晰：默认分区里已经有值落在新分区的范围内，挂载新分区时会冲突。当时我们花了20多分钟才把默认分区里有问题的数据挪回到正确分区，同时修掉了应用层的时间格式Bug。
提示：默认分区不是安全屋，它是“你没把事情做对的最后一道防线”。必须每天都检查默认分区行数，一旦有数据积累，先别急着挂新分区，要先把数据归位。
5.2 唯一索引必须包含分区键，PG在这个问题上一视同仁
有一次开发同事提了一个需求，要给order_no加唯一约束，结果建索引的时候直接报错：
ERROR: unique constraint on partitioned table must include all partitioning columns这个限制很容易被忽略。原因是分区表里，PG只能保证每个分区自己内部唯一，但无法跨分区检查全局唯一。如果唯一键里没有分区键，那两个不同分区里出现相同order_no就必须靠查全表才能发现，这在分布式体系里是一个标准难题。
我们的解决方案是业务层面妥协：唯一性约束从数据库层面减少到应用层保证，给order_no加了普通索引，然后由订单号生成规则保证唯一（前缀包含日期+随机序列）。如果你必须做数据库级全局唯一，那就得把表设计成以业务键为分区键，或者用全局唯一索引方案（比如在分区父表上建基于order_no的表达式索引）——注意PG12的全局唯一索引支持是有限的，我们最终没有走这条路。
5.3 分区键用表达式后，查询条件也必须跟着用表达式
这是一个特别隐蔽的性能坑。
我们父表的分区键是timezone('UTC', created_at)，PostgreSQL的分区裁剪需要优化器能从查询条件里推导出分区范围。如果业务SQL写的是：
WHERE created_at >= '2024-01-01'那么优化器需要做等价推导，实际上在PG12里，这种推导对于表达式分区键的支持并不好，结果是查全表所有分区。
后来我们在应用层强制做了时间归一化：所有SQL条件里统一改写成：
WHERE timezone('UTC', created_at) >= '2024-01-01 00:00:00+00'改写后，EXPLAIN 才正确显示裁剪到对应月份分区。
不同PG版本对表达式分区键的裁剪能力差别很大，PG14以后好了很多，但你在10到12版本上做生产环境，最稳妥的做法还是避免表达式分区键，直接用原始时间列比较。我们当时为了省事用了表达式，后来发现查询要处处留意，属于典型的“图省事反而更费事”。
5.4 UPDATE跨分区移动数据时，必须先开启行移动
分区表的一个冷知识：如果你更新某行数据，导致它从分区A跳跃到分区B（跨分区移动），PostgreSQL会尝试删除原行并插入到新分区。但默认情况下这个操作被禁止，并报错：
ERROR: updated partition key column does not qualify as an allowed partition key change我们的流水表严格来说不应该出现跨分区的created_at更新，但有后台逻辑会“修正”一些脏数据的订单状态和时间戳，结果一执行就报错。
解决办法是在表上启用行移动：
ALTER TABLE sim_order_log ENABLE ROW MOVEMENT;开启后，跨分区更新会被PG内部自动转换为DELETE+INSERT，代价是性能略低且有极小概率的失败窗口。我们只在确有必要的情况下开了，并且做了监控，避免高频跨分区更新拖垮性能。
5.5 大批量迁移后，分区统计信息不准导致执行计划恶化
数据回填完毕后，我们没有立刻跑ANALYZE，结果当天出现了一个奇怪现象：某些分区明明数据量很大，优化器却选了全表扫描，而且估算行数严重偏低。
原因很简单：数据是批量灌进去的，pg_statistic统计信息还停留在旧状态，优化器拿着过时的信息做决策，执行计划自然一塌糊涂。
解决方法是全库ANALYZE：
ANALYZE sim_order_log;它会自动对每个分区重新统计。此后我总结经验：任何大动作（批量导入、大批量删除、DETACH再ATTACH）之后，第一件事就是ANALYZE，否则再好的分区设计也会被烂执行计划拖垮。
6. 分区表日常维护：定时任务、监控与事故防范
分区表不是建成之后就一劳永逸，它是需要“养”的。下面是我们运行稳定后的日常维护清单。
6.1 用pg_cron做三类定时任务
我们引入了pg_cron扩展来做自动维护。三个任务缺一不可：
- 每月25号调用
auto_create_log_partition，创建下下个月的分区。 - 每月1号检查所有分区，确认没有“时间断层”（比如漏建了一个月）。
- 每天凌晨检查默认分区行数，如果超过阈值（我们设定1万行），发告警。
pg_cron的配置很简单：
CREATE EXTENSION IF NOT EXISTS pg_cron; SELECT cron.schedule( 'create-partition-monthly', '0 3 25 * *', $$SELECT auto_create_log_partition(current_date + interval '2 months')$$ );注意一点：pg_cron默认只能调度数据库内部函数，如果你的分区创建逻辑还涉及外部通知，建议用系统 crontab 调用脚本，两边结合。
6.2 分区生命周期管理：留存期定了18个月
我们的订单流水只需要保留18个月，超过的直接DETACH然后删除。这个策略涉及两步：
-- 第一步：摘除 ALTER TABLE sim_order_log DETACH PARTITION sim_order_log_202305; -- 第二步：确认无业务访问后，DROP或归档 DROP TABLE sim_order_log_202305;这里有个小细节：DETACH之后表仍然物理存在，数据不会被删，但已经脱离了父表管理。要彻底删除再DROP。如果有审计需求，不DROP而是转存到历史库，就把DETACH的分区直接移动到别的表空间或转成普通表，也很灵活。
提示：
DETACH操作会短暂锁父表。如果你的分区特别大，DETACH会需要重写分区上的约束验证，耗时可能从毫秒到秒级。最好放在业务低谷期执行，不要高峰期裸奔。
6.3 监控SQL：每季度做一次分区健康体检
我习惯写一套SQL定期检查分区表的健康状态，这里分享几个核心的：
-- 查询每个分区的行数和大小 SELECT child.relname AS partition_name, pg_total_relation_size(child.oid) AS total_bytes, pg_stat_get_live_tuples(child.oid) AS live_tuples FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid WHERE parent.relname = 'sim_order_log' ORDER BY child.relname;-- 检查默认分区是否有数据（重点监控） SELECT count(*) FROM sim_order_log_default;-- 检查最近3个月的分区范围是否覆盖当前时间 SELECT relname, pg_get_expr(relpartbound, oid) AS bound_expr FROM pg_class WHERE relispartition = true AND relname LIKE 'sim_order_log_%' ORDER BY relname DESC LIMIT 5;这套SQL每季度跑一次，能发现很多潜在问题：分区大小严重不均衡、默认分区异常、漏建分区、某个分区膨胀等。配合一个简单的Shell脚本+企业微信告警，基本能做到“问题出现在别人报警之前”。
6.4 分区表函数调用顺序对写入性能的影响
最后说一个优化细节。分区表在写入时，PG需要根据分区键计算目标分区。如果分区函数是表达式，那么每次INSERT都要先做表达式计算，虽然一次计算开销很小，但高频写入下会有可感知的CPU消耗。
我们的流水表峰值写入达到每秒2000行，单次表达式计算微秒级，但累计下来大约增加了5%到8%的CPU开销。后来优化方式是在应用层直接计算出目标月份，SQL里直接指定要插入的分区表名，绕开了PG的分区路由。当然，这对代码有侵入性，我们只对最核心的两张流水表做了，收益是CPU降低，写入P99也更平稳了。
最后分享一点个人的实际体会
分区表这套方案，我到现在已经维护了一年多。最大的感受不是“快”——快是意料之中的——而是整个运维节奏变得可预期了。以前最怕夜里收到清数据任务卡死的告警，现在定时任务就是摘分区，失败了大不了手动再摘一次，风险和焦虑感完全不在一个量级。
如果你也准备做分区表改造，我的建议是：先别急着上生产，找一张业务上“查询必带时间条件、数据可定期淘汰”的表，模拟两三个月的数据量，把迁移脚本、自动建分区函数、监控SQL全部跑通，踩一遍本文提到的这些坑，然后再动核心表。PG的声明式分区本身并不复杂，复杂的是你对自己业务数据访问模式的理解。把这个想透了，分区表才会真正变成你的利器，而不是新的麻烦来源。
