PostgreSQL13高更新表FILLFACTOR调优与HOT更新效果问题咨询

认知纠正 #

你对HOT更新的前提判断是正确的：PostgreSQL执行UPDATE时会校验索引列的实际值是否发生变更，即使SET语句中写了索引列，只要最终值和原值一致，就不会触发索引修改，满足HOT更新的前置要求。

你当前将高更新表FILLFACTOR设为95%属于明显配置过高，这是HOT占比低的核心原因：PostgreSQL默认数据页大小为8KB，95%的FILLFACTOR意味着每个页仅预留约400字节的空闲空间，只要单条行数据宽度超过400字节，更新时就没有空间存放新版本行，自然无法触发HOT更新。 #

统计结果解读标准 #

你用的HOT占比统计逻辑是合理的，仅需补充一个过滤条件：排除n_tup_upd < 1000的表，避免更新量过小的表统计结果失真。核心指标hot_by_total（HOT更新占总更新比例）的参考阈值如下：

• 高UPDATE频率的业务表，理想值为80%以上
• 占比低于50%时说明配置不合理，已经产生大量不必要的索引写入和表膨胀
• 你的测试结果符合底层逻辑：FILLFACTOR越低，单页预留空闲空间越多，可容纳的行更新版本数量越多，HOT占比自然越高，但同时空间浪费也会增加，需要在性能和空间成本之间做平衡

FILLFACTOR科学调优步骤 #

1. 先确认目标表的平均行宽：执行SELECT relname, avg_width FROM pg_stats WHERE tablename = '目标表名';，基于行宽计算初始FILLFACTOR：比如单条行宽1KB，要预留2KB空间存放更新版本，8KB数据页的FILLFACTOR初始值即可设为75。
2. 通用建议：无大字段的高更新表初始值设为70~80，每秒单表更新量超千次的极端高频更新表可以降到60，最低不要低于50，否则空间浪费会超过性能收益。
3. 调整后验证：FILLFACTOR修改后需要执行VACUUM FULL 表名或者重建表才能生效，运行24小时后再统计hot_by_total，如果低于80%就再下调5个点的FILLFACTOR，直到占比达标即可。
4. 注意不要修改全局默认FILLFACTOR，仅针对高更新的业务表单独配置，只读、低更新表保持默认100即可。

三者优化关系总结 #

核心逻辑链：合理的FILLFACTOR预留足够页内空闲空间 → UPDATE操作可在同页内写入新版本行 → 触发HOT更新 → n_tup_hot_upd数值升高，hot_by_total占比提升 → 减少索引写入、降低表膨胀、提升更新性能
优化目标就是在业务可接受的空间占用范围内，尽可能把hot_by_total提升到80%以上。 #

自定义查询优化建议 #

你可以把FILLFACTOR查询逻辑和HOT统计逻辑合并，不需要单独调用自定义函数查询，简化后的统计语句更高效直观：

SELECT 
  t.relname,
  coalesce(substring(reloptions::text, 'fillfactor=(\d+)')::int, 100) as fillfactor,
  s.n_tup_upd as total_update_count,
  s.n_tup_hot_upd as hot_update_count,
  ROUND(coalesce(s.n_tup_hot_upd::numeric / nullif(s.n_tup_upd,0),0) * 100,2) as hot_ratio_percent
FROM pg_stat_user_tables s
JOIN pg_class t ON s.relid = t.oid
WHERE s.n_tup_upd > 1000
ORDER BY hot_ratio_percent ASC;

如果要保留table_get_options自定义函数，可以增加入参校验逻辑，避免传入非表类OID时返回报错，同时增加缓存逻辑降低重复查询的开销。

内容的提问来源于stack exchange，提问作者Morris de Oryx