HOT Updates and fillfactor

An update that changes no indexed column and finds room on the page it is already sitting on can skip index maintenance entirely. The old version's line pointer becomes a redirect to the new one, every index entry in the table stays valid because it still points at that same line pointer, and no index is touched. That is a heap-only tuple update, and it is the difference between one write and ten.

Both conditions are decided by the schema, not by the workload. The first is a question about which columns carry indexes; the second is a question about how full the engine was told to pack pages. On inventory both answers were wrong, and the previous topic's tuning was cleaning up damage that this topic prevents.

The Two Conditions

The manual states them plainly. An update can be HOT when it does not modify any column referenced by the table's indexes, and when the page holding the old version has enough free space for the new one. Both must hold. Either one alone buys nothing.

"Referenced by the table's indexes" is wider than people assume. It covers the columns of every ordinary index on the table, the columns inside an expression index, the columns a partial index names in its predicate, and the payload columns carried in an INCLUDE list — an included column is still stored in the index, so changing it still requires the index to be updated. The one exception is a summarizing index, which does not block HOT at all; BRIN is the only summarizing method in core Postgres, which is a quiet argument in its favour on a table that is both large and updated.

                Indexes:
                    "inventory_pkey" PRIMARY KEY, btree (product_id, warehouse_id)
                    "inventory_warehouse_idx" btree (warehouse_id)
                    "inventory_on_hand_idx" btree (on_hand)   -- idx_scan = 0

-- checkout, nine thousand times a minute at Saturday peak:
UPDATE inventory SET on_hand = on_hand - 1
 WHERE product_id = 4471 AND warehouse_id = 2;

The third index is the whole story. It was created for a stock-coverage report, the report was written, the report was never scheduled, and the index has been scanned zero times since. But on_hand is the one column checkout writes, and indexing it made every single checkout update ineligible for HOT by rule one. Nine thousand updates a minute at peak, and not one of them eligible.

What a Non-HOT Update Costs

A non-HOT update writes a new heap tuple, on another page if the current one is full, and then one new entry in every index on the table, not only the indexes whose columns changed. Each of those index pages is dirtied and each change is written to the WAL, so on orders with its nine indexes, an update to a single column costs ten writes and ten pages' worth of WAL, all of which a replica then has to apply. On inventory's three indexes it was four writes instead of one.

Then the bill continues after the statement. Each of those index entries is now a dead entry that vacuum must find and remove, on a full pass over every index, and the resulting churn is what makes a B-tree bloat independently of its table. A HOT update creates none of that: one new tuple on one page, and nothing for vacuum to chase through the indexes.

fillfactor

fillfactor is the percentage of a page that inserts are allowed to fill. It ranges from 10 to 100 and defaults to 100 for heaps, which means a freshly loaded page is packed solid and an update to any row on it has nowhere to put the new version except a different page. Setting it lower reserves the remainder for exactly one purpose: keeping future versions of the rows already on that page on that page.

ALTER TABLE inventory SET (fillfactor = 85);
VACUUM FULL inventory;   -- fillfactor applies to pages written from now on

-- delivery_events is never updated: reserved space here is pure waste
-- and its default of 100 is exactly right

The setting only affects pages written after it is set, so on an existing table it takes effect as new pages are filled — or immediately, if the table is being rewritten anyway. The trade is 15% more pages for the same rows, paid once in storage and in slightly more pages read per sequential scan, against a permanent reduction in index churn on every update. On inventory that is an obviously good deal. On delivery_events, which is only ever appended to, it would be 15% of 1.2 billion rows' worth of space reserved for updates that never come.

B-tree indexes have their own fillfactor, defaulting to 90 rather than 100, and it means something different: it governs how full leaf pages are packed during a build and when the index is extended at the right-hand edge with new highest keys. It has nothing to do with HOT, and the two settings are confused constantly. Lowering the index's own fillfactor before a bulk load smooths out the rate of page splits during its early life.

Pruning Without a Vacuum

When a row is updated repeatedly on the same page, the versions form a chain, and Postgres can remove the intermediate ones during ordinary page access, a plain SELECT included, without any vacuum involved. The oldest and newest survive; everything between them goes, and the space is immediately reusable by the next update on that page.

This is why a well-shaped hot table stays roughly the same size between vacuums instead of growing until one arrives. It is also why the fix on inventory was not really an autovacuum fix at all. Once a row's successive versions live on one page, the page cleans itself up as it is read, and vacuum's job on that table shrinks to freezing and the occasional straggler.

The HOT Ratio, Measured

The question is settled without guessing by three counters in pg_stat_all_tables. n_tup_upd is every update. n_tup_hot_upd is the subset that needed no index work. n_tup_newpage_upd, added in 16, is the subset whose new version had to go onto a different heap page — those are always non-HOT, and they are the ones a lower fillfactor can rescue.

SELECT relname,
       n_tup_upd,
       round(100.0 * n_tup_hot_upd     / nullif(n_tup_upd, 0), 1) AS hot_pct,
       round(100.0 * n_tup_newpage_upd / nullif(n_tup_upd, 0), 1) AS newpage_pct
  FROM pg_stat_all_tables
 WHERE n_tup_upd > 0
 ORDER BY n_tup_upd DESC;

 relname   | n_tup_upd  | hot_pct | newpage_pct
-----------+------------+---------+-------------
 inventory | 1874002331 |     1.9 |         3.1

Read the two percentages together and they name the failing condition. A low HOT ratio with a high new-page ratio says the updates are landing on other pages: the page had no room, and fillfactor is the lever. A low HOT ratio with a low new-page ratio, which is inventory's 1.9% against 3.1%, says the versions were mostly staying on the same page and something else disqualified them. That something else was an indexed column, which is a schema question and not a storage one. Dropping inventory_on_hand_idx and setting fillfactor to 85 moved the HOT ratio above 95%, and the table stopped generating the work the previous topic spent its time tuning autovacuum to absorb.

Designing for HOT

The design rule that falls out of this is short: index the columns you search by, not the columns you write. A counter, a status flag flipped on every request, a last_seen_at touched on every page view — each of these in an index turns every write on the table into a multi-index write plus vacuum work later. Before adding an index to a table with a high update rate, the question is not only "which query does this serve" but "which column does checkout write".

When a column genuinely has to be both indexed and constantly updated, the shape that works is separation: move the volatile column into a narrow table keyed by the same identifier, so its updates touch one small heap and one small index instead of a wide row with nine of them. That is a real cost, a join on every read, and it is worth paying only when the numbers say so. Chapter 8 covers the other half of this decision, which is finding out whether an index earns its place at all.