Postgres Transaction ID Wraparound: The Shutdown Is Working As Designed

TL;DR

• XIDs are a 32-bit circular counter. Postgres never runs out of "numbers" in the way people imagine. It runs out of the ability to tell past from future.
• At roughly 13 million XIDs remaining you get warnings. At 3 million remaining, new transactions are refused outright. Read-only queries keep working.
• The fix is never "tune autovacuum harder." It's "find out what's holding xmin back or crashing the worker." Every real incident I've worked traces to one of those two things.
• Monitor age(datfrozenxid) as a trend, not a snapshot. A number at 60% of budget that's been flat for six months is fine. The same number climbing 2M XIDs/hour is an outage in progress.
• Recovery takes hours if you attack the blocker first. It takes all day if you vacuum before you've cleared the blocker, because you're just re-running the same failed scan.

📖 Read the full guide: Postgres Transaction ID Wraparound: Causes, Checks, Fix

The 3am Symptom

The pager goes off and the app logs are full of write failures. Somewhere upstream, Postgres has been telling you for weeks and nobody was listening:

WARNING:  database "orders_prod" must be vacuumed within 11,842,003 transactions

Ignore that long enough and it becomes this, on every INSERT/UPDATE/DELETE:

ERROR:  database is not accepting commands to avoid wraparound data loss in database "orders_prod"

That second line is not a bug report. It's Postgres refusing to let the counter lap itself and silently corrupt visibility. It's doing exactly what it's supposed to do, at exactly the moment it's supposed to do it. Painful, yes. Mysterious, no.

The rest of this piece is the part people usually skip: the actual arithmetic behind the counter, the exact numbers Postgres uses at each stage, the SQL to find who's actually pinning your freeze horizon, and a runbook for the 3am version of this problem.

Why a 32-Bit Counter Is a Clock, Not a Ruler

Every row version in Postgres carries an xmin (who created it) and sometimes an xmax (who deleted or updated it). Visibility is decided by comparing your snapshot's XID against those values. Straightforward, except XIDs are 32-bit integers: about 4.29 billion values, with 0, 1, and 2 reserved (Invalid, Bootstrap, Frozen). Normal XIDs start at 3 and, eventually, wrap back around to 3.

Here's the part that trips people up: XID comparison isn't a number line, it's modulo-2^31. For any given XID, roughly two billion XIDs are treated as "in the past" (visible to you) and roughly two billion as "in the future" (not yet visible). There's no absolute ordering, just a rolling window around the current value.

That works fine as long as nothing sits still for too long. But a row frozen... sorry, a row unfrozen since the Clinton administration eventually falls outside that two-billion window on the "past" side. The counter has moved on, and by the modulo-2^31 math, that ancient row now looks like it's from the future. Which means it becomes invisible to everyone. That's the data-loss scenario the whole mechanism exists to prevent.

Freezing is the fix: mark the row version as unconditionally visible to all transactions, so its original xmin no longer matters for visibility decisions at all. Since 9.4, Postgres does this by setting the HEAP_XMIN_FROZEN hint bit rather than physically overwriting xmin with FrozenTransactionId (2), so you can still forensically read the original xmin later if you need to. Freezing doesn't erase history. It just tells the visibility machinery to stop checking.

The Escalation Ladder (With the Actual Numbers)

Parameter	Default	What changes at this point
vacuum_freeze_min_age	50,000,000	VACUUM freezes any row version whose xmin is older than this
vacuum_freeze_table_age	150,000,000	VACUUM switches to an aggressive scan that can't skip all-visible pages
autovacuum_freeze_max_age	200,000,000 (max 2B)	An anti-wraparound autovacuum is force-launched on the table
vacuum_failsafe_age	1,600,000,000	VACUUM drops cost delays and skips index vacuuming to advance relfrozenxid as fast as possible (PG14+)
(warn limit)	~13M remaining	WARNING: database ... must be vacuumed within N transactions starts firing
(stop limit)	~3M remaining	New XID assignment refused; writes fail cluster-wide for that database

Two things worth being blunt about, because they surprise people every time:

Anti-wraparound autovacuum runs even if you set autovacuum = off. That setting stops routine vacuuming, not emergency vacuuming. Once a table crosses autovacuum_freeze_max_age, Postgres launches a worker regardless.

Anti-wraparound autovacuum does not auto-cancel for conflicting locks. A regular autovacuum worker politely backs off if it's blocking someone's lock request. An anti-wraparound worker does not. This is why a routine migration suddenly hangs for two hours on a table you didn't even touch. You're queued behind a vacuum that refuses to yield.

vacuum_failsafe_age is PG14+. If you're on 13 or earlier, there's no failsafe backstop, and a table can genuinely blow through 200M into the billions before anything more aggressive kicks in.

Check Your Risk in 30 Seconds

Per-database, ranked by exposure:

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

    datname     |  xid_age
-----------------+-----------
 orders_prod     | 187442011
 analytics       |  42011932
 template1       |   1204552

That number's budget is 2^31 - 3,000,000 (~2.144 billion). 187,442,011 / 2,144,483,648 is about 8.7% of budget burned. Fine today. Track it weekly and watch the slope.

Per-relation, and this is the query people get wrong by forgetting relkind:

SELECT c.oid::regclass AS relation,
       c.relkind,
       age(c.relfrozenxid) AS xid_age
