生产环境PostgreSQL大表添加外键及并发创建索引的行为咨询

核心结论：CREATE INDEX CONCURRENTLY 不会阻塞写事务，也无需等待所有现有事务结束 #

1. 并发索引的执行逻辑 #

CREATE INDEX CONCURRENTLY 是专为生产环境设计的低影响索引创建方式，执行分为两个关键阶段：

• 第一阶段：扫描全表构建索引的基础数据，同时PostgreSQL会实时跟踪这段时间内针对该表的所有写操作（INSERT/UPDATE/DELETE）。这个阶段完全允许写事务并行执行，不会阻塞任何业务操作。
• 第二阶段：将第一阶段记录的所有写操作同步到新索引中，确保索引与表数据完全一致。这个阶段只会申请短暂的共享锁（不阻塞写），完成后索引即可正常使用。

整个过程不需要等待现有事务全部结束，而是和写事务并行推进，仅在最后收尾阶段需要极短的时间窗口完成一致性校验。

2. 大表添加外键的锁风险规避 #

直接用常规方式添加外键会触发全表数据校验，同时加排他锁，导致长时间阻塞写操作，这对数十亿行的表是致命的。正确的操作步骤是：

1. 先创建并发索引：

   CREATE INDEX CONCURRENTLY idx_xxx_user_id ON xxx (user_id);
   

2. 添加未验证的外键约束：

   ALTER TABLE xxx ADD CONSTRAINT fk_xxx_user_id FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
   

   这个操作仅修改表结构元数据，只会加短暂的排他锁，不会检查现有数据，几乎不影响业务。
3. 异步验证外键约束：

   ALTER TABLE xxx VALIDATE CONSTRAINT fk_xxx_user_id;
   

   验证过程是并发执行的，仅加共享锁，不阻塞写事务，会后台扫描表数据，确保所有已存在的user_id都在用户表中有对应记录。

3. 关键注意事项 #

• CREATE INDEX CONCURRENTLY 执行时间会比常规索引长（需处理并发写），对于数十亿行的表，可能需要数小时甚至更久，建议在业务低峰期执行。
• 若执行过程中断，需手动删除未完成的索引（PostgreSQL不会自动清理），否则下次创建会报错。
• 确保用户表的被引用列（如主键id）已有索引，否则外键验证阶段会触发全表扫描用户表，引发性能问题。

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