PostgreSQL MVCC, bloat, autovacuum and XID wraparound

What it is

PostgreSQL implements multi-version concurrency control by never updating a row in place. An UPDATE writes a new row version and marks the old one dead; a DELETE just marks it dead. Every row version carries two hidden system columns:

SELECT xmin, xmax, id, name FROM users WHERE id = 42;

 xmin  | xmax  | id |  name
-------+-------+----+--------
 88410 |     0 | 42 | alice

xmin: the transaction ID that created this version.
xmax: the transaction ID that deleted or superseded it (0 if still live).

A transaction sees a row version if xmin is committed and visible to its snapshot and xmax is not. That is the whole visibility rule, and every consequence on this page follows from it: readers never block writers and writers never block readers, because they are looking at different physical rows.

The costs are three, and they are all the same cost seen from different angles:

Symptom	Cause
Bloat	Dead row versions occupy pages until vacuumed
Autovacuum load	Something must find and reclaim them, continuously
XID wraparound	Transaction IDs are 32-bit and wrap; vacuum must freeze old rows before they do

What it is confused with: VACUUM is not VACUUM FULL, and the difference matters enormously. Plain VACUUM marks dead tuples' space reusable within the table, online, with no exclusive lock. VACUUM FULL rewrites the entire table to a new file, returning space to the operating system, and takes an ACCESS EXCLUSIVE lock that blocks everything including reads for the duration. Running VACUUM FULL on a large table in production is a full outage of that table.

The problem it solves

MVCC solves the reader-writer conflict without locks. In a lock-based system, a long report reading a table blocks writers, or writers block the report. In PostgreSQL both proceed: the report sees the snapshot it started with, writers create new versions the report cannot see.

The problem MVCC creates is that dead versions accumulate, and three failures follow.

Bloat degrades everything. A table where 80 percent of pages hold dead tuples reads five times as many pages for the same live data. The buffer cache holds mostly garbage. Sequential scans get slower in direct proportion. Index scans get slower too, because indexes point at dead tuples that must be visited and rejected.

Long transactions block reclamation. A dead tuple can only be removed once no snapshot could still need it. One idle transaction open for six hours prevents vacuum from cleaning any tuple that died in those six hours, across the entire database, not just the tables that transaction touched. This is the single most common cause of runaway bloat and it is usually an application connection that ran a BEGIN and then went to sleep.

XID wraparound is a hard stop. Transaction IDs are 32 bits, giving about 4 billion values, and PostgreSQL compares them modularly: roughly 2 billion in the past and 2 billion in the future. A row whose xmin is more than 2 billion transactions old would appear to be in the future and become invisible, which is silent data loss. To prevent that, PostgreSQL refuses to accept new transactions when the oldest unfrozen XID approaches the limit:

ERROR: database is not accepting commands to avoid wraparound data loss in database "prod"
HINT: Stop the postmaster and vacuum that database in single-user mode.

That is a full outage requiring single-user-mode recovery, and it is entirely preventable.

Mechanics

How a dead tuple becomes reusable space

1. UPDATE users SET name='bob' WHERE id=42;
     - old version: xmax = 91007  (dead once 91007 commits)
     - new version: xmin = 91007  (appended, possibly to a different page)

2. The old version is DEAD but still occupies its slot.

3. VACUUM runs:
     - Determines the oldest snapshot any backend could still need (the "xmin horizon")
     - Any tuple whose xmax is older than that horizon is removable
     - Removes index entries pointing to it, then marks its line pointer reusable
     - Updates the free space map so future inserts can use the space

4. The space is now reusable BY THIS TABLE. It is NOT returned to the OS.

Step 3's horizon is the crux. Compute what is holding it back:

-- What is preventing vacuum from cleaning up, right now?
SELECT pid, state, age(backend_xmin) AS xmin_age,
       now() - state_change AS idle_for, query
  FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL
 ORDER BY age(backend_xmin) DESC
 LIMIT 5;

Four things hold the horizon back, and all four must be checked:

Long-running transactions, including idle in transaction.
Replication slots with a lagging or disconnected consumer (pg_replication_slots.xmin). A slot for a CDC connector that has been down for a day holds the horizon for a day. This connects directly to CDC and the dual-write problem: the slot that retains WAL also retains dead tuples.
Standbys with hot_standby_feedback = on, which propagate their query snapshots back to the primary so long queries on a replica hold the primary's horizon.
Prepared transactions left behind by a failed two-phase commit (pg_prepared_xacts), which hold their XID forever until explicitly resolved.

