做了这么多年数据库运维，分区表算是我在 PostgreSQL 上觉得“用了就回不去”的功能之一。早年间在一家电商公司处理过一张 3 亿行的订单流水表，当时还在用传统的“一个月建一张新表 + 定时任务跑 SQL 迁移老数据”的方案，后来被分区表的声明式语法彻底解放了。这篇内容我按自己的实战经验来写，不讲大而全的概念，重点放在：为什么要分区、怎么建一套能落地用的分区表、日常怎么维护、查询优化要注意什么、以及我踩过的那些坑。

1. 分区表的定位：解决什么问题，什么时候该上

1.1 先想清楚：你真正要解决的是“删不掉”还是“查不快”

很多人一听说大表性能差，第一反应就是“上分区”。但分区表不是万灵药，它解决的核心痛点其实非常明确：让你能快速丢弃和归档大片数据，同时让查询尽可能只扫描需要的那一小片数据。

当年我处理的那张订单流水表，问题并不完全出在慢查询上。真正要命的是DELETE FROM order_flow WHERE create_time < '2023-01-01'这种清理动作，扫了几十分钟不说，产生的 WAL 日志能把磁盘撑爆，还会跟业务查询抢锁。后来我改成按月分区，清理某个月的数据直接DROP TABLE order_flow_2022_12，底层就是删除几个数据文件，秒级完成。这才是分区表最实在的价值：把“大数据量的增删改查”拆成“小数据量的文件操作”。

所以如果你面临的是这类问题，分区表非常合适：

• 表里有大量历史数据，但访问主要集中在最近一段时间。
• 定期要清理归档数据，删除或离线存量数据是刚性需求。
• 单表索引膨胀严重，重建索引要锁表很久。
• 某张表的数据量级达到几千万甚至上亿，并且仍在持续增长。

反过来，如果你的表只有几百万行，业务查询也没啥规律，那分区带来的收益可能很小，反而增加了 DDL 和运维的复杂度。分区表不是银弹。

1.2 三种分区策略适合什么场景

PostgreSQL 支持三种内置分区方式：范围分区（RANGE）、列表分区（LIST）、哈希分区（HASH）。我觉得很多人选型时容易想复杂，其实大部分业务场景用 RANGE 就够了。

我大多数时候在业务里用的是 RANGE + 月分区。比如日志表、订单表、流水表，时间字段天然符合“当月数据热、历史数据冷”的访问模型。业务查询如果都带时间范围，分区裁剪的效果会非常好。LIST 我在配置类大表里用过一次，比如全国门店数据按大区分，效果也直接。HASH 分区用的最少，因为它虽然能分散写入压力，但查询如果不带哈希键就没办法裁剪，全分区扫描等于没优化。

1.3 声明式分区 vs 传统继承式分区

如果你在网上搜“PostgreSQL 分区”的老资料，大概率会看到一大堆用INHERITS加触发器实现路由的写法。那是 PG 10 之前的方案，现在完全没必要了。PG 10 开始提供原生声明式分区语法，PG 11 以后支持自动路由插入和默认分区，PG 12 以后查询性能和分区裁剪的成熟度已经足够上生产。

我见过一些项目还在用触发器方案，理由是“老代码改不动”。但触发器方案的代价很大：每一行插入都要多走一次触发器判断，并发高时会成为肉眼可见的开销；更麻烦的是触发器逻辑和分区定义容易脱节，新增分区时漏改触发器，数据就直接写错地方。声明式分区把路由规则写死在表定义里，数据库自己处理，不管是正确性还是性能都可靠得多。

2. 建一套按月范围分区表

2.1 从零搭建：父表、分区、索引

直接给你一套我常用的建表模板。比如要建一张支付流水表，按月分区：

CREATE TABLE payment_log ( id bigserial NOT NULL, user_id bigint NOT NULL, order_id bigint, amount numeric(12, 2), pay_time timestamp without time zone NOT NULL, extra jsonb, PRIMARY KEY (id, pay_time) ) PARTITION BY RANGE (pay_time);

