PostgreSQL 建模、NULL、查询与执行计划

本轮仅做正文和源码静态审阅，以下命令、SQL、集成测试及恢复流程均未执行。过去真实运行记录保留在 migration/knowledge-rebuild/m4-next/postgres/verification.md 及其原始日志；历史结果不证明修改后的示例运行通过。

类型、主外键与数据约束

前置：首次查询。本项目 products 表表示商品当前库存，reservations 表表示一次成功预留。sku 是商品标识；request_id 是客户端生成的一次业务请求标识。一次业务请求可以重试，但必须保持同一参数。完整 DDL 如下，通过CLI 初始化执行同一文件。

CREATE TABLE IF NOT EXISTS products (
  sku text PRIMARY KEY,
  name text NOT NULL,
  stock integer NOT NULL CHECK (stock >= 0),
  price numeric(12,2) NOT NULL CHECK (price >= 0),
  note text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS reservations (
  request_id text PRIMARY KEY,
  sku text NOT NULL REFERENCES products(sku),
  quantity integer NOT NULL CHECK (quantity > 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

text 适合名称、SKU和业务键；没有业务长度限制时不必随意写 varchar(255)。integer 是有界整数；库存极大时应评估 bigint，本课不静默溢出。价格用 numeric(12,2) 表达十进制定点金额，避免 float 二进制表示误差；数量与金额分别建模。timestamptz 表示时间点，显示值受 session TimeZone 影响，不保存原始时区名称；原始地区时区如有业务含义要另存字段。now() 在同一事务内稳定，不能靠它区分事务内各条语句的先后。

PRIMARY KEY 同时提供唯一与非空约束；外键拒绝不存在的 sku。CHECK 拒绝 stock<0，但 CHECK 表达式为 NULL 时也能通过，所以 CHECK(stock>=0) 不能替代 NOT NULL。数据库约束保护所有写入者，应用验证提供较清晰的错误信息，两者都需要。外键不会自动为引用端 reservations.sku 建索引；数据增长、JOIN或删除父表成为瓶颈时再根据访问路径评估。

初始化的 CREATE IF NOT EXISTS 只创建缺失表，不会升级现有列定义。它不是迁移工具。修改 DDL 后要在新实验库验证迁移；不要以为重复运行 init 会自动补列。

NULL 与三值逻辑

NULL 表示未知或缺失，NULL = NULL 结果仍为 NULL；筛选缺失值使用 IS NULL。WHERE 只保留条件为 true 的行，false 和 unknown 都被过滤。COUNT(*) 数行，COUNT(note) 只数非空 note，COALESCE 可以为展示提供默认值，但会隐藏“无记录”和“真实为零”的区别，按业务决定。

SELECT NULL=NULL AS unknown, NULL IS NULL AS missing;
SELECT count(*) AS rows, count(note) AS notes FROM products;
SELECT sku FROM products WHERE note IS NULL ORDER BY sku;

在 verify 重置且没有修改 note 的基线上，rows=2、notes=0，两个商品均能被 IS NULL 查到；第一条查询返回 unknown=NULL、missing=true。psql 默认把 NULL 显示为空白，可用元命令 \pset null '<NULL>' 改善观察。UNIQUE 默认允许多个 NULL；若业务要求未知值也互相冲突，可评估 PostgreSQL 的 NULLS NOT DISTINCT，本课主键幂等键不允许 NULL，因此无需该选项。

JOIN、聚合与未匹配行

LEFT JOIN 保留左侧商品，即使右侧没有预留。对右侧记录用 COUNT(r.request_id)，不能用 COUNT(*) 表示预留数，因为 pen 仍有一条 JOIN 产生的行。GROUP BY 决定聚合粒度；HAVING 筛聚合结果，WHERE 在聚合前筛原始行。

SELECT p.sku, count(r.request_id) AS requests,
       coalesce(sum(r.quantity),0) AS reserved
FROM products p LEFT JOIN reservations r USING(sku)
GROUP BY p.sku ORDER BY p.sku;

verify 后预期 book=1次/2件，pen=0次/0件。把右表筛选条件放 WHERE 可能消除未匹配行；若想“保留所有商品但只连接最近预留”，把时间筛选放 JOIN 的 ON 条件。本例展示当前库存与成功记录，不是审计不可变的财务总账。

确定性分页

分页必须有稳定且唯一的排序。以主键排序时，下一页用 WHERE sku > 上一页最后sku ORDER BY sku LIMIT 10，它是键集分页，避免大 OFFSET 扫过大量行。页大小必须验证范围；示例固定10。若按时间排序，使用 (created_at,request_id) 作为游标和 ORDER BY 的完整二元组，避免同一时间点跳行。多次请求默认读取不同快照，并发增删时不能保证整次翻页看到固定集合；需要一致导出时另开只读事务并控制持续时间。

SELECT sku,name,stock FROM products
WHERE sku > 'book' ORDER BY sku LIMIT 10;

预期只有 pen。不要拿业务键的字典序当创建顺序；需要创建顺序时显式设计列和索引。

索引与 EXPLAIN ANALYZE

B-tree 索引把特定排序的键映射到记录位置，可以减少读取，也增加磁盘、写入和维护成本。复合索引 (bucket,id) 适合 bucket 等值后按 id 排序；不是“建过索引所有查询都快”。统计信息、选择率、返回列与LIMIT会改变计划，小表走 Seq Scan 完全合理。

完整可运行的计划实验放在验证源码，它在独立临时表写入50,000行、创建索引并 ANALYZE，然后执行 EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)。历史运行得到 Limit → Index Only Scan，返回10行，Heap Fetches=10；名字叫 Index Only Scan 也可能因可见性检查访问堆。不要把这个计划当所有机器固定输出。

ANALYZE 选项会实际执行语句。对 UPDATE、DELETE 的 EXPLAIN ANALYZE 会真实写入，即使随后回滚，也有锁和触发器等成本。先用普通 EXPLAIN 检查，再在独立库测试。重点比较 estimated rows 和 actual rows、loops、buffers、执行时间，而非只寻找“Index”一词。ANALYZE 更新统计信息，VACUUM 清理可回收旧版本，两者目的不同；长事务可能阻碍清理。

练习：把 COUNT(r.request_id) 换成 COUNT(*) 并解释 pen 的差异；再推演 (created_at,request_id) 的下一页条件。提示：先手绘 LEFT JOIN 后 pen 的一行，并标出右表各列的 NULL；游标比较必须与 ORDER BY 使用相同列顺序。解答见复习。类型、查询和计划来源：类型、约束、EXPLAIN。