VACUUM, Bloat & XID Wraparound

Overview

MVCC never overwrites a live row in place for an UPDATE. It stamps xmax on the old version and inserts a new one. DELETE only stamps xmax. Those dead versions are the tax: they occupy heap and index until VACUUM proves no snapshot still needs them.

Three problems, one mechanism:

• Bloat — dead tuples and holes after vacuum-too-late; seq scans and cache miss more pages.
• Horizon lag — a single idle-in-transaction session holds back freeze and removal for the whole cluster.
• XID wraparound — 32-bit transaction IDs must be frozen; ignore that and Postgres stops writes to protect you.

This is the ops half of xmin / xmax visibility. Isolation choice does not delete versions; vacuum does.

Why autovacuum plus freeze wins

VACUUM FULL looks like a cleanup hammer. It rewrites the table under an AccessExclusiveLock and returns bytes to the OS. It is the wrong default. The winning path is routine vacuum that reuses pages in place, plus anti-wraparound freeze, plus killing long txns.

1. 1

   dead xmax versions → seq scan pain then wraparound

   Skip vacuum; keep long transactions

   n_dead_tup climbs. Indexes hold dead TIDs. Oldest xmin never freezes. Eventually the cluster refuses INSERT/UPDATE.

2. 2

   horizon = oldest snapshot → dead tuples reusable

   Winner: regular VACUUM + freeze, short txns

   Autovacuum workers reclaim versions no snapshot can see, set freeze XIDs, update the visibility map. idle_in_transaction_session_timeout is part of the vacuum plan.

3. ?

   VACUUM FULL only for true file-shrink

   After a delete of 80% of a table that will not refill, FULL (or CLUSTER / pg_repack) returns space to the OS. Not a substitute for scale_factor tuning on an UPDATE-heavy table that will bloat again tomorrow.

Tuple lifecycle

1. INSERT — new heap tuple, xmin = current XID, xmax = 0 (live).
2. UPDATE — new tuple with new xmin; old tuple xmax = current XID (dead once that XID commits).
3. DELETE — set xmax only; nothing is physically gone.
4. Visibility — a snapshot sees a version if the inserter is committed-as-of-snapshot (or is you) and the deleter is not. Same rule as the hub.

Dead committed versions remain on disk. Indexes still point at them until vacuum. That is heap bloat and index bloat.

HOT (heap-only) updates that stay on the same page reduce index churn. Cold updates that move pages leave more dead index entries.

The vacuum horizon

VACUUM can remove a dead tuple only when every still-running snapshot is recent enough that it cannot see that version. The oldest transaction’s snapshot is the horizon.

A single BEGIN then a coffee break (idle in transaction) pins the horizon. Every other session pays: dead tuples pile up, autovacuum “runs” but cannot prune what that snapshot might still read, disk and cache suffer. This is a fleet incident, not a style note.

Row locks (FOR UPDATE) held across think-time have the same shape: the txn is open, the horizon does not move.

flowchart TB
  app["Application"]
  pg["Heap"]
  av["Autovacuum"]
  dba["DBA"]
  app -->|UPDATE / DELETE| pg
  pg -->|stats: dead| av
  av -->|VACUUM| pg
  av -->|freeze old xmin| pg
  dba -->|VACUUM FULL| pg

Autovacuum

The launcher spawns up to autovacuum_max_workers. Each worker either vacuums (dead tuples), analyzes, or runs anti-wraparound freeze.

Trigger-ish formula: vacuum when

n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

Defaults (50 + 0.2 * live) are too lazy for a 100M-row table that updates 1% — you wait for 20M dead tuples. Per-table storage parameters: lower autovacuum_vacuum_scale_factor on hot tables.

Throttle: vacuum_cost_delay / vacuum_cost_limit (and autovacuum variants) so vacuum yields I/O. maintenance_work_mem sizes sort/index cleanup. autovacuum_naptime is how often the launcher looks.

autovacuum_freeze_max_age (default 200 million) and autovacuum_multixact_freeze_max_age force freeze workers even if dead-tuple counts look fine.

XID wraparound

