# 索引与约束的在线化路径

LLMS 索引： [llms.txt](/llms.txt)

---

索引和约束的“在线化”不是无锁，而是把：

```text
build / enforce new rows / scan old rows / publish identity
```

拆到不同阶段，缩短最强锁的持续时间，并让失败状态可识别。每种对象支持的拆分方式不同；不能把 `NOT VALID`、`CONCURRENTLY` 和 `USING INDEX` 当成通用后缀。

## 11.3.1 `CREATE INDEX CONCURRENTLY` 的阶段与失败残留 {#item-11-3-1}

### Concurrent build 解决什么

普通 `CREATE INDEX` 会阻止表上的写入。`CREATE INDEX CONCURRENTLY` 允许普通 insert/update/delete 继续，但代价是：

- 多阶段目录状态；
- 至少两次 table scan；
- 等待影响旧 snapshot 的 transaction；
- 更多总工作量和更长 elapsed time；
- 每表同一时刻只能有一个 concurrent build；
- 不能在 transaction block 中运行；
- expression/predicate evaluation 仍可能失败；
- 失败可能留下 `INVALID` index。

所以 `CONCURRENTLY` 的意思是“降低对普通写的阻塞”，不是“免费后台任务”。

### 先复用第 9 章的候选纪律

模式发布中的 index 也必须先回答：

```text
query shape and parameterization
operator / collation / opclass
before/after plan and result identity
index size and build WAL
write/HOT cost
replica and disk watermarks
failure cleanup identity
retention or removal phase
```

本章不重复第 9 章的全套收益评估，只把一个 temporary partial index 应用于回填：

```sql
CREATE INDEX CONCURRENTLY
    ch11_order_shipping_missing_idx
ON shop_private.ch11_order (order_id)
WHERE shipping_code IS NULL;
```

它绑定：

```text
WHERE order_id > checkpoint
  AND shipping_code IS NULL
ORDER BY order_id
LIMIT batch_size
```

随着 backfill 完成，predicate 集合缩到零；它是 migration acceleration object，不是永久 schema。

### 为什么必须是独立入口

[online-index.sh](/labs/ch11/online-index.sh) 让 psql 在同一 session 中依次执行：

```text
SET ROLE
SET lock_timeout
SET statement_timeout
CREATE INDEX CONCURRENTLY
```

每个 `--command` 是独立 top-level command，避免把 concurrent build 塞进 implicit multi-statement transaction。下面写法会失败：

```sql
BEGIN;
CREATE INDEX CONCURRENTLY ...;
COMMIT;
```

migration framework 如果默认“每个 migration 自动包事务”，需要为这类命令声明 non-transactional phase；不能偷偷关闭整套 framework 的事务保护。

### 失败后查 catalog，不按文件名猜

```sql
SELECT
    index_class.relname,
    index_catalog.indisready,
    index_catalog.indisvalid,
    index_catalog.indisunique,
    pg_get_indexdef(index_catalog.indexrelid),
    pg_get_expr(
        index_catalog.indpred,
        index_catalog.indrelid
    ) AS predicate
FROM pg_index AS index_catalog
JOIN pg_class AS index_class
  ON index_class.oid = index_catalog.indexrelid
WHERE index_catalog.indrelid =
      'shop_private.ch11_order'::regclass;
```

失败的 concurrent index：

- 可能仍占磁盘；
- 可能给写入带来维护开销；
- 若是 unique build，某些阶段甚至可能开始施加 uniqueness；
- 不会被 planner 当作正常 valid index。

处理顺序：

```text
capture SQLSTATE/stderr and catalog
  → identify exact schema/index/table/definition
  → decide repair/rebuild/drop
  → DROP INDEX CONCURRENTLY exact_name
  → verify catalog absence
```

不要运行模糊 `DROP INDEX IF EXISTS some_name` 后声称“已清理”；同名跨 schema、错误定义和并发新建都需要防护。

### 从 unique index 快速接成约束

对非分区普通表，可以先：

```sql
CREATE UNIQUE INDEX CONCURRENTLY candidate_uidx
ON account (tenant_id, external_ref);
```

验证 valid 后：

```sql
ALTER TABLE account
    ADD CONSTRAINT account_external_ref_key
    UNIQUE USING INDEX candidate_uidx;
```

第二步通常是短 catalog operation。边界：

- 必须是 unique B-tree；
- 使用默认排序；
- 不能是 expression index；
- 不能是 partial index；
- `PRIMARY KEY` 还要求列 `NOT NULL`，否则可能触发扫描；
- 当前不能用该语法直接给 partitioned table 添加约束；
- 转换后 index 由 constraint 拥有，drop constraint 会连带 drop index。

先 concurrent build 再 attach，不消除第二步的锁预算，只缩短需要强锁时做的工作。

