Vacuum, Dead Tuples and the Wraparound Shutdown Nobody Plans For

The database refused writes with a message about vacuum and transaction id age, and the team read it as a disk problem, because refusing writes feels like a storage failure. It was the opposite. The disks were fine. The database was protecting its own numbering system, and it had been warning for weeks, in metrics nobody graphed, before it finally stopped the world.

This is the wraparound failure mode of MVCC, and it is one of the few database failures that is completely deterministic, completely preventable, and completely fatal to availability if ignored.

This was Postgres 16.3 with autovacuum unable to keep up with a bulk update workload, and the mechanics apply to any long lived Postgres.

The numbering system

Every transaction in Postgres gets a transaction id, an xid, a 32 bit counter. Rows carry the xid of the transaction that created them and the one that deleted them, and visibility is decided by comparing a reader's xid against those markers. This is MVCC: readers see a snapshot defined by which xids were committed when they started, and updates create new row versions rather than rewriting, leaving dead tuples behind.

The counter is finite. Four billion values, and when it reaches the top it wraps to the beginning. Wraparound is catastrophic for visibility, because a row created before the wrap would suddenly appear to be in the future of a reader from after it, inverting the visibility logic. Postgres therefore treats xid age as a resource that must never be allowed to run out.

Why vacuum is the defence

The defence is that old row versions, once no transaction can see them, are marked dead and their xids stop mattering. Vacuum does that marking, and a special mechanism, freezing, rewrites very old committed rows so they no longer carry a live xid at all, making them visible to everyone forever and removing them from the age accounting.

So the system's safety depends on vacuum and freeze keeping up with the rate at which xids are consumed and dead tuples accumulate. When they keep up, wraparound is a theoretical concern. When they do not, the age of the oldest unfrozen xid climbs toward the limit, and the database escalates.

The escalation ladder

Postgres does not go straight to shutdown. It warns, with autovacuum becoming more aggressive and log messages about xid age, giving an operator weeks of visible signal. Then, as the age nears the hard stop, it begins refusing to accept new transactions that would consume xids, which in practice means refusing writes, while reads continue. The database has chosen a controlled halt of changes over the uncontrolled corruption wraparound would cause.

The message names the table with the oldest relfrozenxid, which is the table to look at, and the age number tells you how close the system is to the edge.

How a healthy database falls behind

A few workloads defeat autovacuum's default pacing, and they are common.

Bulk updates over huge tables. A single statement that touches millions of rows creates millions of dead tuples and consumes one xid per statement, but the dead tuples it creates must be vacuumed, and autovacuum's per run budget can take many runs to clear a table that large, during which the age climbs. This is the pressure in postgres autovacuum not keeping up.

Long running transactions. A transaction that stays open for hours pins the visibility horizon, because no row version it might still see can be freed. One forgotten analytics session can freeze vacuum's progress on the whole database, which is the same pinning that makes the 40001 retry discussion care about transaction length for a different reason.

High xid burn rate. A workload that runs enormous numbers of small transactions consumes xids quickly even without much data change, because every transaction, even read only ones in some configurations, advances the counter. The burn rate, not the data volume, is what vacuum must outrun.

Watching the age before it watches you

The metric is the age of the oldest unfrozen xid, per database and per table, against the wraparound limit. Postgres exposes it through the age function on the frozen markers:

SELECT datname, age(datfrozenxid) FROM pg_database;
SELECT relname, age(relfrozenxid) FROM pg_class
WHERE relkind = 'r' ORDER BY 2 DESC LIMIT 10;

Graph the maximum age as a line against a threshold, and alert at a fraction of the limit, because the response to a rising age is tuning and manual vacuum, which take time, and time is the thing a late alert removes. The warnings Postgres logs are the same signal earlier, and they belong in the alert pipeline, not in a log file nobody reads.

Recovering and preventing

Once the age is high, the recovery is to vacuum the oldest tables with freeze, giving the work the resources to finish, and to remove the pins, the long running transactions, that block progress. It is urgent, interruptible only at cost, work, which is why prevention is the real practice.

Prevention is tuning autovacuum to the workload, more workers and faster triggers on the big hot tables, keeping transactions short, and monitoring xid age as a first class capacity metric, exactly like disk and connections. A database that has never been watched for xid age is not safe from wraparound. It is merely young.

The rule I keep

MVCC's safety depends on vacuum freezing old rows faster than transactions consume ids, and when that rate fails, Postgres warns for weeks and then stops writes to protect its numbering. Graph the oldest unfrozen xid, alert at a fraction of the limit, kill long transactions, and size autovacuum to the burn rate.

The shutdown is not a bug. It is the database choosing a survivable failure over an unsurvivable one, and the weeks of warnings are the incident, arriving early, in a form that is easy to ignore. The dead tuple and index interaction is the performance cousin in why an index only scan still read the heap.

Published on debugly.dev by Rohit Bhadani | Founder of Krova Cloud