后端面试：如何零停机迁移 PostgreSQL Schema？

题干与适用场景

一个用户服务运行在 PostgreSQL 上，users 表有 2 亿行。username TEXT NOT NULL 具有唯一索引，所有在线请求、后台任务和事件消费者目前都读写它。团队准备把字段改名为 handle，两列在迁移期间表达完全相同的业务值。

服务有 80 个应用实例，一次滚动发布需要 30 分钟；后台 worker 可能晚于 Web 实例重启。正常流量不能暂停，也没有维护窗口。现有 SLO 仍然有效：如果写入 p99、锁等待、复制延迟或数据库负载越过线上阈值，迁移必须能限速或中止。目标是完成改名，同时让每个阶段都有明确的不变量、进入条件、退出条件和回滚路径。

本题中的 2 亿行、80 个实例和 30 分钟是面试假设。“零停机”指不安排计划性停机并保护既有 SLO，不代表可以忽略短锁、失败重试或降速。核心考察能力是把数据库变更拆成多个向前、向后兼容的发布阶段，因此归入 backend。分库分表、跨数据库迁移和多活冲突不在首轮范围。

面试官考察点

第一项信号是候选人是否先识别兼容性问题。直接执行 RENAME COLUMN username TO handle 后，旧实例仍然访问 username，新实例只认识 handle；在 30 分钟混合版本窗口里至少一方会失败。数据库语句执行得快，不等于应用部署是零停机的。

第二项信号是能否区分 Schema 迁移、数据迁移和代码切换。安全方案通常采用 expand-and-contract：先增加旧代码可以忽略的新结构，再发布兼容代码、分批回填和验证，之后切换读写，最后才删除旧结构。每一步都应独立提交、可观测、可重试，不能把 2 亿行更新和破坏性 DDL 塞进一个发布事务。

第三项信号是理解 PostgreSQL 的锁与扫描边界。ALTER TABLE 的不同子命令需要不同锁，未特别说明时会取得 ACCESS EXCLUSIVE。增加一个无默认值的可空列不需要重写整表，但仍要取得锁；若它排在长事务后面等待，后续请求还可能继续排队。NOT VALID、VALIDATE CONSTRAINT 和 CREATE INDEX CONCURRENTLY 可以缩小对并发写入的影响，但每一种都有自己的锁、负载和失败恢复语义。

最后看候选人是否用不变量和门禁驱动发布。强回答需要说明所有 writer 何时兼容、如何发现漏写、回填如何幂等、何时允许只读新列、每个阶段回滚到哪里，以及旧列何时真正失去恢复价值；只列“加列、双写、回填、删列”四个词还不够。

回答前需要澄清的问题

• 所有 writer 都在哪里？ 除 Web 服务外，还要盘点 worker、定时任务、管理脚本、数据库函数、触发器、CDC、ETL、BI 查询、视图和外部集成。一个未升级的 writer 就能持续制造不一致。
• 两列在迁移期间是否必须完全相等？ 本题是纯改名，因此答案是必须。若 handle 还要归一化、改变大小写规则或允许用户重新选择，回填和冲突处理会成为另一类数据迁移。
• username 的唯一性如何实现？ 需要复制等价的唯一索引或约束到 handle，并确认大小写、排序规则、空值和部分索引语义没有改变。
• 应用和数据库能否分开发版？ 必须能够。若 Schema 变更与应用只能在一个不可拆事务中部署，就无法跨多个兼容阶段推进。
• 允许多长的迁移周期？ 2 亿行回填可能持续数小时或数天。方案应允许跨发布运行，并定义暂停、恢复、完成判定和旧列保留窗口。
• 回滚目标是什么？ 回滚应用版本、停止回填、恢复旧列写入和恢复已删除数据是四件不同的事。删除旧列后，常规应用回滚已经不再安全。

30 秒回答框架

“我会采用 expand-and-contract，原地 rename 不进入方案。先用短 lock_timeout 增加可空 handle，拿不到锁就退出重试。第一版兼容应用仍读 username，但在同一数据库事务里同时写两列；等 80 个实例和所有 worker 都升级后，再按主键范围用小事务幂等回填 handle IS NULL 的行。整个过程监控空值数、不一致数、写错误、锁等待、p99、复制延迟、WAL 和 autovacuum。回填完成后并发创建新唯一索引，先加 NOT VALID 检查约束再单独验证，最后设置 NOT NULL。第二版优先读 handle、回退 username 并继续双写；观察通过后再只读 handle，然后停止旧列写入。旧列至少跨过完整回滚窗口，确认代码、任务、视图和消费者都不再引用后才删除。每个门禁失败都停在当前兼容阶段，不把删列当作普通回滚点。”