## 11.3.2 `NOT VALID`、`VALIDATE CONSTRAINT` 与验证扫描 {#item-11-3-2}

### `NOT VALID` 的精确定义

对支持的 CHECK/FK（PostgreSQL 18 还扩展到关系级 NOT NULL），`ADD ... NOT VALID`：

```text
does not scan all pre-existing rows at ADD time
does enforce the constraint for future INSERT/UPDATE rows
records convalidated=false
```

它不是：

```text
constraint disabled
validation optional forever
available to UNIQUE/PRIMARY KEY
no locks
```

本章 expand 后：

```text
ch11_order_shipping_pair_consistent
  contype=c
  convalidated=false

pg_attribute.shipping_code
  attnotnull=false
```

旧行可以 `shipping_code IS NULL`，但新/更新行不能产生错误 pair。

### 为什么 validation 能与 DML 共存

```sql
ALTER TABLE shop_private.ch11_order
    VALIDATE CONSTRAINT
    ch11_order_shipping_pair_consistent;
```

PostgreSQL 扫描旧行时，新/更新行已经由 constraint enforcement 保护，因此 validation 使用 `SHARE UPDATE EXCLUSIVE`，不需要像直接 ADD valid constraint 那样长期阻止普通更新。

这仍然是全表读取：

- 会消耗 IO/buffer/CPU；
- 与某些 DDL、VACUUM family 操作冲突；
- 可能造成 replica/存储侧压力；
- 遇到历史坏值会失败；
- 需要单独 `statement_timeout` 与发布水位。

`convalidated=true` 是完成证据；“命令返回成功”之外还应保存：

```sql
SELECT
    conname,
    contype,
    convalidated,
    pg_get_constraintdef(oid, true)
FROM pg_constraint
WHERE conrelid = '...'::regclass;
```

### 非空的跨版本路径

PostgreSQL 14–17 的通用做法：

```sql
ALTER TABLE orders
    ADD CONSTRAINT orders_new_col_nn
    CHECK (new_col IS NOT NULL)
    NOT VALID;

ALTER TABLE orders
    VALIDATE CONSTRAINT orders_new_col_nn;

ALTER TABLE orders
    ALTER COLUMN new_col SET NOT NULL;
```

当一个 valid CHECK 已经证明列无 NULL，`SET NOT NULL` 可以避免再做一次全表扫描；执行时让该 CHECK 保持存在。

本章实测：

```text
pair CHECK      false → true
non-null CHECK  false → true
attnotnull      false → true
SET NOT NULL relfilenode 19911 → 19911
```

same filenode 说明没有 table rewrite；官方保证与 valid CHECK 共同支持“无需重复验证扫描”的判断。它仍需要短时强锁，不能省略 lock budget。

### PostgreSQL 18 的目录差异

PostgreSQL 17 及以前，relation column 的 NOT NULL 主要表示在：

```text
pg_attribute.attnotnull
```

`pg_constraint` 中 `contype='n'` 主要用于 domain。PostgreSQL 18 把 relation NOT NULL 也提升为完整 constraint：

```text
pg_constraint.contype='n'
pg_constraint.conrelid=<table>
named NOT NULL
convalidated state
inheritance/enforcement metadata
```

本章 PG18.6 验收同时看到：

```text
ch11_order_shipping_code_not_null | n | true
pg_attribute.shipping_code        | a | true
```

因此跨 14–18 的 catalog checker 必须 version-gate：

```text
PG14–17:
  require attnotnull=true
  do not require relation contype=n

PG18:
  require attnotnull=true
  require exactly one validated relation NOT NULL constraint
```

不要因为 PG18 新语法支持 `NOT NULL NOT VALID` 就把它直接写进声明支持 PG14–18 的无条件 migration。通用主体仍可使用 CHECK → VALIDATE → SET NOT NULL。

### NOT VALID 失败恢复

若 validation 发现坏值：

```text
constraint remains present and not valid
new/updated rows remain protected
old violations remain queryable
```

这通常比直接 ADD valid constraint 后整个发布卡住更可控。修复流程：

1. 保存 violation query 与 stable row identity；
2. 暂停或限速 backfill；
3. 修复历史数据；
4. 再次验证 zero violations；
5. 重跑 `VALIDATE CONSTRAINT`；
6. 查 `convalidated`；
7. 才进入 switch。

不应为了让 validation 通过而随手 drop constraint；那会重新打开新债务入口。

## 11.3.3 默认值、非空与类型变更的版本边界 {#item-11-3-3}

### 默认值按 volatility 与目标版本判断

对于：

```sql
ALTER TABLE t ADD COLUMN c type DEFAULT expression;
```

先回答：

```text
expression volatility?
evaluated once or per row?
does target PG support metadata missing value?
is value a real historical fact?
will old application explicitly send NULL?
does default need removal after migration?
```

