Postgres Transaction ID (XID) Wraparound

Every transaction in PostgreSQL gets a Transaction ID (XID), a 32-bit integer that drives its MVCC (Multi-Version Concurrency Control) engine. The idea is simple: with MVCC, many users can read and write the same data at once without blocking each other.

The catch is that 32-bit integers run out. The counter eventually wraps around and starts reusing old IDs.

When that happens and nobody has prepared for it, old rows can become invisible, and in the worst case Postgres will stop accepting writes to protect itself. Here is why, and what to do about it.

How Postgres Differs from Other Databases#

PostgreSQL uses a tuple-based MVCC model. Every change creates a new version (tuple) of a row, stored right in the table itself.

Each tuple carries two hidden fields, xmin and xmax. xmin is the transaction ID that created the tuple, and xmax is the transaction ID that deleted or replaced it.

Other databases version data differently:

The difference comes down to where the old versions live. Postgres keeps them inline with the table, and each one carries its own transaction ID. That gives Postgres very fine-grained visibility control, but it also means transaction IDs have to be reused over time, which is exactly what leads to wraparound. The other databases store old versions in a separate place, so their transaction identifiers can keep growing without ever wrapping.

What Is Transaction Wraparound#

PostgreSQL's transaction IDs are 32-bit integers, running from 0 to 4,294,967,295. Once the counter hits the top, it wraps back to 3 and keeps going.

The trick to understanding this is to stop thinking of XIDs as a straight line. Picture them on a circle.

Transaction ID (XID) Wraparound

On a circle, "older" and "newer" are relative. When the counter wraps, very old tuples can suddenly look like they have "future" XIDs, and Postgres treats them as invisible to every new transaction. The data is still there, but nobody can see it.

Transaction ID (XID) Wraparound

To keep that from happening, Postgres periodically freezes old tuples. A frozen tuple gets a special FrozenXID that means "committed long ago, always visible," so it stays readable no matter where the counter is.

Transaction ID (XID) Wraparound

Consequences of Wraparound#

If freezing falls behind, you are looking at potential data corruption. Once datfrozenxid gets dangerously old, Postgres stops taking new writes and may shut down outright with an error like this:

Real-world cases

• Sentry (2024): Sentry's Postgres database stopped accepting writes after autovacuum couldn't keep up with freezing old transaction IDs. The system hit the wraparound limit, which forced emergency manual vacuuming and downtime. https://blog.sentry.io/transaction-id-wraparound-in-postgres/

• Mailchimp/Mandrill (2016): Autovacuum on a busy shard fell behind, wraparound protection kicked in, and writes halted. Recovery needed truncations and manual vacuums, and the outage ran roughly 40 hours. https://mailchimp.com/what-we-learned-from-the-recent-mandrill-outage/

These are not edge-case horror stories. Wraparound is one of the few PostgreSQL maintenance failures that can take production down completely, and it has done so at companies that knew Postgres well.

How to Monitor and Prevent Wraparound#

1. Autovacuum is the First Line of Defense

Autovacuum scans tables on its own and freezes tuples before they age out. The parameters worth knowing:

• autovacuum_freeze_max_age – threshold for wraparound prevention
• vacuum_freeze_table_age – when to start freezing during normal vacuum
• vacuum_freeze_min_age – minimum XID age before freezing allowed

If autovacuum is turned off or simply can't keep pace, the danger builds up quietly with nothing obvious to warn you.

2. Check XID Age with SQL

To see how close the whole database is to wraparound:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

For table-level detail:

SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY xid_age DESC;

⚠️ Guidelines

• Above 1.5 billion → warning zone
• Above 2 billion → database may lock writes

3. Cloud Provider Recommendations

If you run managed Postgres, the provider gives you its own tooling for this.

AWS RDS / Aurora Use postgres_get_av_diag to monitor autovacuum health and aging tables: https://aws.amazon.com/blogs/database/prevent-transaction-id-wraparound-by-using-postgres_get_av_diag-for-monitoring-autovacuum/

Google Cloud SQL Cloud SQL provides a Recommender for High Transaction ID Utilization - https://cloud.google.com/sql/docs/postgres/recommender-high-transactionid-utilization

Whatever you are running on, the same habits apply:

• Never disable autovacuum.
• Schedule manual VACUUM FREEZE during off-peak hours.
• Avoid long-running idle transactions that block freezing.

The Challenge of Moving to 64-bit Transaction IDs#

The obvious question is why Postgres doesn't just make transaction IDs 64-bit and be done with it. That would push the ceiling from 4 billion transactions to roughly 18 quintillion, which for all practical purposes ends wraparound forever.

People have been asking exactly that for years, and there are real prototypes to show for it:

• Early discussions (2018–2019): https://www.postgresql.org/message-id/flat/DA1E65A4-7C5A-461D-B211-2AD5F9A6F2FD%40gmail.com Developers debated whether to store full 64-bit IDs or use a hybrid scheme (16-bit epoch + 48-bit XID) to retain compatibility.

• Experimental patch for Postgres 15 (2021): https://www.postgresql.org/message-id/flat/CACG=ezZe1NQSCnfHOr78AtAZxJZeCvxrts0ygrxYwe=pyyjVWA@mail.gmail.com It proved feasible but caused major ripple effects:

  • Every tuple grows by 8 bytes (xmin + xmax).
  • Index and WAL formats must be redesigned.
  • Replication and visibility logic rely on 32-bit arithmetic.

• Community view: https://news.ycombinator.com/item?id=19083745 Developers agreed the change would solve wraparound permanently but break on-disk compatibility, forcing every database to migrate storage format.

So for now the trade-off stands. The community is putting its energy into better autovacuum efficiency and wraparound monitoring, and 32-bit XIDs stay part of the architecture until someone works out a migration path that doesn't ask every existing database to rewrite its storage.

Best Practices#

• ✅ Keep autovacuum enabled and tuned
• ✅ Monitor XID age regularly
• ✅ Vacuum frequently on high-write tables
• ✅ Avoid long-running transactions
• ✅ Run VACUUM FREEZE during maintenance windows
• ✅ Partition or archive old data to reduce bloat