如何在不重建大量分区索引的情况下将PostgreSQL分区主表的唯一索引升级为唯一约束

我太懂这种面对海量分区表时的无力感了——任何需要扫数据、重建索引的操作都是绝对不能碰的红线。咱们从你现有的场景出发，一步步用元数据级别的操作解决问题，完全不用动你的核心数据。

问题根源分析 #

你遇到的卡壳点本质是PostgreSQL分区表的两个限制：

1. 分区表不支持直接通过ALTER TABLE ... ADD CONSTRAINT ... USING INDEX将现有索引转成约束；
2. 已挂载的分区会和主表的索引形成依赖关系，导致你无法直接删除主表的旧索引。

但好在所有涉及分区挂载/卸载的操作都是纯元数据操作，不会移动或修改任何业务数据，执行速度都是毫秒级的，完美适配百万级分区的场景。

完整解决方案步骤 #

1. 批量卸载所有分区（解除依赖） #

首先需要把所有分区从主表上临时卸载，这样才能断开分区约束和主表索引的依赖关系。

生成批量卸载脚本

如果分区表命名有规律（比如你的ttab_pX格式），可以用动态SQL生成所有卸载命令：

SELECT 'ALTER TABLE ttab DETACH PARTITION ' || quote_ident(relname) || ';'
FROM pg_class
WHERE relname LIKE 'ttab_p%' -- 匹配你的分区表命名规则
  AND relkind = 'r'
  AND EXISTS (
    SELECT 1 FROM pg_inherits
    WHERE inhrelid = pg_class.oid
      AND inhparent = 'ttab'::regclass
);

执行生成的所有ALTER TABLE ... DETACH PARTITION语句，这些操作都是瞬间完成的，不会影响数据。

2. 删除主表的旧唯一索引 #

现在分区已经全部卸载，主表索引的依赖关系已解除，可以安全删除：

DROP INDEX unq_ttab_1;

3. 在主表上创建唯一约束 #

直接在主表上创建包含分区键的唯一约束（你的场景中已经包含partition_num，符合PostgreSQL分区表唯一约束的要求）：

ALTER TABLE ttab ADD CONSTRAINT unq_ttab UNIQUE (partition_num, id);

这一步会在主表上注册唯一约束，后续挂载分区时会自动关联分区的现有约束。

4. 批量重新挂载所有分区 #

把之前卸载的分区重新挂载回主表，PostgreSQL会自动识别分区上已有的唯一约束，不会要求重建索引。

生成批量挂载脚本

同样用动态SQL生成挂载命令：

SELECT 'ALTER TABLE ttab ATTACH PARTITION ' || quote_ident(relname) || ' FOR VALUES IN (' || regexp_replace(relname, 'ttab_p', '', 'g') || ');'
FROM pg_class
WHERE relname LIKE 'ttab_p%'
  AND relkind = 'r'
  AND NOT EXISTS (
    SELECT 1 FROM pg_inherits
    WHERE inhrelid = pg_class.oid
      AND inhparent = 'ttab'::regclass
);

执行所有生成的挂载语句，依然是纯元数据操作，无数据扫描。

5. 验证新分区的自动继承效果 #

现在创建新分区时，会自动继承主表的唯一约束，无需手动创建索引再转约束：

-- 创建新分区（自动继承主表的约束）
CREATE TABLE ttab_p5 (like ttab including defaults including constraints);
-- 挂载分区
ALTER TABLE ttab attach partition ttab_p5 for values in (5);

查看新分区结构：

\d+ ttab_p5

你会看到PostgreSQL已经自动为新分区创建了唯一约束和对应索引，完全符合你的需求。

关键注意事项 #

1. 版本要求：确保你的PostgreSQL版本是11+，10及更早版本不支持分区表的唯一约束；
2. 约束必须包含分区键：这是PostgreSQL分区表唯一约束的硬性要求，否则无法保证跨分区的唯一性（你的场景已经满足）；
3. 低峰期操作：虽然挂载/卸载是元数据操作，但如果分区上有正在执行的写请求，可能会短暂阻塞，建议在业务低峰期执行；
4. 事务安全：PostgreSQL 12+支持在事务中执行分区挂载/卸载操作，如果担心中间出错，可以把卸载/挂载的批量操作分别放在事务中执行，出错可回滚。

内容来源于stack exchange