# 索引也有写入和生命周期成本

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

---

索引把一次读取节省的工作，变成所有相关写入都要长期承担的工作。一份完整收益表至少有两边：

```text
read benefit:
  fewer heap/index blocks
  no sort or earlier LIMIT stop
  better latency/throughput/tail

lifetime cost:
  insert/update/delete CPU and latency
  extra index pages and cache displacement
  WAL, archive, backup and replication
  vacuum/analyze/build/reindex
  lock, disk peak and failed-build recovery
  lost HOT opportunities
```

只保存 after query 的执行时间，等于只记收益、不记负债。

## 9.4.1 写放大、缓存占用与 WAL {#item-9-4-1}

### 一次逻辑写会触碰多少物理结构

插入一行时，heap、每个相关 index、visibility/free-space metadata 和 WAL 都可能变化。更新在 MVCC 下创建新 row version；若不满足 HOT，它还要为各索引写新 tuple。删除先留下 dead version，之后 vacuum 才清理 heap/index 可回收空间。

索引越多，常见代价越大：

- 更多 access method/operator expression 计算；
- 更多 buffer 被读入、锁定并标脏；
- B-tree page split、GIN pending list、BRIN summary 等各自维护；
- 更多 WAL 传到 archive、streaming replica 与 logical decoding；
- checkpoint 写出更多 dirty page；
- base backup、restore、`pg_upgrade --link` 之外的重建与磁盘巡检范围更大；
- autovacuum/index cleanup 和故障修复窗口更长。

这不是说“索引数量越少越好”，而是每个索引都必须有消费者和证据。

### 用同一条写路径量化

PostgreSQL 可直接给 data-changing statement 取执行证据：

```sql
EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    SETTINGS,
    FORMAT JSON
)
UPDATE counter
SET value = value + 1
WHERE bucket_id BETWEEN 1 AND 1000;
```

`EXPLAIN ANALYZE` **会真的执行写语句**。安全实验应使用专属 fixture，或在能够完全回滚且不涉及 sequence/外部副作用的事务中运行。生产不能为了看 plan 对一条未知 DML 随手加 `ANALYZE`。

比较前后至少保存：

```text
result/affected rows
execution time distribution, not one sample
shared/local/temp buffer hits/reads/writes
WAL records/FPI/bytes
table/index sizes
TPS and concurrent read/write latency
replica WAL receive/replay lag
checkpoint and I/O pressure
```

`WAL bytes` 会受 full-page image、checkpoint 时点、page 初始状态、compression 与版本影响，不能把本机某个精确数值写成阈值。A/B 的稳定断言通常是方向和相对幅度，并要重复、交替顺序。

### 索引大小也是 cache 决策

查看关系分解：

```sql
SELECT
    relid::regclass AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS heap,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes,
    pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
```

查看单个索引：

```sql
SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS bytes,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relid = 'public.orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;
```

一个 9 GB 索引不等于需要 9 GB `shared_buffers`，操作系统 page cache 也参与；但热 working set 彼此竞争是真实的。增加大索引可能让某条 query 更快，却把另一条热路径的数据页挤出 cache。Pigsty 的 table/index、buffer、I/O 和 instance 指标应在同一时间窗关联，而不是孤立看 `idx_scan`。

本章事件候选正是空间决策：400000 行物理时间相关数据上，BRIN 为 24576 bytes，对照 B-tree 为 9003008 bytes，比例约 0.00273。字节值只属于本次 fixture；保留 BRIN、拒绝 B-tree 的理由是 declared range workload 不需要为额外精度和 cache footprint 付费。

## 9.4.2 HOT 更新、页分裂与填充因子 {#item-9-4-2}

### HOT 省掉什么

Heap-Only Tuple update 让新 row version 留在旧 row 所在 heap page，并沿 page 内 HOT chain 查找，因此无需为该更新创建普通 index tuple。它同时减少索引写入和以后清理旧 index entry 的负担。

在 PostgreSQL 16–18，HOT 的关键条件可表述为：

1. 新 tuple 能放进旧 tuple 所在 heap page；
2. 更新没有改变任何 non-summarizing index 引用的列。

