The two-billion transaction cliff

Most database capacity problems give you warning. Disk fills gradually, connection pools saturate under load, query latency creeps up as a table grows. You get a slope, and a slope gives you time.

Transaction ID exhaustion is not a slope. It is a cliff.

Postgres runs normally until it decides that continuing would risk silent data corruption, and then it refuses to assign new transaction IDs at all. Writes stop. The database is still up, still serving reads, still perfectly healthy by every metric on your dashboard except the one nobody is graphing.

I have felt the sinking feeling of watching age(datfrozenxid) climb while vacuums did nothing. Success in the logs. Age still going up. That is the moment this stops being a textbook problem.

The intuitive recovery steps are actively harmful. That is why the failure is worth understanding in detail.

Start with why the counter exists at all. The cliff only makes sense once you see the circle.

Postgres implements MVCC by stamping every row version with the transaction that created it (xmin) and, if deleted or updated, the transaction that removed it (xmax). Deciding whether your transaction can see a given row version means comparing transaction IDs: a row inserted by a transaction 'in the future' relative to yours is invisible to you.

Those IDs are 32 bits. Postgres compares them using modulo arithmetic, so the space is a circle rather than a line. For any given XID, roughly two billion values look older and two billion look newer. That works fine until a row version survives longer than two billion transactions, at which point it silently crosses from the past into the future.

One detail that matters for capacity planning: XIDs are assigned lazily. A read-only transaction gets a virtual ID and costs you nothing. You only spend from the budget when a transaction actually writes. Savepoints and subtransactions each consume one too, which is how an ORM that wraps every statement in a savepoint can burn through the counter far faster than the traffic graph suggests.

Freezing is the escape hatch. Not a clever trick — an exemption from the circle.

The way out is to mark old row versions as frozen, meaning "committed so long ago that everyone can see this, stop comparing." Frozen rows are exempt from the circular comparison and stay valid indefinitely.

Before Postgres 9.4 freezing physically overwrote xmin with FrozenTransactionId (the literal value 2), destroying the forensic record. Modern versions set a flag bit in the tuple header and preserve the original xmin, so you can still see who inserted a row.

Vacuum is what does the freezing, and it records progress in two places: pg_class.relfrozenxid per table, and pg_database.datfrozenxid per database, the latter simply being the minimum of the former.

A single neglected table therefore holds the entire database hostage.

SELECT c.oid::regclass                                    AS relation,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 20;

The escalation ladder

You do not go from healthy to refusing writes in one step. Postgres climbs a ladder first, and each rung is a separate configurable threshold. Knowing which rung you are standing on tells you how much time you have.

The last two rungs produce these messages, which are worth putting into a log alert verbatim:

WARNING:  database "mydb" must be vacuumed within 39985967 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.

ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "mydb"

Here is the part that surprises people, and the part that gave me that sinking feeling in the first place.

Vacuum can only advance relfrozenxid up to the oldest transaction ID that anything in the system still needs. If that horizon is pinned, vacuum can run continuously, burn enormous amounts of I/O, report success, and advance nothing.

The vacuums are running. The age keeps climbing.

There are three usual culprits.

-- 1. Long-running or idle-in-transaction sessions
SELECT pid, age(backend_xmin) AS xmin_age, state,
       now() - xact_start AS duration, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

-- 2. Orphaned prepared transactions (two-phase commit left behind)
SELECT gid, prepared, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY xid_age DESC;

-- 3. Abandoned replication slots, usually a replica nobody rebuilt
SELECT slot_name, active,
       age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;

The replication slot case is the most common in my experience of reading other people's postmortems, and the most avoidable — which is what makes it so infuriating. A slot exists so the primary retains everything a replica has not yet consumed. That is the whole contract. Delete the replica without dropping the slot and the primary will dutifully hold the horizon still, forever, on behalf of a server that no longer exists.

I keep seeing the same sequence. Someone decommissions a replica. The VM is gone, the Terraform is gone, the runbook no longer mentions it. The slot stays. Postgres has no opinion about whether the consumer is real; it only knows a slot asked it to wait. So it waits. Autovacuum works itself into the ground. You graph vacuum duration and think the system is busy and healthy, while the frozen horizon sits exactly where that dead replica left it. By the time anyone thinks to query pg_replication_slots, you are already past autovacuum_freeze_max_age, staring at age(datfrozenxid) climbing while vacuums do nothing useful. Dropping a slot is a one-liner. Forgetting to is how you buy a very long night for a problem that was never subtle.

Three commands that make it worse

Once writes are blocked, the instinct is to reach for the biggest hammer available.

All three of the obvious hammers are wrong, and the documentation is explicit about each.

1. 01VACUUM FULL — it requires a transaction ID to run, so in the failure state it simply fails. Run it in single-user mode and it will consume an XID, pushing you closer to the cliff you are trying to back away from.
2. 02VACUUM FREEZE — it works, but it does substantially more than the minimum needed to restore service. When the objective is to get writes back as fast as possible, extra work is not a virtue.
3. 03Single-user mode — for years this was the standard advice, and it is now explicitly discouraged. It takes the system down and disables the very wraparound safeguards protecting your data.

The supported recovery is unglamorous: resolve prepared transactions, end long-running ones, drop stale replication slots, then run a plain database-wide VACUUM as a superuser. The three-million-transaction margin exists precisely so an administrator has room to do this.

Unglamorous is the point. You want writes back. You do not want a more interesting outage.

The 2015 Sentry outage

On 20 July 2015, hosted Sentry was down for most of the US working day because its primary Postgres database hit wraparound protection and stopped accepting writes. Their public writeup is one of the more useful incident reports in the Postgres world, mostly because of what went wrong during recovery rather than what caused it.

They were a write-heavy workload and had already been tuning autovacuum aggressively. It tripped anyway.

They chose to let the in-flight autovacuums finish rather than restart into single-user mode. Hours later the database still refused writes, one of the autovacuums had apparently failed, and — the detail worth stealing — autovacuum logging was not verbose enough to tell them which one or why. Meanwhile the read-only database backed up Redis buffers until they had to discard the entire event backlog.

Treat it as capacity, not as an incident

XID consumption is a rate, and the budget is fixed. Divide one by the other and you have a time-to-cliff you can graph, which converts a dramatic outage into an ordinary capacity forecast.

SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid)
             / current_setting('autovacuum_freeze_max_age')::numeric, 1)
         AS pct_of_autovacuum_trigger
FROM pg_database
ORDER BY xid_age DESC;

Alert when that percentage crosses something like 150%, not when it approaches wraparound. Being past the anti-wraparound trigger is normal on a busy system; staying past it while the age keeps climbing means vacuum is losing the race, and that is the signal you actually want.

I would rather get that page on a Tuesday than discover the cliff on a Saturday.

There is a real tradeoff in raising autovacuum_freeze_max_age, and it is disk in the commit log rather than risk. Postgres keeps two bits of commit status per transaction back to the horizon:

Half a gigabyte to buy ten times the headroom is a trade most systems should take without much deliberation. The documentation goes further and recommends the maximum outright if that storage is trivial relative to your database size.

I graph this. I alert on it. I tell every new on-call the three commands that will make it worse. None of this is exotic tuning — one graph, one alert, and knowing that three commands are traps. Wraparound keeps taking down real systems not because it is subtle. Nothing warns you until the ladder is nearly climbed.