大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
PostgreSQL有个很反直觉的现象：一张表的数据量没怎么涨，但索引文件却从几百MB膨胀到了几个GB。磁盘空间被吃掉了，查询反而越来越慢。
这个问题在频繁更新的表上尤其严重。根因就在HOT更新——PostgreSQL针对MVCC更新开销的核心优化机制。理解HOT，就能理解为什么有些表的索引会“偷偷”膨胀。
先搞懂几个词：
HOT（Heap-Only Tuple）：PostgreSQL的更新优化机制。当UPDATE不修改任何被索引引用的列，且新行版本能放在与旧行相同的堆页内时，可以跳过索引更新，只在堆内建立新旧行之间的指针。索引不需要新增条目，旧行版本也可以在日常操作（包括SELECT）中直接清理，不需要等VACUUM。
fillfactor：表的填充因子，控制建表或重建时每个数据页预留多少空闲空间。默认100（不预留）。对频繁更新的表，降低fillfactor可以为新行版本预留空间，提高HOT更新的概率。
n_tup_upd / n_tup_hot_upd：pg_stat_all_tables中的两个统计字段，分别记录总更新次数和其中HOT更新的次数。两者的比值直接反映HOT是否生效。
一、HOT的两个前提条件
HOT更新要生效，必须同时满足两个条件：
条件一：UPDATE不修改任何被索引引用的列。包括普通B-tree索引、表达式索引和部分索引。如果UPDATE修改了任何一个索引列，该索引就必须插入新条目，HOT失效。
条件二：新行版本能放在与旧行相同的堆页内。如果旧行所在的数据页没有足够的空闲空间容纳新行，新行会迁移到其他页，HOT也失效。
两个条件同时满足，HOT才会生效。
二、没有HOT时会发生什么
理解HOT失效的代价，才能理解为什么这个问题值得重视。
没有HOT时，每次UPDATE都需要：
在堆中插入新行版本
在所有索引中为每个新行版本插入索引条目
旧行版本和旧索引条目等到VACUUM时才被清理
如果一张表有5个索引，一次UPDATE就要在5个索引中各插入一条新记录。频繁更新的表，索引文件会以远超数据文件的速率膨胀。
更关键的是，索引膨胀后，查询需要扫描更多的索引页才能定位到目标数据，I/O成本上升，缓存命中率下降，形成恶性循环。
三、三个最常见的HOT失效场景
场景一：在频繁更新的列上建了索引
CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, status VARCHAR(20), updated_at TIMESTAMP ); CREATE INDEX idx_status ON orders(status);
每次UPDATE orders SET status = 'SHIPPED'都会修改idx_status索引列，HOT失效。即使status值从'SHIPPED'改成'SHIPPED'（语义上无变化），索引仍然需要插入新条目。
这是最容易被忽略的陷阱。很多人以为“只改了一个索引列，其他索引不受影响”，但实际上PostgreSQL的判断逻辑是“只要UPDATE语句中SET子句引用了索引列，HOT就失效”，而不是“只有值真正变化时才失效”。
场景二：表达式索引和部分索引
CREATE INDEX idx_lower_email ON users(lower(email));
即UPDATE没有修改email列，但如果UPDATE语句的SET子句涉及了email列（即使值没变），表达式索引的HOT也会失效。
场景三：fillfactor=100（默认）
CREATE TABLE orders (...) WITH (fillfactor = 100);
fillfactor=100意味着每个数据页完全填满，没有为新行版本预留任何空间。即使UPDATE不修改索引列，新行也找不到位置，只能迁移到新页，HOT失效。
fillfactor 90意味着每页预留10%空间，fillfactor 70意味着预留30%空间给未来的更新。fillfactor越低，HOT更新的概率越高。
四、fillfactor调优策略
fillfactor是控制HOT概率的唯一直接杠杆。设置策略取决于表的更新频率和行大小。
| 场景 | fillfactor | 预留空间 | 说明 |
|---|---|---|---|
| 只读或极少更新 | 100 | 0% | 默认值，最大化存储密度 |
| 偶尔更新 | 90 | 10% | 轻微预留，几乎不影响存储密度 |
| 频繁更新 | 70-80 | 20-30% | 显著提高HOT概率 |
| 极频繁更新 | 50-70 | 30-50% | 更新频率极高的表 |
注意：fillfactor只对新建表或执行VACUUM FULL/CLUSTER后的表生效。对已有表修改fillfactor后，需要重建表才能生效：
ALTER TABLE orders SET (fillfactor = 80); VACUUM FULL orders;
五、监控HOT是否生效
SELECT schemaname, relname, n_tup_upd, n_tup_hot_upd, CASE WHEN n_tup_upd > 0 THEN ROUND(n_tup_hot_upd::NUMERIC / n_tup_upd * 100, 2) ELSE NULL END AS hot_pct FROM pg_stat_all_tables WHERE relname IN ('orders', 'users') ORDER BY n_tup_upd DESC;判断标准：间隔一段时间（如1小时）对比两个快照中n_tup_upd和n_tup_hot_upd的增长量。如果两者增长量接近，说明几乎所有UPDATE都是HOT的；如果n_tup_hot_upd增长量远小于n_tup_upd，说明HOT没有生效。
一个邮件列表中的真实数据：某表n_tup_upd = 5,997,455,764，n_tup_hot_upd = 3,752,865,557，HOT比例约62.6%。说明约37%的更新没有走HOT，索引膨胀的速度远超预期。
六、小结
HOT更新是PostgreSQL控制索引膨胀的核心机制。当UPDATE不修改索引列且新行能放在同一页时，索引不需要新增条目，膨胀速度大幅降低。在频繁更新的列上建索引、使用表达式索引、fillfactor=100——这三个场景都会破坏HOT。fillfactor是唯一的直接调优杠杆，频繁更新的表应设为70-80。监控n_tup_hot_upd / n_tup_upd的比值，是判断HOT是否生效的最直接方法。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
