A PostgreSQL UPDATE can create a new physical row version without creating corresponding new entries in ordinary tuple-addressing indexes. This heap-only tuple optimization, commonly called HOT, applies when the replacement tuple remains on the same heap page and the update does not change values that disqualify HOT for the table’s indexes.
That distinction matters because PostgreSQL implements MVCC updates by retaining row versions rather than overwriting a tuple in place. Without HOT, an update can add work to both the heap and every index even when the indexed key values remain stable.
HOT keeps index references anchored to a chain
An ordinary index entry identifies a heap tuple by its tuple identifier. HOT allows that index reference to remain anchored at the original member of a version chain while newer versions occupy other line pointers on the same heap page.
Conceptually, the relationship can look like this:
index entry
|
v
heap tuple A
|
v
heap tuple B
|
v
heap tuple CThe later tuples are heap-only versions. An index scan reaches the chain through the existing index entry and follows the on-page version links as required by MVCC visibility.
Keeping the chain on one page is essential. A tuple identifier contains a block number and an item position, and HOT relies on page-local redirection and chain traversal. If the new version cannot fit on the original page, the update cannot use the same HOT mechanism.
Indexed attributes set the eligibility boundary
HOT is available when an update does not modify attributes that require new entries in non-summarizing indexes. This boundary includes more than obvious single-column B-tree keys. Expressions and predicates can make additional table attributes relevant to index maintenance.
BRIN has different semantics because it summarizes block ranges rather than storing a tuple-addressing entry for every indexed row. Current PostgreSQL releases treat summarizing indexes separately when deciding whether an update can remain HOT, although their summaries can still require maintenance.
The practical boundary is therefore tied to the table’s complete index definitions, not merely to columns that appear as simple index keys.
A schema such as:
CREATE TABLE sessions (
id bigint PRIMARY KEY,
account_id bigint NOT NULL,
state text NOT NULL,
last_seen_at timestamptz NOT NULL
);
CREATE INDEX sessions_account_id_idx
ON sessions (account_id);can admit HOT updates to last_seen_at when page space and the other eligibility conditions hold. Adding an index that references last_seen_at changes that boundary. The same logical update can then require normal index maintenance.
Free space determines whether an eligible update stays HOT
Attribute eligibility alone is insufficient. PostgreSQL must also place the new row version on the same heap page.
A page filled tightly by inserts may have too little room for another version of an existing row. In that case, a logically suitable update can become non-HOT because the replacement tuple has to move to another page.
Table fillfactor influences this probability. A lower fillfactor leaves space unused during initial page filling, reserving capacity that later updates can consume. The trade-off is physical density: more heap pages may be needed for the same initial row set.
This makes HOT rate a workload property rather than a fixed schema property. Tuple width, update frequency, page occupancy, index definitions, and pruning activity all affect the observed result.
HOT reduces index write amplification
A non-HOT update may create a fresh index tuple in each affected index path even when many key values are logically unchanged. Those extra versions consume index space, generate WAL, dirty index pages, and eventually become cleanup work.
HOT removes that index insertion path for eligible updates. The heap still receives a new MVCC version, so the optimization does not turn an update into an in-place overwrite. Its main effect is to decouple eligible row-version creation from per-row index entry creation.
The difference becomes material on tables with several indexes and frequently changing non-indexed state. A small status timestamp or counter update can otherwise amplify into writes across multiple index structures.
Page pruning shortens obsolete HOT chains
HOT chains do not need to grow indefinitely. Once older row versions are no longer visible to any relevant snapshot, PostgreSQL can prune obsolete members during normal page access and maintenance activity.
The page can retain a redirect from the index-referenced line pointer toward a surviving chain member while reclaiming storage from dead intermediate versions. This local cleanup is one reason HOT has effects beyond avoiding immediate index writes: obsolete heap versions in a HOT chain can often be removed without waiting for index entries to be deleted first.
Long-running snapshots can delay that cleanup because old versions may remain visible to active transactions. HOT reduces index churn, but it does not override MVCC visibility rules.
Statistics expose the realized HOT rate
PostgreSQL records update counters in statistics views such as pg_stat_user_tables. The n_tup_upd counter records updated rows, while n_tup_hot_upd records updates performed as HOT updates.
A ratio derived from those counters can show whether a workload is actually receiving the optimization:
SELECT
relname,
n_tup_upd,
n_tup_hot_upd,
CASE
WHEN n_tup_upd = 0 THEN 0
ELSE n_tup_hot_upd::numeric / n_tup_upd
END AS hot_ratio
FROM pg_stat_user_tables;The ratio is observational rather than a guarantee. A low value can result from indexed attribute changes, insufficient page space, or a workload whose tuple sizes and page occupancy leave little room for same-page versions.
HOT therefore sits at the intersection of MVCC, heap page layout, and index maintenance. Its eligibility is decided per update, and its benefit is concrete: when the required conditions hold, a new row version can remain reachable through existing tuple-addressing index entries instead of producing another set of index tuples.