这里“引用”包括 key、expression、`INCLUDE` payload 和 partial-index predicate。核心 BRIN 是 summarizing access method；PostgreSQL 16 起，如果只改变 BRIN-indexed key，仍可允许 HOT，但若改变 partial predicate 引用列仍会阻止 HOT。

**版本边界**：PostgreSQL 14–15 还没有“只更新 BRIN 列仍可 HOT”的改进，应按更保守的规则理解：更新任何索引引用列都会阻止 HOT。它是 PostgreSQL 16 引入的能力，不能回写到整个 14–18 范围。

监控：

```sql
SELECT
    schemaname,
    relname,
    n_tup_upd,
    n_tup_hot_upd,
    CASE
      WHEN n_tup_upd = 0 THEN NULL
      ELSE n_tup_hot_upd::numeric / n_tup_upd
    END AS hot_ratio
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;
```

统计是累计观测，先记录 reset epoch 和时间窗；不同写 workload 混在一起时，整体 ratio 不能解释某条 UPDATE。

### 本章 HOT/WAL 对照

两个 50000 行表结构、数据和 table `fillfactor=50` 相同，唯一差别是：

```sql
CREATE INDEX ch09_write_indexed_counter_idx
ON shop_private.ch09_write_indexed (volatile_counter);
```

随后分别执行：

```sql
UPDATE ... SET volatile_counter = volatile_counter + 1;
```

一次 PostgreSQL 18.6 实测：

```text
without volatile index:
  HOT ratio = 1.0
  WAL bytes = 11257432

with volatile index:
  HOT ratio = 0
  WAL bytes = 11631392
```

精确 WAL 会漂移；稳定关系是：

```text
相同更新 + 同样预留 page space
  → 未引用 volatile_counter 的索引集合允许 HOT
  → 把 volatile_counter 放入普通 B-tree 后 HOT 消失
  → 本次 statement WAL 增加
```

这个 counter 没有 declared read query，所以候选被拒绝。若未来确有关键 point lookup，评审要比较读取收益与 HOT/WAL 代价，而不是把“阻止 HOT”当绝对禁令。

### table fillfactor 与 index fillfactor 不同

降低 **table** `fillfactor` 会在 heap page 预留空间，提高后续 row version 留在同页、形成 HOT 的机会：

```sql
ALTER TABLE hot_account SET (fillfactor = 80);
```

它不会把现有 page 自动重写成 80% 装载；要等待 churn 或受控重写，并承担表更大、顺序扫描更多 page 的代价。

降低 **B-tree index** `fillfactor` 则在 build 时给 leaf page 留空间，可能减少后续 insertion/page split，但会让索引更大、cache density 更低。它不创造 HOT 所需的 heap page 空间。两个同名参数作用在不同结构，不能混为一谈。

page split 不是“索引损坏”，是 B-tree 正常维护；真正要评估的是：

- insert key 是否随机、单调或集中在热点；
- page split/WAL 与 tail latency 是否成为问题；
- 低 fillfactor 的空间成本是否值得；
- `REINDEX CONCURRENTLY`/重建是否有真实 bloat 证据；
- 去重、key width 与 payload 是否可优化。

不要把周期性重建所有索引当保养仪式。

## 9.4.3 重复、未使用与失效索引的判断 {#item-9-4-3}

### `idx_scan=0` 只能生成调查清单

`pg_stat_user_indexes.idx_scan=0` 不能单独授权 `DROP INDEX`，因为它可能表示：

- statistics 刚 reset，观察窗太短；
- rare but critical 月结、故障切换或合规查询尚未发生；
- 该索引只在 replica 被读，primary 本地统计看不到；
- planner 用另一条等价路径只是暂态；
- 它支撑 `PRIMARY KEY`、`UNIQUE`、exclusion constraint；
- 它用于 foreign-key parent delete/check 或运维任务；
- 它是 logical replication 的 replica identity；
- 应用版本/feature flag 尚未完整覆盖；
- partition child 各自 workload 不同；
- 统计语义和计数方式在版本间有差异。

至少把数据库统计 reset 时点一并保存：

```sql
SELECT datname, stats_reset
FROM pg_stat_database
WHERE datname = current_database();
```

