生产环境大表用序列回填可空id列的安全方案咨询

关键前置操作：同步序列起始值 #

已有新行通过序列生成ID，首先确保序列的当前值高于现有所有已生成的ID，从根源避免回填时与新行ID冲突：

-- 将序列值设置为现有最大ID+1，无已生成ID则从1开始
SELECT setval('my_table_id_seq', COALESCE((SELECT MAX(id) FROM my_table), 0) + 1);

分批回填空ID行（低锁、高并发友好） #

采用CTE分批处理，每次更新1万行，循环执行直到所有空ID行被填充。使用FOR UPDATE SKIP LOCKED避免长时间锁表，同时跳过已被其他事务锁定的行：

WITH target_rows AS (
    SELECT ctid
    FROM my_table
    WHERE id IS NULL
    LIMIT 10000
    FOR UPDATE SKIP LOCKED
)
UPDATE my_table
SET id = nextval('my_table_id_seq')
FROM target_rows
WHERE my_table.ctid = target_rows.ctid;

• 重复执行此SQL，直到返回的UPDATE行数为0，说明所有历史行已完成回填。
• 用ctid作为匹配条件，是因为当前ID列为空、无唯一键可用，ctid是PostgreSQL中行的物理位置标识，可精准定位目标行。
• FOR UPDATE SKIP LOCKED机制确保当前事务仅处理未被锁定的空ID行，不会阻塞其他正常的插入/更新操作。

规避并发插入的竞态条件 #

• 序列本身具备原子性：无论是新插入行调用nextval，还是回填操作调用nextval，PostgreSQL都会保证序列值的唯一递增，不会出现重复。
• 前置的setval操作确保序列起始值高于所有已存在的ID，彻底杜绝了回填ID与已插入新行ID重复的可能。

收尾：设置非空约束与主键 #

所有行回填完成后，将ID列设为非空并添加主键约束：

-- 设为非空列
ALTER TABLE my_table ALTER COLUMN id SET NOT NULL;

-- 大表建议用CONCURRENTLY避免锁表：先创建并发索引，再关联主键
CREATE INDEX CONCURRENTLY idx_my_table_id ON my_table(id);
ALTER TABLE my_table ADD CONSTRAINT pk_my_table_id PRIMARY KEY USING INDEX idx_my_table_id;

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