What UPDATE actually does to a page · PostgreSQL

Part VI showed what an UPDATE does to a page: a new tuple appears, the old one is stamped with t_xmax and pointed forward. This lesson is about the difference between the cheap version of that and the expensive one, because the gap is large and the condition that decides it is under your control.

The two conditions

An update is a HOT update — heap-only tuple — when both of these hold:

No indexed column changed.
The new version fits on the same page.

When both hold, the new tuple is chained to the old one inside the page and no index is touched at all. The index entries still point at the old line pointer, and a scan arriving there follows the chain forward.

When either fails, the new version needs an entry in every index on the table, whether or not that index has anything to do with the column you changed.

That last clause is the one people underestimate. Change one non-indexed column on a table with six indexes, on a full page, and you have written six index entries.

Measuring the difference

Two tables, identical in every respect except fillfactor, each with 20,000 rows and a primary key on id. The updated column, counter, is not indexed — so the only thing standing between these updates and HOT is whether the new tuple fits on the page.

Read-only / Safebaseline after load and VACUUM ANALYZE— Captured on 18.6. fillfactor 70 reserves 30% of each page for future versions.

$ psql -U postgres -c "SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS heap, pg_size_pretty(pg_indexes_size(oid)) AS indexes FROM pg_class WHERE relname IN ('hot_default','hot_ff') ORDER BY relname"

   relname   |  heap   | indexes
-------------+---------+---------
hot_default | 1016 kB | 456 kB
hot_ff      | 1464 kB | 456 kB
(2 rows)

fillfactor = 70 costs 44% more heap up front. That is the price. Now twenty rounds of UPDATE … SET counter = counter + 1, with a VACUUM between rounds so that neither table is simply drowning in dead tuples:

Read-only / SafeHOT rate after 400,000 updates on each table— Captured on 18.6. n_tup_hot_upd is the count PostgreSQL keeps of updates that avoided the indexes.

$ psql -U postgres -c "SELECT relname, n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/nullif(n_tup_upd,0),1) AS hot_pct FROM pg_stat_user_tables WHERE relname IN ('hot_default','hot_ff') ORDER BY relname"

   relname   | n_tup_upd | n_tup_hot_upd | hot_pct
-------------+-----------+---------------+---------
hot_default |    400000 |          2694 |     0.7
hot_ff      |    400000 |        378716 |    94.7
(2 rows)

0.7% against 94.7%, from one storage parameter.

But look at the sizes, because they do not tell the story you might expect:

Read-only / Safesizes after the same 400,000 updates— Vacuum ran between every round, which is the situation a healthy system is in.

$ psql -U postgres -c "SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS heap, pg_size_pretty(pg_indexes_size(oid)) AS indexes FROM pg_class WHERE relname IN ('hot_default','hot_ff') ORDER BY relname"

   relname   |  heap   | indexes
-------------+---------+---------
hot_default | 2032 kB | 896 kB
hot_ff      | 2784 kB | 888 kB
(2 rows)

With vacuum keeping up, the two tables end at broadly similar sizes. hot_ff is actually the larger of the two, because of the space it reserved. So on size alone, in a healthy system, HOT looks like it barely matters.

The cost is somewhere else.

Where the cost actually lands

The right measurement is WAL volume, because WAL is what leaves the machine. Measured as the difference in pg_current_wal_lsn() across a single full-table update of 20,000 rows:

Read-only / SafeWAL generated by one identical UPDATE statement— Captured on 18.6. Three consecutive rounds of each table, alternating, to show the figures are stable.

$ psql -U postgres -At -c "SELECT pg_current_wal_lsn()"   # before and after each UPDATE

  round 1  hot_default (fillfactor 100): 4262 kB  (4364624 bytes)
round 1  hot_ff      (fillfactor  70): 1413 kB  (1447040 bytes)
round 2  hot_default (fillfactor 100): 4382 kB  (4486768 bytes)
round 2  hot_ff      (fillfactor  70): 1548 kB  (1585024 bytes)
round 3  hot_default (fillfactor 100): 4384 kB  (4489272 bytes)
round 3  hot_ff      (fillfactor  70): 1548 kB  (1585040 bytes)

2.8 times the WAL for identical logical work, and stable across rounds.

Why WAL volume is the number that matters

A local table being 700 kB larger is a rounding error. WAL is not local. Every byte of it is:

written and synced on the primary, competing with commits for the same storage;
shipped to every streaming replica, over a network link that has a capacity;
archived, filling whatever holds your backups;
replayed on every replica, and replay is largely single-threaded, so a primary generating WAL faster than a replica can apply it produces lag that grows without bound;
read back during recovery, so it sets your recovery time.