XIDs are 32-bit. About two billion usable IDs before numbers wrap. Visibility compares ages, not raw integers; that only works if old tuples are frozen (marked older-than-all, FrozenTransactionId) so they stay visible after wrap.

When the oldest unfrozen XID’s age approaches freeze_max_age, autovacuum must freeze. If it cannot (horizon pinned, vacuum starved, autovacuum = off), age keeps climbing. Before wrap would corrupt visibility, Postgres refuses writes. That is a wraparound shutdown: availability incident, not a slow query.

Stages you should name in an interview (numbers are the usual defaults; check your version):

1. Autovacuum freeze — age past autovacuum_freeze_max_age (~200M). Workers go aggressive even if dead-tuple counts look fine.
2. Warnings in the log — age toward ~1B; “database must be vacuumed.” You still have time. Find the oldest backend_xmin / prepared txn / slot and freeze.
3. Wraparound protection — remaining XID budget is critically small; the engine stops INSERT/UPDATE/DELETE (and some DDL) until freeze succeeds. Reads may still work. This is a SEV-1 for a write-heavy service.

Multixacts (used for FOR SHARE and multi-locker states) have their own freeze age. Failure mode: multixact members limit exceeded. Vacuum those tables too. Replication slots and prepared transactions pin XIDs the same way idle sessions do — look there when autovacuum “cannot freeze.”

VACUUM vs VACUUM FULL

The interview trap: “VACUUM shrinks the table.” Regular VACUUM does not. It marks free space for future INSERTs (FSM). If you deleted 90% and will not refill, you need FULL, pg_repack, or a new table.

Visibility map: pages all-visible skip heap fetches on index-only scans. Vacuum is what sets those bits. Skip vacuum, lose index-only.

Free space map: where to put the next INSERT without extending the file.

Bloat patterns and monitoring

• High-frequency UPDATE of wide rows → many dead versions per live row.
• DELETE without INSERT → holes vacuum can reuse but FULL would shrink.
• Long reports / SSI transactions / orphaned sessions → horizon stall.
• Indexes: dead TIDs until vacuum; bloat estimates from pg_class.relpages vs live tuples.

Watch:

• pg_stat_user_tables: n_dead_tup, n_live_tup, last_autovacuum, vacuum_count
• age(datfrozenxid) / age(relfrozenxid) — wraparound fuel gauge
• pg_stat_activity: idle in transaction, old backend_xmin / xact_start
• relation size vs live tuples for bloat

Alert before freeze_max_age, not after writes fail. dead / (live+dead) > 0.2 on a hot table is a tuning ticket, not a FULL panic.

-- Health snapshot (run on a replica or with a short timeout).
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, age(relfrozenxid) AS xid_age
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT datname, age(datfrozenxid) AS db_xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

The first query is bloat. The second is wraparound. A table with low n_dead_tup can still have a dangerous xid_age if it is insert-mostly and never frozen.

Tuning knobs

• Lower autovacuum_vacuum_scale_factor (and maybe threshold) per hot table.
• Raise autovacuum_max_workers carefully (they share I/O).
• vacuum_cost_limit up if vacuum never finishes; down if it fights OLTP.
• maintenance_work_mem for big indexes.
• idle_in_transaction_session_timeout — vacuum policy in disguise.
• Never leave autovacuum = off on a primary that takes writes.

Partitioning shrinks vacuum scope: a partition’s freeze and bloat are local. Still freeze the cluster’s datfrozenxid.

In-memory vacuum (run this)

Simulate xmin/xmax, a horizon, freeze, wraparound refusal. I/O contract: heap + snapshots in; prune/freeze/refuse-writes out.

Press Run. Snippets must be self-contained — no network, files, or native modules.

Interview Q&A

Pitfalls

Go Deeper

• PostgreSQL — Routine vacuuming
• PostgreSQL — VACUUM
• PostgreSQL wiki — Transaction ID Wraparound Failures

Cluster: MVCC hub · Isolation levels · SSI vs SI · FOR UPDATE · Phantoms vs write skew

One-line takeaway: MVCC versions live until the oldest snapshot lets go — vacuum reuses that space and freezes XIDs; lag plus long txns become bloat and then a wraparound write freeze.