PostgreSQL HOT Updates: Fillfactor and the Cost of One More IndexPostgreSQL HOT Updates: Fillfactor and the Cost of One More Index

We've all been there... wanting to check what an index on a frequently updated progress counter would cost. Just me!? Obviously there's the cost of keeping up the index, but the less obvious one is losing an optimization that lets the existing primary-key index sit out the update too.

The interesting investigation is that of a single heap page, usually 8 KiB. An update creates another row version, and that version needs somewhere to live. If it fits beside its predecessor and the relevant indexed values haven't changed, PostgreSQL can keep using the existing index entries. That combination is a heap-only tuple, or HOT, update.

You need both space AND HOT eligibility. Enough space cannot make up for an indexed-column change that blocks HOT.

In my B-tree versus LSM discussion, we looked at where storage structures pay for organizing data. This time I want to follow one SQL update through PostgreSQL's heap and indexes. This is a reproducible lab experiment with a simple progress counter, not a production incident.

The row changed. Its index entry might not need to.

PostgreSQL keeps table rows in a heap, separate from its indexes. Even the primary-key index is a separate structure. An ordinary index entry identifies a heap tuple through its page number and item identifier, often called a line pointer. The tuple's ctid exposes that physical location; it isn't a durable application identifier. The page-layout documentation explains this indirection.

Under MVCC, an update creates a new tuple version while retaining the old one for transactions that might still need it. Tuple headers carry transaction information, including xmin and xmax, used in visibility decisions. Changing an integer doesn't mean replacing it in the existing tuple and calling the job done.

HOT gives these versions a shared path from the index. Here is a schematic chain with invented slot numbers:

primary-key index: id=42 -> heap page 7, slot 1

page 7: slot 1, old version -> slot 4 -> slot 6, newer version

The newer heap-only versions don't have direct entries in the ordinary indexes. The old tuple's t_ctid points forward, with HOT flags identifying the chain. An index scan follows that chain to the version visible to its snapshot, which may not be the newest one. Keeping the chain on one page avoids fetching another heap page just to follow it.

The PostgreSQL 18 implementation notes explain the cleanup benefit. When the root tuple becomes removable, its line pointer can become a redirect. Obsolete intermediate versions and slots can be reclaimed because no index points to them directly. The index keeps its stable entrance while the page recycles space behind it.

This is still a heap update, with visibility checks and WAL work. HOT avoids new per-tuple index entries; it doesn't make the write disappear.

Give the successor somewhere to live

Let's see it in action. I used two freshly loaded tables, differing only in table fillfactor. Both have 10,000 rows, an integer progress counter and padding of 200 bytes. I seeded the progress with row number so it has selective value for later lookup (not percentage).

I ran this on PostgreSQL 18.6 with 8,192-byte pages. Use psql against a disposable database, with autocommit enabled and no enclosing BEGIN:

\set ON_ERROR_STOP on
CREATE TABLE hot100 (
  id integer PRIMARY KEY,
  progress integer NOT NULL,
  pad text NOT NULL
) WITH (fillfactor=100, autovacuum_enabled=false);

CREATE TABLE hot70 (
  id integer PRIMARY KEY,
  progress integer NOT NULL,
  pad text NOT NULL
) WITH (fillfactor=70, autovacuum_enabled=false);

INSERT INTO hot100
SELECT i, i, repeat('x', 200)
FROM generate_series(1, 10000) AS g(i);
INSERT INTO hot70 SELECT * FROM hot100 ORDER BY id;
ANALYZE hot100;
ANALYZE hot70;

SELECT relname, pg_relation_size(relid) AS heap_bytes
FROM pg_stat_user_tables
WHERE relname IN ('hot100', 'hot70');

SELECT format(
  'UPDATE %I SET progress=progress+1 WHERE id %% 10=0;', t
)
FROM (VALUES ('hot100'), ('hot70')) AS v(t)
CROSS JOIN generate_series(1, 5) AS r(n)
ORDER BY t, n
\gexec

SELECT pg_stat_force_next_flush();

That last generated batch runs five separately committed updates per table, each touching the same 1,000 rows. It matters: five committed passes give pruning different opportunities from one large transaction holding all its predecessors alive. I disabled ordinary autovacuum for these short-lived fixtures to reduce background variation. Page pruning still operates. Keep autovacuum enabled on real tables.

