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

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

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

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

插入一行时，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 取执行证据：

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。

比较前后至少保存：

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 决策

查看关系分解：

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;

查看单个索引：

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 更新、页分裂与填充因子

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 范围。

监控：

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 相同，唯一差别是：

CREATE INDEX ch09_write_indexed_counter_idx
ON shop_private.ch09_write_indexed (volatile_counter);

随后分别执行：

UPDATE ... SET volatile_counter = volatile_counter + 1;

一次 PostgreSQL 18.4 实测：

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

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

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

相同更新 + 同样预留 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 的机会：

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 重复、未使用与失效索引的判断

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 时点一并保存：

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

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

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

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

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：

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。失败可能留下：

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，要求：

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
• PostgreSQL 16 Release Notes：BRIN-indexed columns and HOT
• PostgreSQL 18：Monitoring Statistics
• PostgreSQL 18：System Catalog pg_index
• PostgreSQL 18：Routine Reindexing

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