一条 0.3 秒的 DDL，让全站排队了四十秒

事故复盘的开头都很像。周四下午一个"很小的改动"：把 orders.amount 改成更大的数值类型，SQL 就一行：

ALTER TABLE orders ALTER COLUMN amount TYPE bigint;

orders 有 8000 万行。这条语句要拿 ACCESS EXCLUSIVE 锁并重写整张表。执行那一刻，恰好有一个已经跑了 38 秒的报表查询持着 orders 的共享锁，于是：

  1. DDL 进入锁队列，等报表查询结束。
  2. PostgreSQL 的锁队列先来后到、不分对象。排在 DDL 后面的所有 SELECT 和 UPDATE，哪怕与报表查询毫无冲突，也全部排在它后面。
  3. 报表查询结束，重写开始，持续数分钟。

页面超时、连接池打满、依赖雪崩。事后看监控，故障从 DDL 提交那一刻就开始了，而不是从重写开始。在线迁移里，锁等待窗口比执行时长更致命，而且等的受害者往往是不相关的请求。

每条 DDL 的真实代价，先背下来

不需要背完整锁矩阵，但这几条要形成条件反射：

  • ADD COLUMN：任何版本都拿 ACCESS EXCLUSIVE，但通常毫秒级返回。PG11 起带常量默认值不再重写表（只改 catalog），PG10 及以前会全表重写；注意 now()、random() 这类 volatile 默认值在 PG11+ 仍触发重写。
  • ALTER COLUMN TYPE：基本都要重写全表，ACCESS EXCLUSIVE 持有整个重写期。8000 万行就是分钟级的排他锁。
  • CREATE INDEX：拿 SHARE 锁，读不挡、写全挡。
  • CREATE INDEX CONCURRENTLY：SHARE UPDATE EXCLUSIVE，与 DML 兼容。
  • ADD CONSTRAINT ... CHECK (...) NOT VALID：短锁，不扫存量。
  • 外键两步走：ADD FOREIGN KEY ... NOT VALID 立规矩，VALIDATE CONSTRAINT 补验证，验证阶段的锁与 DML 兼容。

演练时配一个实时监控，眼见为实：

-- 谁在等锁、等了多久
SELECT pid, wait_event_type, wait_event,
       now() - query_start AS age,
       left(query, 60) AS query
FROM   pg_stat_activity
WHERE  wait_event_type = 'Lock';

所有迁移 DDL 的前置动作都应该是设锁预算——超时失败无害，重试即可，最怕的是排队：

SET lock_timeout = '3s';        -- 拿不到锁就撤，别当堵塞队列的头
SET statement_timeout = '15min';

CREATE INDEX CONCURRENTLY：不锁写，但多了新的失败模式

CIC 的原理是两遍扫表，再等待所有在建索引启动之前就已存在的事务结束。因此它不能包在事务块里——一旦中途失败（手动取消、唯一冲突、实例重启），索引不会回滚消失，而是残留成 INVALID 状态。这种索引优化器不会用，但每次写入仍要维护它，纯亏。

-- 1. 并发建索引，不阻塞读写
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_created
    ON orders (user_id, created_at);

-- 2. 检查结果：失败的残留是 INVALID
SELECT c.relname, i.indisvalid
FROM   pg_index i
JOIN   pg_class c ON c.oid = i.indexrelid
WHERE  c.relname LIKE 'idx_orders_%';

-- 3. 残留清理：同样用 CONCURRENTLY，别让清理动作再挡一次写
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_created;

两条纪律：CIC 比普通建索引慢得多（两遍扫描加等待），别在高峰期跑；跑之前确认没有长事务——CIC 结束前要等所有先于它启动的事务退出，一个长事务能把它无限拖住。

NOT NULL 也别一步到位

直接 SET NOT NULL 要全表扫描验证存量，扫描全程持有锁。两步法把扫描拆出去：

-- 第一步：加一个不校验存量的 CHECK，只挡新增的 NULL，短锁返回
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_cents_not_null
  CHECK (amount_cents IS NOT NULL) NOT VALID;

-- 第二步：存量回填完成后验证，锁级别与 DML 兼容，不重写表
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_cents_not_null;

-- 第三步（PG12+）：已验证的 CHECK 在场，SET NOT NULL 不再需要全表扫
ALTER TABLE orders ALTER COLUMN amount_cents SET NOT NULL;

外键同理：NOT VALID 先把规矩立起来，VALIDATE 慢慢补验证，两个动作分开提交。

四个阶段：expand、backfill、switch、contract

把"改列"这类需求拆成阶段，每一步都能停、能回滚。以 amount numeric(12,2) 迁到 amount_cents integer（货币统一为最小单位）为例。

expand：新结构上线，老结构原样。

-- 都是短锁 DDL
ALTER TABLE orders ADD COLUMN amount_cents integer;
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_cents_not_null
  CHECK (amount_cents IS NOT NULL) NOT VALID;  -- 应用开始双写后，新行必须有值

应用发布双写版本：同时写 amount 和 amount_cents，读仍然只信旧列。

backfill：把存量搬过去（下一节的脚本），期间新旧代码并存，读不受影响。

switch：读流量灰度切换。

-- 切读前的验收：双写开始后写过的行，新旧列必须一致
-- :dual_write_since 换成双写开始的时间戳（psql -v dual_write_since=...）
SELECT count(*)
FROM   orders
WHERE  updated_at >= :'dual_write_since'
AND    round(amount * 100) IS DISTINCT FROM amount_cents;
-- 期望 0；非 0 先修双写，别切

影子读（新列结果只比对、不返回）→ 按 feature 切 → 按百分比切 → 全量。写切换保持最小窗口：新代码停止写旧列的那一刻起，旧列开始不可信，回滚成本陡增——所以旧列停写要和"观察期结束"绑在一起做决定，而不是顺手关掉。

