Every row your Postgres database has ever written carries the transaction ID that created it, stamped into a tuple header field called xmin. That ID comes from a 32-bit counter. Four billion values sounds like plenty until you realize Postgres compares XIDs with modulo-2^32 arithmetic: the counter is a circle, not a line. For any given XID, two billion XIDs count as "older" and two billion count as "newer."
That circular comparison is what makes MVCC visibility checks cheap — "did this transaction commit before mine started?" is a wrapping integer comparison. It is also a time bomb. A row inserted today is "in the past" for the next two billion transactions. If the row survives longer than that without intervention, the circle rotates underneath it and the row's xmin suddenly compares as being in the future. The row doesn't get corrupted — it gets worse than corrupted. It becomes invisible, silently, to every new query, while still sitting on disk.
Postgres will do a lot of ugly things to stop that from happening, up to and including refusing to accept writes. Here's the machinery, threshold by threshold.
Freezing: a bit that means "infinitely old"
The escape hatch is freezing. A frozen row is exempted from the circular comparison entirely: it is treated as older than every normal XID, forever, so the rotating circle can never catch up to it.
Postgres reserves two special XIDs below the normal range: BootstrapTransactionId (1) for catalog rows created during initdb, and FrozenTransactionId (2). Before version 9.4, freezing literally overwrote the row's xmin with 2 — which is why a database that was pg_upgrade'd from an old cluster can still show rows with xmin = 2. Since 9.4, freezing instead sets a flag bit in the tuple header and preserves the original xmin, which the docs dryly note is kept "for possible forensic use." Same semantics, better archaeology.
So the invariant Postgres must maintain is simple to state: every table in every database must have its old rows frozen at least once every two billion transactions. Everything else — autovacuum, aggressive scans, the failsafe, the read-only lockdown — is an escalation ladder in service of that one rule.
Why there are three thresholds, not one
Freezing is done by VACUUM, and three settings control how eagerly:
| Setting | Default | Meaning |
|---|---|---|
vacuum_freeze_min_age | 50 million | How old an XID must be before vacuum bothers freezing it |
vacuum_freeze_table_age | 150 million | Table age at which vacuum escalates to an aggressive scan |
autovacuum_freeze_max_age | 200 million | Table age at which an anti-wraparound autovacuum is forced |
The gap between them is deliberate. A normal vacuum uses the visibility map to skip pages that have no dead tuples — which is exactly what makes it fast, and exactly why it can't be trusted for wraparound protection: a skipped page may still hold old unfrozen XIDs. The table-level marker relfrozenxid can only advance when every page that might contain unfrozen XIDs has actually been visited.
That's what an aggressive vacuum is: a vacuum that visits every potentially-unfrozen page instead of just the dirty ones. vacuum_freeze_table_age is silently capped at 95% of autovacuum_freeze_max_age for a very practical reason the docs spell out — so a routine scheduled vacuum gets a chance to do the aggressive scan before Postgres forcibly launches an anti-wraparound autovacuum. Similarly, vacuum_freeze_min_age is capped at half of autovacuum_freeze_max_age.
And the forced vacuum at 200 million is genuinely forced: it launches even if you've disabled autovacuum. Turning autovacuum off doesn't opt you out of wraparound protection; it just guarantees the protection arrives at the worst possible time, as a surprise.
Why does the default sit at a "relatively low" 200 million when the hard limit is two billion? Disk, mostly: commit status lives in pg_xact at two bits per transaction, and vacuum is what allows old pg_xact segments to be removed. At the default, that's about 50 MB of pg_xact (and about 2 GB of pg_commit_ts if commit timestamps are on). Raised to the two-billion maximum, those grow to roughly half a gigabyte and 20 GB.
The escalation ladder past prevention
If the forced autovacuums can't keep up — blocked by a long-running transaction, an abandoned replication slot, or an orphaned prepared transaction, all of which pin the oldest XID horizon — Postgres starts escalating:
- 1.6 billion (
vacuum_failsafe_age, floored at 105% ofautovacuum_freeze_max_age): any running vacuum on the endangered table hits the failsafe, "VACUUM's strategy of last resort." It drops all cost-based throttling, skips non-essential work including index vacuuming, and abandons its buffer access strategy so it can use all of shared buffers. Translation: Postgres stops being polite about I/O and sprints. - 40 million XIDs remaining: every command starts emitting
WARNING: database "mydb" must be vacuumed within 39985967 transactions. - Fewer than 3 million remaining: the database refuses to assign new XIDs.
ERROR: database is not accepting commands that assign new transaction IDs. Reads still work, in-flight transactions can finish, and — critically —VACUUMstill runs. The current docs are explicit that stopping the postmaster for single-user mode is "not necessary or desirable" here; the 3-million margin exists mainly so you canTRUNCATEorDROPhuge tables you'd rather not wait to vacuum.
The recovery checklist in the official docs is worth memorizing in order, because vacuuming is useless while something pins the horizon: kill old prepared transactions (pg_prepared_xacts), terminate long-running backends (pg_stat_activity), drop stale replication slots (pg_replication_slots), then run plain VACUUM — not VACUUM FULL, not VACUUM FREEZE.
The day it happened to Sentry
On July 20, 2015, Sentry was down for most of the US working day because of exactly this. Their postmortem is the canonical war story: a write-heavy workload, very large tables, and autovacuum tuned in the wrong direction — autovacuum_freeze_max_age set too high, only three workers, and too much cost delay throttling the vacuums that did run. The XID counter hit the stop limit (then documented as one million remaining; Postgres 14-era releases widened it to three million) and the primary went read-only.
Two details from the recovery stand out. First, they let the in-progress anti-wraparound autovacuums finish rather than restart them — hours of waiting — and at the end, exactly one large table remained unvacuumed. They chose to truncate it and accept the data loss; "five minutes later, the system was fully restored." Second, a test box they pointed at the problem ran a single-user-mode vacuum for about 24 hours. When wraparound protection fires on a big database, your recovery time is measured in vacuum throughput, not in how fast you can type.
Their corrected settings are a reasonable starting point for write-heavy fleets: autovacuum_freeze_max_age = 500000000, autovacuum_max_workers = 6, autovacuum_vacuum_cost_delay = 0. One more lesson from their follow-up: cranking maintenance_work_mem to 100 GB bought nothing, because the relevant vacuum code paths had a 1 GB internal cap.
What to actually watch
Wraparound never ambushes a monitored database — the runway is hundreds of millions of transactions long. Two queries from the docs cover it:
-- per-table age, including TOAST tables
SELECT c.oid::regclass AS table_name,
greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');
-- per-database age
SELECT datname, age(datfrozenxid) FROM pg_database;Alert when age(datfrozenxid) crosses your autovacuum_freeze_max_age and keeps climbing — that means the forced autovacuums are running but losing, and something is pinning the horizon. At that point the fix is almost never "tune vacuum harder." It's finding the idle-in-transaction session, the forgotten replication slot, or the prepared transaction from last quarter that's been quietly holding two billion transactions of runway hostage.