HOT Updates: How PostgreSQL Avoids Index Writes

The one thing to understand first

Because of MVCC, every UPDATE writes a new tuple version. Naively, that new version needs a new entry in every index on the table — even indexes on columns that did not change. On a wide, heavily-indexed, update-heavy table, that index maintenance dominates write cost and generates index bloat. HOT (Heap-Only Tuple) updates exist to avoid it.

If an update touches no indexed column and the new version fits on the same page, Postgres skips all index writes — so HOT eligibility is the single biggest lever on write cost for hot tables. Everything in this lesson is about engineering your schema so updates stay HOT-eligible.

The key insight

If an update changes no indexed column, then every index entry would point to a row whose indexed values are unchanged. So PostgreSQL can skip creating new index entries entirely — provided it can still find the new version starting from the old index pointers. HOT achieves this by keeping the update within a single heap page and chaining versions with line pointers.

How a HOT update works

In heap_update() (heapam.c), when the conditions are met:

• The new tuple is written on the same page as the old one (this requires free space — see fillfactor).
• The old tuple's line pointer becomes a redirect (LP_REDIRECT) pointing to the new tuple's line pointer.
• No new index entries are created. Existing index entries point at the original line pointer, which now redirects to the current version — forming a HOT chain.

An index scan lands on the root line pointer, follows the redirect, and walks the chain to the visible version. The HEAP_HOT_UPDATED and HEAP_ONLY_TUPLE infomask bits mark the chain.

The two conditions for HOT

• No indexed column changed. If you update only last_seen and there is no index on it, HOT is eligible. Update an indexed column and HOT is impossible for that change.
• There is room on the page. The new version must fit on the same page. A full page forces a normal, off-page update with full index maintenance.

fillfactor: reserving room

By default heap pages fill to ~100%, leaving no room for same-page updates. For update-heavy tables, lower fillfactor so each page keeps slack for future HOT updates:

ALTER TABLE sessions SET (fillfactor = 80);
-- Rewrites/new pages keep 20% free, enabling HOT updates.
VACUUM FULL sessions;  -- to apply to existing pages (takes a lock)

Page pruning keeps chains short

HOT chains would grow without bound, but PostgreSQL performs opportunistic page pruning: on a normal page access (even a SELECT), if a page has dead HOT tuples, it can prune the chain — turning dead intermediate versions into reusable space and collapsing redirects — without touching indexes and without a full vacuum. This is why a busy table can stay healthy between vacuums.

Layer 3 — Watch it happen on your own database

SELECT relname,
       n_tup_upd            AS total_updates,
       n_tup_hot_upd        AS hot_updates,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd,0), 1) AS hot_pct
FROM   pg_stat_user_tables
WHERE  n_tup_upd > 0
ORDER  BY n_tup_upd DESC;

relname | total_updates | hot_updates | hot_pct 
---------+---------------+-------------+---------
 demo    |             1 |           1 |   100.0
(1 row)

This is the one number that tells you whether HOT is working: the percentage of updates that stayed on-page. Run it against your busiest tables. A high hot_pct means cheap, index-free updates; a low one on a high-update table means most updates are doing full index maintenance and generating bloat. Lower fillfactor, re-run your workload, and watch the percentage climb.

Layer 4 — The levers this hands you

A low hot_pct on a high-update table is an optimisation opportunity. The levers, in order of impact:

• Don't index volatile columns. An index on a frequently-updated column makes every change to it ineligible for HOT — the most common self-inflicted cause of low hot_pct.
• Drop unused indexes on update-heavy tables; each one is a tax on every off-page update.
• **Lower fillfactor** (e.g. 80) so pages keep slack for same-page updates; apply to existing data with a rewrite.
• Let page pruning work — it keeps HOT chains short on ordinary reads, so a busy table stays healthy between vacuums.

Layer 5 — What an Oracle DBA should expect vs what they get

HOT has no direct Oracle equivalent because the two engines store row versions completely differently:

• Oracle updates in place; Postgres versions in the heap. Oracle modifies the row and pushes the old image into UNDO, so an unindexed-column update never needs index maintenance to begin with. Postgres must write a whole new tuple — HOT is the mechanism that recovers Oracle-like cheapness for that case.
• **fillfactor ≈ PCTFREE.** Both reserve in-block free space; in Oracle PCTFREE mainly avoids row migration/chaining, in Postgres it is specifically what enables HOT.
• No UNDO segment, no ORA-01555. Postgres keeps old versions inline and reclaims them via pruning/VACUUM, trading Oracle's "snapshot too old" for table bloat if vacuum falls behind.
• HOT percentage is a Postgres-only tuning knob. There is nothing to monitor in Oracle that corresponds to n_tup_hot_upd; managing it is a uniquely PostgreSQL skill.

Key takeaway

When an update changes no indexed column and fits on the same page, heap_update() writes the new version on-page, redirects the old line pointer, and creates zero index entries — a HOT update. Keep n_tup_hot_upd high by not indexing volatile columns, dropping unused indexes, and lowering fillfactor; opportunistic page pruning then keeps the chains short. Raising HOT percentage directly cuts write I/O, index bloat, and vacuum work.