Lætitia AVROT：Region 列：数据完整性的一课
内容提要
同事提议为表添加自由文本 region 列，我反对，认为数据完整性应由数据库而非应用保证。比较四种方案：domain 有级联删除风险；enum 无法删除值；check 约束简单灵活；引用表最规范但需连接和索引。最终选用 check 约束。对 2400 万行大表，加列用常量默认值不重写，但加约束仍会扫描，故先用 NOT VALID 加约束再 VALIDATE。新增区域时，先原子替换约束，再择时验证。核心教训：完整性规则应放在数据库，应用与数据库双重防护，且要验证而非假设。
延伸解读
为什么数据库应承担完整性规则
文章强调，应用层校验只覆盖一个入口，而脚本、psql 查询、批量导入等其他入口会绕过它。数据库约束则对所有写入生效，形成最后防线。作者建议应用与数据库双重防护：应用捕获界面输入错误，数据库兜底所有客户端。这并非否定应用校验，而是避免把完整性完全托付给单一层。
四种方案的关键取舍
domain 可复用但 drop domain ... cascade 会连带删除依赖列及数据；enum 类型严格却无法删除值，只适合永久固定的集合；check 约束简单灵活，可增删值而不重写数据；引用表最规范，适合区域有额外属性或集合常变，但需连接、索引和维护纪律。作者团队因区域集合会变化且希望低意外，选择了 check 约束。
大表加约束的安全操作顺序
在 2400 万行表上，PostgreSQL 11 起用常量默认值加列不会重写表，但加 check 约束仍会扫描全表。作者先用 NOT VALID 在同一 ALTER TABLE 中加列和约束，避免立即扫描；再单独执行 VALIDATE，此时仅持 SHARE UPDATE EXCLUSIVE 锁，读写不受阻。可用 pg_constraint.convalidated 确认约束是否已验证。
新增区域时的两段式变更
当区域列表增加新值时，作者建议分两个事务：第一个事务在 lock_timeout 保护下原子地删除旧约束并添加带 NOT VALID 的新约束，确保表始终有约束；第二个事务择时执行 VALIDATE，在较轻锁下扫描旧行。若跳过 VALIDATE，旧行可能不满足新约束却未被发现；若变更删除值，需先迁移旧数据，否则验证会失败。
Q&A
为什么不应该在应用层做数据完整性校验？
应用层校验只能覆盖通过该应用的请求，但数据库可能被其他应用、脚本、psql查询或批量导入直接访问，这些途径会绕过应用规则，导致数据质量失控。数据库才是数据的最终守护者。
PostgreSQL 的 domain、enum、check 约束和引用表各有什么优缺点？
domain：可复用、一致，但 drop domain cascade 会连带删除依赖列和数据；enum：类型严格、存储紧凑，但无法删除值；check 约束：简单灵活、可增删值，但需注意大表加约束的方式；引用表：最规范、可扩展元数据，但需要连接、索引和额外维护。
在 2400 万行的大表上添加带默认值的列会重写全表吗？
不会。从 PostgreSQL 11 开始，添加带常量默认值的列不会导致全表重写，该操作在目录层面被优化掉。但需确认数据库版本是否高于 11。
如何在大表上安全地添加 check 约束而不长时间锁表？
使用 NOT VALID 选项添加约束，跳过对现有行的扫描，快速完成；然后另择时间执行 VALIDATE CONSTRAINT，该操作只取 SHARE UPDATE EXCLUSIVE 锁，不阻塞读写，但会扫描全表验证旧数据。
当需要新增一个区域值时，如何更新 check 约束？
分两个事务：第一个事务原子地删除旧约束并添加包含新值的 NOT VALID 新约束（设置 lock_timeout 避免阻塞）；第二个事务在合适时间执行 VALIDATE CONSTRAINT 验证旧行。确保变更只增不删，否则需先迁移数据。
NOT VALID 约束是什么意思？不执行 VALIDATE 会有什么后果？
NOT VALID 表示约束对新写入和更新的行生效，但对已存在的旧行不检查。如果不执行 VALIDATE，旧行可能违反约束而未被发现，导致约束形同虚设。必须执行 VALIDATE 才能完全生效。
文章最后提到的“验证而非假设”具体指什么？
作者曾假设 PostgreSQL 在添加列和 check 约束时会智能跳过扫描，但实际不会（该优化仍在待办列表）。因此不能依赖假设，而应测试、测量并阅读文档，采用文档化的 NOT VALID + VALIDATE 路径。
