The update that quietly doubled your write load

Hi there —

Welcome to Issue #5 of Vacuum & Analyze. New topic this time, no series to pick up from — just a mechanism that’s easy to benefit from without understanding, and easy to lose without noticing: the HOT update.

A table gets thousands of UPDATEs per minute. It’s been running fine for months — index size stable, write latency low. Then, after a routine migration that added a new indexed column, write latency creeps up. Index size starts growing steadily. Nobody changed the update pattern. Nobody added more writes. What changed is that updates that used to be cheap quietly became expensive, and the table has no obvious symptom pointing at why.

Misconception #1: “An UPDATE either touches an index or it doesn’t — that’s just how it works”

Not quite — Postgres has a specific optimization for this, and it’s opt-in by default rather than something you configure. It’s called a HOT (Heap-Only Tuple) update.

Normally, updating a row in Postgres writes a new row version and requires updating every index that references that table, even if the UPDATE didn’t touch an indexed column — because the new row version has a new physical location (ctid), and every index entry needs to point somewhere valid.

HOT updates are the exception: if an UPDATE doesn’t change any column referenced by an index, and the new row version fits on the same heap page as the old one, Postgres skips updating the indexes entirely. The new version lives in the same page, and a small chain pointer connects old to new. No index writes, no index bloat from that update, no extra index maintenance during VACUUM.

This is a big deal for write-heavy tables. A table with several indexes can see dramatically fewer index writes per UPDATE when HOT is working — index size stays flat, and write latency stays low.

Misconception #2: “If HOT stops working, something will tell me”

This is the one that costs people weeks of degraded performance before anyone investigates. Nothing errors. Nothing warns. The query planner doesn’t complain. The only signal is a slow drift: index size creeping up, write latency creeping up, and no obvious cause in the query patterns.

HOT updates stop happening the moment either condition breaks:

1. The UPDATE touches an indexed column — even if it’s set to the exact same value it already had. UPDATE orders SET status = 'shipped' WHERE status = 'shipped' still counts as touching the status column if status is indexed, even though the value didn’t change.
2. The new row version doesn’t fit on the same page — if the page is too full (low fillfactor, or the table doesn’t have one configured to leave room), Postgres has to place the new version elsewhere, which breaks the HOT chain and forces normal index updates.

A single migration that adds an index to a frequently-updated column, or ORM code that includes every column in every UPDATE statement regardless of what actually changed, can silently switch a table from mostly-HOT to mostly-not-HOT — and the only trace is in a statistics view almost nobody checks.

The diagnostic that actually matters

Check the HOT update ratio directly — this is the number that tells you what’s actually happening, when nothing else will:

SELECT
  schemaname,
  relname,
  n_tup_upd,
  n_tup_hot_upd,
  round(n_tup_hot_upd * 100.0 / nullif(n_tup_upd, 0), 1) AS hot_update_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY n_tup_upd DESC
LIMIT 20;

A table that used to run at 80-90% HOT updates and has dropped into the 10-20% range is the table to investigate first — that drop is invisible everywhere else, but it directly explains rising index size and write latency on tables where nothing else changed.

If a specific table has a low ratio, check two things: whether a recent migration added an index on a column that gets updated often (even to unchanged values), and whether the table’s fillfactor is high enough to leave room for in-page updates:

SELECT reloptions FROM pg_class WHERE relname = 'your_table';

Keeping HOT updates alive

A few concrete levers, in rough order of impact:

• Lower the table’s fillfactor for write-heavy tables that see frequent UPDATEs. The default is 100 (pack pages full), which leaves no room for in-page updates. Something like 80-90 leaves headroom for new row versions to land on the same page:

ALTER TABLE orders SET (fillfactor = 85);
VACUUM FULL orders; -- required once to apply fillfactor to existing pages

• Audit UPDATE statements for unnecessary column writes. ORMs frequently generate UPDATE table SET col1 = ?, col2 = ?, ... for every column regardless of what changed. If any of those columns are indexed, this kills HOT for that update even when the value is identical. Scoping UPDATEs to only the columns that actually changed is often the single highest-leverage fix.
• Be deliberate about which columns get indexed on high-write tables. Every index on a column that gets updated frequently is a column that can break HOT the moment it’s touched. This doesn’t mean avoid indexing — it means the index-vs-write-frequency tradeoff should be a conscious decision, not an afterthought from a migration.

The mental model

HOT updates are a “the system quietly does the right thing until something removes the conditions for it” kind of optimization. There’s no configuration flag to turn it on — it either applies on a given UPDATE or it doesn’t, based on what the UPDATE actually touches and whether the page has room. That means it can silently degrade after a schema change or an ORM update with no error, no log line, and no obvious symptom other than numbers that slowly get worse. n_tup_hot_upd versus n_tup_upd is the one place that degradation is visible directly — worth checking on your highest-write tables now, before the drift becomes the thing you’re debugging at 2 AM.

Reply and tell me: what’s your HOT update ratio on your busiest table? I read every reply.

— Gio