Fillfactor and HOT Updates #

A queue table’s UPDATE … SET state = 'done' writes 4 kB of WAL per row and dirties four index pages, although state is not indexed at all. The table has five indexes on other columns, and the pages are full: there is no room for the new row version beside the old one, so PostgreSQL places it elsewhere and every index must point at the new location.

Index maintenance cost is usually discussed in terms of index count, as in index maintenance and bloat. The mechanism that avoids it entirely is the heap-only tuple update.

The Condition for a HOT Update #

An update qualifies as HOT when both hold:

  1. No indexed column changes. Every column that appears in any index on the table — key or INCLUDE, including expression index inputs and partial index predicates — has the same value after the update.
  2. The new row version fits on the same heap page as the old one.

A HOT update writes the new version into free space on the same page and links it to the old version with a pointer chain. No index is touched: existing index entries still point at the page’s line pointer, which leads to the current version. Later vacuums, and opportunistic page pruning during reads, reclaim dead versions in the chain without touching indexes either.

Fail either condition and the update is a regular one: a new tuple, possibly on another page, plus one new entry in every index on the table, plus WAL for all of it.

Condition 2 is what fillfactor controls. It is the percentage of each heap page that inserts may fill, default 100 — no reserved space. Setting it to 85 leaves 15% of every page free for future versions of the rows already there. The setting applies to pages written after the change, so existing pages need a rewrite (VACUUM FULL, CLUSTER, or an online tool) to benefit.

Statistics to watch: n_tup_upd and n_tup_hot_upd in pg_stat_user_tables, plus n_tup_newpage_upd from PostgreSQL 16, which counts updates that had to move to a new page.

Same statement, two very different writes Left, HOT update with free space on the page: the new version is written on the same page, linked from the old one, and no index is modified. Right, regular update on a full page: the new version goes to another page, and every one of the five indexes gets a new entry pointing at it. HOT update (room on the page) new version on the same page chained from the old line pointer 0 index entries written page pruning reclaims it later regular update (page full) new version on another page 5 index entries written more WAL, more dirty pages index bloat until vacuum an unindexed column change still costs index writes when the page is full

Annotated EXPLAIN Evidence #

SELECT n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables WHERE relname = 'jobs';
 n_tup_upd | n_tup_hot_upd | n_tup_newpage_upd | hot_pct
-----------+---------------+-------------------+---------
  84120044 |       1204110 |          82915934 |     1.4
-- 1.4% HOT: nearly every update moved to a new page

The write cost, measured on the statement:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE jobs SET state = 'done', finished_at = now() WHERE id = ANY (ARRAY[…10000 ids…]);
ROLLBACK;
Update on jobs  (actual time=1204.6..1204.6 rows=0 loops=1)
  Buffers: shared hit=142108 dirtied=41204 written=12044
  WAL: records=71204 fpi=8210 bytes=48210442
  ->  Index Scan using jobs_pkey on jobs  (actual rows=10000 loops=1)
Execution Time: 1210.4 ms
-- 71,204 WAL records for 10,000 rows: ~7 per row = 1 heap + 5 index + overhead

After ALTER TABLE jobs SET (fillfactor = 85) and a rewrite:

Update on jobs  (actual time=402.1..402.1 rows=0 loops=1)
  Buffers: shared hit=38204 dirtied=10204 written=2104
  WAL: records=11204 fpi=2108 bytes=9204118
Execution Time: 406.8 ms
-- ~1.1 WAL records per row: index maintenance is gone
-- hot_pct after a day of traffic: 96.2
Four numbers that show HOT is broken Four items are annotated. A HOT percentage of 1.4 shows almost no heap-only updates. n_tup_newpage_upd close to n_tup_upd shows versions moving to new pages. Seven WAL records per updated row shows index entries being written. Buffers dirtied far above the row count shows the spread of the writes. hot_pct = 1.4 almost no heap-only updates n_tup_newpage_upd ≈ n_tup_upd new versions land on other pages WAL records 71204 for 10000 rows ≈ 7 writes per row: 5 indexes Buffers dirtied=41204 writes spread across many pages target: HOT percentage above 90 on update-heavy tables