然后覆盖一个能代表周、月、批处理和故障流量的时间窗，并查 primary、read replicas 与 query history。

### “重复”要比较完整定义与职责

两个索引列名相似，不代表重复。审查结构至少包含：

```text
access method
key expressions and order
operator classes and collations
ASC/DESC and NULLS
partial predicate
INCLUDE payload
unique/nulls-not-distinct/exclusion semantics
valid/ready/live state
partition attachment
constraint and replica-identity ownership
```

可先取 catalog：

```sql
SELECT
    i.indexrelid::regclass AS index_name,
    am.amname,
    i.indisunique,
    i.indisprimary,
    i.indisexclusion,
    i.indisreplident,
    i.indisvalid,
    i.indisready,
    i.indislive,
    pg_get_indexdef(i.indexrelid) AS definition,
    pg_get_expr(i.indpred, i.indrelid) AS predicate
FROM pg_index AS i
JOIN pg_class AS c
  ON c.oid = i.indexrelid
JOIN pg_am AS am
  ON am.oid = c.relam
WHERE i.indrelid = 'public.orders'::regclass;
```

`(a, b)` 可以支持一部分 `a` lookup，但不等价于 `(a)`：它更宽，可能有不同排序/payload/uniqueness，也可能让短索引更适合 cache。反过来，若所有 `(a)` consumers 都被 `(a,b)` 等价覆盖，短索引才进入候选合并清单。必须用 before/after workload 验证。

### INVALID 是状态，不是自动删除理由

并发创建过程中，catalog 会先出现尚未 valid 的 index。失败可能留下：

```text
indisvalid = false
indisready = true or false depending on failed phase
```

某些 INVALID index 仍会被写路径维护，却不能被查询采用；它既有成本又没有读收益，需要处置。但先确认：

- 是否仍有合法 build/reindex 正在运行；
- index 与 table 的 exact OID/schema/name；
- 是否由 constraint/partition operation 管理；
- 失败 SQLSTATE、phase 与原始 DDL；
- 是否已有人在做恢复；
- drop/recreate 的锁、磁盘、唯一性与 replica 风险。

本章用故意重复数据执行 `CREATE UNIQUE INDEX CONCURRENTLY`，要求：

```text
SQLSTATE 23505
  → catalog 观察到 exact INVALID unique index
  → 保存定义、大小与 flags
  → DROP INDEX CONCURRENTLY exact schema-qualified target
  → remaining=0
```

这不是“定时删除所有 INVALID”的脚本模板。生产应由变更单绑定 exact identity、owner、证据和回退；若失败对象承担约束语义，还要先恢复约束正确性。

### 删除本身也要 A/B 和回退

安全清理流程是：

1. 生成 candidate，不执行 drop；
2. 排除 constraint、replica identity、partition 与 rare critical consumers；
3. 保存完整 definition、owner、size、usage epoch 和依赖；
4. 在可代表的 primary/replica workload 中验证替代路径；
5. 评估 `DROP INDEX` 或 `DROP INDEX CONCURRENTLY` 的限制与窗口；
6. 一次处理少量对象，观察 query latency、CPU/I/O 与 write；
7. 保留可审计的 recreate DDL 与停止条件。

索引生命周期的终点不是“catalog 更干净”，而是正确性不变、关键 SLO 不退化、写入和空间确有改善。

## 延伸阅读

- [PostgreSQL 18：Heap-Only Tuple Updates](https://www.postgresql.org/docs/18/storage-hot.html)
- [PostgreSQL 16 Release Notes：BRIN-indexed columns and HOT](https://www.postgresql.org/docs/release/16.0/)
- [PostgreSQL 18：Monitoring Statistics](https://www.postgresql.org/docs/18/monitoring-stats.html)
- [PostgreSQL 18：System Catalog `pg_index`](https://www.postgresql.org/docs/18/catalog-pg-index.html)
- [PostgreSQL 18：Routine Reindexing](https://www.postgresql.org/docs/18/routine-reindex.html)

---

[上一节：表达式、部分与覆盖索引](../03/) · [返回本章目录](../) · [下一节：验证而不是“加完就快”](../05/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)