Postgres Transaction ID Wraparound: Causes, Checks, Fix

2026-08-02

Transaction ID Wraparound in Postgres: The Mechanism, the Queries, and the Runbook

The 3am symptom: reads fine, writes dead

The page you get looks like the application has half-died. Dashboards render. Reports run. Health checks that do a SELECT 1 are green. But every insert, update and delete comes back with this:

▶ Watch on YouTube: Transaction ID Wraparound: The Failure That Stops Writes

Postgres Transaction ID Wraparound: Causes, Checks, Fix

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.

Here's what's strange the first time you see it: SELECTs still work, read replicas still serve traffic, and VACUUM itself still runs. Only statements that need a fresh transaction ID are refused. That split is the first clue to what's actually happening. Postgres has not crashed, corrupted anything, or lost data. It has refused to assign new transaction IDs because doing so would eventually make committed rows invisible. That refusal is the safety feature working exactly as designed, enforcing a limit it started warning you about roughly 1.4 billion transactions ago. The failure happened weeks earlier, when something started blocking vacuum and nobody had an alert on it.

This is the long version: the mechanism, the exact SQL to check your risk today, the thresholds by version, and the runbook for when you're already past the line.

Why a 32-bit counter is a clock, not a ruler

Every row in a Postgres heap carries xmin (the transaction that inserted it) and xmax (the transaction that deleted or updated it). Visibility is decided by comparing those values against your snapshot. That machinery is why MVCC works without read locks.

Transaction IDs are 32-bit. That's about 4.29 billion values, and IDs 0, 1 and 2 are reserved for invalid, bootstrap and frozen, so normal XIDs start at 3. The counter does not overflow and error out. It wraps.

Comparison is modulo 2^32. For any given XID, roughly 2 billion values are treated as "in the past" and 2 billion as "in the future". That's the whole problem in one sentence. If the counter laps a row that was inserted 2.15 billion transactions ago and never frozen, that row's xmin stops looking like ancient history and starts looking like the future. The row doesn't error. It disappears. Data that was there yesterday quietly stops showing up in query results today, with no exception raised anywhere.

The escape hatch is freezing. VACUUM marks old tuples as unconditionally visible to all current and future transactions, then advances pg_class.relfrozenxid for the table and, once every table is done, pg_database.datfrozenxid for the database. Since 9.4 freezing sets a hint bit (HEAP_XMIN_FROZEN) in the tuple header rather than rewriting xmin to 2, so the original inserting XID is still there for forensics. Useful when you're reconstructing an incident.

VACUUM is the only thing that freezes. Not checkpoints, not REINDEX, not a restart. If vacuum can't run or can't advance the horizon, the clock keeps ticking and nothing stops it.

The cluster-wide part people miss

The limits are not computed per database. SetTransactionIdLimit() takes the single oldest pg_database.datfrozenxid across all databases in the cluster and derives the warn and stop limits from that.

So a database you never connect to can shut down writes for everything else. template0 has datallowconn = false, which means you can't just psql into it. It's frozen at creation and normally fine, but I have seen clusters where someone flipped datallowconn on template0 to install an extension and left it that way. Check it. Check template1 too — I've seen it be the oldest database in a cluster more than once, usually because someone cloned a database from it years ago and nobody has opened a connection to it since. And check that abandoned staging database somebody created in 2022.

The numbers that actually matter

| Setting / constant | Default | What it triggers | Version notes | |---|---|---|---| | XID wrap limit | oldest datfrozenxid + 2^31-1 | Theoretical point where old rows flip invisible | All versions | | Stop limit | wrap limit − 3,000,000 (age ≈ 2,144,483,647) | GetNewTransactionId() refuses new XIDs | All versions | | Warn limit | stop limit − 10,000,000 | WARNING: database "x" must be vacuumed within N transactions | All versions | | vacuum_freeze_min_age | 50,000,000 | Minimum tuple age before VACUUM will freeze it | All versions | | vacuum_freeze_table_age | 150,000,000 | VACUUM goes aggressive: scans every page not marked all-frozen | All versions | | autovacuum_freeze_max_age | 200,000,000 (max 2,000,000,000) | Anti-wraparound autovacuum, even if autovacuum = off | All versions | | vacuum_failsafe_age | 1,600,000,000 | VACUUM skips index cleanup and truncation, drops cost delay | PG14+ | | autovacuum_multixact_freeze_max_age | 400,000,000 | Anti-wraparound vacuum for MultiXacts | All versions | | vacuum_multixact_freeze_min_age | 5,000,000 | Minimum MultiXact age before freezing | All versions | | multixact_failsafe_age | 1,600,000,000 | MultiXact equivalent of the failsafe | PG14+ |