Step-by-Step Resolution #

  1. Measure the HOT ratio per table and rank update-heavy tables by n_tup_upd.

  2. Check whether an indexed column is being updated. Compare the SET list with every index definition, including expression and partial indexes:

    SELECT indexdef FROM pg_indexes WHERE tablename = 'jobs';

    If finished_at is indexed, no fillfactor will make these updates HOT — drop the index if it is unused, or accept the cost.

  3. Set fillfactor on the table and rewrite it so existing pages get the free space:

    ALTER TABLE jobs SET (fillfactor = 85);
VACUUM FULL jobs;          -- takes an exclusive lock; use an online rewrite tool if unacceptable

  4. Choose the value by row width and update frequency. A table whose rows are updated many times between vacuums needs more free space: 70–80 for hot queues, 85–90 for moderately updated tables, 100 for append-only tables where reserved space is pure waste.

  5. Keep vacuum current. HOT chains are only reclaimed by pruning and vacuum; without them the free space fills with dead versions and HOT stops again — see per-table autovacuum settings for hot tables.

  6. Re-measure the HOT ratio after a day of normal traffic, not immediately after the rewrite.

WAL per updated row With fillfactor 100 and five indexes, each updated row generates about 4.8 kilobytes of WAL. At fillfactor 90 it falls to about 1.9 kilobytes. At fillfactor 85 it falls to about 0.9 kilobytes, with a HOT ratio above 96 percent. fillfactor 100 4.8 kB per row fillfactor 90 1.9 kB per row fillfactor 85 0.9 kB per row same statement, same indexes; only the free space per page changed

Before and After #

-- BEFORE: fillfactor 100, hot_pct 1.4
WAL: records=71204 bytes=48210442  Buffers: dirtied=41204                Execution Time: 1210.4 ms

-- AFTER: fillfactor 85 + rewrite, hot_pct 96.2
WAL: records=11204 bytes=9204118   Buffers: dirtied=10204                Execution Time: 406.8 ms

The cost of reserved space #

Fillfactor trades read efficiency for update efficiency. At 85, every sequential scan reads about 18% more pages for the same rows, and the table occupies proportionally more disk and cache. On a table that is read far more than updated, that is a bad trade; on a queue or state table updated many times per row, it is an excellent one. The deciding ratio is updates per row per vacuum cycle: if each row is updated several times before vacuum reclaims the old versions, the page needs space for several versions at once.

Indexes have their own fillfactor, defaulting to 90 for B-trees, which reserves space in leaf pages for future entries and reduces page splits. Lowering it further helps indexes on randomly distributed keys such as UUIDs, where inserts land in the middle of the tree; raising it to 100 suits append-only indexes on monotonically increasing keys, where splits always happen at the right edge.

Common Pitfalls #

Updating an indexed timestamp. updated_at indexes silently disable HOT for every update. Diagnostic signal: HOT ratio near zero despite free space. Fix: drop or reconsider the index.

Setting fillfactor without a rewrite. Existing pages keep their density. Diagnostic signal: no change in HOT ratio for weeks. Fix: VACUUM FULL, CLUSTER or an online rewrite.

Fillfactor on append-only tables. Reserved space is never used. Diagnostic signal: low n_tup_upd and a non-default fillfactor. Fix: leave it at 100.

Expecting HOT to survive vacuum starvation. Free space fills with dead versions. Diagnostic signal: HOT ratio decaying between vacuums. Fix: more aggressive autovacuum for that table.

Frequently Asked Questions #

What is a HOT update in PostgreSQL? #

A heap-only tuple update: the new row version is written on the same heap page as the old one and linked to it, so no index entries are created. It requires that no indexed column changed and that the page has room for the new version.

What does fillfactor do? #

It sets the percentage of each heap page that inserts may fill, leaving the rest free for future row versions on that page. Lower values make HOT updates more likely at the cost of a larger table and more pages read by scans.

Why is my HOT update ratio low even though I do not update indexed columns? #

The pages are probably full. With the default fillfactor of 100 there is no reserved space, so once a page is packed the new version must go elsewhere and every index is updated.

Up: Index Maintenance and Bloat