HOT Update Chain Degradation

Scenario

A session management table receives thousands of UPDATE operations per second, updating a last_seen timestamp. The DBA notices that pg_stat_user_tables shows n_tup_hot_upd is nearly zero while n_tup_upd is very high. Index scan performance degrades over time. Autovacuum runs frequently but the table keeps bloating. The table has an index on last_seen, which is the frequently-updated column.

How to Identify

Conditions:

• pg_stat_user_tables.n_tup_hot_upd is very low relative to n_tup_upd
• HOT ratio (n_tup_hot_upd / n_tup_upd) is below 60% — target should be >80%
• Table has one or more indexes on frequently-updated columns
• FILLFACTOR is at default (100) — no headroom for in-page updates
• Index bloat growing despite autovacuum (index entries accumulate for dead tuples that could not use HOT)

Analysis Steps

-- 1. Check HOT update ratio for all user tables
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_pct,
       n_dead_tup,
       last_autovacuum
FROM   pg_stat_user_tables
WHERE  n_tup_upd > 0
ORDER  BY hot_ratio_pct ASC NULLS LAST;

-- 2. Check FILLFACTOR on the table
SELECT relname, reloptions
FROM   pg_class
WHERE  relname = 'sessions';
-- If reloptions is NULL or does not contain fillfactor=XX, default is 100 (no free space)

-- 3. Identify indexes on the frequently-updated column
SELECT i.indexrelname, ix.indkey, a.attname
FROM   pg_index ix
JOIN   pg_class  c ON c.oid = ix.indrelid
JOIN   pg_class  i ON i.oid = ix.indexrelid
JOIN   pg_attribute a ON a.attrelid = c.oid
   AND a.attnum = ANY(ix.indkey)
WHERE  c.relname = 'sessions'
ORDER  BY i.indexrelname, a.attname;

-- 4. Use pgstattuple to measure actual bloat (requires extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('sessions');
-- Look at: dead_tuple_percent, free_percent

-- 5. Verify HOT eligibility conditions:
-- HOT requires: (a) updated column has NO index, AND (b) new tuple fits same page
-- If either condition fails, HOT is not used

Pitfalls

• HOT updates only work when two conditions are simultaneously true: (1) the updated column has NO index on it, AND (2) the updated tuple fits on the same heap page (requires FILLFACTOR headroom). Violating either condition silently disables HOT for that update.
• Adding any index on a frequently-updated column — even a partial index — kills HOT for all updates that would otherwise qualify, causing index bloat even if the indexed value doesn’t change
• Default FILLFACTOR = 100 leaves no free space on pages; when a page is full, the new tuple must go to a different page, breaking the HOT chain requirement
• Increasing FILLFACTOR (e.g., to 70) requires a table rewrite via ALTER TABLE ... SET (fillfactor = 70) followed by VACUUM FULL or CLUSTER to take effect on existing pages; autovacuum alone will not reorganize pages
• Do not confuse HOT ratio degradation with normal index bloat from deletes; these have different root causes

Resolution Approach

To restore HOT update efficiency: