PostgreSQL HOT updates and fillfactor: reduce bloat and WAL on update-heavy tables

#When to use this

You have a table that's updated constantly, such as job status, balances or session state. Indexes grow far faster than the data, autovacuum never seems to finish, WAL volume and replica lag are high and updates slow down at peak times.

The cause is often a low rate of HOT updates.

#What a HOT update is

PostgreSQL's MVCC writes a new row version for each update. Normally, every index on the table then needs a new entry for that version, even for indexes on columns that didn't change. That's "write amplification".

A Heap-Only Tuple (HOT) update skips it. Two conditions must hold:

1. No column used by any index changes. That includes expression and partial index columns, and predicates.
2. There's free space on the same heap page for the new version.

With HOT, dead versions on a page can also be cleaned up by page pruning during normal reads, without waiting for vacuum.

#Step 1: measure

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_live_tup,
       n_dead_tup,
       pg_size_pretty(pg_table_size(relid))   AS table_size,
       pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_stat_user_tables
WHERE n_tup_upd > 10000
ORDER BY n_tup_upd - n_tup_hot_upd DESC
LIMIT 20;

PostgreSQL 16 and later add n_tup_newpage_upd, which counts updates that had to move to another page. It's a direct signal of missing page space:

SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_user_tables
WHERE relname = 'jobs';

How to read it:

These counters are cumulative since the last stats reset, so sample them twice during real load and compare the difference. That gives you today's behavior instead of a historical average.

#Step 2: find which indexed columns get updated

List every indexed column, including expressions and predicates:

SELECT i.relname AS index_name,
       pg_get_indexdef(ix.indexrelid) AS definition,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(ix.indexrelid)) AS size
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_stat_user_indexes s ON s.indexrelid = ix.indexrelid
WHERE ix.indrelid = 'public.jobs'::regclass
ORDER BY s.idx_scan;

Compare these with what your updates change:

SELECT calls, rows, left(query, 120) AS query
FROM pg_stat_statements
WHERE query ILIKE 'update%jobs%'
ORDER BY calls DESC
LIMIT 10;

A very common example: updated_at has an index and gets set on every update. Every update is then non-HOT, however much free space there is.

#Step 3: remove or redesign blocking indexes

An index with idx_scan = 0 over a full business cycle is a candidate for removal:

DROP INDEX CONCURRENTLY IF EXISTS idx_jobs_updated_at;

Warning

Check before you drop. idx_scan is per-instance, so a standby's reads don't show on the primary. Month-end jobs may also use an index that looks idle. Indexes that enforce a primary key, unique constraint or exclusion constraint must stay. Keep the definition so you can recreate it, and check index tuning.

Other options if you need the query:

Splitting works well, because the hot-update columns get a small table with few indexes:

CREATE TABLE job_state (
  job_id     bigint PRIMARY KEY REFERENCES jobs(id),
  status     text NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
) WITH (fillfactor = 70);

#Step 4: tune fillfactor

fillfactor reserves room on each page for updates. The default is 100 for tables, so new rows pack each page full.

ALTER TABLE jobs SET (fillfactor = 80);

Starting points:

Lower values cost space: 70 means the table is at least about 40% larger, and scans read more pages.

Tip

Changing fillfactor affects only pages written from now on. Existing full pages stay full until they're rewritten.

A plain VACUUM frees space within pages, but it doesn't rewrite them to the new fillfactor. To apply the setting to existing data, rewrite the table. You can use pg_repack, which works online:

pg_repack --dbname=app --table=public.jobs --jobs=2 --dry-run
pg_repack --dbname=app --table=public.jobs --jobs=2

Caution

VACUUM FULL and CLUSTER take an ACCESS EXCLUSIVE lock and block all access until they finish. They also need extra disk space for a full copy. Avoid them on a busy table outside a maintenance window. pg_repack needs its extension installed, extra disk and a primary key or unique index. Test it first, and watch out for its brief locks.

#Step 5: make sure pruning can run

Long transactions hold back the cleanup horizon, so dead versions can't be pruned, and pages stay full:

SELECT pid, state, now() - xact_start AS xact_age,
       backend_xmin, left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start NULLS LAST
LIMIT 10;

SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;

SELECT gid, prepared FROM pg_prepared_xacts;

Also check hot_standby_feedback on replicas, which can hold back cleanup on the primary. See idle-in-transaction sessions and bloat and autovacuum.

Tune autovacuum for this table, so that free space becomes reusable sooner:

ALTER TABLE jobs SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.05
);

#Step 6: verify the gain

Take a before and after sample under comparable load:

SELECT pg_current_wal_lsn();
-- Wait 5 minutes, then:
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '<earlier_lsn>'));

To check page details during analysis, use pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('jobs');
SELECT * FROM pgstatindex('jobs_pkey');

These scan the whole relation, so run them with care on large tables.

#Prevent it next time

• Ask about update patterns during schema review, and don't index updated_at by default.
• Track HOT % and index size for your busiest tables.
• Set fillfactor when the table is created, when you expect heavy updates.
• Review unused indexes each quarter.
• Related: replication lag and memory settings.