分步骤深入解答

先写出迁移不变量和阶段门禁

在执行 DDL 前先定义四条不变量：

1. 混合版本期间，username 仍是可回滚的真源；handle 可以为空，但非空时必须与它相等。
2. 兼容版本全部部署后，任何新写入必须在同一事务中同时更新两列，不能出现一列成功、一列失败。
3. 只有当空值数和非空不一致数都为零、所有 writer 已兼容且新索引与约束有效时，读路径才可脱离旧列。
4. 只有当线上版本、后台任务、视图、报表、CDC 和回滚版本都不再需要 username 时，才允许收缩 Schema。

每一阶段都记录部署版本、开始时间、负责人、进度游标、监控链接和退出条件。迁移 runner 应持有互斥租约或使用数据库 advisory lock，避免两个执行者同时推进同一步；每一步还要有唯一版本号和成功记录，重复启动时读取状态并从断点继续。

阶段一：只扩展 Schema，不改变现有行为

先在接近生产数据量的副本上演练 DDL，并检查长事务、锁队列和磁盘余量。线上增加一个无默认值的可空列：

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE users ADD COLUMN handle text;
COMMIT;

无默认值的可空列不需要重写 2 亿行，但 ALTER TABLE 仍需要取得强锁。lock_timeout 让无法迅速取得锁的尝试及时失败，再由发布系统稍后带抖动重试。执行前要找出异常长事务；执行时监控谁在等待谁。不能无限等待，因为一个排队中的强锁可能让随后访问该表的请求一起堆积。

这一步完成后，旧应用完全忽略 handle，因此可以独立回滚应用。不要同时加 NOT NULL、易变默认值、唯一约束和全量更新；它们会把元数据变更、表扫描、索引构建和数据写放大耦合在一次高风险操作中。

阶段二：发布兼容 writer，旧列暂时仍为读真源

发布版本 A：

• 读路径继续只读 username，用户行为保持不变。
• 创建和更新用户时，在同一 SQL 事务中把相同值写入 username 与 handle。
• 写入任一列失败就回滚整个事务，禁止把双写拆成两个异步请求。
• 记录双写尝试、失败和两列不一致指标，但不记录真实用户名。

版本 A 滚动期间，尚未升级的实例仍只写 username，所以此时看到新的 handle IS NULL 是允许的。必须等 80 个 Web 实例、worker、定时任务和独立消费者都报告兼容版本后，才关闭这个例外。若无法确认所有 writer，例如还有第三方程序直接写库，可以临时使用数据库触发器保持两列一致；代价是隐藏写逻辑、额外写开销，以及对复制、CDC 和故障排查的影响。触发器应被视为有明确删除日期的迁移设施。

阶段三：按主键范围幂等回填

所有 writer 兼容后启动后台回填。使用主键 keyset 范围，不用 OFFSET，每批在短事务里执行：

UPDATE users
SET handle = username
WHERE id > $1
  AND id <= $2
  AND handle IS NULL;

handle IS NULL 使这一步可以安全重试，也避免覆盖已经由在线请求写入的值。runner 持久化最后完成的主键范围；进程在任意批次后退出，重启时从已确认范围继续。初始只开一个 worker，以实测批量大小和间隔推进，不预先承诺固定吞吐。

每批提交后观察写入 p99、数据库 CPU、锁等待、复制延迟、WAL、磁盘、dead tuples 和 autovacuum。任一指标接近线上阈值就减少批量或暂停。回填以在 SLO 内稳定完成为目标。若表存在热点行，可缩小事务或在应用低峰推进，不能用一个覆盖 2 亿行的长事务换取脚本简短。

同时运行两个独立校验：

SELECT count(*) FROM users WHERE handle IS NULL;

SELECT count(*)
FROM users
WHERE handle IS DISTINCT FROM username;

第一个指标应持续下降到零。第二个利用 IS DISTINCT FROM 同时捕获空值差异和非空值不一致；任何非零结果都要停止切换，按主键范围定位来源。不能让回填静默覆盖非空冲突，因为那可能掩盖一个仍在运行的旧 writer 或错误的新逻辑。

