Every UPDATE in PostgreSQL is a delete plus an insert under the hood. Because of MVCC, the old row version stays behind for concurrent readers, and the new version lands somewhere in the heap with a fresh tuple identifier. What surprises people the first time they profile a hot update path is that the heap write is often the cheap part. The expensive part is that a brand-new tuple version normally needs a brand-new entry in every index on the table, even if the column you changed appears in no index at all. PostgreSQL has an optimization for exactly that case, called HOT — heap-only tuple — and whether your workload benefits from it is largely decided by one unglamorous table setting: fillfactor.
I have tuned this on queue tables, session stores, and account-balance ledgers where the same rows get hammered thousands of times a minute. Getting HOT right on those tables is the difference between an autovacuum that keeps up and one that falls behind a little more every hour. I will take the machinery in order — how HOT works, why fillfactor decides whether it can happen, and how to measure the ratio on your own tables.
What a heap-only tuple update actually is
When you UPDATE a row, PostgreSQL writes a new tuple version and links it to the old one through the ctid chain: the old version's ctid field points at the physical location of the newer version. Index entries, meanwhile, point at the head of that chain — the oldest version's line pointer. Normally, an update also forces a new index entry per index, because the new tuple lives at a new physical location and indexes store physical locations.
HOT kicks in when two conditions hold at the same time. First, the update does not modify any column that is indexed — not the value's equality, the column itself; a no-op SET status = status still counts as modifying the column. There is one narrow carve-out: summarizing indexes — BRIN is the only one in core PostgreSQL — do not block HOT, because they can absorb a stale summary range. Second, the new tuple version fits on the same heap page as the old one. When both are true, PostgreSQL writes only the new heap tuple and extends the chain within the page. No ordinary index is touched at all, though a BRIN summary over the changed range may still be updated, which is cheap compared to a per-row btree entry. Index scans still work: the index entry points to the head of the chain, and the executor follows the chain, pruning dead versions as it goes, until it finds the version visible to its snapshot.
Chains cannot cross page boundaries, which is why the same-page requirement is absolute. If the page is full, the update falls back to the cold path and every index gets a new entry, no matter which columns changed.
Why fillfactor decides whether HOT can happen
A table's fillfactor is the percentage of a heap page that INSERT is allowed to fill; the rest is reserved as free space. The default for tables is 100, meaning pages are packed completely full at insert time. That is a fine default for append-mostly data, but it leaves zero room for a HOT update: the very first update to a row on a full page must go cold, because there is no space on the page for the new version.
Setting fillfactor to 80 or 90 deliberately under-fills pages so that one or two in-place update rounds fit next to the original tuple. The syntax is a storage parameter on the table:
ALTER TABLE events SET (fillfactor = 80);
-- Check the setting:
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'events';The catch that bites everyone once: fillfactor only applies to pages written after the change. Existing pages stay packed. To retrofit a table you have to rewrite it — VACUUM FULL, CLUSTER, or pg_repack if you cannot hold the lock — or let time do it gradually: plain vacuum never repacks an existing page to the new fillfactor, but it frees dead space within pages, which is all HOT needs, and every newly filled page follows the new setting. On a busy table I usually set the parameter, let natural turnover plus autovacuum do the work, and only schedule a rewrite if the HOT ratio has not moved after a few days.
Measuring HOT with pg_stat_user_tables
The statistics collector tracks both paths separately. pg_stat_user_tables has n_tup_upd (all updates) and n_tup_hot_upd (the subset that went HOT). Their ratio, measured over a meaningful traffic window, tells you exactly how much index maintenance your update workload is avoiding:
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;Do not judge the ratio from a single reading of the cumulative counters. Take two snapshots across a busy hour and diff them, or your numbers are dominated by whatever the table did for the last month. A ratio above 90 percent on a hot-updated table is healthy. A ratio near zero means every update is rewriting every index, and you should ask why: either the updated columns are indexed (look again — a partial index or an expression index counts too), or the pages have no room.
One more subtlety I learned the hard way: adding a regular index to a column that your hot path updates quietly kills HOT from that moment on. A BRIN summarizing index would not, but nobody adds BRIN to a hot status column. The ratio decays over a day, nobody connects it to the deploy, and vacuum load creeps up. After any schema change on an update-heavy table, re-check this query.
The write amplification you are actually avoiding
It helps to price a cold update. A table with six indexes turns one logical row change into one heap write plus six index inserts, each of which may dirty a different leaf page, each of which generates WAL, and each of which leaves a dead index entry behind that only vacuum can clean. Index pages are also where random I/O concentrates: the heap write might be roughly sequential, but six index leaves are six random pages scattered across the relation. Under checkpoint pressure those pages flush at the worst times.
HOT collapses all of that to the single heap page. The WAL stream shrinks, buffer churn shrinks, and — the part operators feel — autovacuum has dramatically less garbage to collect, because dead index entries never get created in the first place. On one ledger table I worked with, moving a status flag out of an index and dropping fillfactor to 85 cut the table's autovacuum frequency by more than half. The mechanism matters more than my number: you are removing index work per update, and the savings scale with how many indexes the table carries.
When to set fillfactor 70–90, and when to leave it alone
The profile that benefits is narrow: tables where the same rows are updated frequently, the updates touch no indexed column, and rows are small enough that a page holds many of them. Queue and job tables where a status or retry-count column flips constantly are the classic case — as long as nothing indexes those columns. Session state, rate-limit counters, and balance rows fit the pattern too.
How low to go depends on row size and update burstiness. With fillfactor 90 and narrow rows, a page has room for several successor versions before it fills, with vacuum pruning the chains in between; wide rows might get one round, or none if a single row pair already overflows the slack. Price the storage honestly too: fillfactor 80 means each page holds 80 percent of the rows it could, so the heap grows by about 25 percent — not 20 — and every sequential scan reads those extra pages. I start at 90 for wide rows, 80 for narrow ones, and adjust based on the measured HOT ratio. Going below 70 almost never pays; the extra slack buys little, because vacuum eventually prunes the chains and frees the space anyway.
Just as important is where not to touch it. Append-only tables, time-series partitions, and anything read far more than it is updated should stay at 100. Lowering fillfactor there is a pure tax: bigger tables, more pages per scan, worse cache density, zero benefit. Indexes have their own fillfactor default of 90 for a related reason — leaf splits want slack — but that is a separate knob with separate math.
HOT is a contract with your future self
The quiet risk is schema drift. HOT depends on the set of indexed columns never overlapping the set of hot-updated columns. Every new index someone adds "for one report" is a candidate violation of that contract. I keep a short list of tables that rely on HOT, note it in the schema comments, and check the update ratio after migrations. It is five minutes of hygiene that prevents a slow-motion regression.
If you want the broader vacuum picture around this — chain pruning, dead tuple cleanup, freeze work — our PostgreSQL vacuum guide covers the lifecycle, and the PostgreSQL product surface shows where these counters sit in a monitoring view.
Watching update health with MonPG
HOT ratios are invisible until they degrade, and then index bloat, vacuum lag, and WAL volume degrade behind them. MonPG monitors PostgreSQL in production today, and it graphs per-table update and HOT-update rates from pg_stat_user_tables next to dead-tuple accumulation and autovacuum timing. When a deploy adds an index that silently breaks HOT on a hot table, the ratio bends on the dashboard the same day, not during the next capacity review. If update-heavy tables are where your pain lives, the PostgreSQL monitoring setup is where I would start.