contract：确认旧列无人再读后收尾。

-- 逻辑下线：留一个完整业务周期（月末账单、季末对账都要再跑一遍）
COMMENT ON COLUMN orders.amount IS
  'DEPRECATED 2026-10: replaced by amount_cents, drop next quarter';

-- 确认无人访问（pg_stat_statements 的查询文本里搜 amount）后物理删除
ALTER TABLE orders DROP COLUMN amount;

DROP COLUMN 是元数据操作，秒回；被删的数据仍占着磁盘，要等后续 rewrite 或 pg_repack 才真正归还。

每阶段的回滚出口：expand 的逆向是 DROP COLUMN（丢新列数据，业务无损）；backfill 可直接停（脚本幂等，重启接着跑）；switch 的逆向是读切回旧列（双写保证了旧列还活着）；contract 之后才真正不可逆。把所有不可逆动作压到最后一步，是整套流程的核心。

backfill 的节奏：比速度重要

一条 UPDATE 干八千万行是标准的反面教材：单事务巨型 WAL、复制延迟飙升、长事务挡住 autovacuum、锁持有时间不可控。分批才是在线迁移：

#!/usr/bin/env bash
# 按主键区间分批回填：幂等、可中断、每批独立事务
set -euo pipefail

BATCH=5000
MAX_ID=$(psql -Atqc "SELECT max(id) FROM orders")

for ((start = 0; start <= MAX_ID; start += BATCH)); do
  ok=0
  for attempt in 1 2 3; do
    if psql -v ON_ERROR_STOP=1 <<SQL
BEGIN;
SET LOCAL lock_timeout = '2s';      -- 拿不到锁就放弃这批，别排队
SET LOCAL statement_timeout = '30s';

UPDATE orders
SET    amount_cents = round(amount * 100)
WHERE  id >= ${start}
AND    id <  $((start + BATCH))
AND    amount_cents IS NULL;        -- 幂等：回填过的行直接跳过
COMMIT;
SQL
    then ok=1; break; fi
    echo "batch ${start} attempt ${attempt} failed, backoff..." >&2
    sleep $((attempt * 5))
  done
  [ "$ok" = 1 ] || { echo "batch ${start} failed after retries" >&2; exit 1; }

  # 批间让一让：复制延迟超过 100MB 就等它消化完再发下一批
  while [ "$(psql -Atqc \
    "SELECT COALESCE(max(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)), 0) FROM pg_stat_replication")" \
    -gt 104857600 ]; do
    sleep 5
  done
done

几个设计点：amount_cents IS NULL 让脚本天然幂等，中断重跑无副作用；SET LOCAL 保证超时只影响当前事务；复制延迟是刹车而不是参考。批大小从 1000 到 10000 之间起步，标准不是"越快越好"，而是单批事务时长和 WAL 量都在监控的可控范围内。

双写还是逻辑复制，怎么选

数据搬运有三条主线，各有位置：

应用层双写：上面四阶段用的就是它。新旧列同时写，一致性靠双写路径的测试覆盖。灰度粒度最细，读写可以分别切。风险是遗漏路径——定时任务、旁路服务、运维脚本没接双写，新列就有窟窿。

触发器双写：数据库层强制，应用无感，不存在遗漏路径。代价是触发器成了隐形业务逻辑，半年后没人记得它存在，排障多绕一圈。

逻辑复制（publication/subscription）：适合整库搬迁、跨大版本升级、换实例这类"换地基"的迁移。搭建和校验成本高，但写切换窗口能压到分钟级。

选择标准：改个别列、个别表，用双写（应用层或触发器）；动实例、动版本，用逻辑复制。大迁移里两者经常组合着用。

回滚不是一句"恢复备份"

在线迁移的回滚设计要在动手前写好，而不是出事后翻备份：

  • 每个阶段的逆向脚本和正向脚本放同一个 MR 里评审。“到时候再写"等于没有。
  • 旧列至少保留一个完整业务周期。月账单、季对账、年度报表都会"诈尸"式地引用旧列，pg_stat_statements 的查询文本是最可靠的证人。
  • 契约测试锁住新列语义（精度、单位、NULL 规则），回滚切回旧列时用同一套测试反向验证。

常见坑点

长事务是所有 DDL 的天敌

不只 DDL：长事务持有的快照会挡住 VACUUM 回收死元组，表持续膨胀。迁移窗口前先清场——暂停或杀掉报表类长查询，给库配上 idle_in_transaction_session_timeout。DDL 自己则永远带 lock_timeout，宁可失败重试，不要当堵塞队伍的头。

大批量回填之后，统计信息已经漂了

回填改了几千万行，autovacuum 的 ANALYZE 没跟上，之后的查询行数估算开始失准，计划劣化可能在迁移完成后一两个小时才爆发。批间定期 ANALYZE orders，全部结束后做一次完整的 VACUUM ANALYZE。

外键的验证成本藏在最后一步

给 8000 万行的表补外键，NOT VALID 秒回，VALIDATE CONSTRAINT 却要扫全表——虽然不挡 DML，但会产生大量 I/O，和正常流量抢磁盘带宽。排期时把它当一次回填对待：低峰执行、盯延迟、可中断重跑。

总结

在线迁移的全部技巧可以压缩成三句话：知道每条 DDL 拿什么锁，给所有锁加预算；把不可逆动作拆到最后一步，之前的每一步都有回滚出口；回填和验证按节奏推进，盯复制延迟而不是盯进度条。剩下的，是在演练里把这些变成肌肉记忆。

把一次大赌注拆成一串可以反悔的小决定，“在线"才不是靠运气。