HOT updates: the optimisation that avoids most of this

Heap-Only Tuple updates are the reason well-designed PostgreSQL tables bloat far less than the theory suggests. If an update changes no indexed column and the new version fits on the same page, PostgreSQL chains the new version to the old within the page and writes no index entries at all.

Normal update:  new heap tuple + a new entry in EVERY index    (expensive)
HOT update:     new heap tuple on the same page, chained, NO index writes

Two conditions, and both are actionable:

Do not index columns that change frequently. An index on last_seen_at or updated_at converts every update into a non-HOT update, adding an index write per index and preventing in-page cleanup. That single index can be the difference between a table that maintains itself and one that bloats.

Leave free space on the page so the new version fits:

-- For an update-heavy table: keep 20% of each page free for HOT updates.
ALTER TABLE sessions SET (fillfactor = 80);

The default fillfactor of 100 packs pages full, so an update must go to a different page and cannot be HOT. For an update-heavy table, 70 to 85 is a large win, and it costs disk on a table whose problem was never disk.

-- Are your updates actually HOT?
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 > 0
 ORDER BY n_tup_upd DESC LIMIT 10;

Below about 50 percent HOT on an update-heavy table, look for an index on a mutable column.

Autovacuum, and why its defaults are wrong for large tables

Autovacuum triggers when dead tuples exceed a threshold:

threshold = autovacuum_vacuum_threshold          (default 50)
          + autovacuum_vacuum_scale_factor       (default 0.2)
          * reltuples

The scale factor is the problem. Twenty percent of a 500-million-row table is 100 million dead tuples before autovacuum starts, by which point the table is enormously bloated and the vacuum itself is a long, heavy operation.

-- Large tables: a fixed threshold rather than a proportion.
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.0,
  autovacuum_vacuum_threshold    = 100000,     -- vacuum every 100k dead tuples
  autovacuum_analyze_scale_factor = 0.0,
  autovacuum_analyze_threshold    = 50000
);

The second defaults problem is throttling. Autovacuum sleeps to limit its I/O impact, and the defaults were set for hardware from a very different era:

# postgresql.conf, for modern SSD hardware
autovacuum_vacuum_cost_delay = 2ms      # PG12+ default; was 20ms before
vacuum_cost_limit            = 2000     # default 200: autovacuum can do 10x more work
autovacuum_max_workers       = 6        # default 3
autovacuum_naptime           = 15s      # default 60s

vacuum_cost_limit = 200 on an NVMe machine means autovacuum is throttled to a small fraction of the disk's capability. Raising it is the single highest-impact autovacuum change on modern hardware, and it is the one most often left alone because it looks like a safety setting.

Freezing and wraparound

Beyond removing dead tuples, vacuum freezes old live tuples: marking them as visible to all transactions, which removes their dependence on xmin and takes them out of the wraparound calculation.

-- How close is each table to the wraparound limit?
SELECT relname,
       age(relfrozenxid) AS xid_age,
       round(100.0 * age(relfrozenxid) / 2000000000, 1) AS pct_to_wraparound
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE relkind IN ('r','m') AND n.nspname NOT IN ('pg_catalog','information_schema')
 ORDER BY age(relfrozenxid) DESC LIMIT 10;

The escalation ladder, and knowing where you are on it is the point:

age(relfrozenxid) > autovacuum_freeze_max_age (200M default)
    -> An ANTI-WRAPAROUND autovacuum starts. It CANNOT be cancelled by a
       normal lock conflict and will not yield. It runs even if autovacuum
       is disabled entirely.

age > 10M remaining (i.e. ~2 billion)
    -> WARNING in the log on every commit.

age > ~2 billion
    -> Database REFUSES all new transactions. Single-user mode required.

The anti-wraparound vacuum is what surprises people. It appears without being scheduled, cannot be stopped by the usual means, and on a large cold table it reads the entire table. Teams see unexplained I/O and a vacuum they did not start and cannot cancel, and killing it just means it restarts. Since PostgreSQL 9.6 the visibility map tracks all-frozen pages so repeat freeze vacuums skip them, which makes the second one far cheaper than the first, and the first one on a large never-frozen table is still heavy.

The mitigation is to freeze incrementally rather than let it accumulate:

-- Start freezing much earlier, so work is spread out rather than arriving at once.
ALTER TABLE big_table SET (autovacuum_freeze_min_age = 10000000);

A worked example: a 340 GB table holding 40 GB of data