non-volatile constant fast path 在当前支持范围内可用，但强锁仍在。volatile expression 会逐行更新；不同 extension/function 还要确认 volatility declaration 是否真实，不能为追求 fast path 把非 immutable 函数伪装成 immutable。

### default 与 NOT NULL 的组合

新增：

```sql
ADD COLUMN flag integer NOT NULL DEFAULT 7
```

对 constant default 可以很快，但业务语义仍可能错误：

- 所有旧行真的都是 7 吗；
- 未来调用方省略时真的应为 7 吗；
- 旧 application 显式传 NULL 会不会失败；
- 7 是临时 backfill 值还是长期 default；
- 是否需要区分“未知”与“默认”。

物理 fast path 不能替代领域建模。若历史值需要从旧数据计算，应 nullable expand + backfill，而不是给所有历史 tuple 伪造同一个事实。

### 类型变更的四条路径

| 路径 | 适用 | 主要风险 |
|---|---|---|
| in-place ALTER TYPE | 小表/可证明无重写转换 | lock、依赖、plan/statistics |
| new column + backfill | 可双表示、需转换 | coexistence、WAL、contract |
| new table + dual write | 大结构变化/新 key | consistency、cutover |
| logical copy/CDC | 极大表/跨系统 | ordering、lag、reconciliation |

选型不只看表大小。还看 write rate、转换是否可逆、FK/unique、业务 key、partition、可用窗口和 rollback。

### Precheck 必须在写前失败

text → integer 例子：

```sql
SELECT id, old_value
FROM source
WHERE old_value !~ '^[0-9]+$'
   OR old_value::numeric >
      2147483647;
```

真正 migration 仍要处理 precheck 与执行之间的竞态：

- 先加兼容 CHECK 约束；
- 暂停旧 writer；
- 在同一受控 transaction 再检查；
- 或把所有写引到能验证新范围的路径。

还要检查：

```text
default
generated column
views/functions
expression/partial indexes
foreign keys
statistics and extended statistics
logical replication publications/subscribers
driver parameter/result type decoding
```

成功后重新 `ANALYZE`，并验证 query plans 与 driver contract；不能只比较 `information_schema.columns`。

### 版本矩阵写进 artifact

每个 migration repository 应维护至少：

```text
minimum supported major
maximum validated major
version-specific syntax
catalog assertion branch
feature introduced version
known semantic differences
```

本章：

| 能力 | PG14 | PG15 | PG16 | PG17 | PG18 |
|---|---:|---:|---:|---:|---:|
| constant-default metadata path | ✓ | ✓ | ✓ | ✓ | ✓ |
| CHECK/FK NOT VALID | ✓ | ✓ | ✓ | ✓ | ✓ |
| valid CHECK helps SET NOT NULL | ✓ | ✓ | ✓ | ✓ | ✓ |
| relation NOT NULL in pg_constraint | — | — | — | — | ✓ |
| named/relation NOT NULL NOT VALID | — | — | — | — | ✓ |
| DETACH PARTITION CONCURRENTLY | ✓ | ✓ | ✓ | ✓ | ✓ |

“最低 PG14”意味着代码必须先在 PG14 parser/catalog 上成立；不能只在 PG18 运行后根据结果猜兼容。

## 本节验收问题

1. concurrent index 是否真正位于 transaction block 外；
2. build 前后的 query、write、size/WAL 证据是否完整；
3. failure 是否检查 `indisvalid/indisready` 并精确清理；
4. partial migration index 是否有明确 drop phase；
5. `NOT VALID` 是否被正确解释为“新写入已执行”；
6. validation scan 的 IO、lock 与 timeout 是否独立预算；
7. CHECK → VALIDATE → SET NOT NULL 次序是否跨 PG14–18；
8. PG18 relation NOT NULL catalog 是否 version-gated；
9. default 的 volatility 与历史语义是否都评审；
10. ALTER TYPE 是否检查数据、依赖、driver 和 statistics；
11. catalog fast path 是否被误写成“零锁零风险”；
12. dynamic cost/timing 是否只作观测，不作跨环境常数。

## 参考资料

- [PostgreSQL 18：CREATE INDEX](https://www.postgresql.org/docs/18/sql-createindex.html)
- [PostgreSQL 18：ALTER TABLE](https://www.postgresql.org/docs/18/sql-altertable.html)
- [PostgreSQL 18 Release Notes](https://www.postgresql.org/docs/18/release-18.html)
- [PostgreSQL 17：pg_constraint](https://www.postgresql.org/docs/17/catalog-pg-constraint.html)
- [PostgreSQL 18：pg_constraint](https://www.postgresql.org/docs/18/catalog-pg-constraint.html)

---

[上一节：Expand–Migrate–Contract](../02/) · [返回本章目录](../) · [下一节：在线分区化](../04/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)