这里有个细节很多人第一次会栽跟头：声明式分区表的主键和唯一约束必须包含分区键。原因也很直观：每个分区底层都是一张独立物理表，各自的索引只能保证分区内唯一，如果想要全局唯一，数据库需要跨分区检查，代价极大。所以分区键必须并入唯一约束，这是 PG 的设计取舍。

父表建好之后，开始建具体分区：

CREATE TABLE payment_log_2025_03 PARTITION OF payment_log FOR VALUES FROM ('2025-03-01') TO ('2025-04-01'); CREATE TABLE payment_log_2025_04 PARTITION OF payment_log FOR VALUES FROM ('2025-04-01') TO ('2025-05-01'); CREATE TABLE payment_log_2025_05 PARTITION OF payment_log FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');

注意FOR VALUES FROM ... TO ...的区间是“左闭右开”的。也就是说 3 月分区的范围包含2025-03-01 00:00:00，但不包含2025-04-01 00:00:00。这样做的好处是相邻分区边界不重叠，也不会有数据“漏缝”。

索引建议直接在父表上创建：

CREATE INDEX idx_payment_log_pay_time ON payment_log (pay_time); CREATE INDEX idx_payment_log_user_id ON payment_log (user_id);

在父表上建索引时，PostgreSQL 会递归到所有已有分区上创建对应的索引。后续新增分区时，只要父表已经存在索引定义，新分区也会自动创建好索引，不需要你手工去每个分区补。这一点比早期版本省心太多，我早期用 PG 10 时经常漏建新分区的索引，后来升级到 PG 12 就再也没遇到过。

2.2 默认分区：方便背后藏着坑

很多 DBA 喜欢在分区表上再加一个 DEFAULT 分区兜底，防止因为边界写错或者数据异常导致插入报错。这个想法可以理解，但我要明确告诉你一个生产环境里的教训：DEFAULT 分区会让“异常数据”从报错变成静默入库，还会影响分区裁剪的效率。

具体来说，如果建了 DEFAULT 分区，你查WHERE pay_time >= '2025-03-01' AND pay_time < '2025-03-02'这种具体日期的范围，优化器还算能得到有效裁剪；但如果查询范围比较宽，比如pay_time > '2024-01-01'，优化器无法保证 DEFAULT 分区里没有符合条件的数据，所以不得不把 DEFAULT 分区也纳入扫描范围。DEFAULT 分区里的“垃圾数据”越多，这个额外扫描的代价就越高。

我的做法是：生产区可以建 DEFAULT 分区，但必须有监控盯住它的大小。正常的写入不应该有任何数据落到 DEFAULT 分区，一旦发现 DEFAULT 分区有数据，第一时间告警并人工介入。很多更激进的生产环境直接不建 DEFAULT 分区，宁可让写入报错，也不要让脏数据悄悄流进去。这个选择没有绝对对错，核心是别把 DEFAULT 分区当成保险箱就不管了。

2.3 验证分区路由和数据裁剪

建完后，我用一条插入语句验证数据是否自动路由到正确的分区：

INSERT INTO payment_log (user_id, order_id, amount, pay_time) VALUES (1001, 88888, 99.00, '2025-03-15 12:30:00');

然后查分区中的数据量：

SELECT tableoid::regclass AS partition_name, count(*) FROM payment_log GROUP BY tableoid::regclass;

tableoid是每行数据实际所在物理表的系统列。这条 SQL 也是我后来排查“数据是否落入错误分区”时最常用的工具，比去每个分区分别 count 高效得多。

3. 日常运维的细节：新增、归档、删除、自动化

3.1 写一个自动加分区的小函数

按月分区意味着每个月都要新建一张分区表。手工执行 DDL 一次两次没问题，时间久了必出纰漏。我的习惯是写一个自动化函数，配合定时任务在月初跑一次。

下面这个函数是我在生产环境里的简化版：

CREATE OR REPLACE FUNCTION create_payment_log_partition(p_date date) RETURNS void LANGUAGE plpgsql AS $$ DECLARE partition_name text; start_date text; end_date text; BEGIN partition_name := 'payment_log_' || to_char(p_date, 'YYYY_MM'); start_date := to_char(p_date, 'YYYY-MM-01'); end_date := to_char(p_date + interval '1 month', 'YYYY-MM-01'); IF to_regclass(partition_name) IS NOT NULL THEN RAISE NOTICE 'partition % already exists', partition_name; RETURN; END IF; EXECUTE format( 'CREATE TABLE %I PARTITION OF payment_log FOR VALUES FROM (%L) TO (%L)', partition_name, start_date, end_date ); END; $$;

