# Postgres Transaction ID (XID) Wraparound

> A plain explanation of Postgres Transaction ID (XID) wraparound: what causes it, how to check how close you are, and what keeps it from taking your database offline.

Adela | 2025-10-24 | Source: https://www.bytebase.com/blog/postgres-transaction-id-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:

| Database           | MVCC type           | Where old versions are stored      | Wraparound risk |
| ------------------ | ------------------- | ---------------------------------- | --------------- |
| **PostgreSQL**     | Tuple-based         | In the main table (needs `VACUUM`) | ✅ Yes           |
| **MySQL (InnoDB)** | Undo-log-based      | Undo logs in system tablespace     | ❌ No            |
| **SQL Server**     | Version-store-based | Tempdb version store               | ❌ No            |
| **Oracle**         | Undo-log-based      | Undo segments                      | ❌ No            |

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](/content/blog/postgres-transaction-id-wraparound/pg-xid-cycle-circle.webp)

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](/content/blog/postgres-transaction-id-wraparound/pg-xid-cycle.webp)

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](/content/blog/postgres-transaction-id-wraparound/pg-xid-freeze.webp)

### 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:

```
PANIC: database is not accepting commands to avoid wraparound data loss
```

#### 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/](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/](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:

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

For table-level detail:

```sql
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/](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](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](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](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](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