PostgreSQL MVCC and VACUUM, in depth: tuple versions, the xmin horizon, autovacuum arithmetic and freezing

What an UPDATE leaves behind

Every heap tuple carries a header. The two fields that matter here are xmin, the ID of the transaction that inserted this version, and xmax, the ID of the transaction that deleted or replaced it, or zero. A third, t_ctid, points to the newer version when there is one. The pageinspect extension shows them directly:

CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE TABLE acct (id int PRIMARY KEY, balance int) WITH (fillfactor = 100);
INSERT INTO acct VALUES (1, 100);        -- run as transaction 1001
UPDATE acct SET balance = 90 WHERE id = 1;   -- transaction 1005

SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('acct', 0));

-- illustrative output
 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
  1 |   1001 |   1005 | (0,2)    -- old version, deleted by 1005, points to new
  2 |   1005 |      0 | (0,2)    -- current version

After one update the page holds two versions. The old one is still physically there with its xmax set, and every index entry pointing at it still exists unless the update was a heap-only tuple update, HOT, which avoids new index entries when no indexed column changed and the page has room. Multiply this by every update on a busy table and the cost of MVCC is clear: the table and its indexes accumulate versions that no transaction will ever read again.

Snapshots and visibility

A snapshot is three things: xmin, the oldest transaction still running when it was taken; xmax, one past the newest transaction ID assigned; and xip, the list of IDs in between that were still in progress. SELECT pg_current_snapshot(); prints it, for example 1000:1006:1003. Whether a transaction committed or aborted is recorded in the commit log, pg_xact, and cached on the tuple as hint bits on first check.

# Simplified visibility check for a tuple against a snapshot (xmin, xmax, xip).
# Real code also handles subtransactions, hint bits and the current transaction.
def committed_before(xid, snap):
    if xid >= snap.xmax:            # started after the snapshot was taken
        return False
    if xid in snap.xip:             # still running when the snapshot was taken
        return False
    return clog_status(xid) == "committed"

def visible(tup, snap):
    if not committed_before(tup.xmin, snap):
        return False                # inserter not visible: row did not exist yet
    if tup.xmax == 0 or clog_status(tup.xmax) == "aborted":
        return True                 # never deleted, or the delete rolled back
    return not committed_before(tup.xmax, snap)   # deleted after our snapshot

Read Committed takes a new snapshot for each statement; Repeatable Read and Serializable keep the first one for the whole transaction. That difference is why a long Repeatable Read report holds back cleanup for its entire duration.

The xmin horizon: when a dead tuple can go

A dead version can be removed only when no snapshot anywhere could still need it, meaning its xmax is older than the oldest xmin across everything that holds one. That cluster-wide minimum is the xmin horizon. VACUUM can run as often as you like; if the horizon is stuck, it removes nothing and reports the rows as dead but not yet removable.

Four things pin it: long or idle-in-transaction sessions, replication slots whose consumer is gone, prepared transactions nobody committed, and standby queries reported through hot_standby_feedback. The last turns a long analytics query on a replica into bloat on the primary. Check all of them before tuning anything:

-- 1. Oldest snapshots held by sessions (idle in transaction is the classic culprit)
SELECT pid, usename, state, now() - xact_start AS xact_age,
       age(backend_xmin) AS xmin_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC LIMIT 5;

-- 2. Replication slots: an inactive slot pins xmin or catalog_xmin forever
SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;

-- 3. Forgotten two-phase commits
SELECT gid, prepared, age(transaction) AS xid_age FROM pg_prepared_xacts;

Set idle_in_transaction_session_timeout so abandoned sessions are ended, drop slots for consumers that no longer exist, and give replica analytics a statement_timeout. Slot behaviour on the WAL side is covered in PostgreSQL WAL in depth.

What VACUUM actually does

A plain VACUUM, the kind autovacuum runs, takes a lock that allows reads and writes to continue. It works in phases, which pg_stat_progress_vacuum reports by name. It scans the heap, skipping pages the visibility map marks all-visible, pruning dead versions and collecting their tuple IDs. It then vacuums each index, removing entries that point at those IDs, and returns to the heap to mark the line pointers unused. If the memory for dead tuple IDs fills, it repeats the index pass, which is why maintenance_work_mem matters for large tables. Finally it may truncate empty pages at the end of the file, updates the free space map and visibility map, and advances relfrozenxid when it scanned enough to be sure.

Plain VACUUM makes space reusable inside the file; it does not shrink the file, except for empty pages at the very end. VACUUM FULL rewrites the table into a new file and does shrink it, under an ACCESS EXCLUSIVE lock that blocks all reads and writes for the duration. On a large production table that is an outage, which is why online rewrite tools such as pg_repack exist.

Autovacuum arithmetic

The autovacuum launcher wakes every autovacuum_naptime, one minute by default, and starts up to autovacuum_max_workers workers, three by default. A table is vacuumed when its dead tuples exceed the threshold, which PostgreSQL 18 defines as the smaller of autovacuum_vacuum_max_threshold and base plus scale factor times reltuples. Defaults are 100,000,000, 50 and 0.2. A separate insert-driven trigger, 1,000 plus 0.2 times rows times the unfrozen fraction of the table, makes sure insert-only tables are vacuumed and frozen. ANALYZE uses 50 plus 0.1.

A 20 percent scale factor is gentle for small tables and far too lax for large hot ones: a ten-million-row orders table waits for two million dead versions, by which time the indexes are bloated and queries slower. Lower the scale factor per table rather than globally. Before PostgreSQL 18 the cap did not exist, so the billion-row table waited for 200 million.

