跳转到主要内容

9.6 实战：为订单、库存与搜索入口设计索引

本节把前五节合并成一个完整评审：

4 个读取 query family
  → 5 个真实 candidate
  → before/after plans + result assertions
  → HOT/WAL twin
  → concurrent unique failure injection
  → retain/reject ledger
  → final catalog/model/state verification
  → PREF-PLAN-005 candidate evidence

实验只在带 marker 的 shop_private.ch09_* fixture 上执行。它不会触碰 shop 业务表的索引，也不会模拟生产点击动作。

9.6.1 从真实查询清单提出候选索引

先确认目标与身份

准备一个只包含本地 L1 凭据、权限为 0600 的 service file：

[pg36-admin]
host=/absolute/socket/or/host
port=5432
dbname=pg36_shop
user=...

然后：

export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin

psql -X -w \
  --dbname='service=pg36-admin application_name=pg36-ch09-preflight' \
  --command="
    SELECT current_database(), current_user,
           current_setting('server_version'),
           pg_is_in_recovery();
  "

只在已确认可写、可重建的 L1/本地数据库继续。脚本还会执行 ch05 model verification，要求数据库、schema、角色、fixture marker 与业务 checksum 均符合前章合同。没有 PGSERVICEFILE、action 非法或 context 不符时 fail closed。

下载并阅读：

setup 在七个同名关系全部缺失，或全部带精确 marker 时重建；任何同名异物都会拒绝。它属于 R1 fixture rebuild，不需要 reset token，但只能在 disposable L1 使用。

cd static/labs/ch09
export PG36_EVIDENCE_DIR="$PWD/evidence/ch09/candidates-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh candidates

四份 query contract

实验不是先给答案，而是先保存 before：

family predicate/order/result fixture 特征 候选
order customer equality + literal placed + time DESC Top-N；10 行 200000 行，placed 5% partial B-tree + INCLUDE
inventory SKU equality + warehouse order；30 行 300000 行，30 warehouses，现有 warehouse-first PK reverse covering B-tree
search generated tsvector @@ tsquery；100 行 100000 行，同一 simple config GIN
event 10 分钟 timestamptz range；600 行 400000 行，物理时间相关 BRIN；另建 B-tree 对照

原始查询分别在：

候选由 create-candidates.sh 以独立的 CREATE INDEX CONCURRENTLY 创建：

-- 订单：状态在 predicate，customer/time 为 key，窄返回列为 payload
CREATE INDEX CONCURRENTLY ch09_order_placed_cover_idx
ON shop_private.ch09_order_probe
    (customer_id, placed_at DESC)
INCLUDE (order_no, amount_minor)
WHERE order_status = 'placed';

-- 库存：反转已有 PK 的查询方向
CREATE INDEX CONCURRENTLY ch09_inventory_sku_cover_idx
ON shop_private.ch09_inventory_probe
    (sku_id, warehouse_id)
INCLUDE (available, reserved, updated_at);

-- 搜索：query 与 generated document 使用相同全文语义
CREATE INDEX CONCURRENTLY ch09_search_document_gin_idx
ON shop_private.ch09_search_probe
USING gin (search_document);

-- 事件：对物理相关范围保存 block summary
CREATE INDEX CONCURRENTLY ch09_event_occurred_brin_idx
ON shop_private.ch09_event_probe
USING brin (occurred_at)
WITH (pages_per_range = 32, autosummarize = on);

创建后对专属 fixture 做 VACUUM (ANALYZE)，再捕获 after。这里 vacuum 是为了制造稳定的 all-visible 教学条件；生产 index-only 收益必须按真实 autovacuum/churn 复测。

候选要允许被拒绝

event B-tree 也是实际创建的 candidate：

CREATE INDEX CONCURRENTLY ch09_event_occurred_btree_idx
ON shop_private.ch09_event_probe (occurred_at);

脚本保存它的 plan 与 size 后，证明 declared workload 已由小得多的 BRIN 满足，于是按 exact table/index identity 执行：

DROP INDEX CONCURRENTLY
    shop_private.ch09_event_occurred_btree_idx;

“创建成功并被使用”不等于必须保留。能输出 reject 且清理干净，是索引设计实验的重要能力。

9.6.2 在 Pigsty L1 保留、合并或拒绝并记录证据

