Sentry and Mandrill Both Went Down When Postgres Ran Out of Transaction IDs

• Book: Database Playbook
• Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

On Monday, July 20, 2015, Sentry was "down for most of the US working day". Postgres had stopped accepting writes on purpose, and it would not take another one until a vacuum finished.

Three and a half years later, at 05:35 UTC on February 4, 2019, one of the Postgres databases behind Mailchimp's transactional email service Mandrill hit the same wall. It took until 22:09 UTC the next day to resolve, and during the outage Mandrill was "sending only 80% of queued emails."

The root cause in both was transaction ID wraparound. Both postmortems are public, and read together they show how a 32-bit counter takes a healthy database offline, and how far ahead the warning was visible.

A 32-bit counter decides what every query can see

Postgres uses MVCC: every row version carries the ID of the transaction that created it (its XID), and a query decides whether a row is visible by comparing that XID to its own. A row inserted by a transaction "in the future" stays invisible.

The catch is the size of the counter. The Mandrill postmortem puts it plainly: XIDs "are stored with 32 bits, and compared using modulo 2^32 arithmetic." The PostgreSQL 18 docs spell out the consequence: "for every normal XID, there are two billion XIDs that are 'older' and two billion that are 'newer'; another way to say it is that the normal XID space is circular with no endpoint."

A circle has no endpoint, so it also has no safe distance. If a row's XID stays put while the counter moves forward by more than about two billion, that row flips from "past" to "future." Data that was committed years ago becomes invisible. That is the data loss Postgres is protecting you from.

The fix is freezing. VACUUM walks the table and marks old rows as frozen, meaning they were inserted "sufficiently far in the past that the effects of the inserting transaction are certain to be visible to all current and future transactions." Postgres reserves a special FrozenTransactionId that "is always considered older than every normal XID." A frozen row is safe no matter how far the counter travels.

Each table tracks the oldest unfrozen XID it might still contain in pg_class.relfrozenxid, and each database tracks the minimum across its tables in pg_database.datfrozenxid. The age() of those values, the number of transactions since, is what you alert on.

What Postgres does when freezing falls behind

Autovacuum normally handles freezing in the background. When a table's relfrozenxid age passes autovacuum_freeze_max_age (default 200 million), Postgres forces an anti-wraparound vacuum on it, and the docs note this happens "even when autovacuum is otherwise disabled."

If that vacuum cannot finish in time, the database escalates in stages:

1. Since PostgreSQL 14, once a table's age passes vacuum_failsafe_age (default 1.6 billion), VACUUM drops its cost-based delay and skips index vacuuming. The docs call it "VACUUM's strategy of last resort."
2. At 40 million transactions from the wraparound point, Postgres starts emitting WARNING: database "mydb" must be vacuumed within N transactions, per the routine vacuuming docs.
3. At fewer than three million transactions left, Postgres refuses to assign new XIDs: ERROR: database is not accepting commands that assign new transaction IDs to avoid wraparound data loss.

In that last state, "only read-only transactions can be started. Operations that modify database records or truncate relations will fail. The VACUUM command can still be run normally." Your application is up, your reads work, and every write errors out until vacuum gets through every table that holds old XIDs.

The limits were tighter when Sentry hit this. Its 2015 post says Postgres "will stop accepting commands when there are fewer than one million transactions left." PostgreSQL 14 increased the warning time and hard limit so databases get more notice before they stop.

Two postmortems, one mechanism

Sentry, 2015: tuning that was a little too relaxed

"Sentry is a very write-heavy application," David Cramer's postmortem says, with very large relational tables on top, and the team had "gone back and forth on tuning autovacuum to avoid this very situation." The diagnosis: they "previously ran with too high a value for autovacuum_freeze_max_age" and "had been running with far too much delay in vacuuming." A high freeze age means the forced vacuum starts later. A high cost delay means it runs slower once it starts. Together they leave less room before the hard stop.

Recovery meant keeping the primary read-only, serving reads from replicas, failing the primary over to new hardware they had just provisioned for this exact risk, and letting the running vacuums finish. One huge table, which mapped events to their rollups, would not finish. They truncated it, accepting the risk of losing its data, because "in this case, the risk was minor."

The settings they landed on afterwards: autovacuum_freeze_max_age = 500000000, autovacuum_max_workers = 6, autovacuum_naptime = 15s, autovacuum_vacuum_cost_delay = 0, vacuum_freeze_min_age = 10000000. The freeze age is still above the 200 million default; they had been running higher than that. The aggressive part is the zero cost delay, six workers and a 15-second naptime, so vacuum starts sooner and never throttles itself. A later update to the post adds a correction anyone copying it should read: the maintenance_work_mem they raised did not help, because of a hard internal 1GB limit in the vacuum code paths they relied on.

Mandrill, 2019: the warning was in the backlog

