The Ticking Clock: Transaction ID Wraparound and How to Avoid Downtime

Every transaction in PostgreSQL that writes data is assigned a transaction ID. These IDs are thirty-two bits, and visibility is determined by comparing them, so at any moment a transaction can see roughly two billion IDs in its past and two billion in its future. The space is circular: eventually the counter must reuse old values.

For an old row to remain correctly visible as the counter comes around again, it must be marked as frozen, permanently in the past regardless of the current ID. Freezing is the mechanism that keeps a finite counter safe over an unbounded lifetime.

Freezing is performed by vacuum. As vacuum processes a table it freezes old tuples and advances the table's oldest unfrozen transaction age. If vacuum stops running effectively on a table, that age keeps climbing toward the two-billion horizon, and the database moves toward wraparound.

This ties wraparound directly to the vacuum health discussed earlier in this series. A table whose autovacuum is blocked is not only bloating; it is aging.

PostgreSQL defends itself aggressively. Well before the limit, it triggers anti-wraparound autovacuum on aging tables even if they would not otherwise be vacuumed, and it emits escalating warnings in the log. If the age is allowed to approach the hard limit, the database will refuse to accept new write transactions to protect data integrity, requiring intervention to recover.

That protective stop is effectively an outage. It is also the outcome the entire freezing machinery exists to prevent, and reaching it means monitoring failed for a long time.

The relevant metric is exposed directly and cheaply: the age of the oldest unfrozen transaction, available per table and per database from the catalogs. Because wraparound develops slowly and predictably, a rising age is a clear, early, unambiguous warning with a long lead time.

There is no excuse for wraparound to arrive as a surprise. It is among the most detectable conditions in the database, provided something is actually looking at the age continuously.

The correct posture is prevention: keep vacuum healthy so freezing keeps pace, and treat a climbing age as an early signal, not an emergency. Routine vacuum that advances freezing is a low-risk, policy-authorized action. The situations that let age climb, a blocked autovacuum worker, a stuck long-running transaction, an aggressive workload, often need human judgment to resolve at the source.

An automated DBA earns its place here by watching the clock without pause and escalating with weeks of margin, so the fix is a routine adjustment rather than a recovery from read-only lockdown.