Databases / PostgreSQL internals deep dive

2026-09-24

PostgreSQL XID wraparound: how freezing prevents a database from forgetting its past

PostgreSQL uses finite transaction IDs to decide which row versions a transaction can see. The counter wraps. If an old row still carries an ordinary ID after the comparison window passes, visibility can invert. PostgreSQL avoids this catastrophic failure by vacuuming old tuple metadata into a permanent “already committed long ago” state—and by refusing new writes if that safety work falls behind.

1. The concrete problem

MVCC keeps multiple row versions so concurrent readers can see a consistent snapshot while writers make changes. Each tuple records transaction metadata, including the inserting transaction's xmin. A transaction asks whether a tuple's creating transaction committed before its snapshot. That question is easy only if transaction identifiers have an ordering.

PostgreSQL's normal xid is 32 bits. It is a counter, not an infinite timestamp. After roughly 4.29 billion assigned IDs it returns to zero. Ordinary numeric ordering then lies: a newly assigned low ID can appear numerically smaller than an ancient high ID. If a surviving tuple still depends on that old ID, a future transaction could misclassify it as not yet committed and hide data that should be visible.

This is distinct from ordinary dead-row cleanup. A table with almost no updates can still need a scan because old, still-live tuples need freezing.

2. Why 32 bits are not enough

PostgreSQL compares normal XIDs using modular arithmetic. On the circular number line, every normal ID has about 2³¹ IDs that count as older and 2³¹ that count as newer. There is no globally meaningful “less than” relation across the whole 32-bit space; only differences smaller than half the circle are unambiguous.

PostgreSQL therefore freezes eligible old tuple metadata. A frozen tuple is treated as if its inserting transaction were FrozenTransactionId—a special value that is always older than normal transactions. In modern releases, freezing is represented by tuple header status bits rather than relying on every historical tuple's visible xmin literally becoming 2; ancient upgraded clusters can still contain the older representation.

Because vacuum must complete well before the half-range boundary, the operational threshold is deliberately lower. The server launches anti-wraparound autovacuum as a table approaches autovacuum_freeze_max_age; if the system still cannot make progress, it eventually stops assigning new XIDs rather than risk silent visibility corruption.

3. What freezing means on disk

Vacuum scans heap pages, removes tuple versions no active snapshot can see, and freezes old committed tuple metadata. It also maintains the visibility map: per-page bits record whether all tuples are visible to every transaction (all-visible) and whether all are frozen (all-frozen). These are different claims.

• All-visible: index-only scans may avoid fetching the heap page, but some tuple XIDs can still need freezing later.
• All-frozen: no tuple on the page needs future XID freezing, so even an aggressive vacuum can skip it safely.

A regular vacuum can skip pages known to contain no dead tuples. An aggressive vacuum must also inspect pages that are all-visible but not all-frozen, because tuples there may be old even though none are dead. PostgreSQL can eagerly freeze some pages during regular vacuum to reduce the later aggressive scan.

At relation level, pg_class.relfrozenxid is a conservative lower bound on unfrozen XIDs left by a successful vacuum. At database level, pg_database.datfrozenxid summarizes the oldest relation boundary. age(datfrozenxid) is therefore a useful “how far behind safety frontier?” gauge, not a count of dead rows or a precise time-to-outage.

4. The vacuum safety control loop

1. Every table has an XID-age boundary recorded as relfrozenxid.
2. Autovacuum schedules work based on table activity and XID age. Anti-wraparound work is forced by age even when ordinary dead-tuple thresholds would not trigger.
3. A regular scan cleans dead tuples and may freeze tuples on pages it visits.
4. When the freeze-age threshold requires it, aggressive vacuum scans every page not known all-frozen and advances the relation boundary.
5. The database boundary advances only when its oldest relation boundary advances.
6. If the horizon approaches danger and maintenance cannot catch up, PostgreSQL emits warnings and ultimately blocks new XID assignment.

Important configuration includes autovacuum_freeze_max_age (when anti-wraparound work must begin), vacuum_freeze_table_age (when an ordinary vacuum becomes aggressive), vacuum_freeze_min_age (how old XIDs must be before freezing), and autovacuum cost/worker settings that determine throughput. Raising thresholds buys fewer scans at the price of less safety margin and potentially larger work bursts.

In source, the lazy heap vacuum implementation in src/backend/access/heap/vacuumlazy.c coordinates heap scanning, pruning, freezing, visibility-map updates, and relation statistics. The transaction-ID age helpers and wraparound protections span transaction and vacuum modules; exact function names and control flow vary by PostgreSQL major version, so inspect the branch deployed rather than copying internals from an old blog.

5. Why vacuum falls behind