一键运行完整闭环 

cd static/labs/ch09
export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch09/all-$(date -u +%Y%m%dT%H%M%SZ)"

./task.sh all

执行顺序：

manifest + ch05 preflight
  → marker-guarded fixture rebuild
  → four before plans
  → four retained candidates + VACUUM
  → after + custom/generic plans
  → event B-tree comparison and exact rejection
  → HOT/WAL twin updates
  → concurrent unique failure and exact recovery
  → final fixture/catalog/worker/model verify
  → semantic analyzer + rule-proposal validation

PG36_EVIDENCE_DIR 应使用每次唯一的目录。脚本以 umask 077 创建证据，不覆盖旧结果；manifest.txt 保存 server/client/Python 版本、target identity 与所有 source hash。

一次 PostgreSQL 18.6 实测输出：

status=ok
order=partial-covering/index-only/custom:true/generic:false/rows:10
inventory=reverse-covering/heap-fetches:0/rows:30
search=gin/rows:100
event=brin-retained/btree-rejected/size-fraction:0.00273
write=hot:1.0->0/wal:11257432->11631392
concurrent=23505/invalid-observed/exact-drop/remaining:0
decisions=retain:4/reject:4
proposal=0.1.0->0.4.0/PREF-PLAN-005/depends-on-v0.2+v0.3
final=workers:0/rejected:0/checksum:f8a7bfae59c6d16cd323abecfefe1014

不要把 WAL 字节、cost、elapsed、buffer 个数或 exact node tree 当 golden。分析器只断言：

  • 结果行数与语义不漂移；
  • literal/custom 能用 partial，generic status parameter 不能；
  • 订单/库存 after 是 index-only 且 heap fetch 为 0；
  • 搜索使用专属 GIN；
  • event BRIN/B-tree 都能返回 600 行，BRIN 至少小一个数量级；
  • unindexed volatile update 有高 HOT ratio，indexed twin 为 0 且 WAL 更多；
  • unique concurrent build 以 23505 失败，INVALID 被观察并精确删除；
  • rejected index=0、worker=0、业务 checksum 不变。

PG18 before inventory 在本机使用 warehouse-first PK skip scan。分析器记录该事实但不要求 exact node：PG14–17 没有该能力，PG18 也可能因 cost/data 改选其他路径。

读、写、失败三类 artifact

证据目录包含：

manifest.txt
preflight.txt
setup.txt

order-before.json
order-after.json
order-custom.json
order-generic.json
inventory-before.json
inventory-after.json
search-before.json
search-after.json
event-before.json
event-brin.json
event-btree.json

catalog-before-rejection.csv
catalog-final.csv
write-base.json
write-indexed.json
write-stats.csv

concurrent-failure/
  create.stdout
  create.stderr
  invalid-index.csv
  drop.stdout
  drop.stderr
  summary.txt

verify.txt
index-summary.json
index-summary.txt

raw plan/catalog 用于复核，index-summary.json 用于稳定关系，不能只保留最后一行 status=ok。

4 个保留，4 个拒绝

index-decisions.json 为每个结论绑定 query 和 artifact：

candidate 结论 依据
order partial covering retain literal workload 稳定、Top-N、index-only；同时记录 generic 边界
inventory reverse covering retain SKU-first 是 declared access path，返回 30 个 warehouse 且 heap fetch=0
search document GIN retain document/query 使用相同 simple config 与 @@ 语义
event occurred BRIN retain 物理相关范围可用，约为 B-tree 大小的 0.27%
event occurred B-tree reject 对同一 workload 的额外精度/空间不值
warehouse-only B-tree reject 现有 PK 已以 warehouse 为左前缀
generic attributes JSONB GIN reject 没有 declared containment consumer，却会索引大量 token
volatile counter B-tree reject 无读取消费者，HOT 1→0 且 WAL 增加

本 fixture 没有需要 merge 的 pair，但生产 ledger 应允许 merge：例如一个较完整候选在验证后替代两个真正重叠索引。merge 仍需先排除 constraint/replica identity，并验证所有 consumer，不能只做字符串前缀比较。

在 Pigsty 中复核相同关系

实验运行时或生产 shadow 验证时，在同一 UTC 窗口观察：

Query:
  calls, rows, mean/tail, shared/temp blocks, WAL

