A PostgreSQL UPDATE creates a new tuple version rather than overwriting the old tuple in place. That MVCC behavior normally has a second cost: indexes need entries that lead scans to the new version. Heap-only tuple updates, usually called HOT updates, avoid that index work under a specific set of conditions.
HOT is not a separate update command or an optimizer choice exposed in SQL. It is a storage optimization selected while PostgreSQL updates a heap tuple. Its effect is concentrated in the relationship between heap pages and indexes: an existing index entry can remain useful across multiple row versions.
An index entry can anchor a chain of row versions
A conventional non-HOT update can require fresh index tuples for the new heap tuple. Even if an indexed value did not change, PostgreSQL cannot generally leave an old index entry pointing at an unrelated tuple location and expect it to represent the new version.
HOT changes the arrangement. When its conditions are satisfied, the new tuple version is placed on the same heap page as the old version and marked as a heap-only tuple. The old tuple links to the newer version. An index entry that reaches the original tuple location can therefore lead heap access into the version chain on that page.
The index does not need a separate entry for each HOT-created version. PostgreSQL can follow the chain and apply normal MVCC visibility rules to select the tuple version visible to the current snapshot.
This distinction matters for write amplification. Avoiding fresh entries means the update does not add another tuple to each ordinary per-tuple index merely to represent the new row version.
HOT eligibility depends on indexed attributes and page space
Two conditions govern the common HOT case.
First, the update must not change columns referenced by non-summarizing indexes. Index expressions and predicates also matter because their results or applicability can depend on changed attributes. PostgreSQL cannot safely preserve an index representation when an update can alter what that index needs to represent.
BRIN is different because it summarizes block ranges rather than pointing at individual heap tuples. Current PostgreSQL releases can permit HOT when only attributes associated with summarizing indexes change, although those summaries can still require maintenance.
Second, the heap page containing the old tuple must have enough free space for the new tuple version. A version placed on another page cannot participate in the same page-local HOT chain.
These conditions make HOT sensitive to both schema design and physical page state. An update to a non-indexed column is not sufficient by itself; a full page can still force a regular update path.
Fillfactor changes the amount of page space reserved for updates
A table’s fillfactor controls how densely PostgreSQL tries to pack pages during insertion. The default leaves less deliberately reserved room than a lower setting. Reducing the value can leave space for later tuple versions, increasing the chance that eligible updates remain on their original pages.
That does not make a lower fillfactor universally preferable. Reserving more free space increases the number of heap pages required for the same inserted data. Larger tables can require more page reads for scans and consume more cache capacity.
The useful connection is narrower: HOT requires same-page space, and fillfactor is one mechanism that can influence the availability of that space. Workloads dominated by updates to non-indexed attributes can therefore react differently to fillfactor changes than append-heavy tables.
Extra indexes can change update behavior
Adding an index changes more than read plans. It can also change which updates qualify for HOT.
Suppose a table stores an account identifier, a status value, and a frequently changed timestamp:
CREATE TABLE sessions (
id bigint PRIMARY KEY,
status text NOT NULL,
touched_at timestamptz NOT NULL
);An update that changes only touched_at can be HOT-eligible when page space is available because the primary-key index does not reference that column.
Adding an index on the timestamp changes the condition:
CREATE INDEX sessions_touched_at_idx ON sessions (touched_at);Updates to touched_at now affect an indexed attribute, so they no longer qualify for the ordinary HOT path. The new index may support a required query pattern, but its write-side effect includes more than the direct cost of maintaining that one index. Losing HOT can cause fresh entries to be written to the table’s other per-tuple indexes as well.
Included columns deserve the same attention. A column stored with an index through INCLUDE is still referenced by that index for HOT eligibility. Covering more queries can therefore alter the storage behavior of updates to those included values.
Page pruning shortens obsolete HOT chains
HOT chains are not intended to grow without cleanup. Once older versions can no longer be visible to active snapshots, PostgreSQL can prune obsolete members from a heap page.
Recent PostgreSQL documentation describes the original indexed line pointer as a stable anchor that can become a redirect to the oldest version that might still be visible. Dead intermediate versions can be removed and their line pointers made reusable. This page-local pruning reduces the amount of obsolete tuple data that remains between the index entry and a surviving version.
VACUUM still has broader responsibilities across the relation, but HOT allows some obsolete row-version cleanup to happen as part of normal page activity. The optimization therefore affects both index maintenance at update time and the shape of later heap cleanup.
Table statistics expose HOT activity
PostgreSQL records update counters in its statistics views. pg_stat_all_tables and its related views expose n_tup_upd for updates and n_tup_hot_upd for HOT updates.
The counters provide an observation point rather than a target ratio with a universal acceptable value. A table whose workload frequently changes indexed attributes can legitimately show few HOT updates. Another table can have HOT-eligible SQL but still show a lower count when pages lack room for new versions.
Schema changes can also shift the numbers. Adding an index, adding included columns, changing fillfactor, or changing which attributes an application updates can alter HOT eligibility without changing the logical result of the SQL statements.
HOT is therefore best treated as a physical consequence of update shape, index definitions, and heap-page capacity. It can remove substantial index maintenance from qualifying updates, but only while those storage conditions remain true.