The Database Went Read-Only at 4 AM: Transaction ID Wraparound

The page came in at 4 AM: every write to the primary was failing. Not slow, not deadlocked, just flatly refused. The error was one most engineers never see in a career: database is not accepting commands to avoid wraparound data loss in database "app". Reads worked fine. Disk was at 60 percent. There were no long locks in pg_locks, replication lag was zero, and the box was barely doing anything because nothing could commit. Postgres had deliberately put itself into read-only mode to protect our data, and it would not come back until we did something specific.

Every row carries the transaction that created it

Postgres uses MVCC, and the way it decides whether a row version is visible to you is by comparing transaction IDs. Every transaction that writes gets a 32-bit XID, and every row stores the XID that created it in a hidden xmin column. Visibility is "was this row's creating transaction committed before mine started", which is a comparison of XIDs. That 32-bit space is only about 4.2 billion values, and on a busy system you can burn through a billion XIDs in a week, so the counter has to wrap around and be reused.

Reusing a counter that decides visibility is obviously dangerous: if XID 100 gets reused, rows created by the old transaction 100 could suddenly look like they are from the future and vanish from every query. Postgres avoids this with a trick. Old rows that are visible to everyone get their XID replaced with a special frozen marker that always counts as "in the past". Once a row is frozen it no longer participates in the wraparound arithmetic at all. The process that does this freezing is vacuum.

Vacuum is not just about dead rows

Most people think of vacuum as the thing that reclaims space from dead tuples. It does that, but it has a second, more important job: it freezes old live rows so their XIDs can be retired before the counter laps them. Postgres tracks, per table, the oldest unfrozen XID, and the database's overall "age" is the distance between the current XID and the oldest unfrozen one anywhere. When that age approaches the 2 billion mark, autovacuum launches aggressive anti-wraparound vacuums automatically. If those still cannot make progress, and the age crosses a hard safety threshold near the limit, Postgres stops accepting writes rather than risk corrupting visibility. That is the state we were in.

-- which tables are closest to the wraparound limit
SELECT relname,
       age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY xid_age DESC
LIMIT 5;
--      relname       |  xid_age
-- -------------------+------------
--  event_log         | 2146483000
--  users             |    8123455

One table, event_log, had an age over 2.1 billion while everything else was healthy. The whole database was frozen out because of a single table that had not been vacuumed in a very long time.

The real cause: something was holding back the horizon

Autovacuum was running. The logs showed it trying event_log over and over. The problem was that vacuum can only freeze a row if no transaction anywhere still might need to see the old version, and that boundary is set by the oldest running transaction in the entire cluster. We had a reporting connection that had opened a transaction and gone to sleep, an idle-in-transaction session that had been open for nine days. It never wrote anything, but its mere existence pinned the visibility horizon nine days in the past, so vacuum could not freeze anything newer than that. Every anti-wraparound vacuum ran, scanned, and freed nothing, because the one stale transaction said "I might still look at these".

-- the smoking gun: a transaction open for days
SELECT pid, state, now() - xact_start AS open_for, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start
LIMIT 3;
--   pid  |        state        |   open_for    | query
-- -------+---------------------+---------------+--------------
--  41122 | idle in transaction | 9 days 02:14  | BEGIN

So the chain was: an app bug left a transaction open, that pinned the XID horizon, vacuum could not freeze event_log, its age climbed for nine days, and at 4 AM it crossed the wraparound safety line and the database shut the door on writes.

The fix, in order

The recovery is undramatic once you understand it. First, kill the offending transaction so vacuum can actually make progress. Then run vacuum, which you can do even in the read-only wraparound-protection state because that mode specifically allows the operations needed to recover.

-- 1. release the horizon
SELECT pg_terminate_backend(41122);
-- 2. freeze the table that was stuck
VACUUM (FREEZE, VERBOSE) event_log;

The moment that vacuum finished and advanced relfrozenxid, the database's age dropped back to a healthy number and writes resumed on their own. No restart, no data loss. The whole outage came down to one sleeping connection and one table that had been quietly aging for over a week while every dashboard showed green.

Why it hid for so long

Wraparound is a slow leak, not a spike. The age climbs by a few million a day and nothing degrades, no query gets slower, no error appears, right up until the cliff. There is no graceful warning in the default monitoring most teams run, because XID age is not a metric anyone thinks to graph until it has burned them once. The idle-in-transaction session looked harmless too: it used almost no resources and held no table locks, so it never tripped a lock or connection alarm. The only signal was a number slowly counting down to a limit nobody was watching.

Rules of thumb

• Graph age(relfrozenxid) per table and alert well before 1.5 billion. It climbs slowly and silently, then stops all writes at once near 2 billion.
• The wraparound read-only state is a deliberate safety measure, not corruption. You recover by letting vacuum freeze the offending table, which is allowed even in that state.
• Vacuum can only freeze rows older than the oldest running transaction. A single long-lived or idle-in-transaction session pins that horizon and starves vacuum cluster-wide.
• Set idle_in_transaction_session_timeout so a forgotten BEGIN cannot hold the horizon for days. It is the cheapest insurance against this whole class of bug.
• Watch for idle-in-transaction sessions in pg_stat_activity as a first-class health signal, not just locks and connection count.
• Anti-wraparound autovacuum running repeatedly with no progress is a tell that something is pinning the horizon, not that autovacuum is too slow.
• Tuning autovacuum_freeze_max_age down buys margin, but it does nothing if a stale transaction is blocking freezing. Fix the horizon first.