Transaction ID Wraparound

A database that has been healthy for years begins logging warnings about transaction ids, and then refuses to accept writes entirely. Disk is 40% used, CPU is low, replication is current, every query plan is fine. The resource that ran out was not storage or compute: it was the space of transaction identifiers.

Row visibility under multi-version concurrency control is decided by comparing the transaction id that created a row version with the reader's own. In Postgres those ids are 32 bits, so the comparison is only meaningful inside a window of roughly 2 billion transactions. To let ids be reused, very old rows are frozen - marked as visible to everyone, so their creating id no longer needs to be compared. Freezing is vacuum's other job, and it is the one nobody watches.

If freezing falls far enough behind, the engine faces a choice between letting rows silently become invisible and stopping. It stops, requiring a vacuum in single-user mode before writes resume.

Why it matters

It is the only common database limit whose unit is transactions rather than bytes, which is why it bypasses every dashboard a team builds. A write-heavy service can consume ids far faster than its data grows: one small transaction per request at 5,000 requests per second is over 400 million ids a day, so the 2-billion window is a matter of days of headroom, not years.

The recovery is also unusually bad. Normal vacuum runs alongside traffic; the emergency vacuum runs with the database in single-user mode, and its duration is set by the size of the largest unfrozen table. For a multi-terabyte table that is hours, and there is no way to make the service available while it happens. This is a failure to prevent, not to handle.

Implementation patterns

• Alert on age, not on bytes. Track the oldest unfrozen transaction age per database against autovacuum_freeze_max_age (default 200 million) and page well before the engine's own warnings begin.
• Give autovacuum the resources to keep up on large tables: more workers, a lower cost delay, and per-table settings for the few tables that dominate.
• Hunt horizon holders continuously - long transactions, prepared transactions and abandoned replication slots. A replication slot for a decommissioned replica is the classic cause, because nothing complains about it.
• Prefer aggressive freezing on append-mostly tables, where freezing is cheap and the rows will never change again.
• Partition very large tables. Freezing a 200 GB partition is a bounded job; freezing a 4 TB table is the thing that turns a warning into a day of downtime.

Industry example

The failure mode is documented in Postgres's own manual, which has spelled out the stop and the single-user recovery since the 8.x series (2005), and it has been reported publicly by several engineering teams over the years - always with the same shape: a high write rate, a table too large to freeze quickly, and a horizon held by something nobody was monitoring.

It is also a good example of a limit that migrates with your architecture: engines using 64-bit transaction ids have no equivalent, so a store comparison that only looks at throughput and latency misses an operational difference that can cost a day of availability. Worth knowing when someone proposes a change of engine for unrelated reasons.

Failure scenarios

• Write refusal under a pinned horizon, with recovery in single-user mode and an outage set by table size.
• The abandoned replication slot, holding the horizon for months while every other metric looks healthy.
• A high-volume job consuming ids without producing data - a poller running a transaction per second per worker, invisible in storage growth.
• Autovacuum starved on a cluster with many large tables, so the one table that matters never gets a worker.
• Monitoring the wrong number: table and disk growth look fine throughout, which is exactly why the dashboards do not help.

Trade-offs

Freezing aggressively costs I/O now to avoid a stop later: an aggressive freeze setting rewrites pages that might never have needed it, consuming disk bandwidth and write-ahead log volume during hours you would rather keep quiet. Deferring it conserves that I/O and accumulates an obligation with a hard deadline.

The trade is therefore steady, predictable cost against a rare catastrophic one, and the asymmetry is extreme enough that there is no real argument for the lazy setting on a large, write-heavy table. The cost of being early is measured in percentage points of I/O; the cost of being late is measured in hours of total unavailability.

When not to use it

This is not a technique to adopt or avoid - it is a constraint to respect. The genuine decision is how much headroom to buy, and the answer differs by engine and workload: on a 64-bit-id engine the constraint does not exist, and on a low-write database the default settings have years of slack and extra tuning is noise.

The mistake worth naming is treating the engine's own warning threshold as the alert. By the time the database warns you, the remaining headroom is small and the only remedies are slow. The alert belongs far earlier, in your monitoring, on a number nobody looks at by default.

Interview question

Q: Your Postgres cluster starts refusing writes with a transaction-id error. Disk and CPU are fine. Explain what happened, how you restore service, and what you put in place so it cannot happen again.

What a strong answer covers: 32-bit ids and visibility by comparison; freezing as the mechanism that retires ids; the hard stop as a correctness choice rather than a bug; the single-user recovery whose duration is set by the largest unfrozen table; finding the horizon holder, replication slots included; and prevention through alerting on unfrozen age, autovacuum capacity on large tables, and partitioning so that no single freeze job is unbounded.

Quick check

Quiz: Why does an MVCC engine stop accepting writes rather than degrade when transaction ids approach reuse? Because reuse would make old rows compare as being in the future and silently disappear - stopping is the only option that preserves correctness.

Flashcard: Which metric predicts wraparound, and which common metrics do not? The age of the oldest unfrozen transaction id predicts it; disk usage, CPU, latency and row counts all look normal until the stop.