调用方式很简单：

SELECT create_payment_log_partition('2025-06-01');

这里有几个细节值得说：

• to_regclass(partition_name)是用来检查分区是否已存在的安全方法，避免重复建表报错。
• format('%I', partition_name)是为了防止表名被当作 SQL 注入，养成习惯，动态 DDL 一律走format。
• %L会把日期参数转成带引号的字符串字面量，避免拼 SQL 时类型转换出问题。

我一般会在每月 1 号的定时任务里调用它，而且一次创建未来 3 个月的空白分区。这样即使定时任务漏跑一次，下个月的分区也已经提前建好了，留了缓冲空间。

3.2 归档历史数据用 DETACH，别直接 DROP

清理历史数据最常见的操作是DROP TABLE payment_log_2024_01。这个操作本身很快，但要小心：如果还没确认数据是否需要留存就直接 drop，后面想找回来就麻烦了。

我更推荐先DETACH再做决定：

ALTER TABLE payment_log DETACH PARTITION payment_log_2024_01;

这条命令执行后，payment_log_2024_01会变成一张独立的普通表，不再属于分区父表。这个状态非常灵活：

• 你可以把它转储成外部文件归档，比如用COPY导出后压缩存到低成本存储。
• 你可以继续在库里留一段时间，业务需要查老数据时还能直接查这张表。
• 确认彻底不需要了，再DROP TABLE payment_log_2024_01。

需要注意版本差异：在 PG 14 之前，DETACH PARTITION会获取父表的ACCESS EXCLUSIVE锁，如果在业务高峰期执行，会导致依赖这张分区表的写入全部阻塞。PG 14 开始支持CONCURRENTLY选项：

ALTER TABLE payment_log DETACH PARTITION payment_log_2024_01 CONCURRENTLY;

这个操作会在父表和分区之间保留一个临时的约束检查，锁的时间大幅缩短。但CONCURRENTLY模式执行时间更长，而且不能在一个事务块里执行。我在线上一般选择凌晨低峰期执行普通 DETACH，既能保证业务影响最小，又不用等太久。

3.3 ATTACH 批量导入历史数据

如果想把历史数据重新挂回分区表，比如从归档恢复某几个月的流水，就用ATTACH PARTITION：

ALTER TABLE payment_log ATTACH PARTITION payment_log_2024_01 FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

执行之前必须确认两件事：第一，这张表的结构跟父表完全一致；第二，表里的数据都落在声明的边界范围内。如果数据越界，ATTACH 会直接失败。如果表中没有强制约束保证数据范围，数据库会在 ATTACH 时全表扫描去校验，数据量大的话这个校验过程会比较久。

我的经验是：在导入历史数据之前，先给待附加的表补一个 CHECK 约束，比如：

ALTER TABLE payment_log_2024_01 ADD CHECK (pay_time >= '2024-01-01' AND pay_time < '2024-02-01') NOT VALID;

这样 ATTACH 时数据库能利用 CHECK 约束快速确认数据边界，不用做全表扫描。同时这个约束也能防止后续误往这张表里插入越界数据。这张表被 ATTACH 之后，CHECK 约束会自动被识别为分区边界约束，不需要额外删除。

3.4 统计信息与 autovacuum 策略

分区表比普通表多了很多物理文件，统计信息也分散在每张分区上。我在实际运维中有一个比较深的体会：不能只盯着父表做分析，每个分区都要及时 ANALYZE，否则优化器估算行数会严重失真。

PostgreSQL 的 autovacuum 默认对分区表是逐分区处理的。你可以在父表上设置存储参数，它会继承到已有分区和未来新建的分区：

ALTER TABLE payment_log SET (autovacuum_vacuum_scale_factor = 0.05); ALTER TABLE payment_log SET (autovacuum_vacuum_threshold = 10000);