An order management system. PostgreSQL 14, 64-core machine, NVMe storage, an orders table with about 90 million live rows.

Symptoms:

orders table size on disk:        340 GB
estimated live data:              ~40 GB
p99 on a common indexed query:    1,800ms
sequential scan of orders:        22 minutes
autovacuum on orders:             running almost continuously, never finishing
disk usage:                       89% and climbing

Diagnosis, in the order it was found.

First, the bloat estimate confirmed the ratio:

SELECT relname,
       pg_size_pretty(pg_relation_size(oid)) AS size,
       n_live_tup, n_dead_tup,
       round(100.0*n_dead_tup/NULLIF(n_live_tup+n_dead_tup,0),1) AS dead_pct
  FROM pg_class c JOIN pg_stat_user_tables s ON s.relid = c.oid
 WHERE relname = 'orders';

 relname |  size  | n_live_tup | n_dead_tup | dead_pct
---------+--------+------------+------------+----------
 orders  | 340 GB |   90114302 |  684201855 |     88.4

684 million dead tuples. Then the horizon query found why they were not being reclaimed:

 pid  |        state        | xmin_age  |   idle_for    | query
------+---------------------+-----------+---------------+---------------------
 8841 | idle in transaction | 412885109 | 31 days 04:12 | BEGIN

A connection had issued BEGIN and nothing else, 31 days earlier. It came from a reporting tool whose connection pool opened a transaction on checkout and, because that particular connection was never used again, never committed. The transaction held the xmin horizon for 31 days, so no tuple that died in 31 days could be reclaimed, in the entire database.

Autovacuum was running constantly and accomplishing nothing: it scanned the table, found 684 million dead tuples, determined that none were removable because of the horizon, and finished having freed nothing.

Second contributing cause, found by the HOT query:

 relname | n_tup_upd  | n_tup_hot_upd | hot_pct
---------+------------+---------------+---------
 orders  | 1204885012 |      18441029 |     1.5

1.5 percent HOT. There was an index on updated_at, a column touched by every single update, so no update could be HOT, and fillfactor was the default 100, so no page had room anyway.

The fixes, in the order applied.

-- 1. Kill the idle transaction. Immediate: the horizon moves 31 days forward.
SELECT pg_terminate_backend(8841);

Within four hours autovacuum reclaimed the dead tuples, and the table dropped from 340 GB to about 96 GB of allocated space with 40 GB live. Space was reusable but not returned to the OS, which is what plain vacuum does.

-- 2. Prevent recurrence at the database level, not by asking people to be careful.
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
ALTER SYSTEM SET statement_timeout = '120s';
SELECT pg_reload_conf();

That setting alone would have prevented the entire incident, and it is off by default.

-- 3. Make updates HOT.
DROP INDEX idx_orders_updated_at;              -- nothing queried it; it was "for later"
ALTER TABLE orders SET (fillfactor = 85);

-- 4. Autovacuum sized for the table and the hardware.
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.0,
  autovacuum_vacuum_threshold    = 200000
);
ALTER SYSTEM SET vacuum_cost_limit = 2000;     -- was 200, on NVMe
ALTER SYSTEM SET autovacuum_max_workers = 6;

-- 5. Reclaim the 56 GB of allocated-but-empty space, online.
pg_repack -t orders -d prod

pg_repack rebuilds the table into a new file and swaps it, taking a brief exclusive lock only at the swap, rather than VACUUM FULL's hours-long lock on a 96 GB table.

Measured after:

                              before        after
orders table size             340 GB        41 GB
dead tuple percentage         88.4%         2-4% (steady)
HOT update percentage         1.5%          94%
p99 indexed query             1,800ms       24ms
sequential scan               22 min        2.7 min
autovacuum on orders          continuous    ~90s, every few minutes
disk usage                    89%           31%

The p99 improvement of 75x came from reading 8x fewer pages, plus a buffer cache that now held live data instead of dead tuples.

Two things are worth separating here, because the team initially conflated them. The idle transaction caused the acute incident: 31 days of unreclaimable garbage. The missing HOT updates caused the chronic condition: even with vacuum working perfectly, 1.5 percent HOT means every update writes index entries and cannot clean up in-page, so the table was always going to bloat, just more slowly. Fixing only the first would have brought the table back and left it degrading again.

The idle_in_transaction_session_timeout line is the one to remember. It is one setting, it is off by default, and it converts an unbounded database-wide failure into a five-minute connection error that the application's own retry logic handles.

Production evidence

