为PostgreSQL现有索引添加唯一约束的性能影响咨询

我在运维PostgreSQL大表（亿级行规模）时刚好处理过类似场景，来给你唠唠实际操作里的关键点和避坑指南：

先搞懂底层逻辑：利用已有索引省时间 #

你提到UUID列已经有索引了，这点特别关键！PostgreSQL执行ALTER TABLE ... ADD CONSTRAINT ... UNIQUE时，如果目标列存在同列的B-tree索引，数据库不会重新构建索引，而是直接把现有索引标记为唯一索引——这能省去大量索引构建时间，但数据唯一性验证的步骤绕不开：数据库必须扫描全表确认没有重复的UUID值。

停机执行vs在线执行的差异 #

1. 停机执行（最稳妥） #

如果能接受短时间停机，直接跑下面的命令就行：

ALTER TABLE your_table ADD CONSTRAINT uuid_unique UNIQUE (uuid_column);

• 锁表情况：会持有SHARE ROW EXCLUSIVE锁，期间所有写操作（INSERT/UPDATE/DELETE）都会被阻塞，但因为是停机状态，不用担心业务受影响。
• 耗时：主要取决于表的大小和IO性能，比如1亿行的表在SSD磁盘上可能需要20-30分钟，机械盘会更久。
• 优点：步骤简单，没有并发冲突风险，执行完成后直接生效。

2. 在线执行（零停机但需技巧） #

如果不能停机，直接执行上面的命令会出大问题：锁表期间所有写请求都会被阻塞，业务会出现大量超时或报错，甚至导致连接池耗尽。这时候必须用分阶段操作来降低影响：

推荐操作步骤：

• 第一步：提前验证数据唯一性
  先跑查询确认没有重复值，避免后续操作失败：

  SELECT uuid_column, COUNT(*) 
  FROM your_table 
  GROUP BY uuid_column 
  HAVING COUNT(*) > 1;
  

  如果有重复数据，先清理掉再继续，不然加约束会直接失败。

• 第二步：创建并发唯一索引
  用CREATE UNIQUE INDEX CONCURRENTLY构建唯一索引，这个操作不会阻塞任何写操作：

  CREATE UNIQUE INDEX CONCURRENTLY idx_your_table_uuid_unique ON your_table (uuid_column);
  

  注意：这个操作耗时会比普通索引长（需要两次全表扫描，还要处理并发写入的冲突），但胜在完全不影响业务读写，数据库会自动处理新写入数据的唯一性检查。

• 第三步：将索引关联为唯一约束
  等并发索引创建完成后，执行下面的命令把索引转为约束：

  ALTER TABLE your_table ADD CONSTRAINT uuid_unique UNIQUE USING INDEX idx_your_table_uuid_unique;
  

  这一步几乎是瞬间完成的——只是在元数据层面把现有索引和约束绑定，锁表时间极短（毫秒级），对业务几乎无影响。

关键注意事项 #

• 磁盘空间预留：CREATE UNIQUE INDEX CONCURRENTLY会临时占用额外的磁盘空间（约等于现有索引的大小），要确保磁盘有足够余量，避免中途磁盘满了导致操作失败。
• 版本兼容性：你用的PostgreSQL 9.4完全支持上述操作（CREATE UNIQUE INDEX CONCURRENTLY从9.2开始支持，ADD CONSTRAINT ... USING INDEX从9.1开始支持）。
• 监控与重试：执行并发索引时，可以通过pg_stat_progress_create_index视图监控进度；如果中途失败，直接重新执行即可，PostgreSQL会自动清理残留的无效索引。

总结 #

• 能停机选直接加约束，简单高效；
• 不能停机一定要用“并发索引+绑定约束”的组合，把业务影响降到最低。

内容的提问来源于stack exchange，提问作者Selecsosi