阶段四：补齐索引和约束

若 username 原本唯一，则新列也需要等价的唯一索引。完成回填并确认没有重复后，在事务块之外执行：

CREATE UNIQUE INDEX CONCURRENTLY users_handle_key
ON users (handle);

并发构建允许正常写入继续，但需要更多工作、两次表扫描并等待相关事务；它仍会消耗 CPU 和 I/O。失败后还可能留下 INVALID 索引，恢复流程应先检查 catalog，删除失败产物后再重试，不能仅凭命令退出码假定数据库已经恢复原状。

非空约束也分阶段建立：

ALTER TABLE users
  ADD CONSTRAINT users_handle_not_null
  CHECK (handle IS NOT NULL) NOT VALID;

ALTER TABLE users
  VALIDATE CONSTRAINT users_handle_not_null;

ALTER TABLE users
  ALTER COLUMN handle SET NOT NULL;

ALTER TABLE users
  DROP CONSTRAINT users_handle_not_null;

NOT VALID 先阻止后续写入制造新违规，但不立即扫描旧数据；VALIDATE CONSTRAINT 再以较低锁级别检查既有行。有效的 CHECK 可以证明没有空值，使随后的 SET NOT NULL 跳过常规全表扫描。每条 DDL 仍应使用短锁等待、独立执行和上线监控，不能因为“并发”或“无需重写”就当作无成本操作。

阶段五：切换读取，再停止旧列写入

发布版本 B：优先读取 handle，为空时回退 username，并继续在同一事务里双写。虽然门禁要求此时空值为零，回退仍为回滚和异常隔离保留一层兼容性。先 canary，再逐步扩大；对比新旧列读取结果、错误率和业务指标。

观察窗口通过后发布版本 C：只读 handle，仍双写两列。这样若读路径出现问题，可以回滚到版本 B 或 A，因为 username 仍然是最新的。再经过一个包含后台任务、低频接口和完整发布周期的观察窗口，才发布版本 D，停止写 username。

停止旧列写入是一个重要的回滚分界点。此后如果要回滚到只认识 username 的版本，必须先恢复双写并把窗口内的新值反向补回旧列；直接回滚应用会读取过期数据。发布系统应把这个条件写进 runbook，确保故障处置不依赖当班工程师临时记忆。

阶段六：延迟执行收缩

删除前用代码搜索、查询日志、依赖 catalog 和消费者清单证明 username 不再被引用。先移除旧索引、约束、触发器和视图依赖，再在独立发布中删除旧列：

ALTER TABLE users DROP COLUMN username;

删列属于破坏性边界。即使 PostgreSQL 不立即重写整表，旧应用、缓存的 Schema、视图和外部查询仍可能立即失败；删掉的数据也不能通过普通应用回滚恢复。因此它应至少晚于读写切换一个完整回滚保留期，并与“忽略旧列”的代码发布分开。备份用于灾难恢复，无法提供低延迟的发布回滚。

高质量示范回答

“这个需求的主要风险是 30 分钟滚动发布里旧代码和新 Schema 无法同时兼容。我会把迁移拆成扩展、兼容写入、回填验证、读切换和延迟收缩。

第一步增加可空 handle，使用短 lock_timeout；拿不到锁就退出重试。可空无默认列不重写整表，但 DDL 仍会取得强锁，所以执行前清理长事务并监控锁队列。然后发布版本 A，仍读 username，所有 writer 在同一数据库事务中双写两列。只有 80 个实例、worker 和消费者全部升级后，才开始回填。

回填按主键范围用短事务执行 UPDATE ... WHERE handle IS NULL，持久化进度，失败可重试。速率由线上 p99、复制延迟、WAL、dead tuples 和 autovacuum 决定。空值数必须归零；handle IS DISTINCT FROM username 必须始终归零，非空冲突不能被回填脚本覆盖。

数据通过后，用 CREATE UNIQUE INDEX CONCURRENTLY 建立等价唯一索引，并检查失败遗留的 invalid index。非空约束先 CHECK ... NOT VALID，再单独 VALIDATE CONSTRAINT，最后设置 NOT NULL。然后版本 B 优先读新列、回退旧列并继续双写；版本 C 只读新列但继续双写；观察完整周期后版本 D 才停止旧列写入。

