6.3 模式与 DDL 候选规则

DDL 不是把 ER 图翻译成 CREATE TABLE，而是在数据库中发布一组长期合同：名称怎样解析、谁拥有对象、什么输入被拒绝、什么标识保持稳定、旧应用与新 schema 能否共存。表一旦有数据和调用方，类型、约束和默认值就同时影响写入语义、锁、WAL、复制和恢复。

本节把 ch03 的逻辑问题与 ch04 的物理实现收敛成三组评审规则。具体的在线 schema change 编排留到第 11 章；这里先建立每个变更都必须携带的前提、停止线和证据。

6.3.1 命名、所有权、注释与对象边界

pg36_shop 用三个 schema 表达不同稳定性边界：

schema 不是项目文件夹。它同时参与名称解析、USAGE/CREATE 权限与对象归属。边界成立至少要检查四件事：

SELECT
    n.nspname,
    pg_get_userbyid(n.nspowner) AS owner,
    has_schema_privilege('pg36_app', n.oid, 'USAGE') AS app_usage,
    has_schema_privilege('pg36_app', n.oid, 'CREATE') AS app_create
FROM pg_catalog.pg_namespace AS n
WHERE n.nspname IN ('shop', 'shop_api', 'shop_private', 'public');

预期不是“schema 名字存在”，而是 owner、USAGE、CREATE 和 search_path 共同符合合同。shop_private 即使名字带 private，若 application 有 USAGE/EXECUTE，仍不私有。

owner、migration identity 与 runtime identity 分离

本书采用：

pg36_owner  NOLOGIN  持有 database/schema/table/function
pg36_app    LOGIN    只获得应用所需 DML/USAGE/EXECUTE
pg36_ro     LOGIN    只获得对外读取权限
dbuser_dba  LOGIN    通过受审计 direct service 执行 migration，
                     必要时 SET ROLE pg36_owner

对象所有者可以修改或删除自己拥有的对象，也能改变授权；把 owner 直接作为 application login，会让 SQL injection 或应用缺陷越过显式 GRANT。SAFE-ROLE-003 因此是 safety，而不是命名偏好。

NOLOGIN owner 也不等于“不需要保护”：能 SET ROLE 到 owner 的 membership、migration identity 和 SECURITY DEFINER function 都是进入该权限域的路径，必须在 catalog 中验证。日常应用不能为了省事获得 owner、superuser、CREATEDB、CREATEROLE 或 BYPASSRLS。

名称要支持定位，不要假装表达全部语义

默认使用不需双引号的小写 snake_case，原因是客户端、migration 和 catalog 查询更稳定，而不是 PostgreSQL 不支持其他命名。名称应回答：

• relation 表达什么事实，不以当前 UI 页面命名；
• column 的单位或时间语义是否可见，例如 _minor、_at、_date；
• constraint 出错时能否定位业务不变量；
• index 名能否关联 key/order/predicate；
• function 名是否表明它是 command、calculation 还是 trigger implementation。

例如：

CONSTRAINT sales_order_order_no_key UNIQUE (order_no)
CONSTRAINT sales_order_total_minor_nonnegative
  CHECK (total_minor >= 0)

稳定的 constraint name 使负向测试可以同时断言 SQLSTATE 与 CONSTRAINT_NAME，不会把任何 23505 都误认为目标唯一约束。命名本身不保证正确，但让错误、catalog、migration 和 incident evidence 可以指向同一对象。

PostgreSQL identifier 最长受 NAMEDATALEN 限制，默认最多存储 63 bytes；过长名称会被截断。规约应保证关键语义在截断前仍可辨识，并用 catalog 检查真实名称，而不是依赖生成器在内存中的原始字符串。

comment 是运行目录的一部分

COMMENT ON 应覆盖关键 database、role、schema、relation、column 与非显然约束，至少说明：

owner / purpose / unit or semantic / external contract / lifecycle

comment 不是放完整设计文档，也不能包含 secret、个人数据或随请求变化的值。它的优势是跟对象一起出现在 catalog、\d+ 和元数据工具中。设计文档说明“为什么”，comment 则帮助值班者快速确认“这是什么、谁负责”。

由此形成 DEFAULT-NAME-003：边界由 schema、owner、稳定名称和 comment 共同表达；legacy 例外必须有 mapping、owner、迁移计划和 expiry。

6.3.2 类型、约束和默认值的审查问题

“应该用哪个 PostgreSQL 类型”不能只靠一张类型对照表。评审时先完成语义句，再选类型：

类型名不等于业务合同

