单表破亿查询慢到报警这场景我在不止一个项目里见过。最开始可能只是运营那边发一句“报表跑不出来了”后来越来越频繁慢查询日志里全是那张表数据库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 写法改造风险小得多收益却非常明显。如果你现在正被一张亿级大表折磨我的建议是先别急着上各种重方案老老实实把分区键选好分区粒度定好存量数据分批迁移过去再把分区自动运维的机制建立起来。这个过程可能需要两三天但换来的是一两年内不用再操心大表性能问题。真正踩过坑之后你就会明白分区表不是“早晚要做的优化”而是“越早做越省心的投资”。