There is exactly one PostgreSQL failure mode where the database stops accepting new work to protect your data from itself, and it is not disk full, not memory, not corruption. It is transaction ID wraparound: the moment the 32-bit counter that labels every row version comes full circle and the database can no longer tell old data from new. (Multixact IDs wrap around the same way and get the same protective refusal; that cousin deserves its own article.) The good news is that it is completely preventable and loudly telegraphed. The bad news is that I have watched it take down production anyway, in an organization that had every dashboard money could buy and nobody watching age(datfrozenxid). This is the write-up their on-call needed and did not have.
The 32-bit clock under every row
Every tuple header carries an xmin — the transaction ID that created it — and usually an xmax for the transaction that deleted or superseded it. Transaction IDs come from a 32-bit counter, so there are about 4.3 billion values, and visibility is computed modulo that ring: roughly two billion transactions ahead of you is the future, two billion behind is the past. A tuple whose xmin is more than about two billion transactions in the past is ambiguous — it could equally be two billion in the future — and PostgreSQL's answer to that ambiguity is absolute: it never allows the ambiguity to exist. Old tuples must be frozen before their age approaches the horizon.
Freezing is vacuum's other job, the one nobody talks about until it is an emergency. When vacuum visits a page, tuples old enough can be marked frozen: a flag bit is set in the tuple header — modern PostgreSQL preserves the original xmin rather than overwriting it — and from then on the row version is treated as visible to everyone, forever, no further questions. Frozen tuples never need attention again. Each table tracks how far freezing has progressed in pg_class.relfrozenxid, and each database tracks the oldest of those in pg_database.datfrozenxid.
The settings that decide when freezing happens
Two parameters run the show. autovacuum_freeze_max_age, default 200 million, is the tripwire: when a table's relfrozenxid age passes it, an aggressive anti-wraparound autovacuum launches against that table no matter how little dead-tuple garbage it has. Aggressive means the vacuum skips only pages already marked all-frozen; the all-visible bit, which normally exempts a page from the scan, no longer does — precisely so vacuum can freeze everything freezable and advance relfrozenxid as far as possible. vacuum_freeze_min_age, default 50 million, controls how young a tuple may be before vacuum bothers freezing it; raising it defers freeze work on churn-heavy tables, lowering it freezes more eagerly on each pass.
The defaults are sane. The operational failures I have seen come from two places: autovacuum disabled on a database "temporarily" three quarters ago, and a single enormous table whose anti-wraparound vacuum cannot finish between trips around the counter, because it is terabytes of cold data being vacuumed at default cost-delay speeds while the transaction rate climbs.
Monitoring before it is a crisis
The metrics are one query away, and they belong on every dashboard with alerts at levels that give you weeks, not hours:
SELECT datname,
age(datfrozenxid) AS xid_age,
round(100.0 * age(datfrozenxid) / 2147483648, 1) AS pct_of_horizon
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
SELECT c.oid::regclass AS table_name,
greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS table_xid_age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY 2 DESC
LIMIT 10;Alert thresholds I am comfortable with: warn when any database passes 500 million, page someone at 1 billion. autovacuum_freeze_max_age at 200 million means a healthy system never lets a table age far past that for long, so a database-wide age of 500 million tells you freezing is not keeping up. That trend is the finding, long before the emergency number arrives.
The emergency, verbatim
If nobody acts, PostgreSQL escalates in stages. Once the oldest database's unfrozen XIDs come within forty million transactions of the horizon, log warnings begin — "database ... must be vacuumed within N transactions" — repeating as the margin shrinks. Ignore those, and with fewer than three million transactions of safety margin left the database stops assigning new transaction IDs: clients see "database is not accepting commands that assign new XIDs to avoid wraparound data loss". Note what does not happen: the server does not shut down. Transactions already in flight continue, new read-only transactions can still start, and — critically — plain VACUUM still runs, which is your way out. It is the database choosing degraded service over returning wrong answers: the correct engineering choice, and a terrible morning.
Digging out
Recovery is documented and mechanical, and on PostgreSQL 16 and 17 it does not involve single-user mode. The current documentation explicitly recommends against postgres --single here: it takes the system down and disables the wraparound safeguards that are your last line of defense. Work the documented checklist with the server up. First, clear old prepared transactions — look in pg_prepared_xacts for large age(transactionid) values and commit or roll them back. Second, end long-running transactions holding an old snapshot; pg_stat_activity rows with large age(backend_xid) or age(backend_xmin) are the suspects, and pg_terminate_backend is the tool. Third, drop stale replication slots pinning an old xmin — forgotten slots for decommissioned replicas are the classic culprit. Then run a plain database-wide VACUUM in the affected database, as a superuser. Two refinements matter. Superuser, because a non-superuser vacuum skips system catalogs and therefore cannot advance datfrozenxid. And plain VACUUM, because VACUUM FULL needs to consume a new XID the database is refusing to hand out, and VACUUM FREEZE does more work than the emergency requires. Once ages fall back to sane territory the database resumes assigning XIDs on its own — and then you find out why autovacuum fell behind, because whatever caused it will do it again.
Two things not to do. Do not restart repeatedly hoping it clears; each boot re-runs the same arithmetic and reaches the same conclusion. And do not let anyone manually fiddle with the transaction counter using tooling they found in a forum post. Frozen-tuple confusion is data corruption by another name, and it surfaces months later as rows that vanish or resurrect.
Preventing it for real
Prevention is boring and total: leave autovacuum on, tune per-table autovacuum settings aggressively enough on the big cold tables that anti-wraparound runs finish quickly, and graph age(datfrozenxid) per database plus age(relfrozenxid) for your largest tables. If your workload burns transaction IDs fast — tens of millions a day from tiny transactions is common on queue-style systems — do the arithmetic on how many days 200 million buys you and make sure an anti-wraparound vacuum of your largest table fits inside that window. Our vacuum guide covers the tuning levers, and the comparison pages put PostgreSQL's MVCC housekeeping next to engines that handle this differently.
One more source of surprise: standby servers and long transactions. A hot standby with hot_standby_feedback and a runaway query, or a forgotten idle-in-transaction session, can hold back vacuum's freeze horizon on the primary just as surely as autovacuum being off. When freeze progress stalls and the tables look innocent, go looking for the session that is pinning the oldest snapshot.
How MonPG watches the clock
Wraparound is the ultimate monitoring problem: a slow-moving number that means nothing for years and then means everything. Watching age curves by hand is how they get ignored, so I let MonPG do the watching — it monitors PostgreSQL in production today, tracking per-database transaction ID age and per-table freeze progress alongside autovacuum activity, so the trend sits on the same screen as the vacuum runs that are supposed to be fixing it. Alerting on the rate of approach, not just the absolute age, is what turns a two-weeks-from-now problem into a Tuesday-afternoon task. The PostgreSQL monitoring workflow includes these counters out of the box, because nobody should learn what datfrozenxid is from a "not accepting commands" error.