Table/Index:
  seq/index scans, tuple fetch, relation/index size,
  HOT/update/vacuum behavior

Instance:
  CPU, load, memory, disk IOPS/latency, checkpoint

Replication:
  WAL rate, archive status, receive/replay lag

Session/Lock:
  build application_name, phase, waits, long transactions

面板结论回链 manifest + plan JSON + catalog CSV + query identity。L1 的“retain”是机制验收，不自动授权生产上线；生产必须另建时间窗、磁盘/replica 水位、审批与回退。

9.6.3 将索引审查规则追加到规约

从“应验证计划”升级为可运行规则

baseline-v0.4-proposal.json 不改写第 6 章的不可变 v0.1 baseline，而是为 PREF-PLAN-005 追加 candidate evidence：

索引候选必须绑定真实 query/parameter/order/return shape；
保存 before/after plan、结果、buffers、大小、写/HOT/WAL 代价；
记录 concurrent build 失败回收；
明确 retain/merge/reject。

提案绑定三层 provenance：

base:
  0.1.0 canonical checksum

dependencies:
  ch07 v0.2 proposal canonical checksum
  ch08 v0.3 proposal canonical checksum

candidate:
  0.4.0 / PREF-PLAN-005 / ch09 evidence paths

analyze_indexes.py 每次 all/review 都重新 canonicalize JSON 并核对 checksum、依赖顺序、rule id 和 artifact existence。只改 proposal 中的声明、不同步依赖内容会 fail；这避免一条后续规则悄悄引用已漂移的前章证据。

它仍标记为：

{
  "candidate_baseline": "0.4.0",
  "status": "candidate"
}

只有以下条件完成后才可晋升：

  1. PostgreSQL 14–18 compatibility matrix 通过并保留 PG18 skip-scan 差异；
  2. 至少一个真实 Pigsty workload 窗口复测 read/write/build 水位；
  3. 先审查并晋升依赖的 v0.2/v0.3；
  4. 生成新的不可变 release artifact 和 canonical checksum。

章节成功不等于治理基线已发布。

reset 需要两个独立确认

all 最终保留七个 ch09 fixture 和四个 retained candidate，便于复核。若要删除，只在已确认的 L1 执行 R2：

cd static/labs/ch09
export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch09/reset-$(date -u +%Y%m%dT%H%M%SZ)"

export PG36_RESET_TOKEN=RESET_CH09_INDEX_LAB
export PG36_RESET_TARGET=pg36_shop/shop_private/ch09
./task.sh reset

两个 token 各自防一类误操作：

  • action token 证明调用者明确要求 reset；
  • target token 绑定 database/schema/chapter。

脚本还会逐一核对 marker；同名异物存在时，即使 token 正确也拒绝。成功后必须看到：

status=ok
reset_target=pg36_shop/shop_private/ch09
remaining_ch09_relations=0

并再次运行 ch05 verification，业务 checksum 仍为：

f8a7bfae59c6d16cd323abecfefe1014

负向验收同样重要：空 token、错 action token、错 target、无 service file 和非法 action 都必须非零退出，并保持对象与业务 checksum 不变。

最终复现清单 

# shell/Python/JSON 静态检查
bash -n static/labs/ch09/*.sh
PYTHONPYCACHEPREFIX=/tmp/pg36-pycache \
  python3 -m py_compile static/labs/ch09/analyze_indexes.py
python3 -m json.tool static/labs/ch09/index-decisions.json >/dev/null
python3 -m json.tool static/labs/ch09/baseline-v0.4-proposal.json >/dev/null

# 完整机制验收
export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch09/final-$(date -u +%Y%m%dT%H%M%SZ)"
static/labs/ch09/task.sh all

# 独立状态验收
static/labs/ch09/task.sh verify

验收通过后，团队得到的不是“索引速查表”，而是一套可以迁移到真实 query review 的方法：先证明 operator/predicate/order，再证明收益覆盖写入与生命周期成本，最后让 retain/reject 都有可审计证据。

上一节：验证而不是“加完就快” · 返回本章目录 · 下一章：顾此失彼：并发控制与隔离异常 · 查看全书目录 · 查看索引中心

最后修改 2026年8月28日 · chore: remove GitHub Pages workflow (83aa397)