单表破亿，查询慢到报警，这场景我在不止一个项目里见过。最开始可能只是运营那边发一句“报表跑不出来了”，后来越来越频繁，慢查询日志里全是那张表，数据库CPU居高不下，磁盘IO一直打满，索引加了几个，收效却微乎其微。如果你也遇到过这种“加索引也没救”的阶段，我建议先停一下，别急着升级硬件，也别想什么分库分表，先看看手上的 PostgreSQL 能不能用分区表把问题解决掉。
PostgreSQL 分区表不是一个新特性，但从 10 版本开始声明式分区才算真正好用，到了 11、12 之后基本可以无脑上生产。核心思路其实不复杂：把一张上亿行的大表，按照某个字段拆成若干张小表，查询时只访问需要的那张或者那几张，数据量从“一个亿”降到“几百万”，性能自然就回来了。
这篇文章我会从为什么要分区、分区怎么设计、存量数据怎么迁移，再到实际踩坑和排查技巧，完整走一遍。适合正在处理单表数据量爆炸的开发者或DBA，也适合还没遇到瓶颈但想提前做规划的同学。
1. 内容整体设计与思路拆解
1.1 单表1亿行到底慢在哪里
不少人有个误解，觉得“数据量大了，加了索引就该快”。但一亿行这种量级，问题往往不只是索引本身能解决的。
首先是索引体积。你去查一个亿行的表，即使走了索引，索引本身也得有几百MB甚至上GB，这已经超出了内存中可缓存的合理范围，数据库只能大量走随机磁盘IO，性能直接受限于磁盘寻道时间。SSD还好点，机械盘基本就是灾难。
其次是统计信息失效。PostgreSQL 的查询优化器依赖表的统计信息来估算返回行数。一亿行的大表，数据分布可能很不均匀，而 autovacuum 的采样对超大表来说往往不够精确，优化器就很容易估算错，选了错误的执行计划。明明一个只该扫几十万行的查询，它可能选择了全表扫描，那能不慢吗？
再有就是写放大和锁竞争。一亿行的表，更新和删除带来的 dead tuple 量非常大，vacuum 要处理的时间更长，表膨胀的速度也会比较快。而且单表上的大量并发写入和 DDL 操作，会让锁冲突更严重。这些问题叠加在一起，表现在应用层就是：查询慢、写入慢、有时候一个 ALTER TABLE 卡半天。
1.2 为什么分区表和索引优化不冲突
很多人的第一反应是“我已经加了复合索引，还有必要分区吗？”这两件事解决的是不同层面的问题。
索引优化解决的是“在单次查询中减少扫描行数”，而分区表解决的是“把大象关进小房间”。分区之后，每个分区的数据量都会小很多，索引体积也随之变小，缓存命中率明显提升，查询自然更快。而且分区还有一个额外好处——分区裁剪。你查某一天的数据，优化器直接就能定位到对应分区，那个分区甚至可能只有几百万行，这时候即使走全表扫描也很快。
所以本质上，分区表和索引不是二选一的关系。分区是数据组织结构的调整，索引仍然要建，二者协同工作才能把性能发挥到极致。
比如一个订单表，按月份分成12个分区，每片大概800万行，还能单独给每个分区建索引，整个索引体积是原来的十二分之一，缓冲命中率上来了，查询计划的准确性也上来了，整体效果非常明显。
1.3 分区表如何影响应用层和运维层
分区表不仅对查询性能有帮助，对运维也是有直接好处的。
比如删旧数据，如果不用分区表，你想删掉三年前的数据，那就是一个几千万行的 DELETE，光事务持续时间就可能拖垮主库。用了分区表之后，直接 DROP TABLE 或者 DETACH 掉一整年的分区，秒级完成，而且不会产生大量死元组，也不会给 autovacuum 造成压力。
不过也要提醒一点：分区表不能解决所有问题。如果你的业务查询条件里根本没有分区键，那分区裁剪机制就派不上用场，优化器还是会扫描所有分区。这也是使用分区表之前必须想清楚的事情——分区键必须和真实查询模式对齐。
2. 核心参数解析与分区方案设计
2.1 分区键如何选
分区键的选择是所有设计的根基，选错了整个方案基本就废了。
从实际经验看，最常用、也最稳妥的是时间字段。业务数据天然带有时间维度，很多慢查询都按时间范围走，而且时间字段单调递增，写数据时能均匀分布到各分区，不会出现某几个分区特别大、其他分区特别小的情况。
如果一个表的核心查询条件都是某个业务ID，比如用户ID，那就得考虑用哈希分区。PostgreSQL 11 之后支持 HASH 分区，可以把行均匀打散到不同分区，保证每个分区的数据量差不多。但注意，哈希分区没法做范围裁剪，你查一个用户的数据，还是要遍历所有分区才能找到数据，它的收益主要在于“把大索引变小”以及“让并行处理更高效”。
我还见过一种方案，就是分区键和主键搞混了。这里必须强调：分区键不一定要是主键，但如果你在分区表上建主键或者唯一约束，那么约束的字段里必须包含分区键。比如按月分区的账单表，主键是“用户ID+账单ID”，这就不行，因为这里没有分区键，需要改成“账单ID+账单月份”或者干脆用 sequence 作为全局唯一主键。
2.2 分区粒度怎么定：日、周、月还是年
分区粒度是个典型的取舍问题。
分区太细，比如按天分区，一年就是365个小表。如果查询时间范围跨一个月，优化器就要把30多个分区拼接起来，分区表元数据管理和锁开销会变大，而且很多日常运维操作，比如添加新分区、删除旧分区，操作频率也高。分区数量到几千个的时候，系统整体稳定性会下降。
分区太粗，比如按年分区，一年一张表，数据量可能还是几千万甚至上亿，分区的意义就被削弱了，单个分区性能依然上不去。
我的建议是：一般业务按月分区就够了。一个月几百万到一两千万行，索引和查询都很舒适。如果某些表一个月就能上亿行，可以压缩到按周分区；如果一个月只有几十万行，那按季度或者按年分区也行，没必要为了分区而分区。
还能考虑一个折中方案：按周分区，然后保留最近一个季度是细粒度，更老的按季度汇总成更大的分区，降低分区总数。这个稍微麻烦，但性能确实好。
2.3 冷热数据分层与表空间策略
分区表还有个很实际的应用场景是冷热分层。
比如一张订单表，最近三个月的数据查询很频繁，三年前的数据几乎不被业务访问，但还留着做合规审计。这时可以给不同分区指定不同的表空间，热数据放快速盘，冷数据放容量盘。PostgreSQL 的表空间机制做这件事很简单，各分区就是独立的表，DETACH 之后也可以单独转移表空间或者压缩存储。
从运维角度看，这个设计还会带来一个额外好处：备份策略可以差异化。热分区全量备份，冷分区低频率归档，节省不少存储成本。
我在某项目的实际操作中，就是这样做的：最近三个月的分区放在高速SSD表空间，三个月到一年的分区放到普通SATA盘，一年以上的分区转成归档表空间。整体存储成本下降不少，而且查询性能影响其实很小，因为真正频繁访问的就是那三个月的热数据。
3. 实操过程与核心环节实现
3.1 用声明式分区从零建表
PostgreSQL 10 以上支持声明式分区，也就是用 PARTITION BY 语法直接在建表语句里定义分区规则，不需要像老版本那样手动建一堆继承表。这是目前最推荐的方案，因为优化器对声明式分区的裁剪效果比传统继承表更好。
以一张模拟的订单流水表为例，按月份做 RANGE 分区：
CREATE TABLE order_flow ( order_id BIGSERIAL, user_id BIGINT NOT NULL, order_amount NUMERIC(10,2), order_status INT NOT NULL, created_at TIMESTAMP NOT NULL ) PARTITION BY RANGE (created_at); CREATE TABLE order_flow_2024_01 PARTITION OF order_flow FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); CREATE TABLE order_flow_2024_02 PARTITION OF order_flow FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'); -- 如果允许负无穷的时间，旧数据也可以这样兜底 CREATE TABLE order_flow_default PARTITION OF order_flow DEFAULT;指定了 DEFAULT 分区之后，凡是落不到任何已有分区的数据，都会自动进 DEFAULT 分区。这个设计有利有弊，好处是写入不会因为缺分区报错，坏处是如果忘记创建新分区，数据就会悄悄堆积到 DEFAULT 分区，几个月后一看，DEFAULT 分区反而变成最大的表，分区意义直接没了。
所以我的建议是：DEFAULT 分区可以保留，但必须配合监控，时刻盯着它的大小。更重要的是，养成提前创建分区的习惯，用定时任务在月底前把下个月的分区建好。
3.2 分区表上的索引和约束怎么建
分区表建索引有两种方式：在主表上建全局索引，或者在每个分区上单独建索引。
PostgreSQL 11 之前在分区表主表上 CREATE INDEX 是不支持自动应用到所有分区的，需要手动对每个分区执行。PostgreSQL 11 之后支持在主表上直接建索引，并且自动传递到所有已有分区和未来新建的分区。
CREATE INDEX idx_order_flow_user_id ON order_flow (user_id); CREATE INDEX idx_order_flow_status ON order_flow (order_status);这个语法非常省心，新分区创建时索引会自动创建出来。注意，主表上的索引本身是虚拟的，实际数据索引落在每个分区上，所以分区数量多的时候，建索引主表上的操作会很耗时，因为是逐分区建的。实践中最好在数据导入前先建好分区，再一次性建索引，避免把时间浪费在没用的事情上。
关于约束，有一个我踩过的坑：分区表的主键必须包含分区键。假设上面这张订单表，如果把 order_id 定义成主键，直接就会报错，因为 order_id 不包含分区键 created_at。业务上如果确实需要全局唯一订单ID，可以不用主键约束，改成普通索引加 UNIQUE 约束（同样要包含分区键），或者用一个全局 sequence 来生成 ID。
3.3 存量一亿行数据如何改造迁移
存量表改造成分区表，最麻烦的不是建表，而是数据迁移。
比较奢侈的方案是直接停机维护，把原表改名，建新分区表，然后 INSERT INTO ... SELECT 把数据搬过去。一亿行的数据量，如果按顺序插入，可能要好几个小时，这种方式只适合允许长时间停机的系统。
如果停机窗口很短，就得做在线迁移。思路大概是这样：
先建好新分区表，然后创建一个批量的回填函数，按时间范围分批把旧表数据搬入对应分区：
-- 按天为单位，把数据分批搬过去 DO $$ DECLARE start_date DATE := '2020-01-01'; end_date DATE := '2024-01-01'; cur_date DATE; BEGIN FOR cur_date IN SELECT generate_series(start_date, end_date, INTERVAL '1 day')::date LOOP INSERT INTO order_flow SELECT * FROM legacy_order_flow WHERE created_at >= cur_date AND created_at < cur_date + 1; COMMIT; END LOOP; END $$;这里要注意，分批的粒度不能太大也不能太小。一天一批，每批几十万行，事务不至于太大，失败后可以从中断点继续跑。如果一批上千万行，一个事务跑太久，磁盘、日志、锁全部都会出问题。
迁移过程中，旧表的数据可能还在写入。这个就复杂一些，一般做法是：迁移期间应用写入切换到新表，或者通过触发器双写。我接触过的项目里，更稳妥的是先用历史数据回填，然后短暂停写，做一个增量补录，再切换读写。具体的切换窗口要根据业务容忍度来定。
3.4 验证分区裁剪是否生效
迁移完成后，最重要的一件事就是验证查询计划是否真的做到了分区裁剪。
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM order_flow WHERE created_at >= '2024-01-05' AND created_at < '2024-02-01' AND user_id = 12345;正常情况下，执行计划里应该只出现 order_flow_2024_01 这一个分区。如果执行计划里出现了 Append 且后面接了一大串子节点，把所有分区都扫了一遍，那就说明条件写法有问题，或者统计信息没更新，需要马上排查。
我也碰到过一种诡异情况：应用代码里参数类型不对，比如 created_at 在 JDBC 驱动中传成字符串，PostgreSQL 优化器为了隐式转换，放弃了对分区键的范围裁剪，导致全分区扫描。这种问题单看 SQL 很难发现，必须看实际执行计划才能找到真凶。
所以我的建议是：迁移完不是结束，而是开始。至少跑一周的慢查询日志分析，专门找出那些没有裁剪到单分区的查询，一个一个修掉。
4. 常见问题与排查技巧实录
4.1 更新分区键会跨分区移动，怎么办
分区表的 Update 有一个大坑：如果更新了分区键字段，PostgreSQL 会先 DELETE 旧分区的行，再 INSERT 到新分区。这个操作在数据量大时代价极高，而且写放大严重。
比如业务想要把订单的 created_at 从2024年1月改成2023年12月，表面上是一条 UPDATE，实际是跨分区搬移，可能要 DELETE 和 INSERT 一整行的数据，加上索引维护，代价相当可观。
所以设计时必须问自己：“这个表的分区键未来会不会被更新？”如果会，那就要么调整业务逻辑，禁止修改这个字段；要么考虑用更合理的中间状态字段，而不是直接改 created_at。在分区表设计阶段就把这个点想清楚，能省掉后面大量的麻烦。
4.2 自动创建分区的定时任务
手工创建分区的缺点是容易忘，忘了数据就进 DEFAULT 分区，时间一长性能照样崩。所以生产环境里必须建立自动分区管理机制。
最简单的做法就是写一个函数，比如每月1号自动创建下个月的分区：
CREATE OR REPLACE FUNCTION auto_create_partition() RETURNS void AS $$ DECLARE next_month_start DATE; partition_name TEXT; BEGIN next_month_start := date_trunc('month', now()) + INTERVAL '1 month'; partition_name := 'order_flow_' || to_char(next_month_start, 'YYYY_MM'); EXECUTE format( 'CREATE TABLE IF NOT EXISTS %I PARTITION OF order_flow FOR VALUES FROM (%L) TO (%L)', partition_name, next_month_start, next_month_start + INTERVAL '1 month' ); END; $$ LANGUAGE plpgsql;然后使用 pg_cron 扩展或操作系统的 cron 调度，每个月调度一次。
这里有个经验：新分区创建后，记得顺手 ANALYZE 一下该分区，让统计信息尽快生效。否则新分区刚建好，统计信息缺失，优化器可能会估算错。另外，自动创建分区的脚本一定要有日志输出和失败告警，不然创建失败了你都不知道，数据又偷偷进 DEFAULT 了。
4.3 索引命名冲突和主表索引的坑
一个分区表会有很多分区，如果每个分区都有索引，索引名称需要全局唯一。假如你在两个分区上创建了相同名字的索引，PostgreSQL 会直接报错。
最稳妥的做法是用统一命名规范，比如“idx_{表名}{字段}{分区后缀}”，或者干脆依赖 PostgreSQL 11 之后在主表上统一建索引的机制，让它自动命名，避免手工出岔子。
另外千万别在主表上只创建索引，然后忘记给分区创建索引。PostgreSQL 11 之前的版本，在主表上建索引不会自动覆盖到分区，如果你只是习惯性在主表上 CREATE INDEX，然后没检查分区索引，查询的时候会发现执行计划不走索引，因为索引根本不存在于实际分区上。
4.4 分区数量无限膨胀和锁升级
分区表的另一个问题是：分区数量太多，一些操作会变慢。比如主表上的 DDL，需要对所有分区做元数据同步，几千个分区时，一条 ALTER TABLE 可能要等很久。
我见过有团队按天做分区，跑了三年，上千个分区，后来哪怕是最简单的查询，执行计划的解析时间都很长。优化器要解析所有分区的约束，判断哪些符合裁剪条件，这种开销在分区数量大了以后很可观。
我的经验建议是：无论什么粒度，分区数量最好控制在几百个以内。对于超过一年的旧数据，可以定期把月分区合并成季度分区或者年分区，或者直接迁移到归档表。这一步看着麻烦，但为了长期稳定，值得做。
4.5 管理 DEFAULT 分区和未来扩容
再提一次 DEFAULT 分区，因为它太容易被忽略了。很多新人在建表时都加一个 DEFAULT 分区做兜底，结果几个月后所有数据都躺里面，分区表彻底失去意义。
如果真的需要 DEFAULT 分区，我建议对它有专门的监控，每天检查 DEFAULT 分区行数，一旦发现超过某个阈值就立即告警。同时，所有自动创建分区的定时任务，一定要有“提前量”，不要到了当月才建，至少提前三五天。
另外一个容易被忽略的点：提前规划未来数据量。如果你的业务每个月增长1000万行，那按月的分区半年后就变成2000万行了。最好提前两三个月复核一次分区粒度，数据增长过快时及时调整新分区的范围，不要让分区头重脚轻。
5. 额外提升：分区表的维护与监控
5.1 定期清理和归档策略
分区表最大的运维红利就是 DROP 旧分区极其高效，但我还是建议先把数据归档再删除，不能直接DROP了事，毕竟有些行业数据要保留很久。
通常做法是：DETACH 旧分区，然后转成独立表，再用 pg_dump 逻辑备份到低成本存储，最后再物理删除或压缩存放。这样既不影响业务，也能降低主库压力。
比如：
ALTER TABLE order_flow DETACH PARTITION order_flow_2022_01;执行完之后它就变成一张普通表，可以随意转移到冷备环境去。这个操作所花费的时间是毫秒级的，相比 DELETE 几千万行数据的方案，体验完全不同。
5.2 用自动化监控分区健康度
分区表不是建完就完的，需要日常监控。我习惯在每个分区表上做这么几个监控指标：
- 最大分区行数是否超过预估阈值
- 分区总数是否超过规划值
- DEFAULT 分区是否有数据流入
- 是否有未创建的未来分区（可以通过查询分区边界和当前日期的差来判断）
这些指标写成简单的 SQL，定期通过定时任务检查，大部分问题在爆发之前就能发现。特别是那些“分区慢慢不均匀”“忘记创建新分区”之类的问题，靠人记是不现实的，必须有监控兜底。
5.3 环境差异与版本兼容性
最后提一下版本问题。分区表相关的功能在 PostgreSQL 10 到 15 之间变化不小，比如 11 开始支持默认分区、主表索引自动应用到分区，12 开始支持分区外键，13 开始支持分区级行级触发器，14、15 又做了不少优化。如果还在跑老版本，建议先做完整的功能测试再决定是否大规模使用分区表。
如果你用的是云数据库，看到界面里那些“分区表支持”的选项，也不要直接信心满满，先确认你用的内核版本本身有没有限制，有些云厂商对分区数量、自动分区扩展策略是有附加配置的，这些和社区版不完全一致。
在我个人项目里，吃过的最大教训就是版本差异：同一个逻辑，在 PostgreSQL 12 上正常，在某个云数据库的内核上出现了索引不同步的情况。所以无论如何，建分区表之前，先在测试环境把“建分区、插数据、查数据、删分区”全部跑一遍，确认行为符合预期，再上生产。
结尾
做了这么多年 PostgreSQL 相关的工作，我最大的体会是：分区表不是银弹，但它确实是处理单表数据量爆炸时最稳妥、最可控的手段之一。相比分库分表那种牵一发动全身的改动，分区表几乎不改变应用层的 SQL 写法，改造风险小得多，收益却非常明显。
如果你现在正被一张亿级大表折磨，我的建议是先别急着上各种重方案，老老实实把分区键选好，分区粒度定好，存量数据分批迁移过去，再把分区自动运维的机制建立起来。这个过程可能需要两三天，但换来的是一两年内不用再操心大表性能问题。真正踩过坑之后你就会明白，分区表不是“早晚要做的优化”，而是“越早做越省心的投资”。