After the workload session finishes, inspect the counters from a new session:

SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_user_tables
WHERE relname IN ('hot100', 'hot70')
ORDER BY relname;

These statistics separate total updates, HOT updates, and updates whose successor moved to another page. Cross-page updates are always non-HOT; same-page updates needn't be HOT. Cumulative statistics can lag and are cached within transactions, so the explicit flush and fresh observation session keep that from affecting the experiment.

Lower fillfactor holds space during insertion, increasing the chance of a same-page successor. It cannot guarantee HOT forever. Pages have finite room, tuple sizes matter, and snapshots can delay reuse. On the flip side, fillfactor 100 doesn't mean HOT is impossible: packing, migration and subsequent reclamation can leave usable space.

My initial heaps occupied 304 pages at fillfactor 100 and 435 at 70. Across the first 5,000 updates, the packed table recorded just 71 HOT updates; the roomier table recorded 5,000. No indexed value changed in either table. The packed table's 4,929 cross-page updates exposed the other failed condition: placement.

Add the index, then repeat the same update

Before adding the index, we check the read it is supposed to help. In five passes, progress=5005 matches two rows: one untouched row and the updated row that started at 5000.

ANALYZE hot70;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, pad FROM hot70 WHERE progress=5005;

CREATE INDEX hot70_progress_idx ON hot70(progress);
CREATE INDEX hot100_progress_idx ON hot100(progress);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, pad FROM hot70 WHERE progress=5005;

Selecting pad makes this a heap-fetching comparison, not an index-only scan example. I then repeat the five-pass update batch unchanged and compare counter deltas, subtracting the first observation from the second. Don't compare the second phase's cumulative HOT percentage with the first: its old successes are still in the denominator.

These are measured update counts, reproduced in two fresh runs, not throughput benchmarks:

The last column is updates - HOT - cross-page. The final row changed my diagnosis: all successors stayed on-page, yet none were HOT. Space was available; HOT eligibility was gone.

The index also gained something. Before it existed, the lookup used a sequential scan with 435 shared-buffer hits and 9,998 rows filtered out. Afterwards, an index scan returned the same two rows with one shared hit and two shared reads.

I wouldn't paint these buffer accesses as a portable latency ratio or a count of physical device reads. This single-client test shows the mechanism; row width, update distribution, concurrent snapshots and cache state will change the numbers.

With a B-tree on progress, each increment changes an indexed value. The update is no longer HOT eligible even if its successor fits on the same page. Lower fillfactor may improve placement, but it cannot restore that eligibility.

The surprising part is the primary-key index. We didn't touch id, but its entry points to the previous physical version. A non-HOT update needs a successor entry there too. Both full B-tree indexes receive new entries.

The B-tree implementation documentation describes this version churn, including indexes whose logical key didn't change. One more index has changed the work done by the existing one.

This fan out has some good prior art in Adyen's engineering account of HOT and write amplification. I wouldn't translate index maintenance into a fixed number of physical disk writes, though. Buffer changes, WAL records and eventual page writes are different quantities.

The chain still needs an exit

HOT's page-local cleanup is part of why it helps. During normal access, PostgreSQL can prune versions that no snapshot needs and reuse their space without removing an index entry for every intermediate version. An old snapshot can keep versions alive, consume the reserved room, and eventually force a successor onto another page. More space buys time; it doesn't advance the visibility horizon.

After HOT is lost, obsolete heap versions and index entries still need reclamation. Modern B-trees have bottom-up deletion and deduplication that can mitigate version churn, so zero HOT doesn't guarantee runaway index growth. Those mechanisms don't eliminate routine vacuuming, and a high HOT ratio isn't permission to retire autovacuum.

Conclusion

For this counter, I'd keep the B-tree if its measured lookup benefit matters at the actual read frequency. If progress searches are rare, I'd first consider a scan, a lookup by job ID, or updating the indexed progress less frequently where the application permits it. Those choices change either read work or freshness, so they need an explicit workload requirement.

Before lowering fillfactor, I'd compare interval deltas for HOT and cross-page updates, inspect changed columns against every index definition, and check whether old snapshots delay cleanup. If successors move off-page, reserved heap space is worth testing. If they stay on-page but HOT disappears after indexing progress, the decision belongs with that index and the read it earns.