Postgres HOT Updates and Fillfactor: Cheaper UPDATEs

In PostgreSQL an UPDATE never changes a row in place. Because of MVCC, it writes a new version of the row (a tuple) and marks the old one dead; vacuum cleans it up later. (Postgres MVCC explained)

The expensive part isn't the new heap tuple. It's that the new tuple has a new physical location, so every index on the table needs a new entry pointing at it — even indexes on columns you didn't touch. A table with eight indexes pays for nine writes on every update, plus nine times the WAL and nine places for bloat.

HOT (heap-only tuple) updates avoid that. When conditions are right, Postgres updates only the heap, and the indexes keep pointing at the old tuple, which forwards to the new one.

How HOT works

When a row is updated via HOT:

1. The new version is written on the same heap page as the old one.
2. The old tuple's header gets a pointer to the new one, forming a HOT chain within the page.
3. No index entries are added. Index lookups land on the chain's root and follow it to the visible version.
4. Later, when any query reads the page and finds dead tuples in a chain, page pruning reclaims their space immediately — without waiting for a full VACUUM.

That last point matters as much as the index savings: HOT turns update-heavy workloads from "bloat, then vacuum" into "self-cleaning pages". (Postgres vacuum and bloat)

The two conditions

An update is HOT only if both hold:

1. No indexed column changed. If the update modifies any column that appears in any index — key columns, INCLUDE columns, expression index inputs, partial index predicates — it can't be HOT. (Since Postgres 16, columns used only by BRIN indexes no longer block HOT, because BRIN summarises ranges rather than pointing to tuples.)
2. There's free space on the same page for the new version.

Miss either, and you get a normal update with full index maintenance.

Measuring your HOT ratio

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_tup_newpage_upd   -- Postgres 16+: updates that moved to a new page
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC;

• High n_tup_upd, low hot_pct — your most-updated tables are paying full price.
• High n_tup_newpage_upd — updates are failing HOT because pages are full (condition 2).
• Low HOT but low newpage — it's mostly condition 1: you're updating indexed columns.

Fixing condition 1: indexed columns

The classic offender is an updated_at column with an index on it, or a status column indexed for a dashboard. Every touch of the row updates an indexed column, so nothing is ever HOT.

Questions to ask:

• Is the index used? Check pg_stat_user_indexes.idx_scan. Unused indexes cost writes for nothing — drop them. (Database indexes)
• Can a counter or timestamp move out of the hot row? A last_seen_at updated on every request doesn't need to live on users with its five indexes. Put it in a narrow user_activity table with only a primary key.
• Partial index instead of full? An index WHERE status = 'pending' still blocks HOT for updates that change status — a partial index's predicate column counts as indexed — but it's smaller and cheaper to maintain.
• BRIN for append-ordered timestamps. On 16+, a BRIN index on created_at doesn't block HOT and is tiny. (Postgres index types)

Fixing condition 2: fillfactor

By default, tables are packed to 100%: INSERT fills each 8 KB page completely. The first update of any row on a full page has nowhere to put the new version, so it goes to another page — not HOT.

fillfactor reserves space on each page for future updates:

ALTER TABLE accounts SET (fillfactor = 85);
-- Only affects newly written pages. To rewrite existing data:
VACUUM FULL accounts;     -- takes an ACCESS EXCLUSIVE lock; or use pg_repack

Guidelines:

• Insert-only or rarely updated tables: leave at 100.
• Frequently updated rows: 80–90 is a common starting point. Lower if rows are wide or updated many times between vacuums.
• The cost is a bigger table (and slower full scans): fillfactor 80 means ~25% more pages.

Combine with vacuum tuning: pruning reclaims dead HOT tuples, but VACUUM still needs to run to mark space reusable and keep the visibility map current.

Long transactions break HOT too

Page pruning can only remove tuples that are dead to every running transaction. A long-running transaction — or an idle one left open by a connection pool — holds back the cleanup horizon. Dead versions accumulate, pages fill, and updates start spilling to new pages even with a good fillfactor. (Postgres timeouts)

SELECT pid, now() - xact_start AS age, state, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 5;

Replication slots and hot_standby_feedback from replicas can hold the horizon back in the same way.

Designing for HOT

For a table that sees heavy updates — sessions, job queues, counters, inventory — design with HOT in mind:

1. Keep the index set minimal; every index is a potential HOT blocker.
2. Keep frequently changing columns unindexed, or move them to a separate narrow table.
3. Set fillfactor below 100.
4. Keep rows narrow so more versions fit per page; push large blobs to separate tables or let TOAST handle them. (Postgres TOAST)
5. Watch hot_pct after schema changes — a single new index can quietly drop it from 95% to 0%.

A job queue table using SKIP LOCKED is a good example: if status is indexed and every state change touches it, you get no HOT updates and fast bloat. A partial index on just the claimable rows, plus fillfactor around 70–80 and aggressive autovacuum, keeps it healthy. (Postgres SKIP LOCKED job queue)

---

EasySpawn runs Postgres on your own server, so you can tune fillfactor and autovacuum per table — and Claude Code can read pg_stat_user_tables with you to find tables losing their HOT updates. See how it works or join the waitlist.

Related: Postgres MVCC Explained · Postgres Vacuum and Bloat · Postgres Index Types · Postgres TOAST