Replication slots are particularly subtle: they intentionally preserve WAL and/or row versions for a consumer. A slot is not “stuck” merely because it is old; it may be protecting a live replica or logical-decoding client. Dropping it can make that consumer impossible to resume without rebuilding. Treat cleanup as a dependency decision, not a knob-turn.

6. Production diagnosis

First measure the frontier across every database, then identify the relation that pins it:

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

SELECT n.nspname, c.relname, age(c.relfrozenxid) AS xid_age
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY xid_age DESC
LIMIT 20;

Find possible horizons and current vacuum progress:

SELECT pid, usename, state, xact_start, age(backend_xmin) AS xmin_age,
       wait_event_type, wait_event, left(query, 100) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

SELECT slot_name, slot_type, active,
       age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY GREATEST(COALESCE(age(xmin), 0),
                  COALESCE(age(catalog_xmin), 0)) DESC;

SELECT * FROM pg_stat_progress_vacuum;

Interpret these as clues, not a single magic alarm. Compare age growth over time, inspect server logs for anti-wraparound warnings, verify which database and relation is oldest, and check whether a vacuum is progressing, blocked, or repeatedly cancelled. Alert on both an absolute age budget and its rate of increase; a smaller age growing rapidly can be more urgent than a larger stable one.

7. Reproducible lab

Goal: observe table-level XID age, freezing, and the database safety frontier in an isolated PostgreSQL instance. Use Docker with PostgreSQL 16 or newer; do not run this experiment on production.

docker run --rm --name xid-lab -e POSTGRES_PASSWORD=lab \
  -p 55432:5432 -d postgres:16

# Wait until ready, then create a table and inspect its freeze boundary.
docker exec xid-lab psql -U postgres -c \
  "CREATE TABLE freeze_demo AS SELECT g AS id, repeat('x',100) AS payload FROM generate_series(1,10000) g;"
docker exec xid-lab psql -U postgres -c \
  "SELECT age(relfrozenxid) FROM pg_class WHERE relname='freeze_demo';"

# Force a controlled freeze pass, then re-check the relation and DB boundaries.
docker exec xid-lab psql -U postgres -c "VACUUM (FREEZE, VERBOSE) freeze_demo;"
docker exec xid-lab psql -U postgres -c \
  "SELECT c.relname, age(c.relfrozenxid) FROM pg_class c WHERE c.relname='freeze_demo';"
docker exec xid-lab psql -U postgres -c \
  "SELECT datname, age(datfrozenxid) FROM pg_database WHERE datname=current_database();"

docker rm -f xid-lab

Expected observation: the initial relation has a recent but nonzero XID age; after VACUUM (FREEZE), its relfrozenxid advances and its reported age falls close to zero. Other catalog relations may still determine the database-level minimum, so the database age need not become exactly zero.

Variation 1: create a long-running transaction in a second session, update rows in another session, and compare vacuum's cleanup behavior before and after ending the old snapshot. It demonstrates why visibility horizons matter for dead tuples; it is not a way to manufacture wraparound.

Variation 2: inspect pg_stat_progress_vacuum during a larger table scan. Use VACUUM (VERBOSE) to correlate progress with pages scanned and frozen. Do not try to generate billions of transactions to reproduce wraparound; the risk is cluster-wide and the time cost teaches little.

Lab note: container startup and Docker availability were not exercised as part of this lesson build. If Docker is unavailable, run the SQL in a disposable local PostgreSQL 16+ database. Do not lower production freeze thresholds or disable autovacuum for the experiment.

8. Recovery, tradeoffs, and security

When age rises, find and safely release the blocker first: resolve genuinely abandoned prepared transactions; repair, advance, or remove only truly obsolete replication slots; end runaway snapshots after confirming application impact; then ensure vacuum has enough time and I/O to advance the oldest relation. Confirm the frontier moves afterward. Do not reflexively kill every old backend or drop every slot.

Freezing trades routine background I/O and occasional aggressive scans for protection against an otherwise catastrophic correctness failure. Too little vacuum capacity risks a write outage; too much aggressive work can compete with latency-sensitive queries. Tune for sustained scan throughput and headroom, not merely a quiet graph.

Security and reliability overlap: a compromised or misconfigured logical-decoding client can hold a slot horizon and indirectly threaten availability. Restrict slot creation and replication privileges, inventory consumers, alert on slot age and inactivity, and include slot ownership/retirement in offboarding. Do not expose diagnostic views or database credentials in public dashboards.

Takeaway

PostgreSQL transaction IDs form a circle, so old tuple metadata cannot be left untouched forever. Vacuum freezes sufficiently old committed tuples, tracks the oldest remaining unfrozen boundary in relfrozenxid/datfrozenxid, and forces maintenance before comparisons become ambiguous. Diagnose the oldest relation and the horizon holding it back; protect the maintenance window; and treat the final refusal to allocate XIDs as a deliberate safety stop, not a tuning inconvenience.