PostgreSQL HOT Updates: Why Some UPDATEs Write to Every Index, Fillfactor, the Indexed-Column Trap and Measuring the HOT Ratio

An UPDATE is an insert plus a promise

PostgreSQL implements an UPDATE by writing a new version of the row and leaving the old one for vacuum, because concurrent transactions may still need to see it. With no further optimisation, every new version needs a new entry in every index on the table, since index entries point at physical row versions. A table with six indexes then pays six index insertions for an update that changed one integer, and later pays vacuum to remove six dead index entries as well as the dead heap tuple.

Heap-only tuples, HOT, avoid that. The PostgreSQL documentation states the two conditions: the update does not modify any column referenced by the table's indexes, not counting summarizing indexes such as BRIN, and there is enough free space on the page that holds the old row for the new one. When both hold, no new index entries are created. Indexes keep pointing at the original line pointer, which becomes a redirect to the chain of versions on the same page, and intermediate versions that nobody can see any more can be removed during normal operation, including SELECTs, without waiting for vacuum.

Condition one: the indexed-column trap

The first condition is about which columns change, not which columns the WHERE clause uses. A sessions table updated on every request with SET last_seen_at = now() gets HOT updates only if no index includes last_seen_at. Someone adds an index on it for a cleanup job that runs once a night, and from then on every request writes to every index on the table. Nothing fails. Write volume, WAL volume and vacuum work rise, and the cause is a migration from weeks ago.

The check is mechanical: list the columns each frequent UPDATE sets, list the columns each index references (including expression and partial-index predicates), and look for the overlap. If the overlap is an index used rarely, weigh it against the write amplification. Alternatives include a separate narrow table for the volatile column, a BRIN index where the access pattern allows it, or a periodic batch job that does not need the index at all.

Read each definition for the columns your hottest UPDATE statements set.

-- Columns referenced by each index, including expressions and predicates
SELECT c.relname AS index_name, pg_get_indexdef(i.indexrelid) AS definition
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'app.sessions'::regclass
ORDER BY 1;

Condition two: room on the page

The new version must fit on the same 8 kB page. A freshly loaded table with the default fillfactor of 100 packs pages full, so the first update of most rows finds no room and the new version goes to another page; that update is not HOT. The CREATE TABLE documentation describes fillfactor as a percentage from 10 to 100: inserts fill pages only to that percentage and the rest is reserved for updates of rows on that page, which makes HOT updates more likely.

Pruning helps too: once old versions on a page are dead to every transaction, a later access can reclaim their space for new versions, so a page with some headroom can absorb update after update. A worked approximation shows the trade. With rows of about 200 bytes including the tuple header and line pointer, a full page holds roughly 40 rows. At fillfactor 80 it holds roughly 32 and keeps about 1.7 kB free, room for about eight new versions before pruning has to make space. The table grows by a quarter for the same rows; in exchange, updates that change no indexed column stay on their page. These are layout estimates, not measurements; the statistics below tell you what actually happens.

Measuring the HOT ratio

The view pg_stat_user_tables (part of pg_stat_all_tables) counts total updates, HOT updates and, since PostgreSQL 16, updates whose new version went to a new page. The last one separates the two failure causes: high n_tup_newpage_upd means the page was full; non-HOT updates that are not new-page updates changed an indexed column.

Counters are cumulative since the last statistics reset; compare two snapshots taken an hour apart.

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,
       n_tup_upd - n_tup_hot_upd - n_tup_newpage_upd          AS same_page_non_hot
FROM pg_stat_user_tables
WHERE n_tup_upd > 10000
ORDER BY n_tup_upd DESC
LIMIT 20;

Changing fillfactor on a live table

ALTER TABLE ... SET (fillfactor = 80) takes a SHARE UPDATE EXCLUSIVE lock and, as the ALTER TABLE documentation notes, does not modify the table contents immediately. It affects how future inserts fill pages. Existing full pages stay full until the table is rewritten with VACUUM FULL or CLUSTER, both of which hold an ACCESS EXCLUSIVE lock for the duration, or with an online rewrite tool. For a large hot table, set the parameter, let new pages carry the reserve, and decide separately whether a rewrite is worth its lock.

Index fillfactor is a different parameter with a different default: B-tree indexes default to 90, and the CREATE INDEX documentation suggests values from 50 to 90 for indexes on tables with many inserts and updates. HOT reduces how often indexes are touched at all, which is usually the bigger win.

Worked example: one index, ten times the writes

A sessions table has a primary key and four secondary indexes, five in total. Each request updates last_seen_at and a request counter, 2,000 updates per second. Fillfactor is 90 and no index covers either column, so almost every update is HOT: one heap write per update and no index writes.

A new index on last_seen_at arrives with a cleanup feature. Now every update changes an indexed column, so none can be HOT: each writes the heap tuple plus six index entries, one per index including the new one. Index insertions go from roughly zero to 12,000 per second, with the matching WAL and vacuum work. In the statistics, hot_pct falls towards zero while n_tup_newpage_upd stays low, which points at the index, not the fillfactor. Replacing the cleanup query with a scan over a BRIN index on last_seen_at, or keeping the column in a narrow side table, restores HOT. These figures are arithmetic from the stated assumptions, not a benchmark.

Verification

• Snapshot pg_stat_user_tables before and after any migration that adds an index to a frequently updated table.
• For a single page, the pageinspect extension shows line pointers and tuple headers, including redirects and HOT chains, if you need to see the mechanism directly.
• Watch WAL generation per second next to hot_pct; a falling HOT ratio usually shows up as more WAL first.

Failure modes

• An index on a timestamp or counter that every request updates.
• Fillfactor 100 on a table whose rows are updated soon after insert.
• Setting fillfactor and expecting existing pages to change without a rewrite.
• Long-running transactions that keep old versions visible, so pruning cannot reclaim space and pages fill anyway.
• Reading hot_pct without n_tup_newpage_upd and lowering fillfactor when the real cause is an index.

Practice and related reading

Further reading:

• Vacuum and bloat
• DDL behind a lock queue and lock_timeout
• PostgreSQL logical replication

Sources and scope

Checked against these sources on 21 October 2026. Examples and figures are illustrative, not measurements.

• PostgreSQL documentation: Heap-Only Tuples (HOT)
• PostgreSQL documentation: CREATE TABLE storage parameters (fillfactor)
• PostgreSQL documentation: The Cumulative Statistics System (pg_stat_all_tables: n_tup_hot_upd, n_tup_newpage_upd)
• PostgreSQL documentation: ALTER TABLE (SET storage parameters does not rewrite the table)
• PostgreSQL documentation: CREATE INDEX (B-tree fillfactor default 90)
• PostgreSQL documentation: pageinspect (heap_page_items)