As of PostgreSQL 18, XIDs in core are still 32-bit. The 64-bit XID patches have been circulating on pgsql-hackers for years and none of them have landed. This remains an operational responsibility on every supported version.

Check your risk right now: five queries

1. Per-database age, sorted by risk.

SELECT datname,
       age(datfrozenxid)                                    AS xid_age,
       2144483647 - age(datfrozenxid)                       AS xids_until_stop,
       round(100.0 * age(datfrozenxid) / 2144483647, 1)     AS pct_of_runway,
       mxid_age(datminmxid)                                 AS mxid_age,
       datallowconn
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Anything over 200 million means autovacuum has already been asked to do anti-wraparound work and hasn't finished. Anything over 500 million on a healthy cluster means something is wrong.

2. Per-table age, including TOAST.

TOAST relations have their own relfrozenxid and they are frequently the actual laggard, because the heap is small and the TOAST tables hold the wide columns. Take the greater of the two.

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,
       c.reloptions
FROM pg_class c
LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relkind IN ('r', 'm')
  AND c.relnamespace::regnamespace::text NOT IN ('pg_catalog','information_schema')
ORDER BY max_age DESC
LIMIT 20;

Watch the reloptions column. That's where you'll find {autovacuum_enabled=false} left behind by a migration two years ago.

3. MultiXact age. Covered in query 1 via mxid_age(datminmxid). Per table, use mxid_age(relminmxid) from pg_class.

4. XID burn rate. Run this twice, some minutes apart:

SELECT pg_snapshot_xmin(pg_current_snapshot()) AS xid_now,
       clock_timestamp();

Use this, not pg_current_xact_id() and not the legacy txid_current(). Those functions assign a real XID, which means your monitoring query is itself burning runway every time it polls. pg_snapshot_xmin(pg_current_snapshot()) reads the counter without touching it.

5. Days to limit.

burn_per_day  = (xid_now_2 - xid_now_1) / elapsed_seconds * 86400
days_to_stop  = (2144483647 - current_max_age) / burn_per_day

Worked example from a real cluster. Two samples 600 seconds apart gave a difference of 298,400 XIDs, so 497 XIDs/sec, or 42.9 million per day. The oldest datfrozenxid age was 412,000,000. That leaves 1,732,483,647 XIDs of runway, which is 40 days if freezing stops dead. Forty days sounds comfortable until you remember the blocker had already been in place for nine of them.

That's the general shape: about 500 write transactions per second burns roughly 43 million XIDs per day and would eat the full 2.14 billion runway in under two months. "Two billion is plenty" holds only for as long as vacuum keeps pace — the runway shrinks with every transaction and refills only when VACUUM freezes rows, so treat it as a countdown clock, not a stockpile.

Postgres's escalation ladder

| Rung | Trigger | What changes | What it costs you | |---|---|---|---| | Normal autovacuum | dead tuple thresholds | Opportunistic freezing of pages it already visits | Cost-delayed, cancels itself for lock waiters | | Aggressive vacuum | table age > vacuum_freeze_table_age (150M) | Scans every page not marked all-frozen in the VM | Much more I/O, full heap pass | | Anti-wraparound autovacuum | table age > autovacuum_freeze_max_age (200M) | Runs even if autovacuum = off; will not cancel for lock waiters | Blocks ALTER TABLE and other DDL for its whole duration | | Failsafe | table age > vacuum_failsafe_age (1.6B) | Skips index vacuuming and heap truncation, ignores cost delay | Index bloat accumulates; logs that it is bypassing nonessential maintenance | | Warning | fewer than 10M XIDs to stop limit | WARNING: database "x" must be vacuumed within N transactions | Logged on every XID assignment | | Stop | fewer than 3M XIDs to wrap | New XIDs refused | Writes down; reads and VACUUM still work |

The warning looks like this and you will see it a lot once it starts:

WARNING:  database "orders" must be vacuumed within 7382914 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions,
        or drop stale replication slots.

