如果你维护过一张几千万行甚至上亿行的大表肯定没少被慢查询折磨过。且先不谈业务SQL怎么写光是全表扫描、索引膨胀、清理脏数据这几个问题就够喝一壶了。PostgreSQL里的分区表正是为了解决这一类问题而存在的设计把一张极大的物理表拆成多个较小的物理表对外仍然是一张逻辑表。应用层不用改SQL数据库自己会把数据分门别类放到对应分区里。这话听起来美好实际操作中却有不少讲究。我最早接触PostgreSQL分区表时是接手一个统计系统里面有张累积了快三年的用户行为表体量超过八千多万行。查询时经常只关心最近一个月的数据但因为整张表混在一起索引再努力也扛不住数据持续膨胀垃圾数据清理又不敢随便删。后来拆成按月分区旧分区直接卸载归档查询走分区裁剪效果立竿见影。从那时起我就意识到分区表不是一个“高深的优化技巧”而是每个PostgreSQL使用者迟早要面对的基本功。这篇文章就围绕PostgreSQL分区表的原理、设计、实操和排障来展开尽量让没碰过的朋友能照着手动一遍也让正在用的人少踩几个坑。1.1 分区表解决的核心问题分区表本质上是“分而治之”。它把一个大表的数据分散到多个子表查询计划器在解析SQL时会根据约束条件自动跳过无关分区。这个动作叫分区裁剪也是性能收益的主要来源。比如按订单日期分区查询某一天的数据计划器只访问那一个分区IO和内存消耗都降了一个量级。除了查询性能分区表对维护的改善往往更明显。PostgreSQL的MVCC机制决定了老数据不能直接物理删除要靠清理进程回收空间。表越大清理越吃力vacuum来不及跑事务ID回卷的风险也会上升。而分区表可以按分区单独维护甚至把不需要的历史分区直接detach下来归档后drop掉避免了在大表上反复delete产生的巨额开销。另一个常被忽略的好处是统计信息。大表的统计信息是全表粒度的数据分布如果偏斜计划器容易判断失误。分区后每个子表单独统计数据特征更集中计划器对行数的估算也会更准。这一点在复杂查询里影响很大。1.2 不是所有大表都需要分区很多人一听分区表能优化就把所有大表都拿来分区结果反而变慢。分区不是万能药有几类场景要慎重。第一数据量还不够大却分了很多区。分区表本身在查询时需要对分区做约束检查如果数据只有几百万行全表扫描本身很快分区带来的优化收益很小反而增加管理和写SQL的复杂度。第二业务查询条件里不带分区键。比如按月分区的表查询却经常按用户ID过滤此时计划器仍然要扫所有分区效果和没分区没区别甚至因为要检查多个子表而更慢。这种情况更适合用普通表加合适索引或者考虑HASH分区配合等值查询。第三频繁更新分区键的场景。PostgreSQL更新一条记录后如果分区键变了需要把行从原分区移动到新分区也就是一次delete加一次insert。如果业务更新很频繁这样的开销会放大。实在要更新也要保证更新条件包含分区键让计划器少走弯路。此外分区表在报表和OLAP场景中的意义同样不小。数据仓库里经常要保留很长的时间跨度同时每次汇总却只关注某段时间。如果不分区查询每跑一次都要扫描全部时间范围的数据存储成本和时间成本都浪费。分区后冷热数据天然分开ETL任务可以只读需要的时间分区归档和重算都更容易控制。很多看起来“很慢”的报表改成时间分区后不需要动SQL性能就上来了。所以分区表的设计一定要从实际查询和写入模式出发先想清楚业务到底按什么维度访问数据再决定分区键和分区粒度。2. 动手之前先想清楚分区键、粒度与方案选型分区表最大的成本不是建表那一下而是方案定型之后的迁移和返工。我曾经见过一张表原本按年分区结果业务要查三个月前的单月数据每次都要扫一整年后来花了整整一个晚上把所有分区重新切了一遍。所以动手之前一定要把分区键、分区粒度、分区方式先想明白。2.1 分区键怎么选分区键是整个分区方案的核心选错了后面处处被动。最常用的有三种RANGE范围分区、LIST列表分区、HASH哈希分区。RANGE分区适合按时间或数值区间划分比如order_date、id范围。它的优势是容易理解方便按历史时间段归档。LIST分区适合枚举值比如按地区、按状态比如把华东、华北等区域放在不同分区。HASH分区适合没有天然区间、但等值查询较多的场景比如按用户ID哈希到不同分区让写入分布更均匀。选分区键时我一般先回答三个问题。第一业务SQL的WHERE条件里最常出现哪个字段第二数据有没有明显的时间属性或枚举属性第三历史数据需不需要定期清理或归档三个问题都能对上分区键就基本定了。需要注意的是分区列必须是表上的普通列不能是表达式或函数加工后的结果。如果你经常按date_trunc(month, created_at)查那就需要单独保存一个created_month字段或者直接用created_at本身作为分区键否则无法实现分区裁剪。2.2 声明式分区还是继承分区PostgreSQL 10之前没有声明式分区大家普遍用继承加触发器实现分区父表是普通表子表通过INHERITS继承父表结构再用触发器或规则把数据路由到子表。这种实现需要自己维护很多样板代码而且计划器对继承表的裁剪能力有限性能和易用性一般。从PostgreSQL 10开始官方引入了声明式分区。所谓的声明式就是建表时用PARTITION BY告诉数据库分区方式随后CREATE TABLE ... PARTITION OF创建分区插入数据时数据库自动路由。到PostgreSQL 11以后功能逐渐补齐支持分区表上的主键、部分索引、外键支持HASH分区支持默认分区。到了PostgreSQL 12/13分区裁剪和并行查询的能力也进一步成熟。所以没有历史的包袱的话直接用声明式分区不要再去碰手工继承。现在的版本对声明式分区已经支持得比较完整管理也方便。如果你还在老旧版本上话可以分两步先升级再谈分区。在PostgreSQL 9.x上硬做分区成本和收益不成正比。2.3 分区粒度日、月还是年分区粒度直接影响分区数量和单分区数据量。粒度太细分区数量动辄几千元数据本身就成负担粒度太粗单分区又太大清理和裁剪的优势不明显。我通常按一个原则估算单个分区数据量控制在百万到千万级别同时保证每年分区数量不要太多。比如日活百万的日志表一天几百万行按天分区可能一年三百六十五个分区有点多但也不是不能用按月分区则每个分区几千万行相对适中。订单类数据通常按月分区就比较合适既能满足归档查询裁剪也合理。还要注意PostgreSQL对分区数量的限制。虽然理论上可以建很多分区但每个分区都会增加执行器的检查成本超过几千个以后计划时间会明显变长。如果觉得分区数失控可以把粒度调大比如从日分区改成月分区。另一个办法是保留热分区、把历史分区合并成更大粒度的归档表后面维护部分会展开说。3. 实操从建表到数据落地前面说的都是设计接下来进入实际操作。我以一套订单系统为例演示从零开始创建分区表、写入数据、验证裁剪、维护分区的完整过程。这套流程在PostgreSQL 12以上版本都能直接跑。3.1 创建父表与第一批分区用一张订单表orders举例。业务场景是按照order_date做范围分区按月拆。父表定义如下CREATE TABLE orders ( id bigint NOT NULL, order_no varchar(64), customer_id bigint, amount numeric(12,2), order_date date NOT NULL, status varchar(20) DEFAULT NEW ) PARTITION BY RANGE (order_date);注意父表定义本身不会存储任何数据它只是模板。建表之后马上建几个分区CREATE TABLE orders_2024_01 PARTITION OF orders FOR VALUES FROM (2024-01-01) TO (2024-02-01); CREATE TABLE orders_2024_02 PARTITION OF orders FOR VALUES FROM (2024-02-01) TO (2024-03-01); CREATE TABLE orders_2024_03 PARTITION OF orders FOR VALUES FROM (2024-03-01) TO (2024-04-01);每个分区实际上是一张独立的物理表分区名最好能直接看出数据范围。FOR VALUES里用的是半开区间也就是说2024-02-01这条数据会进二月的分区不会进一月的分区。这个边界理解错会出大问题我建议每个表建好后都手动插入一条边界数据验证一下。这里有个细节建父表的时候分区键列必须加NOT NULL。因为RANGE分区需要根据该列路由数据如果允许NULL数据库不知道往哪个分区放即便有默认分区语义上也容易出错。PostgreSQL实际上会自动为分区键列加上NOT NULL但我习惯在建表时就写明阅读代码的人也清楚意图。3.2 索引、约束与默认分区分区表的总索引不能在父表上建要在每个分区上分别建。从PostgreSQL 11开始在父表上创建索引时如果已经有了分区会自动递归到每个分区CREATE INDEX idx_orders_customer_id ON orders (customer_id); CREATE INDEX idx_orders_order_date ON orders (order_date);这条命令执行后现有子表上都会自动创建对应索引新加的分区如果没有显式建索引也会在插入数据时自动带上前面的索引。不过如果子表是通过ATTACH直接挂载进来的挂载前最好确保索引已经建好否则父表上的索引不会自动补充到已存在的子表上容易漏。默认分区不是必选项但我强烈建议加一个。它用来承接那些没有对应分区的数据比如时间超过已知分区范围的记录。创建方式是CREATE TABLE orders_default PARTITION OF orders DEFAULT;有了默认分区数据不会因为边界漏了而报错。但默认分区也有副作用计划器对默认分区无法做裁剪因为任何不符合其他分区的值都可能落在里面。如果你的数据严格可控也可以不用默认分区让漏数据直接报错反而是种校验。分区表上的主键要包含分区键。例如ALTER TABLE orders ADD PRIMARY KEY (id, order_date);这看起来有点违反常规主键把分区键也包含了。原因在于全局唯一性在分区表里很难实现数据库只能保证每个子表内部的唯一性。如果你的业务主键确实只需要id唯一那需要业务层面兜底或者接受跨分区重复的风险。不加主键只走唯一索引也一样。3.3 数据写入与分区裁剪验证普通INSERT不需要指定分区PostgreSQL会自动路由。比如INSERT INTO orders (id, order_no, customer_id, amount, order_date) VALUES (1, A001, 1001, 88.50, 2024-01-15);这条数据会进orders_2024_01。检查数据位置SELECT tableoid::regclass, * FROM orders WHERE id 1;tableoid表示数据实际所在的物理表用这个可以验证路由结果。接下来验证分区裁剪。先用EXPLAIN查询一月的订单EXPLAIN SELECT * FROM orders WHERE order_date 2024-01-01 AND order_date 2024-02-01;如果计划器正常工作输出的Seq Scan节点应该只包含orders_2024_01而不是orders父表。在PostgreSQL 12以上Append节点下的子计划只会列出被裁剪后的分区。我见过不少朋友建完分区后兴致勃勃地查询结果EXPLAIN一看扫的还是所有分区后来才发现查询条件里写的是date(order_date)函数包裹了分区列计划器没法直接推导范围分区裁剪就失效了。这类问题几乎天天见排查第一步永远是看执行计划。3.4 分区维护增删、归档与拆分业务跑起来之后分区维护是日常事务。典型操作是提前创建未来几个月的分区、把过期分区卸载归档、以及删除不再需要的数据。新增分区CREATE TABLE orders_2024_04 PARTITION OF orders FOR VALUES FROM (2024-04-01) TO (2024-05-01);如果业务季初才确定历史没入账还可以用DETACH把分区从逻辑表上摘掉ALTER TABLE orders DETACH PARTITION orders_2024_01;DETACH之后orders_2024_01变成一张普通表数据还在但不会再参与父表的查询。通常我会先DETACH再备份或迁移数据最后DROP那张表比直接在父表上DELETE安全得多。对归档表也可以继续保持物理存在查询老数据时单独访问。反过来当你想把一个外部数据表挂成分区需要先建好同样结构的表然后ALTER TABLE orders ATTACH PARTITION orders_2023_12 FOR VALUES FROM (2023-12-01) TO (2024-01-01);ATTACH之前要确保该表中没有违反分区边界的数据。PostgreSQL会在attach时检查一次数据量大时很耗时。更稳的做法是先校验再attach。拆分分区有一个常见场景某个月数据特别多想把一个月拆成两个周分区但开始没设计好。这个操作没法原地拆需要新建两个分区、把旧分区数据按条件搬过去、再替换。过程比较重尽量在选粒度时避免别把希望寄托在事后拆分上。3.5 数据导入与分区表上的COPY从外部文件批量导入分区表时虽然没有额外指定分区但COPY会自动触发路由因此性能瓶颈往往在路由判断上。对于超大批量导入可以先关闭约束检查SET session_replication_role replica;这会跳过触发器和外键检查导入完成后改回origin。分区表没有触发器也能路由因为路由是执行器原生完成的所以这条设置只影响约束不影响分区路由。另外如果目标分区已经存在且为空也可以直接copy进对应分区表绕过父表的路由开销。4. 计划器、索引与性能调优分区表能不能发挥威力最终要看执行计划。很多人建完分区表后发现查询变慢了大概率不是分区表本身的问题而是计划器的裁剪没有按预期生效。这一部分专门聊性能调优和计划器相关的话题。4.1 分区裁剪不是银弹分区裁剪是分区表性能的核心但它不是自动在所有场景里生效。能否裁剪取决于查询条件能不能被推导出分区范围。能直接落到一个条件区间时裁剪最彻底只给等值条件时如果分区键是范围分区也能定位到具体分区但如果是范围条件跨多个分区裁剪后会留下多个分区。更麻烦的是查询条件里对分区键做了函数转换比如WHERE date_trunc(month, created_at) 2024-01-01这样的情况计划器无法推导只能全分区扫描。解决办法是直接用分区键本身做范围条件或者在表里冗余一个可用于分区的原始日期列让应用在查询时传入原始值。另一个容易被忽略的因素是参数化查询。如果条件写成WHERE order_date $1 AND order_date $2计划器在预编译阶段不知道参数值不能预裁剪执行时才能确定。PostgreSQL会等到实际参数传入后再做裁剪但这需要执行计划的参数化支持某些特殊写法下会退化。尽量用常量或明确的范围表达式能让计划器更早做出好的选择。4.2 索引与约束排除分区表的索引在每个分区上独立维护。查询时由于已经裁剪到少数分区每个分区的索引大小会小很多B-Tree的层级也浅扫描效率高。但要注意分区表上的全局索引无法像单表那样一键维护比如重建索引需要逐分区执行REINDEX INDEX idx_orders_customer_id;如果父表索引是递归创建的各分区上的索引名可能与父表索引名不完全一致REINDEX父表索引并不能覆盖子表。实际操作中我一般直接对分区子表分别执行REINDEX或者用一个循环脚本遍历分区名。约束排除和分区裁剪经常被混在一起。分区裁剪是逻辑层直接去掉不需要的分区而约束排除是计划器利用CHECK约束判断子表是否可能包含匹配行。声明式分区大量使用内建的边界约束所以两者的效果重叠但如果你自己往分区上加了额外的CHECK约束约束排除也能帮忙剪掉更多分区。在排查性能时可以同时看两个方向。4.3 写入路由与并发注意插入数据时PostgreSQL会去扫描所有分区的边界约束找到匹配分区后插入。分区数量多时这个路由过程会线性增加成本。所以写入性能对分区数量非常敏感。我看到一些人把分区粒度设到小时级一年几千个分区结果写入吞吐一直上不来最后只能回头合并分区。并发写入分区表时每个分区有自己独立的锁父表上的锁只在路由或DDL时短暂存在所以并发能力通常不是瓶颈。真正的瓶颈出现在DDL场景比如在业务高峰期间执行ALTER TABLE orders ATTACH PARTITION因为要获取父表锁并对整个分区做校验可能长时间阻塞写入。这类维护操作一定安排在低峰期或者先用可重复读事务做校验再在业务侧做切换。并行查询在分区表上也需要注意。PostgreSQL 12以后Append节点支持并行扫描多个分区但如果分区数量很多并行worker的调度开销可能抵消收益。我通常会把max_parallel_workers_per_gather和同时访问的分区数结合起来看不要把每个分区都当成并行单元。5. 常见问题速查与排查心得分区表用久了遇到的问题大多有规律可循。我把高频问题和排查思路整理了一下方便在实际操作里快速对照。当然技术版本也在迭代遇到具体问题还是要结合当前的PostgreSQL版本来判断。5.1 高频问题速查表我把日常运维中碰到的问题整理成了一个表遇到类似情况可以按图索骥。现象可能原因处理方法查询还是扫全部分区WHERE条件对分区键做了函数包装改写为分区键的范围/等值条件插入数据报“no partition found”没有匹配分区也没有默认分区检查边界新增分区或建默认分区插入后查不到或进错分区对FOR VALUES边界理解错误用半开区间手测边界值ATTACH很慢新表数据未按边界校验先单独跑CHECK约束校验默认分区数据量异常大漏配的分区全落进默认分区定期检查默认分区按数据补齐分区分区数量太多导致写入慢粒度太细路由成本高调大粒度合并历史分区父表创建索引后新ATTACH分区无索引递归索引不覆盖老分区在ATTACH前手动补齐索引跨分区查询计划不稳定每次裁剪覆盖范围不同统一查询条件避免跨多月大查询这些坑都不是极端情况而是分区表运维里最常见的问题。排查时先看执行计划再看分区定义基本能定位到根因。5.2 分区表监控与日常巡检分区表引入之后监控不能只看父表大小要按分区维度看。我一般会用一个查询列出所有分区的大小、行数和最近写入时间SELECT child.relname AS partition_name, pg_size_pretty(pg_total_relation_size(child.oid)) AS total_size, c.reltuples::bigint AS approx_rows FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent parent.oid JOIN pg_class child ON pg_inherits.inhrelid child.oid JOIN pg_namespace ns ON ns.oid child.relnamespace JOIN pg_class c ON c.oid child.oid WHERE parent.relname orders ORDER BY child.relname;这张表能帮助发现某个分区异常增长也能看出“下个月的分区还没建”之类的问题。我习惯于建一个定时任务每天检查未来两天的分区是否存在不存在就自动补建。比很多商业化监控都直接。日常巡检还应该关注默认分区的大小。如果默认分区持续增大说明业务产生了预期外的数据边界很可能是新版本代码里日期字段格式变了或者分区范围规划漏了一段。及时把数据拆出来再规划正式分区比数据积压到年底再处理轻松得多。5.3 我踩过的几个坑第一个坑是建分区表时没考虑索引。第一年数据量增长快我在父表上建了索引以为万事大吉结果某个分区因为数据量特别大查询速度明显下降。一开始没反应过来后来发现该分区的索引膨胀严重单独重建后恢复。分区表里每一个分区都是独立物理表日常维护也要按分区一个个来。第二个坑是默认分区。有一段时间业务突然产生了一批带历史日期的补录数据因为对应历史分区早就删了这些数据全进了默认分区。默认分区越来越大查询又没法裁剪导致一个后台报表越跑越慢。后来我把默认分区里的数据按月拆出历史归档才把性能救回来。默认分区不是保险箱要经常看。第三个坑是关于分区键更新。有张表按状态做LIST分区业务里用户状态从“待审核”改成“已通过”结果每改一次状态数据库都执行一次跨分区移动。原本一条UPDATE能跑完的事务实际变成了删除再插入锁范围也变大。后来把状态这种频繁变更的字段改成普通字段分区键换成业务上基本不变的地域字段问题才算解决。分区表这个东西用好了能救命用不好会添乱。我个人的体会是不要一上来追求“最先进”的配置先用最朴素的范围分区跑起来观察执行计划和数据分布踏踏实实把分区裁剪这一条做对再去考虑更细的调优。前几次踩坑总是难免但只要你坚持“每次变更都先看执行计划、记录分区大小”分区表会越用越顺手。最后再分享一个小技巧所有分区表都建一个文档记录分区键、粒度、边界约定和定期维护脚本。这个文档可能比数据库注释更管用因为几个月后你自己也会忘记最初的设计理由。