后端面试：如何用 NOT VALID 在线迁移 PostgreSQL 约束？

题目

线上 PostgreSQL 大表需要新增 CHECK 或外键约束。表中可能已有不合规行，业务又不能接受长时间写入阻塞。请设计从发现脏数据、添加 NOT VALID 约束、修复历史数据到 VALIDATE CONSTRAINT 的完整流程，并说明锁、并发、监控和失败处理。

面试官考察点

能否区分添加约束时的锁行为与后续验证扫描。
能否解释 NOT VALID 只跳过历史行扫描，新插入或更新的行仍会被检查。
能否让数据修复、验证和应用发布互相协调，而不是直接执行一条阻塞 DDL。
能否在验证失败、长事务或回滚时保持约束状态可见且可恢复。

参考答案

先用只读查询估算违规行数量、索引可用性和长事务，再在低峰期执行 ADD CONSTRAINT ... NOT VALID。该步骤不扫描已有表行，约束会立即约束之后的插入和更新；历史行仍可能不满足约束，因此状态必须标记为待验证。

接着以批次修复历史数据，每批有明确上限、提交边界和进度记录。修复逻辑必须与应用规则一致，必要时先部署兼容代码。修复完成后执行 VALIDATE CONSTRAINT，观察锁等待、扫描耗时和数据库负载。验证成功后，约束目录状态变为有效，迁移才算完成。

外键还要确认被引用列有合适的唯一约束，并评估验证期间的写入和删除。失败时保留 NOT VALID 约束以继续保护新写入，修复剩余历史问题后重试；只有确认不再需要时才删除约束。

迁移流程

sql

-- 1. 先记录违规行并建立修复任务
SELECT count(*) FROM orders WHERE total < 0;

-- 2. 快速加入约束；不扫描历史行
ALTER TABLE orders
  ADD CONSTRAINT orders_total_nonnegative
  CHECK (total >= 0) NOT VALID;

-- 3. 分批修复历史数据后再验证
ALTER TABLE orders
  VALIDATE CONSTRAINT orders_total_nonnegative;

迁移工具应保存约束名、批次游标、开始与结束时间、验证结果和操作者。发布前检查所有应用写路径都遵守同一规则，避免修复脚本与业务逻辑互相覆盖。

NOT VALID 的价值是把昂贵的历史扫描从添加约束步骤中拆开；验证阶段仍会扫描表并获取相应锁，不能把它当作零成本操作。先检查长事务和复制延迟，设置 statement timeout 与锁等待告警，在可控窗口执行验证。

验证过程中，新事务继续受到约束检查，历史行修复则需要避免与业务更新相互覆盖。批次修复使用稳定索引范围和短事务；必要时用 FOR UPDATE SKIP LOCKED 领取工作，但要评估跳过行是否会让进度统计失真。

常见误区

以为 NOT VALID 代表约束完全不生效，继续允许新的脏数据写入。
直接运行 VALIDATE CONSTRAINT，没有检查长事务、锁等待和复制容量。
用一次大事务修复所有历史行，导致膨胀、长时间锁和难以回滚。
只修复 CHECK，忽略外键验证所需的引用索引和删除路径。
验证失败就删除约束，丢失对新写入的保护和后续诊断线索。

失败处理与回滚

把迁移状态分为 planned、not_valid、backfilling、validating、validated 和 aborted。每次状态变化写入迁移表和审计日志。验证发现违规行时记录主键样本和约束名，暂停验证但保留约束；修复后可从进度点继续。

如果应用发布需要回滚，兼容代码应仍能处理旧数据和新约束。删除 NOT VALID 约束是最后手段，并需要确认新写入不会再次产生同类问题。任何 DROP CONSTRAINT 都应有审批、备份和重新添加计划。

可观测性

至少监控违规行数量、修复速率、剩余估计时间、验证扫描进度、锁等待、长事务年龄、WAL 增长和复制延迟。告警要区分“新写入违反约束”与“历史数据尚未验证”，两者的处理优先级不同。

验证后查询 pg_constraint.convalidated 与约束名，确认目录状态已更新；把结果、执行计划和负载窗口写入迁移记录。对外报告只使用脱敏主键和聚合数，避免把业务数据写入日志。

PostgreSQL 17 ALTER TABLE 文档：NOT VALID、VALIDATE CONSTRAINT 的锁和并发语义。
PostgreSQL 当前约束文档：CHECK、外键和历史数据验证规则。
PostgreSQL 17 pg_constraint 文档：convalidated 等目录字段。

追问

为什么新写入会被检查而旧行可以暂时不满足？

NOT VALID 省略的是创建时对已有行的扫描；约束定义仍立即作用于之后的 INSERT 和 UPDATE。这样可以先阻止脏数据继续增长，再安排历史修复。

验证期间能否继续写入？

可以继续写入，但验证会持有相应锁并读取表，锁等待和负载必须可控。新事务产生的行会被检查，修复任务要避免覆盖业务更新。

如何估算验证会运行多久？

根据表大小、扫描计划、缓存命中率、并发读写和历史窗口做压测或抽样估计，不能只用行数线性猜测。上线前设置超时和取消策略。

外键迁移还需要注意什么？

确认被引用列有唯一约束、索引和稳定的删除语义。验证时既要检查子表已有行，也要观察并发删除、更新及锁冲突。

什么时候应该放弃并删除约束？

只有在约束需求被撤销或设计错误，并且已有替代保护时才删除。验证失败本身不是删除理由；保留 NOT VALID 能继续保护新数据并保留整改路径。

题目