An UPDATE in PostgreSQL creates a new row version. That MVCC behavior can imply fresh index entries even when an application changes only a non-indexed attribute. Heap-only tuple updates, usually called HOT updates, provide a narrower path: under specific conditions, PostgreSQL can link the new row version on the same heap page and keep existing index entries in place.

The optimization reduces index maintenance for eligible updates. Its boundary is physical as well as logical. Unchanged indexed values are not enough; the heap page must also have room for the new tuple version.

HOT keeps the index pointer anchored

A regular index entry identifies a heap tuple through a tuple identifier. With a HOT chain, an index entry can remain anchored at the chain root while PostgreSQL follows links among row versions on the same heap page.

This arrangement matters because an update normally creates a new heap tuple. Without HOT, indexes that need tuple-level entries generally receive entries for the new row version. HOT avoids that work when its eligibility rules are satisfied.

Consider a table with an index on account_id:

CREATE TABLE account_state (
    account_id bigint PRIMARY KEY,
    status text NOT NULL,
    note text
);

UPDATE account_state
SET note = 'reviewed'
WHERE account_id = 42;

The update changes note, not account_id. If the new tuple version fits on the same heap page, PostgreSQL can use a HOT update. The primary-key index does not need a fresh entry for that row version.

The same statement is not guaranteed to be HOT on every execution. Available page space is part of the decision.

Indexed attributes define an eligibility boundary

HOT depends on whether modified attributes participate in indexes that require tuple-level maintenance. In current PostgreSQL releases, summarizing indexes are treated differently; BRIN is the summarizing index method provided by core PostgreSQL.

For ordinary B-tree indexes, changing an indexed attribute prevents the classic HOT path. An expression index also makes its referenced attributes relevant to update eligibility. The database must preserve correct index semantics for every index attached to the table.

This makes index design part of update cost. Adding an index can affect more than reads: it can change which updates qualify for HOT and can add index writes for row versions that previously avoided them.

A table with several indexes can therefore see different behavior after a schema change even if the application sends identical UPDATE statements.

Page space is a second condition

PostgreSQL stores HOT-linked row versions on one heap page. If that page cannot hold the new tuple version, the update must place it elsewhere, so the same-page HOT chain cannot be extended.

Table fillfactor can reserve space on heap pages during insertion. A value below 100 leaves more room for later row versions, increasing the chance that eligible updates remain on the original page.

For example:

CREATE TABLE session_state (
    session_id uuid PRIMARY KEY,
    touched_at timestamptz NOT NULL,
    payload jsonb
) WITH (fillfactor = 85);

A lower fill factor is not free capacity in an abstract sense. It means pages are packed less densely, so the table can occupy more pages. That can affect scan volume and cache residency. The useful setting depends on row size, update frequency, access patterns, and the amount of same-page space actually needed by new versions.

Changing fillfactor also does not rearrange existing pages immediately. Its effect is tied to subsequent storage activity.

HOT chains reduce index churn

The direct saving from HOT is avoiding new tuple-level index entries for the eligible row version. That can also reduce the amount of obsolete index state produced by repeated updates.

Heap cleanup has another useful property. PostgreSQL can prune dead intermediate versions in a HOT chain during normal page access when visibility rules permit it. Current PostgreSQL documentation describes the chain root as the location retained by indexes while page item redirects and chain links allow obsolete intermediate versions to be removed.

This does not eliminate vacuum. Vacuum still handles broader cleanup, visibility-map maintenance, transaction ID concerns, and dead tuples that cannot be removed by page-local pruning at a given moment. HOT narrows some update and cleanup work; it does not replace PostgreSQL’s maintenance machinery.

Statistics expose HOT activity

pg_stat_all_tables reports update counters that make HOT activity visible. Two useful columns are n_tup_upd, the number of updated rows, and n_tup_hot_upd, the subset represented by HOT updates.

A compact inspection query is:

SELECT
    relname,
    n_tup_upd,
    n_tup_hot_upd
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY n_tup_upd DESC;

These counters describe accumulated activity since the relevant statistics reset. They do not prove that a particular statement used HOT, and a ratio alone does not establish an optimal fillfactor. They are evidence about observed update behavior that can be combined with schema details and workload measurements.

Recent PostgreSQL versions also expose n_tup_newpage_upd, which counts row updates whose new tuple version was placed on a different heap page. That counter can add context when same-page placement is a concern.

Index additions can alter write behavior

An index created for a read path can have consequences for unrelated-looking updates if those updates modify attributes used by that index. The effect is especially easy to miss with expression indexes, where the indexed key is derived from one or more table attributes.

For a frequently updated table, index review can therefore include the update paths that touch each indexed attribute. An index that earns its storage and read cost may still be appropriate, but its effect on HOT eligibility belongs to the same accounting.

BRIN is a special case in current PostgreSQL because it summarizes heap ranges rather than maintaining the same per-tuple structure as B-tree. PostgreSQL’s HOT eligibility rules exclude summarizing indexes from the indexed-attribute restriction, although a summary can still require maintenance.

HOT is a property of a specific row update

It is tempting to describe a table as using HOT or not using HOT, but eligibility is decided for individual updates. One row may have enough free space on its page while another does not. One statement may modify only non-indexed attributes while another touches an indexed value.

That local character is the useful boundary. HOT is not a query-planner choice and not a persistent table mode. It is a heap update optimization that becomes available when index semantics permit reuse and the physical page can accept the next row version.

For update-heavy relations, those two conditions connect schema design with page layout. Index definitions determine whether reuse is valid; available heap space determines whether the new version can stay in the chain. Treating both as part of the write path gives HOT statistics a concrete interpretation instead of turning them into a target by themselves.

PostgreSQL Database MVCC Indexes