如果某些分区数据量特别大，单独调小这些参数，避免大量更新后触发 vacuum 不及时导致表膨胀。另外，分区表的统计信息采集我建议手动加一个定时任务，每周对最近一个月内的分区做一次ANALYZE，保证优化器拿到的是最新分布情况。毕竟数据倾斜很常见，月份之间差异有时远超 autovacuum 的估算。

4. 查询优化：分区裁剪为什么有时不生效

4.1 分区裁剪是怎么工作的

分区裁剪（Partition Pruning）的原理不复杂：优化器在执行计划生成阶段，会根据查询条件中的分区键，判断哪些分区不可能包含符合条件的数据，然后把这些分区的扫描直接从计划里去掉。

比如你查：

SELECT * FROM payment_log WHERE pay_time >= '2025-03-05' AND pay_time < '2025-03-06';

优化器看到时间范围落在 2025 年 3 月分区内，执行计划里就只会出现payment_log_2025_03这一个扫描节点，其他分区完全不碰。这就是分区裁剪的价值——它不是在“扫描后过滤”，而是在“扫描前决定要不要扫”。

但这种裁剪是有条件的。最核心的一点是：查询条件里必须包含分区键，而且条件要能推导出明确的范围。如果查询写成WHERE amount > 100，优化器没法从条件里判断该扫哪个分区，只能全分区扫描。所以我在设计表结构时，都会跟业务确认：核心查询是不是一定带时间范围。如果不带，分区收益就要打个问号。

4.2 破坏裁剪的几种典型写法

以下写法我在实际排查中都遇到过，每一个都曾让人困惑“为什么我建了分区表还是这么慢”。

一道常见的错误是把分区键包在函数里：

SELECT * FROM payment_log WHERE date_trunc('month', pay_time) = '2025-03-01';

只要索引列或分区键被函数包裹，优化器就无法直接推导出分区边界。这种情况下即使 pay_time 上建立了索引，也很难走索引扫描，分区裁剪自然失效。正确写法是范围条件：

SELECT * FROM payment_log WHERE pay_time >= '2025-03-01' AND pay_time < '2025-04-01';

二是隐式类型转换。如果分区键是timestamp，查询条件却传入一个字符串，且两边类型不一致，优化器有时候会做隐式转换导致条件无法与分区边界直接比较。最稳妥的方式是保证传入参数类型与分区键一致，或者在 SQL 里显式给参数加类型。

三是查询条件使用了OR。比如：

SELECT * FROM payment_log WHERE user_id = 1001 OR pay_time >= '2025-03-01';

OR条件会把裁剪逻辑打乱，优化器可能不得不把所有分区都纳入计划。遇到这种情况，我通常建议改写为UNION ALL或者调整 SQL 结构，把可以裁剪的条件独立出来。

四是 DEFAULT 分区带来的拖累。前文提到过，DEFAULT 分区会让范围较大的查询无法排除它。所以如果分区表常驻 DEFAULT 分区，而且里面又有大量数据，查询计划可能把 DEFAULT 分区也加进去扫一遍。我经历过最夸张的一次，一张 6 个月分区表因为 DEFAULT 分区有 2000 多万条脏数据，一个本来只该扫单分区的查询硬生生扫了全部 7 张表，慢了几十倍。

4.3 用 EXPLAIN 验证是否真的只扫了需要的分区

验证分区裁剪效果最简单的方法就是看执行计划。我用两个命令：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM payment_log WHERE pay_time >= '2025-03-05' AND pay_time < '2025-03-06';

执行计划里如果出现类似下面的输出，说明裁剪成功：

Append Subplans Removed: 5 -> Seq Scan on payment_log_2025_03

Subplans Removed: 5表示从 6 个分区里裁掉了 5 个，只剩 3 月分区被扫描。如果没有看到这行，或者 Append 下面挂了多张分区表，说明裁剪没有生效，需要回头检查 SQL 写法。

补充一个参数：enable_partition_pruning默认是开启的，一般不用动。早期 PostgreSQL 版本用constraint_exclusion参数控制约束排除，默认值是partition，只在分区表上做约束排除。如果你把constraint_exclusion改成off，分区表的裁剪能力会被关掉一小部分，除非遇到特殊问题，否则保持默认即可。

5. 常见故障与排查经验