FROM pg_class c
WHERE c.relkind IN ('r','m','t')
ORDER BY 3 DESC
LIMIT 15;

       relation        | relkind |  xid_age
------------------------+---------+-----------
 pg_toast.pg_toast_16419| t       | 198774221
 orders                 | r       | 187442011
 orders_mv_daily        | m       |  90113402

That TOAST table topping the list isn't a fluke. TOAST tables carry their own relfrozenxid, and because they're invisible in the normal table list, they're frequently the actual oldest object in the cluster. A database's datfrozenxid can only advance as far as the oldest relfrozenxid among everything it owns. One un-vacuumable TOAST table pins the entire database's age.

For a days-to-limit estimate, don't trust the raw age number alone. Track burn rate instead:

-- run this, wait an hour, run it again
SELECT pg_current_xact_id()::text::bigint AS current_xid, now();

Take the delta between two samples, divide into hours elapsed, and you have XIDs/hour. Divide remaining budget by that rate and you have actual days-to-wraparound. That's the number that matters, not the static age.

Who Is Actually Holding the Horizon Back

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

  pid  |        state        | backend_xmin | xmin_age  | xact_duration
-------+----------------------+--------------+-----------+---------------
 41022 | idle in transaction  |    182200114 |  92211004 | 6 days 03:12:00

There it is. A connection that's been sitting "idle in transaction" for six days is holding the xmin horizon exactly where it was six days ago. VACUUM can run forever on this database and never advance past that point.

Also check pg_prepared_xacts for orphaned two-phase transactions, and replication slots:

SELECT slot_name, active, xmin, catalog_xmin, restart_lsn
FROM pg_replication_slots;

   slot_name    | active | xmin  | catalog_xmin | restart_lsn
-----------------+--------+-------+--------------+-------------
 old_etl_slot    | f      | 91223 |       91442  | 3A/1C4F2210

Don't conflate the two failure modes a stale slot causes. restart_lsn pins WAL segments on disk, that's a storage problem, your disk fills up. xmin/catalog_xmin pins the freeze horizon, that's a wraparound problem, your XID budget stalls. Same slot, two independent ways to hurt you. A standby with hot_standby_feedback = on does the equivalent of pinning xmin from the other direction.

The smoking gun, when you're not sure which of these is your culprit, is in VACUUM VERBOSE output:

INFO:  vacuuming "public.orders"
INFO:  "orders": found 41102 removable, 892011 nonremovable row versions
oldest xmin: 91223004

If "nonremovable" is huge and "oldest xmin" hasn't moved in days, that's not a vacuum performance problem. That's a horizon problem. Stop tuning autovacuum_vacuum_cost_delay and go find the session, slot, or standby holding that xmin.

War Story: The Missing .so That Starved a Whole Cluster

A client migrated hosts. Somewhere in the shuffle, a shared library backing an installed extension didn't get copied over. Every autovacuum worker that touched that particular database hit the missing .so, errored out, and died. Silently, into the log, where nobody was watching closely enough during a migration week.

This went on for nineteen days.

Autovacuum kept doing what it's designed to do: re-prioritize by oldest XID age. So it kept relaunching workers against the same handful of oldest-XID tables, which kept failing on the same missing library, which meant ordinary bloat vacuuming on the rest of the database simply never happened. One high-ingest table grew to 177 GB. That inflated the full backup, inflated every diff after it, inflated WAL volume, and eventually filled the backup bucket.

The lesson that stuck with me: "autovacuum is running" and "autovacuum is succeeding" are different claims, and the log is the only place that tells you which one you've got. Wraparound pressure isn't just a risk in itself, it's a symptom generator for a bun ch of cascading failures: missing bloat vacuuming caused by dead autovacuum workers spills into WAL spikes, full backups that blow out your storage quotas, and index bloat that degrades query performance long after the wraparound alarm is silenced. The log, the horizon, and the worker’s outcome are the three signals you watch, and you watch them as a system, not in isolation.

Your 3am Actions (and How to Avoid Them)

When you’re standing in the middle of the refused-writes fire, the order of operations matters more than the volume of vacuuming you attempt:

1. Kill or resolve the horizon blocker — that idle in transaction session, the stale replication slot, or the orphaned prepared transaction. No vacuum can advance past the oldest xmin, so every second spent vacuuming before this step is wasted.
2. Force an anti-wraparound vacuum on the single oldest table with VACUUM (FREEZE, VERBOSE) your_table; and watch for the oldest xmin line to actually move. If it doesn’t budge, you missed a blocker.
3. Once datfrozenxid starts climbing, let autovacuum catch up on the rest — you’ve bought the cluster breathing room.
4. After the fire is out, instrument the burn rate. The pg_current_xact_id() delta query, scheduled hourly and plotted as a trend, catches acceleration long before the warning threshold. A tool that constantly monitors age(datfrozenxid) trends and horizon blockers — like MyDBA — takes the guesswork out of the slope watching and would have caught that stale slot or six-day idle-in-transaction session before it became a crisis.

Wraparound shutdowns don’t arrive by surprise. They announce themselves in warnings you tune out, in horizons that stay flat instead of advancing, and in autovacuum workers that die silently while the dashboard still says “green.” The counter is predictable; the operator’s attention usually isn’t.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-transaction-id-wraparound-the-shutdown-is-working-as-designed

If you haven’t checked your freeze horizon in the last week, take 30 seconds and run the age(datfrozenxid) query above — or let MyDBA’s free health check surface the slope for you.