Heap-Only Tuple Updates and fillfactor

An UPDATE in Postgres normally writes one new entry into every index on the table — not into the indexes whose columns you changed, into all of them. The Heap-Only Tuple optimisation, HOT, is the escape hatch that writes into none of them. It has exactly two conditions, and the second one is about free space on a single 8 kB page.

Postgres never updates a row in place. It writes a whole new version and leaves the old one behind, which is the subject of MVCC and vacuum. The part that costs you on an update-heavy table is not the extra heap row. It is that every index normally has to learn where the new version lives, so a table with six indexes pays six index writes for one UPDATE — six more leaf pages dirtied, six more entries in the write-ahead log, six more places for the old entry to sit dead until vacuum removes it.

A Heap-Only Tuple is a row version with no index entry of its own. The indexes keep pointing at the original version's slot on the page, and a forward link inside the page leads from there to the new version. Postgres can do this when both of these hold:

• the statement changes no column referenced by any index on the table, except summarizing indexes — BRIN is the only summarizing index type in core Postgres; and
• the new version fits in the free space on the page the old version is already sitting on.

The panel loads a 100,000-row table, then runs an UPDATE workload against it and reports what each statement was allowed to do. The slider that carries the lesson is fillfactor, the percentage of each page that INSERT is permitted to fill. It starts at 100, the default, which reserves nothing.

At the defaults — fillfactor 100, a column no index mentions — 64.5% of the statements manage a HOT update and the other 71,097 write 355,485 index entries between them, 1.78 per statement on a table that has five indexes and a workload that logically changes none of them. Drag fillfactor from 100 to 90 and that number goes to 0.00. The whole cost disappears, and the price is a table that starts out 13.2 MB instead of 12.0 MB.

Now put fillfactor back to 100 and switch the column to status. The HOT rate drops to 0.0%, index writes go to 1,000,000 — five per statement, exactly the number of indexes — and now drag fillfactor down through its whole range. The rate stays at 0.0% the entire way. That is the shape of the lesson. One condition is a space problem you can tune. The other is a schema fact you cannot.

The condition that is not about space

With status selected, drag fillfactor all the way down to 10. The heap goes from 12.0 MB to 130.2 MB — eleven times the size, for a table holding exactly the same 100,000 rows — and the HOT rate stays at exactly 0.0%. Nothing you do to page layout can buy back a HOT update that the column test already refused.

The test is one line of heap_update: if (!bms_overlap(modified_attrs, hot_attrs)). Both operands are bitmaps over the table's columns. modified_attrs is the set of columns whose stored bytes this statement actually changed — bitwise, so writing the same value back does not count as a modification and stays HOT-eligible. hot_attrs is the union of every column referenced by every non-summarizing index on the table.

"Referenced" is broader than people expect, and each of these puts a column into hot_attrs:

• a key column of any B-tree, GiST, GIN, SP-GiST or hash index;
• a column in an <code>INCLUDE</code> clause, even though it is not a key and cannot be searched on;
• a column used only inside a partial index's WHERE predicate, which does not appear in the index at all;
• a column that feeds an expression index, such as lower(email) — the index stores the result, but the input column is what is tracked;
• a column in a unique constraint, primary key or exclusion constraint, because each of those is backed by an index.

Only BRIN escapes. BRIN stores a summary per block range rather than one entry per row, so it has no per-row pointer to maintain and cannot go stale in the way that matters here. The ingested_at option in the panel is exactly that case: the column is indexed, the first HOT condition still passes, and the BRIN summary is refreshed without a single B-tree write.

The consequence of failing the test is the part that surprises people. Because the new version gets its own line pointer, every index has to be able to find it, so the executor inserts into all of them. Add a seventh index to a table for one reporting query, and every non-HOT UPDATE on that table becomes 17% more expensive even if the query never runs and the new index shares no column with anything you write. Move the indexes on the table slider with status selected and watch index entries per UPDATE track it exactly.

The condition that is about space, and why fillfactor is the knob

Put the column back to last_seen_at and leave fillfactor at 100. The HOT rate is not zero — it is 64.5%. That surprises people who have read that fillfactor 100 disables HOT. Following why it is 64.5% rather than either 0% or 100% is the whole of the space condition.