PostgreSQL's documentation on routine vacuuming states the wraparound risk explicitly and describes the failure mode (the database refusing commands) in the manual rather than in a wiki, which is unusual placement and reflects severity.

idle_in_transaction_session_timeout was added in PostgreSQL 9.6, and the commit discussion is essentially the failure above: connection poolers and ORMs leaving transactions open, holding the xmin horizon indefinitely, with no way for the database to defend itself.

pg_repack (originally pg_reorg, from NTT) exists because VACUUM FULL's exclusive lock makes it unusable on production tables. That a widely-used external tool exists solely to work around a built-in command's locking is a strong signal about how often people need to reclaim space online.

Amazon RDS and Aurora emit CloudWatch metrics for MaximumUsedTransactionIDs, and AWS publishes guidance on wraparound prevention. Managed providers monitoring this specific counter is evidence that customers hit it.

GitLab, Sentry and Heroku have all published post-mortems involving PostgreSQL bloat or wraparound. The recurring shape is the same: a long-running or idle transaction, autovacuum unable to reclaim, and the discovery only when disk or performance forced an investigation.

PostgreSQL 13 added autovacuum_vacuum_insert_threshold, addressing insert-only tables that never triggered autovacuum (no dead tuples) and therefore never got frozen or got their visibility map updated, which meant index-only scans did not work and wraparound crept up invisibly. That gap existed for many years and is worth knowing if you run an append-only table on an older version.

The debate

