· Development

When a PostgreSQL UPDATE needs no new index entries: HOT updates and fillfactor

PostgreSQL HOT (heap-only tuple) updates avoid new index entries when no indexed column changes and the page has room. Covers both rules, fillfactor, and pg_stat_user_tables counters.

DevelopmentDatabasesPostgreSQL

In PostgreSQL, an UPDATE does not change a row in place. Because of multiversion concurrency control (MVCC), it adds a new row version to the table, and every index pointing at that row may need a new entry as well. So bumping a single number can also mean extra index writes. Under certain conditions PostgreSQL skips those new index entries (summarizing indexes such as BRIN may still need updating, an exception covered below), and that optimization is called HOT (heap-only tuples). This article follows the docs for PostgreSQL 18 (minor release 18.6), the newest stable release as of October 12, 2026.

The two conditions for HOT

The docs list two cases in which HOT is possible:

  1. The update does not modify any column referenced by the table’s indexes. Summarizing indexes don’t count here, and in the core distribution BRIN is the only summarizing index method.
  2. The page holding the old row has enough free space for the updated row.

Take this table:

CREATE TABLE sessions (
    id           bigint PRIMARY KEY,
    user_id      bigint NOT NULL,
    last_seen_at timestamptz NOT NULL,
    hits         integer NOT NULL DEFAULT 0
);
CREATE INDEX sessions_user_id_idx ON sessions (user_id);

UPDATE sessions
SET hits = hits + 1, last_seen_at = now()
WHERE id = 42;

The only indexes are on id (the primary key) and user_id, and this UPDATE changes only hits and last_seen_at. It meets the first condition, so if the page has room it will be a HOT update.

Add CREATE INDEX ON sessions (last_seen_at); and the same UPDATE now modifies a column an index references, so it can no longer be HOT. One index added to speed up reads, if it covers a column that changes often, can change the cost of every update.

Note that the docs say “columns referenced by the table’s indexes”, not “index keys”. A column used inside an expression index, or in the WHERE clause of a partial index, is still referenced by that index. For example, CREATE INDEX ON sessions (user_id) WHERE hits > 100; has user_id as its key but references hits in its predicate. Even if the application treats a column as “just a counter, not a search key”, updating it fails the first condition as long as it appears anywhere in an index definition.

A BRIN index on last_seen_at, on the other hand, is a summarizing index and does not break the first condition. The docs do note that summarizing indexes may still need to be updated during a HOT update.

What HOT saves

When both conditions hold, the docs describe two benefits.

First, no new index entries are needed to represent the updated row. In the example above, each UPDATE no longer writes entries to the primary key index and to sessions_user_id_idx.

Second, when a row is updated several times, every version except the oldest and the newest can be removed completely during normal operation, without waiting for periodic VACUUM. The docs say explicitly that this includes SELECTs, so a read query can end up tidying a page.

The page layout explains why this works. Tables and indexes are stored as arrays of fixed-size pages, usually 8 kB, and each page has an array of 4-byte item identifiers pointing at the actual rows. A row pointer in PostgreSQL (a CTID) is a page number plus the index of an item identifier, and since an item identifier is never moved until it is freed, it can be referenced long-term. With HOT, indexes always point at the item identifier of the original row version. On cleanup, that version’s data is removed and its item identifier becomes a redirect to the oldest version that may still be visible to some concurrent transaction. Intermediate versions that nobody can see are removed entirely, and their item identifiers become available for reuse.

This also suggests why the same-page condition exists. The index only knows the original item identifier, so the new version has to be on the same page for it to be reachable without changing the index.

Leaving room on the page with fillfactor

You can make the second condition more likely with the table’s fillfactor. According to the CREATE TABLE docs, a table’s fillfactor is a percentage between 10 and 100, and the default is 100, which means complete packing. With a smaller value, INSERT fills pages only to that percentage and keeps the rest for updates to rows on that page. That gives UPDATE a chance to put the new copy of a row on the same page as the original, which the docs describe as more efficient than using a different page and as making HOT updates more likely.

For a new table, set it in the WITH clause. The 70 below is the value used in the docs’ example, not a recommendation.

CREATE TABLE sessions (
    id           bigint PRIMARY KEY,
    user_id      bigint NOT NULL,
    last_seen_at timestamptz NOT NULL,
    hits         integer NOT NULL DEFAULT 0
) WITH (fillfactor = 70);

For an existing table, use ALTER TABLE ... SET:

ALTER TABLE sessions SET (fillfactor = 70);

The ALTER TABLE docs say this command does not modify the table contents immediately, and depending on the parameter you might need to rewrite the table to get the effect you want. The ways they list are VACUUM FULL, CLUSTER, or a form of ALTER TABLE that forces a rewrite. Each of these rewrites the whole table, so check its locking behavior on its own docs page before running it on a live table. Changing fillfactor itself takes a SHARE UPDATE EXCLUSIVE lock.

The docs don’t give a number. All they say is the direction: complete packing is best for a table whose rows are never updated, and smaller fillfactors are appropriate for heavily updated tables. By definition, filling pages less means the same number of rows needs more pages. And HOT still happens without lowering fillfactor, because new rows naturally migrate to new pages and leave existing pages with free space.

Checking the HOT ratio

The docs point to the pg_stat_all_tables view for monitoring HOT and non-HOT updates. For user tables only, pg_stat_user_tables has the same columns. Three of them matter here:

  • n_tup_upd: total rows updated. It includes the updates counted in the next two columns plus the remaining non-HOT updates.
  • n_tup_hot_upd: rows HOT updated, meaning no successor version was needed in the indexes.
  • n_tup_newpage_upd: rows whose successor version went onto a new heap page. These are always non-HOT.
SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       n_tup_newpage_upd,
       n_tup_upd - n_tup_hot_upd - n_tup_newpage_upd AS other_non_hot,
       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
LIMIT 20;

other_non_hot is derived from the docs’ definitions: updates that stayed on the same page but still weren’t HOT. Going by the two conditions, a large value here suggests many updates change a column some index references, while a large n_tup_newpage_upd hints that pages ran out of room. Updates that moved to a new page can also include ones that changed an indexed column, though, so to pin down the cause you need to look at the actual UPDATE statements and the index definitions together.

A few things to keep in mind when reading these numbers:

  • They are cumulative: totals since the last reset. To see current behavior, query twice some time apart and compare the difference.
  • Statistics survive a clean shutdown, but all counters are reset when the server starts after an immediate shutdown or a crash, or from a base backup or point-in-time recovery.
  • They are not live. Each server process flushes its statistics to shared memory just before going idle, at most once per second by default (a build-time setting), so a transaction still in progress is not counted yet. In the default configuration, values you read inside a transaction are cached until that transaction ends.
  • Table and index access statistics are collected only while track_counts is on.

The docs set no target HOT percentage. If updating an indexed column is simply what the application does on every write, a low ratio is normal for that table. Treat the number as a starting point for asking why a table’s updates touch its indexes, not as a goal.

Results don’t change

HOT is a storage-level optimization that cuts the cost of updates. It is not a correctness setting. HOT or not, MVCC still creates a new row version, and which version each statement sees is still decided by the isolation level, as covered in READ COMMITTED and snapshots. So when you decide whether to change fillfactor or drop an index, the questions are about storage, write cost, and which reads that index actually helps, not about query results.

Docs used

Links and content checked on October 12, 2026.

Back to the blog