Maintenance

HOT updates

An UPDATE creates a new row version and usually needs new index entries. If it does not change columns referenced by non-summarizing indexes and the new version fits on the same heap page, PostgreSQL can use a HOT update and avoid new index tuples.

In plain words

In PostgreSQL an UPDATE never changes a row in place. It writes a new version of the row and marks the old one as expired. Normally every index on the table then needs a new entry pointing to the new version, even indexes on columns that did not change. That is a lot of extra writing.

A HOT update (heap-only tuple) avoids new index tuples. It is possible when the update does not change columns referenced by non-summarizing indexes and the new version fits on the same heap page. Core BRIN is a summarizing index and does not by itself prevent HOT. Existing index entries continue to lead to the HOT chain on the heap page.

Later, any query that visits the page can tidy up: dead versions are removed and the slot becomes a short redirect. No VACUUM needed. Use the toggles to see when an update can be HOT and when it cannot.

For DBAs and operators

Conditions for HOT

No column used by any index (key columns, INCLUDE columns, expressions, partial-index predicates) changes value. Since PostgreSQL 16, columns used only by summarizing indexes (BRIN) do not block HOT.
The new version fits in free space on the same heap page. If it does not, the update goes to another page and is counted in n_tup_newpage_upd (PG16+).

How the chain works

The old tuple gets the HEAP_HOT_UPDATED flag and its t_ctid points to the new tuple, which carries HEAP_ONLY_TUPLE. Index entries still reference the root line pointer; an index scan follows the chain within the page until it finds the version visible to its snapshot.

SELECT lp, lp_flags, t_ctid,
       (t_infomask2 & 16384) <> 0 AS hot_updated,
       (t_infomask2 & 32768) <> 0 AS heap_only
  FROM heap_page_items(get_raw_page('t', 0));
-- lp_flags: 1 normal, 2 redirect, 3 dead, 0 unused

Page pruning

When a backend reads a page and finds it nearly full (free space below the larger of the fillfactor reserve and 10% of the page) with prunable old versions, it prunes on the spot: dead heap-only tuples are removed, the root line pointer becomes a redirect to the first live version, and the page is defragmented. This needs a cleanup lock but no VACUUM and no index work. Only VACUUM can reclaim the line pointers of non-HOT dead tuples, because index entries still point at them.

Measuring and tuning

SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
  FROM pg_stat_user_tables ORDER BY n_tup_upd DESC LIMIT 20;

Low hot_pct with high n_tup_newpage_upd: pages are full. Lower the table's fillfactor (e.g. ALTER TABLE t SET (fillfactor = 80); it only affects future inserts, so pages that are already full stay full until the table is rewritten).
Low hot_pct with few new-page updates: an index covers a column you update often (an updated_at index is a classic). Consider dropping or changing that index.
Each extra index on an often-updated column multiplies WAL, index bloat and VACUUM work.

Long-running transactions hold back the pruning horizon too: old versions cannot be pruned while someone might still need them, so HOT chains grow and pages fill up.

For developers: inside the source

Where it lives

README.HOT: the design (chains, redirects, pruning vs defragmentation, why indexes must not change).
heap_update() decides; heap_hot_search_buffer() follows chains for index scans.
pruneheap.c: heap_page_prune_opt(), heap_page_prune_and_freeze(), heap_prune_chain(), heap_page_prune_execute(), heap_get_root_tuples().
execIndexing.c: ExecInsertIndexTuples() with onlySummarizing.

The decision in heap_update()

It asks the relcache for RelationGetIndexAttrBitmap() with INDEX_ATTR_BITMAP_HOT_BLOCKING and INDEX_ATTR_BITMAP_SUMMARIZED, and computes modified_attrs with HeapDetermineColumnsInfo() (binary comparison of old and new values). After choosing the target buffer:

if (newbuf == buffer)
{
    if (!bms_overlap(modified_attrs, hot_attrs))
    {
        use_hot_update = true;
        if (bms_overlap(modified_attrs, sum_attrs))
            summarized_update = true;
    }
}

A HOT update sets HeapTupleSetHotUpdated() on the old version and HeapTupleSetHeapOnly() on the new one (HEAP_HOT_UPDATED, HEAP_ONLY_TUPLE in t_infomask2), Every update, HOT or not, also calls PageSetPrunable(), which records the XID in pd_prune_xid. The result goes back through the TU_UpdateIndexes out-parameter: TU_None, TU_Summarizing (only BRIN-style indexes get new entries) or TU_All. pgstat_count_heap_update(relation, use_hot_update, newbuf != buffer) feeds n_tup_hot_upd and n_tup_newpage_upd.

Pruning

heap_page_prune_opt() is called from heap scans, index fetches and bitmap heap scans. It returns early during recovery, when pd_prune_xid is unset or not yet removable (GlobalVisTestIsRemovableXid()), or when free space is above Max(fillfactor reserve, BLCKSZ / 10). It only prunes if ConditionalLockBufferForCleanup() succeeds, so it never waits. The result is one WAL record from log_heap_prune_and_freeze().

Observe and test

Use pageinspect (heap_page_items(), and heap_tuple_infomask_flags() to decode HEAP_HOT_UPDATED/HEAP_ONLY_TUPLE) and pg_walinspect to see the PRUNE_ON_ACCESS records (rmgr Heap2). Regression tests: stats.sql checks that updating a BRIN-indexed column stays HOT (table brin_hot, pg_stat_get_tuples_hot_updated()); isolation spec predicate-lock-hot-tuple covers SSI with HOT chains; amcheck's verify_heapam() validates HOT chains.

Parameters that shape this

autovacuum autovacuum_vacuum_scale_factor vacuum_buffer_usage_limit