Govind
← all posts

What VACUUM taught me about batching writes

1 min read postgres · databases

Every Postgres table I've ever inherited had the same invisible reviewer: autovacuum, running behind me, grading my write patterns. It took writing pgstat — an exporter for WAL rates, slot lag and bloat — before I started reading the feedback properly.

Here's the version of the lesson I wish I'd skipped to.

Dead tuples are an invoice

Postgres doesn't overwrite rows in place. Every UPDATE writes a new tuple, and the old one hangs around as garbage until VACUUM collects it. A workload that updates the same row 50 times a minute is, structurally, a workload that writes 50 rows a minute and asks the database to clean up quietly in the background.

If vacuum can't keep up, you don't get an error. You get:

  • tables that grow on disk while pg_stat_user_tables shows stable row counts,
  • index bloat that makes every lookup pay a tax,
  • and eventually wraparound, the maintenance window you can no longer postpone.

The pattern that made it stop

The fix wasn't tuning autovacuum. It was changing what we asked of it — batching:

-- before: 40k single-row updates per minute
UPDATE counters SET value = value + 1 WHERE key = $1;

-- after: one statement per partition batch
UPDATE counters AS c
SET value = c.value + b.value
FROM unnest($1::counter_batch[]) AS b(key, value)
WHERE c.key = b.key;

Same business logic, 1/50th the dead tuples, and an easier time for autovacuum to stay ahead. The dashboards stopped looking like sawtooths.

What I watch now

Write amplification is the number I check first on any new service:

  1. n_tup_upd vs n_tup_hot_upd — HOT updates skip index maintenance; a low HOT ratio means your indexes are getting rewritten constantly.
  2. pg_stat_all_tables.n_dead_tup trended over a week, not a point in time.
  3. WAL bytes generated per business event. This is the honest cost of your write path, and it doesn't lie the way query latencies do.

None of this is exotic. It is just the database telling you exactly what you're doing to it, in tables designed for that purpose, and me finally being bothered to listen.