Two billion transactions later: a wraparound near-miss
Of all the ways a Postgres database can die, transaction ID wraparound is the most cinematic. Not a crash - a slow-motion refusal to start, discovered during a restart you did for unrelated maintenance, with the database issuing warnings you have been ignoring for months.
The 32-bit odometer
Every row in Postgres carries the transaction IDs that created and last modified it. Those IDs come from a 32-bit counter, which wraps around at roughly 4.3 billion. When the counter approaches its limit, Postgres must freeze old rows - stamping them as ancient so future comparisons stay correct - or visibility checks would eventually see new transactions appear older than old ones, and rows would vanish or resurrect at random.
To prevent that apocalypse, autovacuum periodically sweeps tables and freezes old row versions. It runs in the background, quietly, forever. Unless it doesn't.
How autovacuum silently stops keeping up
The classic killers are long-running transactions. A forgotten psql session, a stuck analytics job, a
replication slot nobody dropped - anything holding a transaction open pins the minimum horizon, and nothing older
than that horizon can be frozen. The counter keeps climbing; the horizon stays put; the gap - which you can watch
in pg_stat_activity and the age of the oldest unfrozen datfrozenxid - grows by every write.
Then the warnings begin:
WARNING: database "prod" must be vacuumed within 10000 transactions HINT: Stop the postmaster and vacuum the database in single-user mode.
The first warning appears a billion transactions early. It scrolls past in the logs, unmonitored. The second appears at ten million remaining. The third is the one you notice, because by then the database has forced itself read-only to avoid data corruption, and the fix is an aggressive manual VACUUM FREEZE on your largest tables while your application is down - hours, if the table is the size that usually triggers this.
What makes it unforgivable
Every wraparound incident is preventable, months in advance, with metrics that are free to watch. The age of the oldest transaction ID per database is one query. Long-running transactions are one query. The whole failure mode telegraphs itself in numbers that merely need to be looked at. But those numbers live in the database, and looking at the database is nobody's assigned job, so the odometer rolls on.
The bitter irony: the fix itself - aggressive vacuum - is also the thing that causes the most production disruption (IO storms, lock contention, plan changes from freshly gathered statistics). Teams learn to fear VACUUM, delay it, and thereby guarantee the emergency version of it. Fear of maintenance causes the emergency that maintenance was for.
Living without the cliff
- Monitor transaction ID age per database and alert far before the thresholds.
- Kill long transactions automatically; a transaction older than an hour is almost always a bug.
- Drop unused replication slots; an abandoned slot is a frozen-horizon bomb.
- Tune autovacuum to be more aggressive on your biggest tables, not less, so it never becomes a big-bang job.
Or: don't run the odometer yourself. That is the deal lastbase offers - a Postgres-compatible database where the counter, the horizon, and the vacuum schedule are our problem, not your pager's. More here.