A table doing non-HOT updates at volume charges that 2.8x multiplier to all five. This is the mechanism behind a class of incident that looks mysterious from the outside: replication lag appears, nothing about the query workload changed, and the cause is an index added last week to a column the application updates constantly.

An index on the updated column ends HOT entirely

The second condition is the more absolute one. Same table, same fillfactor = 70, same UPDATE — with one index added on counter, the column being updated.

Configuration changethe effect of adding one index to the updated column— Captured on 18.6. Statistics were reset before the measured round so the percentages are for that round alone.

$ psql -U postgres -c "CREATE INDEX hot_ff_counter_idx ON hot_ff (counter)" -c "VACUUM ANALYZE hot_ff"

-- before the index:  94.7% HOT,  1548 kB WAL per round
-- after the index:

wal_for_one_round
-------------------
3984 kB

relname | n_tup_upd | n_tup_hot_upd | hot_pct
---------+-----------+---------------+---------
hot_ff  |     20000 |             0 |     0.0

From 94.7% to zero, and WAL multiplied by 2.6. No amount of fillfactor recovers this, because the first condition is not about space — the index entry for the old value is now wrong, so a new one must be written no matter where the tuple goes.

Adding an index to a hot column because a query was slow

The reasoning is unimpeachable in isolation. A query filters on status, status is not indexed, the plan is a sequential scan, so add the index.

What the reasoning does not account for is that status is also the column the application updates on every state transition. The index makes the read faster and takes every update on that table from HOT to non-HOT — including updates to columns that have nothing to do with status, because the condition is per row, not per column.

The measured cost here was 2.6x WAL on the whole table. The index’s own few hundred kilobytes were the cheapest part of it.

This is not an argument against the index. It is an argument for checking n_tup_hot_upd before and after, and for knowing that the question “should we add this index” has a write-side answer as well as a read-side one.

Reading your own tables

The numbers are already being collected. Nothing needs to be enabled.

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct,
       n_dead_tup,
       last_autovacuum
  FROM pg_stat_user_tables
 WHERE n_tup_upd > 0
 ORDER BY n_tup_upd DESC
 LIMIT 20;

Reading it:

High n_tup_upd, high hot_pct. Working as intended. Leave it alone.
High n_tup_upd, low hot_pct. Every update is writing index entries. Establish which condition is failing: is an indexed column changing, or are the pages full? The first is a schema question, the second is a fillfactor question.
High n_tup_upd, low hot_pct, and the columns being updated are not indexed. This is the case fillfactor fixes. It is also the case where lowering it is most clearly worth the extra space.

fillfactor and vacuum are one mechanism, not two

An earlier run of the same experiment, without a VACUUM between rounds, produced a much weaker result for the low-fillfactor table:

   relname   | n_tup_upd | n_tup_hot_upd | hot_pct | heap
-------------+-----------+---------------+---------+-------
 hot_default |    200000 |             0 |     0.0 | 11 MB
 hot_ff      |    200000 |         62922 |    31.5 | 11 MB

31.5% instead of 94.7%. The reserved space had filled with dead tuples that nothing had reclaimed, so from the third or fourth round onward there was no more room on the page than in the default table.

The reading: fillfactor reserves space, and only vacuum returns it. Setting fillfactor on a table whose autovacuum cannot keep up buys one round of HOT updates and then stops helping, while permanently costing the extra space.

This is a general shape worth recognising. fillfactor, HOT, the free space map and autovacuum are not four independent things to tune. They are one loop: vacuum frees space within pages, the free space map advertises it, HOT updates consume it, and fillfactor sets how much headroom the loop starts with. Tuning one in isolation usually disappoints.

PostgreSQL does prune HOT chains opportunistically when a page is accessed, without waiting for vacuum, which is why the unvacuumed table still reached 31.5% rather than zero. But opportunistic pruning within a page is not a substitute for vacuum, which is what makes space visible to the rest of the table.

What to take from this

HOT requires both that no indexed column changed and that the new version fits on the page. Failing either writes an entry in every index.
The cost of non-HOT updates shows up in WAL volume, not primarily in table size — measured here at 2.8x, and 2.6x from adding one index.
WAL is a shared, remote cost: replication, archiving, recovery time.
n_tup_hot_upd against n_tup_upd tells you where you stand, on statistics already being collected.
fillfactor only helps while vacuum is reclaiming the space it reserves, and only applies to pages written after it is set.

Cross-course references

Linux for Production Sysadmins — Part XLI (Storage Performance) covers the device-level cost of the extra page writes a non-HOT update produces.
Observability for Production Sysadmins — Part LIX (Database observability) covers tracking the HOT-update ratio over time, which is the number that tells you whether fillfactor is working.