PostgreSQL transaction ID wraparound happens when the 32-bit XID counter runs out of safe room and the database stops accepting new writes to protect committed data from silently disappearing. It's not corruption — it's a deliberate safety interlock. Here's the mechanism, the exact diagnostic queries, and the recovery path that actually works.
📖 Read the full guide: Postgres Transaction ID Wraparound: Causes, Checks, Fix
▶ Watch the video walkthrough: Transaction ID Wraparound: The Failure That Stops Writes
https://www.youtube.com/watch?v=t9AhvAefBdk
The 3 a.m. symptom: writes stop, reads keep working
The page says the application is down. You connect, run a SELECT, and it returns instantly. Then you try an INSERT and get this:
ERROR: database is not accepting commands to avoid wraparound data loss in database "orders"
HINT: Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
That asymmetry is your first real diagnostic clue. Reads work. Writes don't. If the database were actually broken, reads would fail too. Nothing in the storage layer is corrupt, and no disk is full. PostgreSQL has deliberately stopped issuing new transaction IDs because issuing one more would risk making committed data silently invisible.
If you scroll back in the log, this did not arrive without notice. You'll usually find, for days beforehand, lines like:
WARNING: database "orders" must be vacuumed within 10982374 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
Ten million transactions of warning. On a busy OLTP cluster that might be an hour. On most systems it's days. Either way, the interlock fired at the end of a long sequence of ignored signals, not out of nowhere.
Why a 4-billion counter only gives you 2 billion
PostgreSQL transaction IDs are 32-bit — about 4.29 billion distinct values. When the counter reaches the top, it wraps back to the bottom. So far, so ordinary.
The subtlety is in how two XIDs get compared. PostgreSQL doesn't ask "is 5 less than 4,000,000,000?" It compares modulo 2^31. From the perspective of any given XID, roughly two billion values count as the past and roughly two billion count as the future. The counter behaves like a clock face: 11 o'clock is both three hours before 2 and nine hours after.
That halving is where your budget goes. The usable window between the oldest unfrozen row in the cluster and the current XID is about two billion, not four.
Now think about what happens if you blow through it. A row inserted by transaction 100, still carrying xmin = 100, is visible today because 100 is comfortably in the past. Let the counter advance past the halfway mark relative to 100, and that same 100 flips into the future. The row becomes invisible — still on disk, fully intact, but every query behaves as though it was never inserted.
That's the data loss the interlock exists to prevent: committed rows quietly disappearing from view, with no crash and no corruption anywhere in sight. It's considerably worse than an outage, because you might not notice for a week.
Freezing: how the clock gets reset
The escape hatch is freezing. Freezing marks a row version as unconditionally visible regardless of the current XID value, taking it out of the comparison game entirely. Historically this meant overwriting xmin with FrozenTransactionId (2). Since PostgreSQL 9.4, it's recorded with a frozen hint bit in the tuple header instead, preserving the original xmin for forensics while giving the same semantics.
Vacuum does the freezing, and it keeps score in the catalogs:
pg_class.relfrozenxid— the per-relation freeze horizon. Every row in that relation is guaranteed frozen or newer than this XID.pg_database.datfrozenxid— the minimumrelfrozenxidacross every relation in the database. One neglected table drags the whole database's number down.age()on either value returns how many XIDs have elapsed since then — your position on the runway.
Two things people forget. TOAST tables are separate relations with their own relfrozenxid, so a table can look perfectly young while its TOAST relation is the one holding the whole cluster hostage. And materialized views have relfrozenxid too, which matters if you have a large matview nobody has refreshed or vacuumed since it was created.
The escalation ladder, with the real numbers
Every rung below is a chance to act. By the time you hit the bottom two, someone ignored the previous four.
| Setting / event | Default | What happens |
|---|---|---|
vacuum_freeze_min_age |
50 million | Rows older than this get frozen when vacuum is already touching the page |
vacuum_freeze_table_age |
150 million | Vacuum turns aggressive and scans pages not marked all-frozen in the visibility map |
autovacuum_freeze_max_age |
200 million | Anti-wraparound autovacuum is forced on the table, even if autovacuum is turned off |
vacuum_failsafe_age |
1.6 billion | Vacuum drops cost-based delays and skips index vacuuming to finish freezing fast (PG14+) |
| Warn limit | ~13 million XIDs remaining | WARNING: database "x" must be vacuumed within N transactions |
| Stop limit | 3 million XIDs remaining | New XIDs refused; the error at the top of this article |
autovacuum_freeze_max_age caps out at 2 billion. The failsafe has a MultiXact sibling, multixact_failsafe_age, also defaulting to 1.6 billion.
Two behaviours worth internalizing. A normal autovacuum worker gets automatically cancelled when it blocks a conflicting lock request, which is why your ALTER TABLE usually goes through. An anti-wraparound autovacuum is not auto-cancelled. It sits there holding its lock for as long as it takes, and your DDL waits. People discover this at the worst moment and reflexively kill the worker — exactly backwards.
The failsafe is why PostgreSQL 14+ clusters are noticeably harder to push into the stop limit. If you're on 12 or 13, you don't have that net.
Measuring your actual risk in three queries
Start at the database level — this is the number the interlock cares about.
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY 2 DESC;
datname | xid_age
------------+-----------
orders | 412193884
postgres | 87421119
template1 | 87421119
Anything over 200 million means anti-wraparound autovacuum should already be running somewhere. Over 500 million means something is actively blocking it.
Then find the relation responsible, TOAST included:
SELECT c.oid::regclass AS relation,
greatest(age(c.relfrozenxid),
coalesce(age(t.relfrozenxid), 0)) AS max_age,
age(c.relfrozenxid) AS heap_age,
age(t.relfrozenxid) AS toast_age,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relkind IN ('r', 'm')
ORDER BY 2 DESC
LIMIT 20;
Run it in every database, not just the one you happen to be connected to. A forgotten analytics_scratch database with a two-year-old table will pin the cluster just as effectively as production.
Finally, the burn rate. Static thresholds are useless without velocity; 400 million on a system burning 5 million XIDs a day is a monthly chore, and 400 million on a system burning 160 million a day is a Tuesday problem.
-- PostgreSQL 13+
SELECT pg_snapshot_xmin(pg_current_snapshot()) AS xid, clock_timestamp();
-- older releases
SELECT txid_current() AS xid, clock_timestamp();
Take two samples ten minutes apart. Suppose you get 1,110,000 XIDs consumed in 600 seconds — that's 1,850 XIDs per second, or 159,840,000 per day. Call it 160 million.
Now the arithmetic. Your budget from the oldest datfrozenxid is 2,147,483,648 minus the 3 million stop-limit reserve, so 2,144,483,648 usable XIDs. With age(datfrozenxid) at 412,193,884, you have 1,732,289,764 left. Divide by 160 million per day and you get roughly 10.8 days.
Ten days gives you room to plan, but nowhere near a quarter's worth of slack. That's the number to put on the incident ticket, not "age is 412 million," which means nothing to anyone who hasn't read this article.
Note that txid_current() assigns an XID, so sampling it on a wedged cluster will itself fail. Use the snapshot function instead.
Who is holding the horizon: the four usual suspects
Vacuum can't freeze a row that some open snapshot might still need to see. So anything holding an old xmin freezes your freeze horizon in place. There are four candidates, and it's almost always one of them.
Long-running or idle-in-transaction sessions
SELECT pid, datname, usename, state,
age(backend_xmin) AS xmin_age,
now() - xact_start AS xact_age,
left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 20;
The classic offender is idle in transaction with a three-day xact_age — usually an ORM connection that opened a transaction, threw an exception, and never rolled back. Terminate it with pg_terminate_backend(pid) and set idle_in_transaction_session_timeout so it can't happen again.
Orphaned prepared transactions
SELECT gid, prepared, owner, database, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;
If you aren't deliberately running a two-phase-commit coordinator, any row here is garbage left by a crashed distributed transaction manager. ROLLBACK PREPARED '<gid>'; and move on. These are pernicious because they survive restarts and hold their xmin forever.
Replication slots and hot_standby_feedback
SELECT slot_name, slot_type, active, database,
age(xmin) AS xmin_age,
age(catalog_xmin) AS catalog_xmin_age,
restart_lsn
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;
An inactive logical slot from a decommissioned CDC pipeline is the single most common cause seen in the field. It pins catalog_xmin, holds WAL, and nobody remembers creating it. Drop it. If it's active but lagging badly, fix the consumer or accept you're trading replication continuity for cluster availability. Similarly, a standby with hot_standby_feedback = on propagates its oldest snapshot to the primary, so a long analytics query on the replica can stall freezing upstream.
Autovacuum that's throttled, starved, or crashing
SELECT relname, last_autovacuum, last_vacuum, n_dead_tup,
autovacuum_count
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST
LIMIT 20;
autovacuum_max_workers defaults to 3, and each worker handles one table at a time. Three enormous tables past autovacuum_freeze_max_age will occupy every worker for hours while everything else waits. Check the logs for autovacuum errors too — that's the incident that follows.
Also worth remembering: orphaned temp tables left behind by crashed backends used to sit around indefinitely holding back datfrozenxid, because ordinary autovacuum wouldn't touch them. PostgreSQL 13 added automatic cleanup of these. On older major versions, check for them explicitly in pg_class where the schema is pg_temp_* and no session owns it.
War story: 19 days of dead autovacuum that looked like a disk problem
A client called about disk growth. Their primary had been climbing steadily for two and a half weeks, backups had doubled in size, and WAL retention was eating the archive volume. Their working theory was a runaway table or a storage-layer problem. They'd already added a terabyte.
The actual sequence: an OS package upgrade had replaced a shared library and left one extension's .so file missing. Every autovacuum worker that started tried to load the extension via shared_preload_libraries handling and errored out — cluster-wide. Autovacuum hadn't completed a single run in 19 days, and nobody was alerting on last_autovacuum going stale because the metric they watched was dead tuple count, which was rising but had no threshold attached.
Meanwhile the tables aged past 200 million and anti-wraparound autovacuum kicked in, meaning the workers that did occasionally start were monopolised by wraparound priority work on the largest relations, starving everything else further. One heavily updated table reached 177 GB, most of which was dead tuples and index bloat. That was the disk growth. The WAL inflation was the same problem from another angle: every full-page write on a bloated table costs more.
The bucket filled from four directions at once, and the wraparound countdown was the quietest of them. By the time we checked age(datfrozenxid), it was north of 900 million.
The fix took ten minutes once identified: reinstall the package, restart, watch the workers finally run. The cleanup took days. The lesson has stuck since: wraparound rarely announces itself as wraparound. It shows up as disk growth, slow queries, inflated backups, and a support ticket about something else entirely.
You're at the stop limit. Now what?
Here's the part the documentation buries. The XID exhaustion check lives in GetNewTransactionId(), which means only operations that need a new XID fail. Read-only queries work. And VACUUM doesn't assign an XID.
So you can usually just connect normally and vacuum. The HINT telling you to stop the postmaster and use single-user mode is conservative advice from before the failsafe existed, and following it costs you an unnecessary full outage plus a restart.
The decision tree
1. Clear the blockers first. Kill the idle-in-transaction sessions, roll back the prepared transactions, drop the dead replication slots. If you freeze without doing this, vacuum will run for hours and advance relfrozenxid by nothing, because it still can't freeze past the oldest snapshot.
2. Connect normally and order tables by age. Use the per-table query above. Work oldest first. The only relation that matters right now is whichever one is holding datfrozenxid down.
3. Freeze with index cleanup off.
VACUUM (FREEZE, VERBOSE, INDEX_CLEANUP OFF) public.events;
INDEX_CLEANUP OFF (PostgreSQL 12+) skips index vacuuming entirely. On a 200 GB table with six indexes that can be the difference between forty minutes and four hours. You leave index bloat behind, cleaned up later on your own schedule. Right now you're buying XIDs, not tidiness.
4. Monitor it. A long freeze with no output is unnerving. Watch it:
SELECT p.pid, p.relid::regclass, p.phase,
p.heap_blks_total, p.heap_blks_scanned,
round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS pct
FROM pg_stat_progress_vacuum p;
Available since 9.6. If heap_blks_scanned is moving, you're fine. If it's pinned, check for a lock conflict.
5. Single-user mode is the fallback, not the opener. If the cluster genuinely won't accept connections, stop the postmaster and run, as the postgres OS user:
postgres --single -D /var/lib/postgresql/data orders
At the backend> prompt, statements are terminated by a newline, not a semicolon:
backend> VACUUM (FREEZE);
One database per invocation. Repeat for each database with a high datfrozenxid, including template1 and postgres, which people skip and then wonder why the cluster-wide number hasn't moved.
Sentry's widely cited 2015 post-mortem on their own wraparound outage is still worth reading for the organisational shape of these incidents, even though the tooling has improved a great deal since.
The MultiXact twin nobody checks
There's a second 32-bit counter with its own wraparound protection, and it fails identically while every dashboard you built stays green.
MultiXact IDs are allocated when multiple transactions hold row-level locks on the same tuple simultaneously. Heavy SELECT ... FOR SHARE, SELECT ... FOR UPDATE on parent rows, or foreign key checks against a hot parent table will generate them by the million.
SELECT datname, mxid_age(datminmxid) AS mxid_age
FROM pg_database
ORDER BY 2 DESC;
SELECT c.oid::regclass AS relation,
mxid_age(c.relminmxid) AS mxid_age,
pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
AND c.relminmxid <> '0'
ORDER BY 2 DESC
LIMIT 20;
autovacuum_multixact_freeze_max_age defaults to 400 million — twice the regular XID default — which lulls people into treating it as less urgent. Same forced anti-wraparound vacuum triggers regardless; it's just a different clock running underneath. Beyond the ID counter, MultiXact member space can also fill up on workloads with many lockers per tuple, which produces its own class of failure. If you run a schema with a small number of very hot parent rows referenced by millions of children, put mxid_age on your dashboard next to age(datfrozenxid).
Settings that actually prevent this
Opinions, stated plainly, because folklore around this subject runs deep and most of it is wrong.
Cut autovacuum_vacuum_cost_delay. Since PostgreSQL 12 the default is 2ms, down from 20ms, which made default autovacuum dramatically more capable. If you're on 12+ and someone has manually set it back to 20 or 50 to "reduce IO impact," that person cost you your safety margin. If you're still on an older major version with the 20ms default, updating that single setting is often the highest-leverage change available. On decent NVMe, run it at 2ms or 1ms.
Raise autovacuum_max_workers carefully. Three is low for a cluster with dozens of large tables. Six or eight is reasonable, but do it cautiously — more workers means more concurrent I/O and CPU competing with your application load. Remember the cost limit is shared across workers by default, so adding workers without raising autovacuum_vacuum_cost_limit divides the same IO budget more ways and achieves nothing.
Set idle_in_transaction_session_timeout and statement_timeout. Something like 5 minutes and 30 seconds respectively for application roles, with generous per-role overrides for reporting users who genuinely need long queries. An open transaction from a forgotten psql session is a completely avoidable cause of this entire outage, and this one setting eliminates the most common blocker.
Monitor replication slot lag and drop dead slots. A slot with nobody consuming it is a silent, growing liability. Have a policy: any inactive slot older than 24 hours gets dropped after a Slack message, not after a wraparound incident.
Raising autovacuum_freeze_max_age is a credit card, not a fix. It buys time by letting the age climb higher before autovacuum intervenes. The freezing work doesn't go away — it arrives later, in a bigger batch, on a bigger table, with less runway to react. There are legitimate uses (very large append-mostly tables where 200 million triggers wasteful scans), but if you're raising it because anti-wraparound vacuums keep firing, you're treating the alarm, not the underlying rot.
Upgrade if you're pre-14. The failsafe at 1.6 billion is genuinely the difference between a bad afternoon and an outage.
The monitoring you should have had
Static thresholds on age(datfrozenxid) are weak. An alert at 300 million tells you nothing about whether you have three days or three months. This is a solved problem with plenty of public post-mortems, and it keeps happening anyway because the failure is slow and unglamorous until it isn't. Alert on the derived numbers instead:
- Days to limit, computed from age and a rolling burn rate. Page at under 7 days, warn at under 21.
- Age trend, not age. A flat 350 million is fine. A 350 million that climbed 40 million overnight is an incident.
- Oldest blocking xmin, taken as the max age across
pg_stat_activity.backend_xmin,pg_prepared_xacts, andpg_replication_slots.xmin/catalog_xmin. This is the leading indicator — it moves beforedatfrozenxiddoes. - Autovacuum liveness. Alert if the newest
last_autovacuumacross the entire cluster is older than a few hours. That single check would have caught the 19-day incident on day one. mxid_age, same treatment, separate metric.
Most teams discover they were missing three of those five during the post-mortem. If you'd rather not build the collectors yourself, MyDBA runs a free health check that reports the countdown and the blocking xmin among other things — a reasonable way to find out where you stand this afternoon.
The interlock is doing its job. Whether it ever has to is entirely a monitoring question.