企业官网建设流程全解析

如果你维护过一张几千万行甚至上亿行的大表，肯定没少被慢查询折磨过。且先不谈业务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 高频问题速查表

我把日常运维中碰到的问题整理成了一个表，遇到类似情况可以按图索骥。

这些坑都不是极端情况，而是分区表运维里最常见的问题。排查时先看执行计划，再看分区定义，基本能定位到根因。

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能跑完的事务，实际变成了删除再插入，锁范围也变大。后来把状态这种频繁变更的字段改成普通字段，分区键换成业务上基本不变的地域字段，问题才算解决。

分区表这个东西，用好了能救命，用不好会添乱。我个人的体会是，不要一上来追求“最先进”的配置，先用最朴素的范围分区跑起来，观察执行计划和数据分布，踏踏实实把分区裁剪这一条做对，再去考虑更细的调优。前几次踩坑总是难免，但只要你坚持“每次变更都先看执行计划、记录分区大小”，分区表会越用越顺手。最后再分享一个小技巧：所有分区表都建一个文档，记录分区键、粒度、边界约定和定期维护脚本。这个文档可能比数据库注释更管用，因为几个月后你自己也会忘记最初的设计理由。