The MVCC Hangover: Bloat and Vacuuming

The Cost of Multiversioning

MVCC is powerful, but it has a significant operational cost: dead tuples.

• Every UPDATE creates a new row version. The old version is not deleted — it's marked with an xmax value indicating it was superseded.
• Every DELETE marks the row with an xmax value. The row data remains on disk.
• These old, invisible row versions are called dead tuples.

Over time, a high-write table accumulates enormous numbers of dead tuples. This is table bloat:
- The table occupies far more disk space than the live data requires.
- Index entries for dead tuples remain, causing index bloat.
- Sequential scans must skip over dead tuples to find live ones, wasting I/O.
- The query planner may make poor decisions based on inflated table size statistics.

The Cleanup Crew: VACUUM

PostgreSQL's answer to dead tuple accumulation is the VACUUM process. Vacuum scans table pages, identifies dead tuples that are no longer visible to any active transaction, and marks their space as reusable.

There are two variants:

VACUUM (standard):
- Marks dead tuple space as reusable within the same page.
- Does NOT return space to the operating system (the file size stays the same).
- Can run concurrently with normal reads and writes.
- Run automatically by autovacuum in the background.

VACUUM FULL:
- Rewrites the entire table to a new file, compacting it.
- DOES return space to the operating system.
- Requires an exclusive lock on the table — all reads and writes are blocked.
- Use sparingly, during maintenance windows, for severely bloated tables.

Autovacuum: The Automatic Janitor

PostgreSQL's autovacuum daemon runs automatically in the background, triggering vacuum on tables when they accumulate enough dead tuples (configurable via autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor).

A critical DBRE responsibility is ensuring autovacuum is keeping up with the write rate. If autovacuum falls behind, bloat accumulates. If bloat gets severe enough, query performance degrades significantly. pg_stat_user_tables exposes n_live_tup, n_dead_tup, last_autovacuum, and last_autoanalyze per table — ranking by dead-tuple percentage surfaces bloat candidates. A table where that percentage is consistently above 10-20% is a sign that autovacuum is not keeping up and needs tuning.

The Transaction ID Wraparound: The Nuclear Scenario

There is a more severe consequence of failing to vacuum: Transaction ID (XID) wraparound.

XIDs are 32-bit integers. After approximately 2 billion transactions, the XID counter wraps around. PostgreSQL uses a circular comparison to determine which transactions are "in the past" vs. "in the future." If a table has rows with very old XIDs and vacuum has never run on it, PostgreSQL will eventually refuse to accept new transactions to prevent data corruption — it will shut down with a "database is not accepting commands to avoid wraparound data loss" error.

This is a catastrophic failure mode. Autovacuum specifically tracks and prevents this with anti-wraparound vacuums, but DBREs must monitor pg_stat_user_tables.relfrozenxid and the system-level age(datfrozenxid) — ordering pg_database by that age surfaces which databases are closest to the wraparound limit, and an age above roughly 1.5 billion should trigger an alert.