Note the distance between rungs. By the time the PG14 failsafe fires at 1.6 billion, you have driven past the aggressive threshold and the anti-wraparound threshold and ignored roughly 1.4 billion XIDs of escalating behaviour. Wraparound is a monitoring failure, not a Postgres failure.

Why vacuum falls behind: the usual pileup

VACUUM cannot advance relfrozenxid past the oldest xmin held by any running transaction, prepared transaction, or replication slot. Run VACUUM a hundred times against a blocked horizon and you'll get a hundred rounds of I/O and zero progress. Kill the blocker first, then vacuum.

Four things hold the horizon.

Long-running and idle-in-transaction backends:

SELECT pid, datname, usename, state,
       backend_xmin,
       age(backend_xmin)                            AS xmin_age,
       now() - xact_start                           AS xact_duration,
       left(query, 80)                              AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Orphaned two-phase transactions:

SELECT gid, prepared, owner, database, transaction, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

If you don't use two-phase commit deliberately, anything in here is garbage from a crashed distributed transaction manager. ROLLBACK PREPARED '<gid>';

Replication slots:

SELECT slot_name, slot_type, database, active,
       xmin, catalog_xmin,
       age(xmin)                                    AS xmin_age,
       age(catalog_xmin)                            AS catalog_xmin_age,
       pg_size_pretty(pg_wal_lsn_diff(
         pg_current_wal_lsn(), restart_lsn))        AS wal_retained
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;

An inactive slot with a non-null xmin is holding both your freeze horizon and your WAL. The disk symptom usually shows up first.

Standbys with hot_standby_feedback = on. A stuck query on the replica propagates its xmin back to the primary and pins the horizon there. Check pg_stat_replication.backend_xmin on the primary.

Then the operational failures: too few autovacuum_max_workers for the number of large tables, a cost limit that can't keep pace with a 2TB table, autovacuum_enabled=false set per table during a migration and never reverted, and autovacuum workers that crash before finishing.

That last one is worth its own section.

War story: the extension that ate 19 days of autovacuum

Last month I picked up an incident that started as a disk alert, not an XID alert.

We had migrated a cluster to new hosts. The build was almost right, but postgis-3.so was missing on the new box. Every autovacuum worker that got assigned a database containing PostGIS-dependent objects died at connection time trying to load the library. The worker exited, the launcher started another one, and because tables at wraparound risk are prioritised for selection, the launcher picked the exact same target every cycle.

Autovacuum made no progress cluster-wide for 19 days. Not slow progress. None.

What surfaced first was storage. One ingest table bloated to 177GB. Full backups grew to match, differentials grew, WAL volume grew, and the backup bucket filled. The team spent the first hours of the incident looking at retention policies and S3 lifecycle rules, because that's what the alert said. Root cause was confirmed on 2026-07-29: a missing shared library.

Two lessons I took from it. First, autovacuum liveness and wraparound risk are the same alarm, and neither of them was wired up. If we had been alerting on "no successful autovacuum in this database in the last N hours" we'd have caught it on day one. Second, the visible symptom of a vacuum failure is very often disk before it's ever XIDs. If you're chasing unexplained growth, check autovacuum's pulse before you start tuning fillfactor.

The recovery runbook

1. Free the horizon before you vacuum anything. Run the three blocker queries above. Kill the idle-in-transaction backend, ROLLBACK PREPARED the orphan, drop the dead slot. Vacuuming while a blocker holds xmin is burning I/O for nothing.

2. Vacuum the oldest tables first, not the whole database. A database-wide VACUUM will spend hours on tables that aren't the problem. Take the top of your per-table query and go one at a time:

VACUUM (FREEZE, VERBOSE, INDEX_CLEANUP OFF, TRUNCATE OFF) public.event_ingest;

INDEX_CLEANUP OFF skips the index scan phase, which on a heavily indexed table is most of the runtime. TRUNCATE OFF avoids the exclusive lock at the end. Both became VACUUM options in PG12. You'll pay for the skipped index cleanup later with a regular vacuum, and that's a fine trade at 2.1 billion.

3. Watch progress.

SELECT p.pid, p.datname, p.relid::regclass AS relation, p.phase,
       p.heap_blks_scanned, p.heap_blks_total,
       round(100.0 * p.heap_blks_scanned /
             nullif(p.heap_blks_total, 0), 1)       AS pct_done,
       now() - a.xact_start                         AS running_for
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid);