numeric 不知道币种和舍入规则，text 不知道 Unicode identity，timestamptz 不保存原始时区名称，jsonb 不自动提供领域约束。DDL 需要组合 type、column name、NOT NULL、default、named constraint、reference 与 comment。

本书把金额写成 minor unit integer 并显式保存 currency。这适合当前教学订单，但并非所有财务系统通用：支持任意精度计量、汇率、税务舍入或多币种分摊时，应重新建模，不能从“整数没有浮点误差”推出“整数能表达所有货币语义”。

若业务没有真实长度上限，PREF-TEXT-001 倾向 text，而不是习惯性 varchar(255)。若外部协议明确限制 64 个字符，则必须说明计数单位是字符、bytes 还是规范化后的 code points，并定义超长输入的错误合同。

约束优先保证单库可表达的不变量

适合进入数据库约束的包括：

• 值域与行内一致性：CHECK、NOT NULL；
• 候选键和幂等键：UNIQUE；
• 引用完整性：FOREIGN KEY；
• 时间/空间排斥：EXCLUDE；
• 需要 transaction 末尾成立的关系：可延迟 constraint。

每个关键约束至少有一个反例，且验证“因预期约束失败”：

SQLSTATE=23514
CONSTRAINT_NAME=sales_order_total_minor_nonnegative

只检查“INSERT 失败”可能掩盖权限错误、类型解析错误或另一个约束先触发。只测试正向 seed 则无法证明数据库真的拒绝坏状态。

跨 database、外部支付系统或时间变化事实通常不能靠单个 declarative constraint 完整表达。此时 SAFE-CONS-005 允许受控 breakglass，但必须记录 application control、reconciliation、owner 和到期复查；“数据库做不了”不是“不需要保证”。

四种标识不要混成一列

DEFAULT-KEYS-005 区分：

order_id       内部 join/physical identity
order_no       用户可见业务编号
provider_ref   外部系统引用
request_key    请求幂等键

它们的 authority、生命周期、隐私和唯一范围不同。把可变外部字符串同时当 primary key、公开 URL 和 retry key，会让 provider 变化、内部迁移和 API 合同互相绑死。并非每个模型都需要四列；若用一个标识，review 必须证明这些责任确实一致。

default 是写入规则，不是历史真相

default 回答“调用方省略列时写入什么”，不回答旧行原本是什么。常见审查点包括：

• now() 是 transaction start time；是否需要 statement/clock time；
• identity/sequence 生成的是唯一候选值，不承诺无间隙或按 commit 排序；
• 空字符串、空 JSON 与 NULL 是否真的同义；
• volatile default 是否触发表重写或让重跑结果不稳定；
• 新增 default 后，旧应用显式发送 NULL 时会发生什么；
• backfill 值能否从已有事实确定，还是在伪造历史。

默认值方便不应覆盖领域语义。一个字段若在业务上必须由调用方明确选择，省略 default 反而能尽早暴露错误。

JSON、array、enum 与 partition 都要证据

PREF-SEMI-002 只把 shape 可变、整体读写、有明确 owner/validation/retention 的附属值放进 JSONB/array；需要独立引用、唯一、局部更新或生命周期的事实优先拆成 relation。这不是“永远范式化”，而是让数据库能对核心事实提供统计和约束。

分区同理。PREF-PART-003 要求 retention/drop lifecycle、可测规模瓶颈或稳定 pruning 证据先出现，再设计 partition key、unique/PK、FK 和迁移。预计未来会有一亿行不是设计完成；分区会立刻增加约束、索引和运维复杂度，收益却可能多年不出现。

6.3.3 可逆迁移与版本化 DDL

“所有 migration 都必须可回滚”听起来安全，实际上容易产生虚假承诺。删除列后，down 可以重新创建空列，却不能恢复已经丢失的业务语义；旧应用已经按新格式写入后，数据库回滚也不保证旧代码能理解数据。

更准确的目标是服务可恢复：

兼容发布 + transaction rollback（尚可时）
  + 明确停止线
  + application rollback window
  + forward repair
  + backup/PITR 作为灾难恢复底线

expand—backfill—validate—switch—contract

跨 application release 的变更按五阶段设计：

1. expand：添加旧代码可以忽略的新对象，避免立即收紧；
2. backfill：分批填充，限制 lock/WAL/replica lag，过程可重入；
3. validate：检查无坏值、约束成立、读写双路径一致；
4. switch：先切写路径，再切读路径，保留观测和回退窗口；
5. contract：确认旧代码/旧数据路径退出后再删旧对象。

