Postgres transaction IDs are 32-bit integers, which caps the counter at roughly 4.29 billion values before it has to wrap. Autovacuum's job is to freeze old row versions so their XIDs stop mattering β it's the only thing standing between normal operation and a forced shutdown. If it falls far enough behind, Postgres will refuse write transactions rather than risk row visibility going wrong.
π Read the full guide: Postgres Transaction ID Wraparound: Causes, Checks, Fix
You don't have to find out the hard way. Run this now and see where you stand:
SELECT datname, age(datfrozenxid) FROM pg_database;
What an XID actually is
Every transaction gets a transaction ID, and that ID is a 32-bit unsigned integer β about 4.29 billion (2^32) possible values. That sounds like a lot until you factor in that a busy OLTP system can burn millions of XIDs a day from normal writes, and a poorly-tuned batch job can burn through them a lot faster. Because the counter is finite, it wraps: transaction number 4,294,967,295 is followed by transaction 0 again, the same way an odometer rolls over.
Postgres's MVCC visibility rules don't just compare raw XID numbers and call "bigger" newer. A row is visible based on whether its inserting XID is "in the past" relative to your snapshot, and that comparison has to account for wraparound β which is exactly where things get dangerous.
Why wraparound would corrupt data if nothing stopped it
XID ordering is circular, not linear. Comparisons happen modulo 2^32, following a "half past, half future" rule: from any given XID, roughly 2 billion values read as earlier and roughly 2 billion read as later. That's fine as long as every row's XID is genuinely recent relative to current transactions.
The problem starts when a row's XID never gets frozen and the counter keeps climbing past it. Say a row was inserted years ago with XID 500 and is still unfrozen. The counter keeps advancing, wraps past its ceiling, and starts issuing low numbers again. A fresh transaction gets XID 400. Under circular comparison, that old row with XID 500 can look like it was inserted in the future relative to the new one β which is exactly the kind of nonsense that lets deleted data reappear or committed data vanish from view.
Freezing a row's XID β replacing it with a sentinel that always reads as "in the past" β takes it out of this comparison entirely. That's the whole point of vacuum's freeze step, and it's the only thing keeping the circular counter from causing real corruption.
Postgres's escalating defense ladder
Postgres doesn't wait for corruption to happen; it has a graduated response. Knowing where you sit on this ladder is the whole game.
Normal autovacuum
Tables get vacuumed based on dead tuple counts, same as always. Nothing special yet.
Age-based prioritization
As a table's unfrozen XIDs get older, autovacuum starts prioritizing it over tables that are simply bloated. Wraparound risk outranks disk space.
Anti-wraparound vacuum
Once a table's XID age exceeds autovacuum_freeze_max_age (default 200 million), autovacuum triggers an anti-wraparound vacuum on it. It freezes eligible tuples even if they're not otherwise due for cleanup, and it runs even if autovacuum is disabled elsewhere in your config β Postgres doesn't let you opt out of freezing.
The autovacuum failsafe
If age keeps climbing past that point, Postgres has one more lever: the autovacuum failsafe (vacuum_failsafe_age, default 1.6 billion). Once triggered, it strips out cost-based throttling and skips index cleanup so the freeze can finish as fast as possible, even at the cost of I/O spikes. It's a sign things are already bad, not a preventive setting.
Hard stop: single-user mode recovery
If none of that catches up in time and the age crosses roughly 2 billion, Postgres shuts down write access entirely. You'll see database is not accepting commands to avoid wraparound data loss in database "yourdb", and every INSERT, UPDATE, and DELETE fails until it's fixed. Reads still work; writes don't. At this point, recovery usually means starting the cluster in Postgres single-user mode recovery and running VACUUM FREEZE directly against the offending database before normal connections are allowed back in.
This is deliberate. An ugly, visible outage is the alternative to quietly corrupting data, and Postgres will take the outage every time.
What actually causes autovacuum to fall behind
The failure mode that gets people here is rarely "autovacuum is off." It's autovacuum falling behind for reasons that look unrelated to freezing at all. In one incident, a missing PostGIS shared library (postgis-3.so, lost after a host move) caused autovacuum workers to crash silently on startup. Because wraparound-priority tables got starved cluster-wide, one table bloated by 177GB over 19 days before anyone noticed, by which point its age was well into anti-wraparound territory.
Long-running transactions and idle-in-transaction sessions cause the same starvation: they hold back the oldest XID horizon vacuum can advance past, so even a well-tuned autovacuum config can't make progress no matter how many workers you throw at it.
Checking your exposure and buying time
Run this against every database, not just the one you're worried about:
SELECT datname, age(datfrozenxid) FROM pg_database;
Cross-reference against anything sitting idle in pg_stat_activity for hours β a single forgotten open transaction can stall freezing across the whole cluster. This is one of the checks worth automating rather than remembering to run manually; MyDBA tracks age(datfrozenxid) trends alongside autovacuum worker activity, so the climb shows up weeks before it becomes an emergency instead of after.
If you're already close, VACUUM FREEZE on the oldest tables resets the horizon and buys real time. It's not free: it takes an ACCESS EXCLUSIVE lock, so plan a maintenance window rather than running it against your busiest table mid-afternoon. Treat it as a disruptive, last-resort move, not routine maintenance.
The author builds MyDBA, a Postgres monitoring and health-check tool β https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-transaction-id-wraparound
If you'd rather have wraparound risk, bloat, and replication lag watched for you instead of queried by hand every Monday morning, that's what MyDBA's health checks do. Worth a look before your next age(datfrozenxid) surprise.
Top comments (0)