A page loaded at fillfactor 100 holds 65 row versions of 120 bytes each and has 108 bytes left over. A new version needs 124: 120 for the tuple and 4 for its line pointer. So the first UPDATE to touch any freshly loaded page cannot be HOT. The row is written on some other page, gets its index entries, and leaves a dead version behind.

That dead version is 120 bytes the page can have back — eventually. heap_page_prune_opt runs whenever a page is read and has less free space than MAX(fillfactor target free space, BLCKSZ/10), which at fillfactor 100 is 819 bytes, so a page sitting at 108 free is always a candidate. Set oldest snapshot lag to 0 and you can watch what happens when pruning always succeeds: the HOT rate goes to 99.2%, one failure per page for the whole run, and the heap grows 2%. Every page pays for one migration and is then self-sustaining forever, recycling a single 120-byte slot between the dying version and the new one.

That is the documentation's claim that HOT updates still happen at the default fillfactor "because new rows will naturally migrate to new pages and existing pages with sufficient free space". It is true, and it describes a database with one session in it.

Pruning may only remove a version that no open snapshot can still see. At a lag of 2,000 statements there are always about 2,000 dead versions in the table that pruning is not allowed to touch, spread over 1,539 pages, so a typical page is carrying one or two of them at any moment. It has 108 bytes of slack and 120 to 240 bytes of untouchable garbage. Every second or third update to that page therefore migrates. The decision log counts it: pruning ran into the snapshot lag 121,190 times, and that is where the 35.5% of statements that were not HOT went.

Lowering fillfactor buys headroom that survives the lag. At fillfactor 90 the load packs 59 rows per page and leaves 852 bytes — six row versions of headroom — and the HOT rate goes to 100.0%, index writes to 105 for the whole run, heap growth to nothing. The price was paid in advance: the initial load is 13.2 MB instead of 12.0 MB. That is the actual trade. You are not saving space by lowering fillfactor; you are spending space to avoid index writes.

The fix has a boundary, and the lag slider is how you find it. Leave fillfactor at 90 and push the lag to 20,000 — one moderately long reporting query is worth far more than that on a busy table. The six reserved slots per page are now facing about twelve untouchable dead versions each. The HOT rate collapses to 28.9%, index writes climb to 711,445, and the heap doubles, from 13.2 MB to 27.2 MB. Drop fillfactor to 70 at the same lag and it recovers to 99.2% — so the fix still works, it has simply become much more expensive. This is the same pinned-horizon failure that VACUUM has, arriving through a different door: pruning obeys the same visibility test vacuum does, so the transaction somebody left open in a psql window is silently setting your table's HOT rate.

Two more things about the trade are easy to get wrong.

First, the reserve is per-page and shared, not per-row. A page holding 59 rows with room for six versions supports six unprunable dead versions across all 59 of those rows, not six updates per row. That is why the lag matters so much, and it is why row width matters too. Widen the row width slider to 400 bytes: the fillfactor-90 page still reserves 819 bytes, but a row version now costs 428, so the headroom falls from six spare slots to two and the HOT rate slips from 100.0% to 98.7%. At fillfactor 100 the same page has 36 bytes spare and manages 61.2%. fillfactor is a percentage; HOT needs whole row versions. Fat rows need a lower fillfactor to buy the same number of slots.

Second, ALTER TABLE ... SET (fillfactor = 90) does not reorganise anything. fillfactor is consulted when a row version is placed on a page, so it governs pages built after the change. The pages you already have stay packed to 8,168 bytes and will never spontaneously make room. To apply it you have to rewrite the table — VACUUM FULL, CLUSTER, or pg_repack if you cannot take the ACCESS EXCLUSIVE lock. Setting the parameter and watching nothing change for a week is the single most common way this tuning attempt fails.

Where the space goes when HOT does not happen

A non-HOT update does not necessarily grow the heap. Select status and set fillfactor to 90, then read the decision log's outcome line: of 200,000 cold updates, 199,926 still put the new version on the same page. They had the room. They were simply not allowed to skip the index writes. Postgres asks the two questions separately, and so should you. Same-page placement is a free-space question. Skipping the indexes is a column-overlap question. n_tup_newpage_upd, which PostgreSQL 16 added to pg_stat_all_tables, counts only the first kind of failure — which is exactly what makes it the diagnostic that tells the two apart.