Is MVCC-by-copying the right design? The alternative is an undo log (Oracle, MySQL's InnoDB), where the current row is updated in place and old versions live in a separate undo segment. Trade: InnoDB's tables do not bloat from updates the way PostgreSQL's do, and it pays for it with undo-log growth, more complex rollback, and the "snapshot too old" class of error when a long query outlives the undo it needs. PostgreSQL's design makes rollback free (just abandon the new versions) and makes cleanup an ongoing background cost. Neither is strictly better, and the practical consequence is that PostgreSQL requires vacuum tuning as an operational discipline in a way InnoDB does not.

Should you tune autovacuum per table? Yes, for any large or write-heavy table, and the defaults are genuinely wrong there. A 20 percent scale factor on a 500-million-row table means 100 million dead tuples before vacuum starts. Set autovacuum_vacuum_scale_factor = 0 and a fixed threshold on big tables and leave the defaults for small ones. This is one ALTER TABLE per table and it is the highest-value routine tuning in PostgreSQL.

Should vacuum_cost_limit be raised? On SSD or NVMe, yes, substantially. The default of 200 dates from spinning disks and throttles autovacuum to a small fraction of what modern storage sustains. Raising it to 1000 to 4000 lets vacuum keep up. The counter-argument is that vacuum then competes with foreground traffic for I/O, which is real, and the answer is that vacuum falling behind competes with foreground traffic too, by making every query read more pages. I would rather pay a controlled, continuous cost.

VACUUM FULL or pg_repack? pg_repack, essentially always, on any table where an outage matters. VACUUM FULL takes ACCESS EXCLUSIVE for the whole rewrite, blocking reads as well as writes, which on a 100 GB table is hours. pg_repack builds a copy with triggers capturing concurrent changes and takes a brief exclusive lock only for the final swap. The cost is that it needs disk for the duplicate and it is an extension you must install.

Is hot_standby_feedback worth its cost? It prevents query cancellations on standbys by having them report their snapshots to the primary, and it therefore lets a long analytics query on a replica hold the primary's xmin horizon. My position: leave it off and set max_standby_streaming_delay instead, so replica queries get cancelled rather than the primary bloating. A cancelled report on a replica is visible and retryable; primary-side bloat caused by a replica is neither.

Follow-up Q&A

"A table is bloated. Walk me through the diagnosis."

Confirm the bloat first with n_dead_tup against n_live_tup and the on-disk size, because "the table is big" and "the table is bloated" are different problems. Then find what is holding the xmin horizon, which is the reason dead tuples are not being reclaimed: query pg_stat_activity for the oldest backend_xmin, and check pg_replication_slots and pg_prepared_xacts too, because a stale slot or an orphaned prepared transaction holds it just as effectively as an idle session. Then check whether autovacuum is even triggering: the default 20 percent scale factor on a large table means it starts very late. And check the HOT ratio, because a low one means the table generates far more garbage per update than it needs to.

"What is XID wraparound and what happens?"

Transaction IDs are 32-bit and compared modularly, so a row older than about 2 billion transactions would appear to be in the future and become invisible: silent data loss. To prevent that, PostgreSQL refuses new transactions as the oldest unfrozen XID approaches the limit, which is a full outage requiring single-user mode. Before that, an anti-wraparound autovacuum starts automatically, cannot be cancelled by normal lock conflicts, and runs even if autovacuum is disabled. Vacuum prevents all of this by freezing old tuples, which removes them from the calculation. Monitor age(relfrozenxid) per table against autovacuum_freeze_max_age.

"What is a HOT update and why does it matter?"

If an update changes no indexed column and the new version fits on the same page, PostgreSQL chains it within the page and writes no index entries at all. That avoids one write per index and allows in-page cleanup without a full vacuum. The two things that break it are an index on a frequently-updated column (an index on updated_at makes every update non-HOT) and fillfactor = 100 leaving no room on the page. Getting a table from 1.5 percent to 94 percent HOT changes its bloat profile completely.

"An idle transaction has been open for hours. What is the actual harm?"

It holds the xmin horizon, so no tuple that died after it started can be reclaimed anywhere in the database, not only in tables it touched. Autovacuum still runs, scans, finds dead tuples and determines none are removable, so it burns I/O accomplishing nothing. Bloat grows for as long as the transaction lives. The fix is idle_in_transaction_session_timeout, which is off by default and turns an unbounded database-wide problem into a connection error the application retries.

"VACUUM or VACUUM FULL?"

Plain VACUUM marks space reusable within the table, runs online with no exclusive lock, and is what autovacuum does. VACUUM FULL rewrites the table into a new file, returns space to the OS, and holds ACCESS EXCLUSIVE for the entire rewrite, blocking reads as well as writes. On a large table in production that is an outage, so the practical answer is pg_repack, which achieves the same reclamation with only a brief lock at the swap.

"How would you tune autovacuum for a 500-million-row append-mostly table?"

Fixed thresholds rather than scale factors, because 20 percent of 500 million is 100 million dead tuples before it starts: autovacuum_vacuum_scale_factor = 0 and autovacuum_vacuum_threshold around 100,000 to 500,000. Raise vacuum_cost_limit well above the default 200 if the storage is SSD. And on PostgreSQL 13+ set autovacuum_vacuum_insert_threshold, because an append-only table produces no dead tuples and therefore never triggers a normal autovacuum, so it never gets frozen and never gets its visibility map updated, which breaks index-only scans and lets wraparound age creep up unnoticed.

Common misconceptions

"VACUUM returns disk space to the operating system." Plain VACUUM marks space reusable within the table. Only VACUUM FULL, pg_repack or a CLUSTER returns it. A vacuumed table stays the same size on disk and stops growing.

"Autovacuum handles everything." It handles it with defaults tuned for small tables and old hardware. On a large table, a 20 percent scale factor and a vacuum_cost_limit of 200 mean it starts too late and works too slowly.

"Bloat is caused by deletes." Updates cause far more bloat in practice, because every update creates a dead version, and updates are usually far more frequent than deletes.

"An idle transaction only affects tables it touched." It holds the xmin horizon for the entire database. A BEGIN with no statements blocks reclamation everywhere.

"XID wraparound is a theoretical concern." It takes down production databases regularly, which is why managed providers publish a dedicated metric for it. The anti-wraparound vacuum that precedes it is itself disruptive: unschedulable, uncancellable, and heavy on a large cold table.

Interview delivery note

Say this verbatim: "Bloat is not really a vacuum problem, it is usually an xmin horizon problem. Autovacuum can run continuously and reclaim nothing if one idle transaction, a stale replication slot, or an orphaned prepared transaction is holding the horizon, because no tuple that died after that point is removable anywhere in the database." That reframes the question from "tune autovacuum" to "find what is blocking it," which is the diagnosis that actually resolves it.

The senior-versus-staff separator is naming all four holders of the horizon. A senior engineer finds the long-running query in pg_stat_activity. A staff engineer also checks pg_replication_slots (a CDC connector that has been down for a day holds a day of dead tuples, which connects bloat to the same slot that retains WAL), pg_prepared_xacts for orphaned two-phase transactions, and whether hot_standby_feedback is letting a replica's long query hold the primary's horizon. Three of those four are invisible if you only look at active queries.

The second signal is HOT updates. Saying "I would check n_tup_hot_upd against n_tup_upd, and if it is low, look for an index on a mutable column like updated_at and a fillfactor of 100" shows you understand the chronic cause rather than only the acute one.

The Staff & Team Lead Interview Reference