Mandrill ran a set of Postgres databases as a sharded key/value store, and one shard took more writes than the rest. The postmortem by Eric Muntz says that in November 2018, engineers had noticed "the XIDs were climbing to approximately half their total limit during peak load." They judged it not an immediate threat and put a monitoring ticket in the backlog.

Three months later the shard stopped taking writes. Retries of the failed writes caused "a significantly elevated load" on it. The first exception was logged overnight, but "unrelated exceptions coming in from other systems drowned it out." When the team put the database into standalone (single-user) mode and started the vacuum at 14:16 UTC on February 4, the estimate was grim: "Our highest estimate was 40 days."

What ended it was the same move Sentry made. Two tables, Search and Url, were "on the order of TB" while most others were in the MB range, and the team "had determined it would be possible to run the app without the Search and Url tables." They truncated them at 19:12 UTC on February 5, and the vacuum finished about an hour later. XID wraparound alerting went live at 22:40 that night.

Both teams got out by deleting data they could live without. Mailchimp is candid that this was luck: "We were lucky that we had data we could delete at all, and even more lucky that it included the largest table holding up the vacuum." Most databases do not have a terabyte table they can throw away.

The two age queries, and where to set the alert

Start at the database level. The docs query is SELECT datname, age(datfrozenxid) FROM pg_database;. Add a percentage against 2^31 so the number reads the same on every dashboard:

SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483648, 1)
         AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;

Then find the tables holding it back. This is the docs' per-table query, which also counts each table's TOAST relation, with size and ordering added:

SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid),
                age(t.relfrozenxid)) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid))
         AS total_size
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 10;

For thresholds, an AWS Database Blog post on the RDS MaximumUsedTransactionIDs metric gives a rule of thumb that works for any Postgres: "I recommend an alarm when this metric reaches a value of 1 billion." It adds that "for some situations, a low severity warning at 500 million might also be useful." With the default autovacuum_freeze_max_age of 200 million, forced vacuums start long before 500 million, so a database that crosses it usually has an anti-wraparound vacuum that is running and not finishing. If you have raised autovacuum_freeze_max_age as Sentry did, put the warning comfortably above it. One billion is about half the limit, which is the number Mandrill's engineers saw in November 2018.

When the warning fires, check what is pinning the horizon before you tune anything. The docs list three culprits: old prepared transactions, long-running sessions, and stale replication slots. Vacuum cannot freeze past any of them:

SELECT gid, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY xid_age DESC;

SELECT pid, state, age(backend_xmin) AS xmin_age,
       now() - xact_start AS open_for
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC
LIMIT 5;

SELECT slot_name, active,
       age(xmin) AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;

If you are already at the hard stop, the current docs say it is "not necessary or desirable to stop the postmaster or enter single user-mode": clear those three, then run a plain database-wide VACUUM as a superuser. Not VACUUM FULL, and not VACUUM FREEZE. Single-user mode is only for the move both teams made, truncating or dropping tables you can give up. Mandrill's 2019 vacuum ran in single-user mode, which earlier releases sometimes recommended. The docs now say it "should be avoided whenever possible, since it involves taking the system down."

When the churn is telling you about the schema

Look at what the truncated tables had in common. Sentry's mapped events to rollups. Mandrill's were the Search and Url tables, on a shard used as a key/value store. Each was far larger than its neighbours, the application could run without it, and it lived in a transactional database that has to freeze every row it keeps.

Sentry drew that conclusion in 2015: "ultimately we're going to be moving away from a SQL-based architecture." In May 2019 it introduced Snuba, a ClickHouse-backed service for event search, after finding that Tagstore's "number of mutations executed exceeded our ability to replicate them on a single Postgres machine." The post also notes "our data is immutable, for the most part." Event data went to a columnar store, while Postgres stayed for the rest.

You do not need to migrate to stop the bleeding. Some signals worth acting on, from the cheapest change to the most expensive:

• The top of the per-table query is always the same one or two giant tables. Partition them by time, so old data leaves through DROP on a partition instead of a vacuum over terabytes.
• The table is insert-only and never updated. Check that insert-driven autovacuum (autovacuum_vacuum_insert_threshold) has not been disabled, so those pages get visited in regular small passes instead of waiting for one giant anti-wraparound scan.
• You are using Postgres as a key/value store or an event log at very high write rates. Ask whether that data needs MVCC at all. If it is immutable and queried in aggregate, a columnar store is a better fit, as Sentry found.
• One shard takes far more writes than its siblings, as Mandrill's did. Your shard key is concentrating XID burn on one database. Rebalancing fixes the wraparound risk and the hot-spot problem at once.

The hard stop is Postgres refusing to corrupt your data when the workload outruns its housekeeping. The two age queries above cost nothing to run. Put them on a dashboard today, with a page at one billion.

If this was useful

Both outages started long before the error: with a write-heavy workload in a store that has to freeze every row it keeps. The Database Playbook is about that earlier decision: matching the store and the schema shape to how data is written, read and retired, so the high-churn table lands somewhere built for it.