When a version does have to move, Postgres asks the free space map for a page with at least tuple size + fillfactor reserve free, and extends the file if there is none. The reserve applies here and not to the same-page case, which is a detail with a visible consequence: at a low fillfactor a page can be simultaneously eligible to receive a HOT update from a row already on it and ineligible to receive a migrating row from elsewhere. Postgres protects the reserve against newcomers and spends it on residents.

Pruning is what keeps the page map from filling up with red, and it is worth being precise about how much work it is doing: at the defaults it reclaims 195,861 dead row versions over the run, 22.4 MB, without a single vacuum and without touching an index. That is HOT's second gift and it is larger than the first. Vacuum is left with only the line pointers and the index entries — which is why a HOT-friendly table can survive on a much lazier autovacuum schedule than a HOT-hostile one of the same size.

The index entries themselves are the part that does not recover on its own. Autovacuum deletes the entries pointing at removed heap tuples and marks the leaf pages reusable, but a B-tree never merges partly-full pages and never returns them to the operating system, so a burst of non-HOT updates leaves an index that is permanently larger than its contents — see index bloat. PostgreSQL 14 added a strong mitigation here, bottom-up index deletion: when a leaf page is about to split because of version churn, nbtree first tries to delete the obsolete duplicate versions on that page, and usually succeeds. It targets precisely the indexes that were not logically modified by the UPDATE — the innocent bystanders in the "all indexes get an entry" rule. It is not modelled in the panel, so treat the index sizes there as the pre-14 worst case for the B-trees that share no column with the update.

Checking it on a real system

Start with the ratio, per table. This is the one number that tells you whether any of this applies to you:

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       n_tup_newpage_upd,                       -- PostgreSQL 16 and later
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC
LIMIT 20;

A table with millions of updates and hot_pct near zero is paying full index write amplification on every statement. Before you reach for fillfactor, find out which condition is failing, because the two have completely different fixes. On PostgreSQL 16 and later n_tup_newpage_upd answers it directly: if it is close to n_tup_upd - n_tup_hot_upd, your problem is space and fillfactor is the lever. If it is near zero while hot_pct is also near zero, the new versions are fitting on their pages perfectly well and you are failing the column test.

Then find out which column. This lists the columns that are in hot_attrs for a table — the ones whose modification forfeits HOT:

SELECT DISTINCT a.attname, i.relname AS index_name, am.amname
FROM pg_index x
JOIN pg_class i     ON i.oid = x.indexrelid
JOIN pg_class t     ON t.oid = x.indrelid
JOIN pg_am    am    ON am.oid = i.relam
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY (x.indkey)
WHERE t.relname = 'orders'
  AND am.amname <> 'brin'      -- the only summarizing AM in core, so the only one that does not block HOT
ORDER BY a.attname;

indkey covers key and INCLUDE columns. Columns that appear only in an expression or a partial-index predicate show up as attribute number 0 in indkey and have to be read out of pg_get_indexdef(x.indexrelid) instead — which is worth doing, because an expression index on a column you update every second is a completely invisible way to lose HOT.

Cross that list against the columns your hot statement writes. The fix, in order of preference: stop writing the column if the write is spurious (SET updated_at = now() on a row whose other columns did not change is the classic); drop the index if nothing needs it — check pg_stat_user_indexes.idx_scan first; or move the churning column into a narrow side table keyed by the same primary key, so the churn happens on a table with one index instead of seven.

Only when the column test is passing and n_tup_newpage_upd is high does fillfactor become the answer:

ALTER TABLE orders SET (fillfactor = 85);
-- existing pages are unaffected until the table is rewritten:
VACUUM FULL orders;        -- ACCESS EXCLUSIVE, needs 2x the space
-- or, without the outage:
pg_repack -t orders

Verify with a before-and-after on hot_pct, having reset the counters with pg_stat_reset_single_table_counters('orders'::regclass) so you are comparing the new steady state rather than the table's whole history. 85 to 90 is the usual landing spot; below about 70 you are paying more in extra pages read per sequential scan than you are saving in index writes, and the panel's heap on disk readout at fillfactor 10 shows how far that can go.

Next: what happens to those index entries afterwards, in why a B-tree never shrinks; and the visibility rule that all of this sits on top of, in MVCC and what VACUUM does not do.