PostgreSQL：无停机无锁为现有表添加唯一约束的方案咨询

一、无锁添加新VARCHAR列的最优方式 #

PostgreSQL中，添加默认值为NULL的新VARCHAR列是近乎无锁的操作，不会阻塞表的读写，也无需停机。执行语句如下：

ALTER TABLE "my_table" ADD COLUMN "my_column" VARCHAR(255) NULL;

原理：PostgreSQL在添加NULL默认值的列时，不会修改现有数据行，仅更新系统目录中的表结构元数据，因此锁表时间极短，几乎不影响业务。

如果需要给列设置非NULL默认值，需分三步避免长时锁表：

1. 先添加NULL列（如上语句）
2. 用UPDATE并发批量更新现有行的列值（可分批执行，避免锁表）
3. 最后修改列的非NULL约束（此时表中已有值，操作耗时极短）

-- 分批更新示例（根据表大小调整batch_size）
WITH batch AS (
    SELECT id FROM "my_table" WHERE "my_column" IS NULL LIMIT 1000
)
UPDATE "my_table" SET "my_column" = 'default_value' WHERE id IN (SELECT id FROM batch);
-- 重复执行直到所有行更新完成

ALTER TABLE "my_table" ALTER COLUMN "my_column" SET NOT NULL;

二、基于现有并发索引添加唯一约束的锁表情况 #

你提到的ALTER TABLE ... ADD CONSTRAINT UNIQUE USING INDEX ...操作，不会产生长时表锁，只会持有极短时间的ACCESS EXCLUSIVE锁，完全不会导致停机或阻塞业务。

原因：当你已经通过CREATE INDEX CONCURRENTLY创建了唯一索引，该索引已经验证了表中所有行的唯一性。此时执行添加约束的语句，PostgreSQL仅需将现有索引标记为约束（更新系统元数据），不需要重新扫描表或验证数据，锁持有时间仅为毫秒级，对业务无影响。

执行语句保持不变即可：

ALTER TABLE "my_table" ADD CONSTRAINT "my_unique_constraint" UNIQUE USING INDEX "my_unique_index";

三、关于唯一约束不支持NOT VALID的替代方案 #

确实，PostgreSQL的唯一约束不支持NOT VALID选项，因为唯一约束的核心要求就是所有行必须满足唯一性，不存在“先创建约束再验证”的逻辑。但你可以通过以下方式实现无锁的唯一约束落地：

1. 先通过CREATE INDEX CONCURRENTLY创建唯一索引（此操作无锁，会在后台扫描表并验证唯一性，不阻塞读写）
2. 再执行上述ALTER TABLE语句将索引转为约束（极短锁，无影响）

这就是最优的无锁流程，既保证了唯一性，又避免了长时锁表。

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