PostgreSQL Transaction ID Wraparound: The Slow-Motion Outage You Can See Coming

TL;DR

• Wraparound is what happens when Postgres's 32-bit transaction ID counter runs out of room and autovacuum hasn't frozen old rows fast enough to make space. It is not a random failure mode — it's the endpoint of weeks of autovacuum falling behind.
• Run SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC; right now. Anything north of a few hundred million deserves attention; past 1 billion, you need a plan this week.
• Postgres escalates in stages: normal autovacuum, then forced anti-wraparound vacuum at 200 million XIDs from the limit, then failsafe vacuum at 1.6 billion age, then a hard write-block with about 1 million XIDs left.
• The fix is almost never single-user mode. VACUUM is exempt from the write freeze, so in most cases you vacuum your way out while the app stays up (mostly) online.

Quick hit before we start

If you've watched our whiteboard explainer on wraparound, you've got the mental model: a clock that runs out of numbers. This article is the version with the copy-paste SQL, the exact default thresholds, and the recovery nuance the video simplifies — namely that hitting the wall doesn't automatically mean single-user mode. Keep reading if you want the actual queries you'd run at 2am.

The 32-bit clock: what an XID actually is

Every transaction in Postgres gets a transaction ID — a 32-bit integer, assigned in order, used by MVCC to decide which rows are visible to which transactions. A 32-bit counter gives you roughly 4.2 billion values before it wraps back to 0. That sounds like a lot until you remember every insert, update, delete, and even some reads inside a transaction consumes one. On a busy OLTP system doing a few hundred transactions per second, that's a few years of runway under ideal conditions — and a lot less if things go sideways.

Why it's a circle, not a line

Here's the part most people skip past: Postgres doesn't treat XIDs as a simple ascending line. It splits the XID space into a "past half" and a "future half" relative to the current XID, and uses that split to decide whether one transaction happened before or after another. This is what makes wraparound genuinely dangerous rather than just an inconvenient counter reset. If old, unfrozen rows sit around long enough that the counter wraps past them, those rows can suddenly look like they were written in the future from the perspective of current transactions. MVCC visibility logic then treats them as not-yet-visible — and they silently disappear from query results. This is documented plainly in the Postgres manual's section on preventing wraparound failures: it's framed explicitly as a data-loss scenario, not a housekeeping nuisance.

Postgres's escalating defenses

Autovacuum is the only thing standing between you and this outcome, and Postgres has built an escalating alarm system on top of it:

1. Normal autovacuum — runs based on dead tuple thresholds, freezing rows as it goes via vacuum_freeze_min_age.
2. Anti-wraparound vacuum — triggered per table once its age exceeds autovacuum_freeze_max_age, default 200 million. These vacuums can't be canceled by lock conflicts the way ordinary ones can.
3. Failsafe vacuum — introduced in PG 14, triggered at vacuum_failsafe_age, default 1.6 billion. This mode disables cost-based delays and skips index vacuuming entirely to race toward freezing tuples before the wall.
4. Hard stop — Postgres emits warnings once you're within about 10 million XIDs of the limit, and refuses to assign new XIDs once you're within roughly 1 million. At that point, new writes are blocked cluster-wide.

None of this is subtle. If autovacuum is keeping pace, you'll never see stage 2, let alone 3 or 4.

Check your risk right now

Two queries, thirty seconds:

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

This tells you, per database, how many XIDs old the freeze horizon is. Danger zone starts getting real around 1.5–2.1 billion — the failsafe kicks in at 1.6 billion, and the hard stop is at roughly 2.1 billion minus a million.

SELECT relname, age(relfrozenxid) FROM pg_class
WHERE relkind IN ('r','m') ORDER BY 2 DESC LIMIT 20;

This finds the actual tables dragging the database's age up. Almost always it's one or two large, high-churn tables — not an even distribution.

Find what's actually blocking vacuum

Autovacuum age climbing steadily usually means one of these:

• Idle-in-transaction sessions holding an old xmin open.
• Long-running transactions — batch jobs, ETL, reporting queries wrapped in BEGIN and forgotten.
• Abandoned or inactive replication slots, which hold back catalog_xmin even when nothing is consuming them.
• Orphaned prepared (two-phase) transactions left by a crashed application or broker that never called COMMIT PREPARED.

Check pg_stat_activity for state = 'idle in transaction' and long xact_start values, and pg_replication_slots for slots with no active consumer. Any of these can freeze the effective freeze horizon even while autovacuum looks perfectly healthy in the logs.

What happens when you actually hit the wall

The oversimplified version says "you're stuck in single-user mode." That's mostly wrong. VACUUM, including a manual VACUUM (FREEZE, VERBOSE), is specifically exempt from the wraparound write-block — it needs to run precisely because it's the cure. Most recoveries happen with the database up, reads working, and targeted vacuums running against the worst offenders.

Single-user mode is genuinely required only in narrower cases: disk full so vacuum can't write, superuser connections exhausted so nobody can even connect to run the fix, or catalog corruption bad enough that normal startup fails. A real incident we reviewed involved a missing PostGIS shared library silently blocking autovacuum cluster-wide for 19 days — the extension load failure meant autovacuum workers kept erroring out on startup, table bloat climbed toward 177 GB on one ingest table, and WAL inflation from the backlog eventually filled the backup bucket. No corruption, no single-user mode needed — just three weeks of nobody noticing the age climbing.

Recovery playbook

1. Run the pg_class query, sort by age, identify the worst tables.
2. VACUUM (FREEZE, VERBOSE) tablename; directly on those tables, largest first.
3. Kill idle-in-transaction sessions holding back xmin: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND xact_start < now() - interval '1 hour';
4. Drop abandoned replication slots you've confirmed are dead.
5. Watch pg_stat_progress_vacuum for live progress on the emergency vacuums.
6. Re-run the pg_database age query every few minutes until the trend reverses.

Preventing the next one

Set an alert on age(datfrozenxid) well before 1 billion, not at the failsafe threshold. Turn on log_autovacuum_min_duration so slow or skipped autovacuum runs show up in logs instead of silence. Audit for long-lived transactions and batch jobs that hold connections open across commits. Review replication slots quarterly — a slot with no subscriber is a liability, not a backup plan. If cost-based throttling (autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit) is still at conservative defaults on a busy cluster, autovacuum is structurally guaranteed to lose the race eventually.

If you want a second set of eyes on where your freeze age actually sits, MyDBA's free health check flags this exact metric alongside bloat and replication lag in one pass — worth running before it becomes a page at 2am.

---

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgresql-transaction-id-wraparound-the-slow-motion-outage-you-can-se

If this piece saved you from finding out about wraparound the hard way, run the two queries above today, and consider pointing MyDBA at your cluster for a standing check on freeze age, bloat, and slot hygiene.