每个阶段都保留回滚路径：切读前可直接回滚应用；只读新列但仍双写时可以回旧读；停止旧写后，要先恢复双写并反向补数据才能回到旧版本。最后确认代码、任务、视图、CDC 和报表都不再引用 username，跨过回滚窗口后才在独立发布删除它。任何门禁不满足就停在兼容状态，不继续做破坏性收缩。”

常见错误

• 直接 RENAME COLUMN → 旧实例和新实例在滚动窗口内需要不同列名 → 通过新增列和多版本兼容发布完成改名。
• 一次事务更新 2 亿行 → 长事务放大 WAL、锁、复制延迟、表膨胀和故障恢复时间 → 按主键范围用短事务幂等回填。
• 应用刚开始双写就启动回填 → 尚未升级的实例仍能制造新的空值 → 等所有 writer 兼容后再把空值归零作为门禁。
• 双写使用两个独立请求 → 一次超时就可能只更新一列 → 在同一数据库事务中原子更新两列。
• 只检查 handle IS NULL → 两列都非空但值不同仍会漏过 → 同时检查 IS DISTINCT FROM，并调查非空冲突。
• 把 ADD COLUMN 视为无锁 → 元数据操作仍需取得表锁，排队中的 DDL 可能放大阻塞 → 使用短锁等待、重试并监控长事务和锁队列。
• 普通方式创建唯一索引 → 大表索引构建会长时间阻塞写入 → 使用 CONCURRENTLY，同时处理额外负载和 invalid 索引恢复。
• 回填完成后立刻删旧列 → 低频 worker、视图、Schema 缓存或应用回滚仍可能依赖它 → 跨完整观察与回滚窗口延迟收缩。
• 把备份当作回滚按钮 → 恢复 2 亿行数据库的时间和影响远大于应用回滚 → 在删列前一直保持可在线回退的兼容结构。
• 承诺“零影响迁移” → DDL、索引和回填都有锁或资源成本 → 承诺不计划停机、守住 SLO，并在越界前自动暂停。

追问及应对

追问一：为什么不直接给 handle 加默认值？

PostgreSQL 对非易变常量默认值可以避免整表重写，但默认值无法表达“复制每行现有的 username”。即使某个默认值在物理上很快，它仍要取得 DDL 锁，也不能解决旧实例只写旧列的问题。本题仍需要兼容 writer 和数据回填；是否设置未来插入默认值应由业务语义决定，不能拿它替代迁移流程。

追问二：如果 handle 需要转小写并重新保证唯一呢？

这已不再是纯改名。先定义归一化函数和冲突策略，在只读副本或离线任务中统计 lower(username) 的碰撞，决定保留、加后缀还是让用户处理。回填保存原值与转换状态，唯一索引只能在冲突归零后创建。双写路径必须使用同一归一化实现，不能让在线写入和回填脚本各自解释规则。

追问三：所有 writer 无法在同一时间升级怎么办？

若直接写库的外部系统无法快速升级，可在数据库内增加临时同步触发器，让任一旧列写入补齐新列，并记录触发次数以发现遗留调用方。触发器要处理两列同时提供但不一致的请求，通常直接拒绝；还要评估递归、复制和 CDC 语义。等所有调用方迁移后先停用并观察，再删除触发器，避免永久保留隐藏业务逻辑。

追问四：回填期间主库复制延迟持续升高怎么办？

先暂停新批次，让副本追平；此时增加 worker 只会继续放大 WAL。检查批量大小、提交间隔、WAL 产生率、长查询和 autovacuum，再以更小批次和更低并发恢复。若读流量依赖副本，复制延迟本身就是用户可见风险；回填完成时间必须服从线上 SLO。

追问五：CREATE UNIQUE INDEX CONCURRENTLY 失败后能直接重跑吗？

不能盲目重跑。同名 invalid index 可能仍留在 catalog；并发唯一索引在失败阶段还可能已经对其他事务执行唯一性检查。先检查索引是否有效、确认失败原因和数据冲突，按 runbook 删除失败索引，再重新构建。该命令不能放在普通事务块中，迁移工具也必须支持这一点。

追问六：在哪个阶段最难回滚？

停止旧列写入后，旧列开始落后；删除旧列后，数据和 Schema 都跨过破坏性边界。前者需要恢复双写并反向补齐后才能回旧应用，后者通常只能前向修复或从备份恢复。因此发布计划应把这两个动作拆开，并让旧列在足够长的观察窗口内持续保持最新。