Your Postgres UPDATE Rewrites Every Index

Most UPDATEs don't touch indexed columns, yet Postgres rewrites every index entry — unless a HOT update fires. One table setting decides.

Dafallah Ibrahim

— 2 min read
Share

Every UPDATE in Postgres writes a new row version into the heap. What surprises most engineers: by default it also writes a new entry into every index on that table, even the ones whose columns you didn't touch.

A table with six indexes pays seven writes per update. Postgres has a fast path that skips all six, and whether it fires is mostly decided by one storage setting almost nobody changes.

Why this matters

Index entries point at a row's physical location, not a logical ID. Every update moves the row, so every index needs a new pointer. You pay for that in WAL volume, buffer churn, and slow index bloat that eventually demands a REINDEX.

On update-heavy tables this dominates the write budget. A heartbeat table with eight indexes and one update per device per second burns hundreds of millions of pointless index writes per day, plus the vacuum work to clean them up.

How it works

Postgres has an optimization called HOT: heap-only tuple updates. It fires when two conditions hold — the update changes no indexed column, and the new row version fits on the same 8KB page as the old one.

When both are true, Postgres stores the new version next to the old one and leaves a forward pointer in the old tuple. Indexes keep pointing at the original location; scans follow the chain. Zero new index entries. Cleanup becomes cheap page-local pruning instead of dead tuples scattered across every index.

The catch is page space. Tables default to fillfactor = 100, packing pages completely full. If the new version doesn't fit, HOT is off and you're back to rewriting every index. Setting fillfactor to 70–90 leaves slack on each page so updated versions land beside their ancestors.

Where this helps

  • Heartbeat and last_seen tables — devices ping every few seconds, and the churny columns are rarely the indexed ones.
  • Session and state stores — flip an expiry or status flag without touching the keys used for lookups.
  • Counter and metrics rollups — increment-and-rewrite workloads where the key never changes after insert.

Watch out

Update an indexed column and HOT is impossible for that update. If you index the column you constantly rewrite, you've opted out by design.

ALTER TABLE ... SET (fillfactor = 70) only affects pages written afterward. Existing full pages stay full until the table is rewritten — pg_repack does it without a long lock; VACUUM FULL does it with one you'll regret.

Lower fillfactor costs read density: same data spread over more pages means more pages to scan and cache. Skip it on append-only tables; they never update, so they'd just waste space.

Try it yourself

-- How many of your updates are HOT right now?
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
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY n_tup_upd DESC
LIMIT 10;

-- Give an update-heavy table room for HOT:
ALTER TABLE device_heartbeats SET (fillfactor = 70);

-- Then rewrite so old pages get the space (online):
-- pg_repack -t device_heartbeats -d mydb

TL;DR

  • What changed: every UPDATE rewrites an entry in each index unless a HOT update fires — same page, no indexed column touched.
  • Why it matters: HOT turns update cost from O(number of indexes) into roughly one page write, cutting WAL and index bloat on hot tables.
  • Try today: check n_tup_hot_upd / n_tup_upd on your busiest table. A low ratio means repack with fillfactor — or stop indexing the column you keep rewriting.

Read more