HOT update, and what fillfactor buys

A HOT update writes the new row version onto the same heap page as the old one and touches no index. PostgreSQL can do this when no indexed column changed and the page has room. The old version keeps a pointer to the new one, so an index entry aimed at the old line number finds the current row with one step inside the page. Fillfactor is the share of each page an insert may fill, and the remainder is the room a later HOT update needs.

The cheapest write the server can make

An ordinary update costs one new heap row version plus one new entry in every index on the table, and each of those index entries is itself a write-ahead log record and a page that will later need cleaning. A HOT update costs the heap row version and nothing else. On a table with five indexes that is a sixfold difference in write amplification for exactly the same logical change, which is why the HOT ratio is one of the few per-table numbers worth a panel of its own.

It also changes who has to clean up. Dead heap-only versions can be removed by a page-level prune during an ordinary read, without a vacuum pass and without the index scan a vacuum would need. A table whose updates are mostly HOT largely maintains itself; a table whose updates are mostly not accumulates index bloat that only a full pass removes.

Two things break it, and only one of them is about space. Updating any indexed column disqualifies the update outright, however much room the page has, so an index on a column the application rewrites constantly is expensive in a way that its scan statistics never show. The other is the page filling up, which is what fillfactor exists to postpone and cannot prevent.

Measuring the ratio on a table you control

The counters are cumulative per table, and the fixture below makes the effect visible in one pass.

CREATE TABLE session_state (id int PRIMARY KEY, payload text) WITH (fillfactor = 70);
INSERT INTO session_state SELECT g, 'first' FROM generate_series(1, 1000) g;
UPDATE session_state SET payload = 'second';
SELECT pg_sleep(1);
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'session_state';

CREATE TABLE
INSERT 0 1000
UPDATE 1000
 pg_sleep 
----------
 
(1 row)

 n_tup_upd | n_tup_hot_upd 
-----------+---------------
      1000 |           448
(1 row)

Fewer than half the updates stayed HOT, and the fixture was built to make that point: fillfactor 70 left room on each page for a few extra versions, that room was consumed in the order the pages were visited, and every update after it ran out had to find a new page. The same run on 18.6 gives the identical split, so this is arithmetic rather than version behaviour. The pg_sleep is there because these counters are reported asynchronously and a read issued immediately after the update can arrive before the backend has flushed them.

Lowering fillfactor further raises the ratio and costs proportionally more disk for the same rows, so it is a trade rather than a setting with a right answer. Tables that are inserted once and never updated should be left at the default.

Where the ratio leads next

A low HOT ratio is a bloat story, and the guide on autovacuum and table bloat covers what the resulting index growth does and which of the three causes of dead rows you are actually looking at. If the reason the ratio is low is an index on a volatile column, the question is whether that index earns its keep at all, which is what unused and missing indexes is for.