从n_tup_hot_upd看PostgreSQL索引膨胀：HOT机制深度拆解与调优实践

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

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都需要：

1. 在堆中插入新行版本

2. 在所有索引中为每个新行版本插入索引条目

3. 旧行版本和旧索引条目等到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只对新建表或执行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 不愁

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~