5.1 数据莫名进了默认分区，怎么处理

前面刚提到 DEFAULT 分区的风险，这里给出实际操作方案。如果发现 DEFAULT 分区有数据，第一件事是搞清楚为什么会进去。最常见的原因是分区键为 NULL：范围分区无法把 NULL 映射到任何区间，就会落到 DEFAULT 分区。

排查方法：

SELECT pay_time, count(*) FROM payment_log_default GROUP BY pay_time;

如果确实存在大量 NULL，根本解法是给分区键加上NOT NULL约束，从数据源头上堵住。如果数据本身有正确的时间，只是由于边界问题跑错了，可以把数据捞出来重插：

INSERT INTO payment_log SELECT * FROM payment_log_default WHERE pay_time IS NOT NULL; DELETE FROM payment_log_default WHERE pay_time IS NOT NULL;

这种搬数据操作量大的时候要分批做，并且在业务低峰期执行。搬完之后，我把 DEFAULT 分区的数据量加到了监控大盘上，每天巡检，超过阈值就告警。

5.2 DETACH 卡住或长时间锁等待

有一次我在线执行DETACH PARTITION，结果发现几十分钟都没结束，业务侧开始报写入超时。排查思路很简单：先看是不是有长事务持有锁。

SELECT pid, state, wait_event_type, wait_event, now() - xact_start AS xact_age, left(query, 100) AS query_text FROM pg_stat_activity WHERE query ILIKE '%payment_log%' ORDER BY xact_start;

结果发现有一个跑了很久的分析查询没提交，阻塞了 DETACH 的锁请求。处理方式要么等它结束，要么在业务允许的情况下终止会话：

SELECT pg_terminate_backend(pid);

有了这次教训，我现在做分区维护前都会先跑一遍pg_locks检查是否有长事务。如果必要，也可以把 DETACH 安排在凌晨定时任务里，最大程度避开高峰。

5.3 分区表的唯一约束和主键设计限制

前文提过声明式分区表的主键必须包含分区键。这里展开说。假设你想给payment_log的order_id加一个唯一约束，直接执行：

ALTER TABLE payment_log ADD UNIQUE (order_id);

大概率会收到类似这样的报错：

ERROR: unique constraint on partitioned table must include the partitioning column

原因在于 PG 的分区只是逻辑上的组织方式，物理上每张分区表是独立存储的。唯一索引只能约束单个分区内部，跨分区唯一性靠索引本身无法保证。如果业务确实需要全局唯一，而分区键又在业务上无法合并进唯一键，那只能说这个表不适合做分区，或者需要换一种唯一性实现方案（比如应用层保证）。这个限制在设计表结构之初就要想清楚，否则后面改起来非常痛苦。

5.4 分区数量失控带来的隐患

分区表不是分得越细越好。我见过有人把表按天分区，半年就是 180 个分区。分区数量过多时，系统表元数据膨胀、查询计划阶段的开销增大、锁管理的成本也上升。尤其在事务里涉及大量分区 DDL 时，锁等待和元数据竞争会变得明显。

建议控制一个度。按周或按月分区通常够用。我通常把一张分区表的分区数量控制在 500 以下，按月就是 40 年左右。如果超过这个量级，应该考虑把更老的分区 DETACH 出去归档，而不是无限保留。还要注意给分区命名规范化：统一用YYYY_MM后缀，排序、查找、脚本处理都方便。

6. 分区表管理常见问题速查

最后说几句个人体会

我在项目里维护分区表已经有好几年了，从最初的手建表，到后来写定时任务自动化，踩过不少坑。现在回过头看，分区表的难点其实不在“建”，而在“日常管理是否规范”。一个分区表方案能不能长久跑下去，看三点：有没有自动建分区的机制，有没有 DEFAULT 分区数据量监控，有没有定期归档和清理老分区的流程。这三点都做到了，分区表基本不会出大问题。有一个小习惯特别想分享：每次创建分区前，我都会把未来三个月的分区一次性建好，宁可多建一个月空分区，也不要在数据到来的当天手工建表。空分区几乎不占存储，却能给定时任务故障留出足够恢复时间。这个小技巧帮我避免过好几次月初数据写入失败的尴尬。