线上 DDL 锁表导致服务挂了？谈谈 PostgreSQL 数据库迁移的 Review 习惯

前阵子刚经历了一次惊心动魄的线上事故：某个核心业务表需要紧急新增一个 status 字段，带有默认值并且是 NOT NULL。开发同学觉得这只是个简单的 DDL，直接写了个迁移脚本上线了。结果执行的一瞬间，监控报警铺天盖地而来，整个服务的 API 响应时间陡增，连接池瞬间爆满，数据库 CPU 直接拉满。

后来排查才明白，PostgreSQL 对该表执行 ALTER TABLE ... ADD COLUMN ... DEFAULT ... NOT NULL 时，需要获取该表的 AccessExclusiveLock（排他锁）。在低版本 PG 中，这不仅会阻塞所有的读写请求，如果该字段有非空默认值，PG 还会去全表重写数据文件，直接导致这个锁一直被持有，直到千万级数据全部改写完毕。高流量下，排队的读写事务瞬间把连接池占满，直接把整个系统拖崩溃了。

这次血泪教训让我们深刻意识到，绝对不能在生产环境对大表“裸奔”执行 DDL。现在，我们的数据库迁移 Review 规范里多了三条死命令：

第一，必须设置合理的 lock_timeout。宁可让 DDL 迁移事务因为拿不到锁而超时报错，也绝对不能让它挂在那里排队阻塞后续所有的业务查询。 第二，拆分 DDL 步骤，避免大表重写。先加一个允许 NULL 且无默认值的字段，这在 PG 里是秒级的（只改元数据），然后再通过背景任务分批更新（Backfill）默认值，最后加上 NOT NULL 约束。 第三，创建索引时必须使用 CONCURRENTLY 关键字，防止建索引期间锁死整张表。

下面是我们在生产环境对一张大表安全新增一个 NOT NULL 带有默认值字段的完整 SQL 方案：

-- 1. 设置会话级别的锁超时时间，防止 DDL 事务长时间排队挂死服务
SET statement_timeout = '5s';
SET lock_timeout = '3s';

-- 2. 添加一个允许为 NULL 且不带默认值的字段（元数据操作，瞬间完成）
ALTER TABLE users ADD COLUMN vip_level INTEGER;

-- 3. 释放锁。接下来在应用层或者通过分批脚本回填默认值
-- 下面是分批回填（Backfill）的伪代码 SQL 逻辑，建议每次更新小批量数据（例如 5000 条），并在每次迭代间短暂 sleep
-- 比如在后端写一个循环逻辑：
-- UPDATE users SET vip_level = 0 WHERE id >= :start_id AND id < :end_id AND vip_level IS NULL;

-- 4. 确认历史数据全部回填完毕后，添加 DEFAULT 约束（这只对新插入的数据生效，不涉及历史数据重写）
ALTER TABLE users ALTER COLUMN vip_level SET DEFAULT 0;

-- 5. 使用安全的方式添加 NOT NULL 约束（PG 12+ 支持通过 VALIDATE CONSTRAINT 减少锁时间）
-- 我们可以先添加一个 NOT VALID 的约束，这样不需要全表扫描锁表
ALTER TABLE users ADD CONSTRAINT check_vip_level_not_null 
  CHECK (vip_level IS NOT NULL) NOT VALID;

-- 6. 然后并发地去验证这个约束，此时只会持有 RowShareLock，不阻塞读写
ALTER TABLE users VALIDATE CONSTRAINT check_vip_level_not_null;

-- 7. 如果你追求完美，想转成真正的 NOT NULL 属性，可以再执行（此时因为约束已被验证，PG 会瞬间完成转换）
ALTER TABLE users ALTER COLUMN vip_level SET NOT NULL;

如果表里的数据有千万级，回填数据的过程干脆用 Python 或者 Go 写一个游标分页脚本，通过主键 ID 范围一点点滚过去。千万别指望一个 UPDATE users SET vip_level = 0 WHERE vip_level IS NULL; 搞定，不然光是 Undo/Redo 日志和表锁就能让你的数据库卡死几个小时。

这个方案虽然折腾，但换来的是生产环境的绝对丝滑。每次看到有些项目组还在直接用 ORM 框架的自动 migrate 搞线上发布，我心里都捏一把汗。

你们在对大表做迁移时，会用 gh-ost、pt-online-schema-change 这类外部工具，还是像我们一样手写多步 SQL 迁移？有什么好用的 PG 锁监控脚本推荐吗？