Postgres：为千万级存量表添加bigserial字段致应用停机问题

直接执行ALTER TABLE "table_1" add column "serial_id" BIGSERIAL会阻塞全表读写的核心原因是：BIGSERIAL本质是bigint类型+序列关联+默认值nextval(序列名)，PostgreSQL会扫描全表为每一行生成序列值，这个过程会持有排他锁，直到所有行赋值完成，期间表上的所有读写操作都会被阻塞。

以下是无阻塞的分步实施方案：

1. 添加可空bigint字段
   这一步瞬时完成，无需扫描全表或给行赋值，不会阻塞业务：

   ALTER TABLE "table_1" ADD COLUMN "serial_id" bigint;
   

2. 创建关联序列
   手动创建序列并关联到字段，替代BIGSERIAL自动创建的逻辑：

   CREATE SEQUENCE table_1_serial_id_seq OWNED BY "table_1"."serial_id";
   

3. 分批更新现有数据
   一次性更新数百万行会锁表并占用大量资源，因此采用分批更新的方式，每次处理小批量数据（比如1000行，可根据服务器性能调整）：

   WITH batch AS (
       SELECT id FROM "table_1" WHERE "serial_id" IS NULL LIMIT 1000
   )
   UPDATE "table_1" t
   SET "serial_id" = nextval('table_1_serial_id_seq')
   FROM batch b
   WHERE t.id = b.id;
   

   重复执行此SQL，直到返回的更新行数为0（所有行都已赋值）。可以用psql的循环脚本或应用端定时任务自动执行。

4. 设置字段默认值
   让新插入的行自动获取序列值，这一步不会阻塞：

   ALTER TABLE "table_1" ALTER COLUMN "serial_id" SET DEFAULT nextval('table_1_serial_id_seq');
   

5. 创建唯一索引（非阻塞）
   使用CONCURRENTLY创建唯一索引，避免阻塞表的读写：

   CREATE UNIQUE INDEX CONCURRENTLY idx_table_1_serial_id ON "table_1"("serial_id");
   

6. 将字段设为主键
   基于已创建的唯一索引转换为主键约束，这是原子操作，只会短暂锁表：

   ALTER TABLE "table_1" ADD CONSTRAINT pk_table_1_serial_id PRIMARY KEY USING INDEX idx_table_1_serial_id;
   

7. （可选）替换原UUID主键
   待业务验证serial_id可用后，在低峰期删除原UUID主键约束和字段。注意先确保所有关联表的外键已切换到serial_id。

注意事项 #

• 分批更新的批量大小需根据服务器CPU、内存性能调整，避免单次更新占用过多资源。
• 所有操作尽量在业务低峰期执行，减少对用户的影响。
• 操作前务必备份全表数据，防止意外情况导致数据丢失。

内容的提问来源于stack exchange，提问作者yangli-io