4. Give it resources. Raise maintenance_work_mem for the session (2GB is reasonable on a machine with headroom), raise autovacuum_vacuum_cost_limit, and set vacuum_cost_delay = 0 in your manual session.

5. If you're already past the stop limit, just connect as a superuser and vacuum. The 3-million-XID margin exists precisely so an administrator can recover from a normal client connection. The check lives in GetNewTransactionId(), so reads work and VACUUM works, because VACUUM does not normally consume an XID. The HINT in the error text telling you to use single-user mode is conservative advice that predates the practical reality; don't reach for it before trying a normal superuser session first.

6. Single-user mode, genuine last resort:

sudo systemctl stop postgresql
sudo -u postgres postgres --single -D /var/lib/postgresql/16/main orders

backend> VACUUM (FREEZE);

In single-user mode the stop-limit check is bypassed (it's conditional on IsUnderPostmaster). Gotchas: no autovacuum, no background writer help, statements terminate on EOF rather than semicolon in some builds, and your entire cluster is down while you do it. On PG14+ with the failsafe, I have not needed this once.

MultiXact wraparound: the second counter nobody watches

SELECT ... FOR SHARE, FOR KEY SHARE, and any row locked by more than one transaction at a time create a MultiXactId — shared foreign key checks are a common source of this. These have their own 32-bit counter, their own SLRU for offsets, and a separate members SLRU that can be exhausted independently of the offsets counter. A workload with heavy foreign-key contention can hit MultiXact limits while age(datfrozenxid) looks perfectly healthy.

SELECT datname, mxid_age(datminmxid) AS mxid_age
FROM pg_database
ORDER BY mxid_age DESC;

The error text mirrors the XID one:

ERROR:  database is not accepting commands that assign new MultiXactIds
        to avoid wraparound data loss in database "orders"

Governed by autovacuum_multixact_freeze_max_age (400M), vacuum_multixact_freeze_min_age (5M), and on PG14+ multixact_failsafe_age (1.6B). Put mxid_age on the same dashboard as age(datfrozenxid), not as an afterthought, and you'll never think about it again.

Monitoring that makes this boring

The raw age threshold is the wrong alert. "Age exceeds 500 million" tells you nothing about urgency, because 500 million on a cluster burning 2 million XIDs a day is a year of headroom and on a cluster burning 100 million a day it's sixteen days.

Alert on the countdown:

Days to stop limit, computed from measured burn rate. Warn at 30 days, page at 10.
Autovacuum liveness: no successful autovacuum recorded in a database for N hours. This is the alert that would have caught the PostGIS incident on day one.
Oldest xmin holder age, from pg_stat_activity, pg_prepared_xacts and pg_replication_slots combined, so you know what is pinning the horizon before it matters.
Per-table max age including TOAST, so you know which relation to hit first.
Set log_autovacuum_min_duration = 0. The log volume is trivial and the record of which tables autovacuum touched and when is the single most useful artefact during an incident.

Building all of that from scratch is a couple of days of work and then ongoing maintenance as versions change. MyDBA's free health check reports XID age against the actual computed limit, a days-to-limit countdown based on your measured burn rate, what is currently blocking vacuum (backends, prepared transactions, slots), and it generates the recovery script for your specific oldest tables. It's read-only and takes a couple of minutes.

If you'd rather build it yourself, the queries in this article are the whole thing. The point is that somebody owns the countdown.

Prevention checklist

Set idle_in_transaction_session_timeout (5 to 15 minutes for OLTP) and a sane statement_timeout. This kills the most common blocker class outright.
Monitor slot lag and drop stale slots. Consider max_slot_wal_keep_size as a backstop.
Size autovacuum_max_workers and autovacuum_vacuum_cost_limit for your biggest table, not your average one.
Audit reloptions for leftover autovacuum_enabled=false. Make it a CI check on your migration files.
Verify template0 and template1 ages during every upgrade and every host migration.
Re-measure burn rate after any significant traffic change. A new batch job can double it overnight.
Upgrade. The PG14 failsafe alone converts a lot of would-be outages into ugly log lines, and the PG15 and PG17 vacuum improvements materially cut the time an emergency freeze takes.

Postgres gives you 200 million XIDs of hints, then 1.4 billion more, then ten million transactions of explicit warnings, then it stops. Nobody gets surprised by wraparound. They get surprised by how long they weren't looking.