-- Where each table stands against its autovacuum trigger (PostgreSQL 18 defaults,
-- ignoring per-table overrides set with ALTER TABLE ... SET).
SELECT s.relname,
       s.n_dead_tup,
       LEAST(100000000, 50 + 0.2 * c.reltuples)::bigint AS vacuum_trigger,
       s.n_ins_since_vacuum,
       s.last_autovacuum,
       age(c.relfrozenxid) AS xid_age
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
ORDER BY s.n_dead_tup DESC
LIMIT 10;

-- A hot table gets its own, much lower threshold
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01,
                        autovacuum_vacuum_threshold = 1000);

Speed is throttled by cost accounting. Each page costs vacuum_cost_page_hit (1) if found in shared buffers, vacuum_cost_page_miss (2) if read, and vacuum_cost_page_dirty (20) if dirtied. After spending the limit, 200 by default, a worker sleeps for autovacuum_vacuum_cost_delay, 2 milliseconds. That is 100,000 credits a second, shared across all running workers: about 50,000 page reads, roughly 390 MB/s, but only 5,000 dirtied pages, about 39 MB/s, when most pages need cleaning. On a table with heavy churn, the dirtying rate is the one that matters. Raise the cost limit if the disks have headroom.

Freezing and transaction ID wraparound

Transaction IDs are 32 bits and compared modulo 2^32: each ID sees about two billion as past and two billion as future. A row whose xmin drifted more than two billion transactions back would suddenly look like it came from the future and vanish. Freezing prevents this. VACUUM marks old enough tuples as frozen, visible to everyone regardless of their xmin, and a table's relfrozenxid records the oldest unfrozen XID it may still contain.

The settings form a ladder. Normal vacuums freeze tuples older than vacuum_freeze_min_age, 50 million, on the pages they visit. Once a table's age passes vacuum_freeze_table_age, 150 million, vacuum turns aggressive and scans every page not already all-frozen. At autovacuum_freeze_max_age, 200 million, autovacuum starts an anti-wraparound vacuum even on tables where autovacuum is disabled. At vacuum_failsafe_age, 1.6 billion, vacuum drops cost delay and skips index cleanup to finish freezing fast. The server warns once fewer than 40 million transactions remain and refuses to assign new XIDs below 3 million, which stops all writes until a manual vacuum completes.

PostgreSQL 18 adds eager freezing: normal vacuums also try to freeze some all-visible pages that are not yet all-frozen, capped by vacuum_max_eager_freeze_failure_rate, 0.03, so the eventual aggressive vacuum has less to do. Multixact IDs, used when several transactions lock one row, age the same way and need the same watching.

SELECT datname, age(datfrozenxid) AS xid_age, mxid_age(datminmxid) AS mxid_age
FROM pg_database ORDER BY 2 DESC;

SELECT c.oid::regclass AS table_name, age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY 2 DESC LIMIT 10;

-- Watch a running vacuum
SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total, index_vacuum_count
FROM pg_stat_progress_vacuum;

Worked example: a queue table

A jobs table holds about 200,000 live rows; workers insert, update status twice and delete each job, a million jobs a day. Under defaults the trigger is about 40,000 dead tuples, crossed roughly every 20 minutes, which seems fine. Then a reporting user opens a transaction at 9 a.m. and leaves the session idle in transaction. Autovacuum keeps running and removes nothing. By 5 p.m. the table holds about a million dead versions on 200,000 live ones, the index on status is several times its normal size, and the worker query that picks the next job slows from milliseconds to seconds.

The fix is in order: end the idle session and set idle_in_transaction_session_timeout; let a vacuum run; lower this table's scale factor so it vacuums every few thousand dead tuples; and, because indexes bloated, rebuild the status index with REINDEX INDEX CONCURRENTLY. Tuning autovacuum first would have changed nothing while the horizon was pinned.

Failure modes

• Dead tuples not removable: the horizon is pinned; check sessions, slots, prepared transactions and standby feedback.
• Steady bloat on big hot tables: a 0.2 scale factor waits too long; set per-table thresholds.
• Autovacuum always running, never finishing: cost limit too low for the churn, or too little maintenance_work_mem causing repeated index passes.
• Sudden I/O storm on a quiet table: an anti-wraparound vacuum on a large, rarely vacuumed table; freeze it ahead of time.
• Writes refused: XID headroom below 3 million; vacuum the oldest tables manually, after clearing whatever pinned the horizon.
• VACUUM FULL in production: an exclusive lock for the length of a full rewrite.

Trade-offs

PostgreSQL's design keeps old versions in the table itself, so readers never block writers and rollback is instant, but cleanup is deferred, shared with production traffic and sensitive to any long-lived snapshot. Aggressive autovacuum settings cost I/O continuously and save you from bloat and emergency freezing; lax settings feel cheap until they are not. Lower fillfactor helps HOT updates at the price of a bigger table. Snapshot semantics behind these trade-offs are compared across systems in Snapshot isolation.

What to do next

1. Run the horizon queries and fix any session, slot or prepared transaction older than an hour.
2. Set idle_in_transaction_session_timeout, and statement_timeout on replicas with hot_standby_feedback on.
3. Run the trigger query and give your ten largest hot tables their own scale factor.
4. Check age(datfrozenxid) and the oldest tables; alert well before 200 million and long before 1.6 billion.
5. Set log_autovacuum_min_duration so slow vacuums are logged, and read what they report.
6. Raise the autovacuum cost limit if vacuums lag and storage has headroom; size maintenance_work_mem for your largest tables.
7. Ban VACUUM FULL on large production tables; use pg_repack or REINDEX CONCURRENTLY instead.