PostgreSQL 的 transaction DDL 很强，但并非所有命令都能放在同一个事务，锁取得时机与强度也不同。CREATE INDEX CONCURRENTLY、VACUUM 等有自己的事务限制；大型 backfill 即使可回滚，也可能产生巨大 WAL、dead tuples 和 replica lag。不能用“BEGIN 包住了”替代容量与锁评估。

对新 CHECK/FK，可以在合适场景先 NOT VALID，使新写入受约束，再单独 VALIDATE CONSTRAINT 扫描旧数据；这不自动适用于 UNIQUE/PK，也不消除所有 lock。具体 lock mode、版本差异与 workload 影响必须在 ch11 用当前目标版本实测。

破坏动作之前先证明可表示

SAFE-MIGR-006 要求在类型收窄、列删除、表重写或约束收紧前完成：

precheck bad rows / dependencies
lock_timeout + statement_timeout
兼容 application 版本范围
WAL / temporary space / lag 预算
最大批次与停止线
失败后 transaction rollback 或 forward repair
post-state catalog + checksum

precheck 必须在真正写入前失败。例如把 text 转成 integer，先找出所有无法转换的值并固定 conversion rule；不要让 ALTER TABLE ... TYPE 运行数十分钟后才撞到最后一个坏值。precheck 与执行之间仍可能有竞态，所以迁移还需限制并发写、使用兼容约束或在同一受控边界重新确认。

高风险 contract 不因为有 backup 就可以随时执行。backup/PITR 是最大故障恢复证据，不是低成本 undo；恢复时间、数据丢失窗口和对其他 database 的影响都要进入风险说明。

fresh install 与 upgrade 只有一条权威链

两套脚本最容易漂移：

create-latest.sql     # 新环境
V001...V042.sql       # 旧环境升级

如果两者由人独立维护，很快会产生“同版本不同 schema”。DEFAULT-VERS-010 要求 fresh install 也消费同一条 versioned migration chain，或者由这条链可重复生成并验证 latest snapshot。数据库内要有可查询的 schema version 和 migration identity；重复执行要么幂等成功，要么在修改状态前明确拒绝。

本书使用 shop_private.schema_version 标记 ch04-v1，并由每章 context guard 检查。版本号本身不证明 schema 正确，所以还需要 catalog assertions 与稳定 relation checksum；checksum 又不能覆盖全部权限、function body 和运行参数，因此验收必须是多项事实，不是单个魔法哈希。

变更说明先于执行

使用 change-template.md 填写：

• target、owner、窗口与 application release；
• 当前 schema version、relation size、write rate 和依赖方；
• 五阶段迁移与重跑行为；
• lock/WAL/temporary space/lag 预算；
• transaction rollback、application rollback 与 forward repair；
• precheck、负向 SQLSTATE、post-state checksum；
• 最大可信故障、停止条件和审批。

无法填写的字段不是“文档以后补”，而是设计尚未完成。真正执行在线 DDL 前，还要在第 11 章为具体 PostgreSQL/Pigsty 环境补齐锁实验、监控窗口和发布编排。

本节验收问题

评审任意一个 DDL change，应能回答：

1. schema、owner、runtime role 和 API boundary 是否明确；
2. 名称/注释能否从 catalog 定位领域语义和 owner；
3. 每个类型是否闭合单位、范围、时间、文本与 NULL 语义；
4. 关键约束是否命名，并有精确 SQLSTATE/constraint 反例；
5. 内部、业务、外部和幂等标识是否被有意区分；
6. JSON/array/partition 的收益与边界是否有 workload 证据；
7. fresh install 与 upgrade 是否进入同一 version authority；
8. destructive step 前是否有可表示性 precheck、timeout 和停止线；
9. application rollback 时 schema 是否仍兼容；
10. 丢失语义时是否诚实声明只能 forward repair 或 restore。

如果其中任何高影响问题只能回答“应该没事”，该变更仍是 candidate，不应进入生产发布队列。

参考资料

• PostgreSQL 18：Schemas
• PostgreSQL 18：Privileges
• PostgreSQL 18：Data Definition
• PostgreSQL 18：Constraints
• PostgreSQL 18：Date/Time Types
• PostgreSQL 18：Numeric Types
• PostgreSQL 18：Transactional DDL Caveats
• PostgreSQL 18：ALTER TABLE

上一节：连接与会话候选规则 · 返回本章目录 · 下一节：查询与事务候选规则 · 查看全书目录 · 查看索引中心