HOT – HDUO Lab

排查过程中接到告警，某核心订单库主节点 IOPS 持续处于 100% 饱和状态，iowait 飙升导致 CPU Load Average 冲破 80，P99 延迟从平时的 10ms 劣化到 4000ms+。排查发现 QPS 并没有出现突增，但磁盘的写入吞吐量（Write Bandwidth）却达到了平时的十倍以上，且主要消耗在 WAL 日志写入和 Autovacuum 进程上。直接抛出结论：开发人员在 updated_at 字段上创建了毫无意义的单列 B-Tree 索引，且业务代码每次 UPDATE 必定刷新该时间戳。这一行为彻底破坏了 PostgreSQL 的 HOT（Heap-Only Tuples） 机制，导致每一次普通的数据行更新都退化为全局索引更新，叠加 Checkpoint 后的 Full Page Writes (FPW)，引发了灾难级的 WAL 写放大与 MVCC 索引膨胀。删掉废柴索引并微调 fillfactor 后，IOPS 直接断崖式下降 85%。

案发现场与指标佐证

登陆机器第一步，先看系统 I/O 状态，iostat -x 1 的数据简直刺眼：

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme1n1           0.00     0.00  120.50 8540.20  1024.00 98560.00    23.00    14.50    1.68    3.50    1.65   0.11 100.00

高 w/s，高 wkB/s，磁盘完全被写请求打满。

切进数据库，看看到底是哪些表在作妖。通过查询 pg_stat_user_tables，我抓取了系统里 Update 最频繁的前 5 张表，重点观察它们的 HOT 更新率（HOT Ratio）：

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       ROUND((n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0)) * 100, 2) AS hot_ratio
FROM pg_stat_user_tables
WHERE n_tup_upd > 100000
ORDER BY n_tup_upd DESC LIMIT 5;

执行结果：

  relname   | n_tup_upd | n_tup_hot_upd | hot_ratio 
------------+-----------+---------------+-----------
 orders     |  85940321 |        120340 |      0.14
 users      |   4039102 |       3980120 |     98.53
 inventory  |  15039201 |      14800300 |     98.41

orders 表的 HOT 更新率竟然只有可怜的 0.14%。这就意味着，这表中 99.86% 的 Update 操作，PG 内核都在老老实实地写入全套索引记录。

我查了一下 orders 表的索引结构：除了主键和常规的状态、用户 ID 索引外，赫然躺着一个 idx_orders_updated_at。问业务研发为什么建这个索引？答曰：“ORM 自动生成的，想着以后可能会按修改时间排序拉取数据，就先建上了。” 毫无数据访问逻辑支撑的“防御性建索引”，在 MySQL 里可能只是浪费点空间和微小的写入性能，但在 PostgreSQL 里，这简直是自杀。

为什么打破 HOT 机制是致命的？

PostgreSQL 的 MVCC 实现与 MySQL (InnoDB) 截然不同。InnoDB 是原地更新（In-Place Update），旧版本数据存放在 Undo Log 中；而 PG 没有独立的 Undo 表空间，它的 Update 本质上是 Delete + Insert。即标记旧元组（Tuple）为不可见，并在页（Page）中插入一个全新的元组。

如果每次 Update 都要在所有索引树上插入新指针，这种写放大是不可接受的。因此 PG 引入了 HOT (Heap-Only Tuples) 机制。满足 HOT 的条件极其严苛，但收益极高：

更新的字段上不能有任何索引。
当前数据页（Block）内部必须有足够的剩余空间存放新元组。

只要满足上述条件，PG 就只会在 Heap 层面做行链更新（旧行指向新行），所有的索引结构都无需修改。

在本次事故中：

业务线每修改一次订单状态，都会执行类似 UPDATE orders SET status = 2, updated_at = NOW() WHERE id = 100; 的语句。
因为 updated_at 存在索引，直接破坏了 HOT 条件 #1。
表的 fillfactor 是默认的 100，页面平时塞得满满当当，破坏了 HOT 条件 #2。

蝴蝶效应开始了：

写放大 X 倍： orders 表共有 6 个索引。一次单行更新，不仅要写 1 个新的 Heap 数据，还要在 6 个 B-Tree 索引中各插入 1 条新记录。
WAL 风暴： PG 为了保证崩溃恢复（Crash Recovery），在每次 Checkpoint 后的第一次页面修改时，会将整个 8KB 页面完整写入 WAL（即 Full Page Writes, FPW）。因为索引频繁变动，大量的数据页和 6 个索引树的页面都在被高频触碰，导致大量触发 FPW，WAL 日志生成速度高达数百 MB/s，直接榨干 SSD 的 IOPS。
Autovacuum 瘫痪： 索引膨胀导致 Autovacuum 清理死元组时需要扫描和清理庞大的索引树，进一步加剧了磁盘 I/O 竞争，引发恶性循环。

破局与调优落地

弄懂了底层逻辑，解决起来极其简单，只需要做减法。

1. 物理超度无用索引 既然业务根本没有使用 updated_at 过滤数据的慢查询，果断 DROP INDEX CONCURRENTLY idx_orders_updated_at;。（注意：必须加上 CONCURRENTLY 以避免锁表阻塞线上交易）。

2. 预留页内空间，召唤 HOT 机制 对于这种极度高频 Update 的核心表，必须将页填充率（fillfactor）下调，给页内更新留出空间：

ALTER TABLE orders SET (fillfactor = 85);

注：这条命令仅对新分配的页生效。对于存量数据，我不建议在线上直接跑 VACUUM FULL，而是用 pg_repack 选个低峰期重整表结构，或者就让它随着增量数据自然过渡。

3. WAL 压缩兜底 为了防御未来可能的类似写放大，在 postgresql.conf 中开启 WAL 压缩（PG 15+ 推荐 zstd，低版本用 on 启用 pglz）：

wal_compression = zstd

开启后会消耗微量的 CPU 算力（1%-2%），但能将 FPW 造成的 WAL 日志体积压缩 60% 以上，对 I/O 瓶颈极具性价比。

调整执行后的 10 分钟内，orders 表的 HOT Ratio 迅速攀升至 80% 以上，磁盘写入量暴跌至几兆每秒，iowait 归零，P99 延迟回归 10ms，世界清静了。

排查清单：PostgreSQL HOT 机制与 WAL 异常速查

如果你遇到类似的 PG 磁盘 I/O 瓶颈，请按以下 4 步排查：

查 HOT 命中率 执行本文上方的 pg_stat_user_tables 检查 SQL，如果高频更新表（n_tup_upd 大）的 hot_ratio 低于 50%，必有隐患。
查索引命中与浪费 通过 pg_stat_user_indexes 查询哪些索引从未被 Scan 过： sql SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND schemaname = 'public'; 没有任何查询命中的索引，如果还在被高频 Update 字段牵连，立刻 Drop。
查 WAL 生成速率 在 pg_stat_bgwriter 或系统级通过监控面板查看 WAL Write 速率。如果 WAL 写入量远超实际业务数据变动量，重点排查大面积 Update 打破 HOT 引发的 FPW 风暴。
查 fillfactor 配置 执行 SELECT relname, reloptions FROM pg_class WHERE relname = '你的表名';。如果高频 Update 且无索引覆盖的表 fillfactor 还是默认的空值（即 100），建议改至 85-90 之间。

标签： HOT

深入 PostgreSQL 陷阱排查：频繁更新索引列打破 HOT 机制引发的 WAL 写放大与 IOPS 耗尽实战

案发现场与指标佐证

为什么打破 HOT 机制是致命的？

破局与调优落地

排查清单：PostgreSQL HOT 机制与 WAL 异常速查