PostgreSQL分区表ATTACH PARTITION性能诊断与优化：default分区数据膨胀案例分析

分区表是大规模数据处理中常用的技术。PostgreSQL自版本10起支持原生分区语法，版本11后增加了对default分区的支持。近期，某客户反馈在使用pg_partman管理PostgreSQL分区表时，增加空分区的操作（ATTACH PARTITION）变得越来越慢，每个分区耗时约3秒。本文将深入分析此问题。

在使用pg_partman自动管理分区时，其内部操作是遍历执行一系列SQL语句，核心是ATTACH PARTITION操作。

导致ATTACH PARTITION操作缓慢的常见原因包括：

• 系统资源紧张，如CPU存在争用。
• 业务高峰期，存在锁资源争用。
• 待附加的分区表不为空，ATTACH过程中需要自动创建与父表一致的索引。
• 分区数量过多。
• 存在default分区，且需要进行数据扫描校验。

在本案例中，客户确认业务空闲、无锁竞争且新分区表为空。进一步检查表结构发现，存在一个45GB的default分区。这极有可能是问题的根源，因为在执行ATTACH PARTITION时，PostgreSQL不仅需要校验新分区，还必须扫描default分区的所有数据，以确保没有数据与新分区的条件范围冲突。下面通过实验来验证这一推断。

实验验证：Default分区数据量对ATTACH速度的影响

首先，我们创建一个分区表，并附加一个空分区，此时速度非常快。

-- 环境 PostgreSQL v17
CREATE TABLE t1 (
    id INTEGER NOT NULL,
    name INTEGER NOT NULL,
    marka varchar(2000),
    PRIMARY KEY(id)
) PARTITION BY RANGE (id);

CREATE TABLE t1_p1 (
    id INTEGER NOT NULL,
    name INTEGER NOT NULL,
    marka varchar(2000)
);

CREATE TABLE t1_other (
    id INTEGER NOT NULL,
    name INTEGER NOT NULL,
    marka varchar(2000)
);

\timing
-- 附加空分区，速度极快
ALTER TABLE t1 ATTACH PARTITION t1_p1 FOR VALUES FROM (10000001) TO (20000000);
-- 执行时间：~5 ms

接下来，将t1_other表设置为DEFAULT分区，并向其中插入1000万条数据，模拟数据膨胀的default分区。

ALTER TABLE t1 ATTACH PARTITION t1_other DEFAULT;

-- 向DEFAULT分区插入1000万条数据
INSERT INTO t1
SELECT x, x, 'xxxxxxxx' FROM generate_series(1, 10000000) AS x;
-- 执行时间：~32.5 秒

\dt+ t1
-- 列表显示 t1_other 表大小约为 498 MB

现在，再次尝试将空的t1_p1附加为分区。可以看到，耗时从毫秒级飙升到了近1秒。

ALTER TABLE t1 ATTACH PARTITION t1_p1 FOR VALUES FROM (10000001) TO (20000000);
-- 执行时间：~965 ms

为了更清晰地观察趋势，我们继续向DEFAULT分区追加数据，使其体积增长到约1.5GB，然后附加一个范围不冲突的新分区。此时，ATTACH时间进一步增加到了约2.5秒。

-- 向DEFAULT分区再插入1000万条数据 (ID: 20000001 - 30000000)
INSERT INTO t1 SELECT x,x,'xxxxxxxx' FROM generate_series(20000001, 30000000) AS x;

\dt+ t1
-- 此时 t1_other 表大小约为 1493 MB

ALTER TABLE t1 ATTACH PARTITION t1_p1 FOR VALUES FROM (30000001) TO (40000000);
-- 执行时间：~2479 ms (约2.5秒)

通过开启Debug日志，可以确认ATTACH操作确实在执行verifying table “t1_other”。

SET client_min_messages = debug5;
ALTER TABLE t1 ATTACH PARTITION t1_p1 FOR VALUES FROM (30000001) TO (40000000);
-- 日志输出包含：
-- 调试:  verifying table "t1_p1"
-- 调试:  verifying table "t1_other"
-- 执行时间：~2222 ms

实验结论：随着DEFAULT分区 (t1_other) 数据量的增大，ATTACH PARTITION 操作的耗时显著增加。核心原因是需要全表扫描DEFAULT分区以进行约束冲突校验。

扩展场景：待附加分区非空的影响

如果待附加的分区表本身也包含数据，ATTACH操作时间会更长，因为它需要同时校验两张表。

-- 先分离分区，并向 t1_p1 插入1000万条符合其分区键范围的数据
ALTER TABLE t1 DETACH PARTITION t1_p1;
INSERT INTO t1_p1 SELECT x,x,'xxxxxxxx' FROM generate_series(30000001, 40000000) AS x;

-- 再次附加（此时需校验 t1_p1 和庞大的 t1_other）
ALTER TABLE t1 ATTACH PARTITION t1_p1 FOR VALUES FROM (30000001) TO (40000000);
-- 执行时间：~3686 ms (约3.7秒)

性能优化建议

基于以上分析，可以采取以下措施优化ATTACH PARTITION性能：

1. 预先创建Check约束
在ATTACH之前，手动为分区表创建与目标分区范围一致的CHECK约束，可以减少ATTACH时的一部分验证工作。

ALTER TABLE t1 DETACH PARTITION t1_p1;
-- 手动添加CHECK约束
ALTER TABLE t1_p1
    ADD CHECK (id IS NOT NULL AND id >= 30000001 AND id < 40000000);
-- 执行时间：~168 ms

ALTER TABLE t1 ATTACH PARTITION t1_p1 FOR VALUES FROM (30000001) TO (40000000);
-- 执行时间：~2113 ms (相比3.7秒有提升)

2. 分离DEFAULT分区后再操作
这是最有效的提速方法。在业务低峰期，先将DEFAULT分区分离，再进行分区维护操作，最后重新附加DEFAULT分区。这完全避免了扫描DEFAULT分区的开销。

ALTER TABLE t1 DETACH PARTITION t1_p1;
ALTER TABLE t1 DETACH PARTITION t1_other; -- 关键步骤：分离大体积的DEFAULT分区

ALTER TABLE t1 ATTACH PARTITION t1_p1 FOR VALUES FROM (30000001) TO (40000000);
-- 执行时间：~1.7 ms (恢复毫秒级)

-- 待所有分区维护完成后，再重新附加DEFAULT分区
-- ALTER TABLE t1 ATTACH PARTITION t1_other DEFAULT;

总结

当使用ATTACH PARTITION方式管理PostgreSQL分区表时，操作性能主要受以下两点影响：

1. DEFAULT分区的数据量：ATTACH过程中必须扫描整个DEFAULT分区以检查数据冲突，这是导致性能下降的主要原因。
2. 待附加分区本身的数据量：如果待附加分区非空，也需要进行数据校验。

在数据库性能优化实践中，对于包含大数据量DEFAULT分区的表，建议在维护窗口期采用“先分离DEFAULT分区，再维护，最后重新附加”的流程，可以极大提升分区管理操作的效率。此外，在高并发访问的系统规划分区策略时，应尽量避免DEFAULT分区积累大量数据，或者考虑使用其他分区管理策略。