Tejas GPBrowse

Home/Postgres Internals/Episode 2

The counter that ran out

Postgres transaction IDs are 32-bit. At Mailchimp's Mandrill, one shard hit the limit and the database stopped accepting writes. Email sending resumed about 40 hours later.

7 Nov 2026 · 5 min read

mandrill=# INSERT INTO messages …ERROR: database is not accepting commands that assign new transaction IDs to avoid wraparound data loss

The reel covered this in 105 seconds. Here's the slower version: what the counter is, how it runs out, what happened at Mandrill, and what to check on your own database today.

In 60 seconds

  • Postgres gives every write transaction a number: the transaction ID (XID). It's 32 bits.
  • A 32-bit number can't grow forever, so it wraps around and starts again (wraparound).
  • If old rows aren't frozen in time, they suddenly look like they're from the "future" and stop being visible.
  • To prevent that, Postgres stops handing out new XIDs near the limit. Reads keep working; writes stop.
  • The fix is VACUUM, which autovacuum runs in the background. You need to keep an eye on it.

What is an XID?

In Postgres, several transactions can be looking at the same row at once. To decide which version each of them sees, Postgres stamps every row with the transaction that created it. That number lives in the row's xmin.

You can see it yourself:

sql
SELECT xmin, * FROM users LIMIT 3;

Why does the counter wrap?

A 32-bit XID gives about 4.29 billion numbers. That's not infinite, so Postgres uses them in a circle, like a clock.

The Postgres docs say XIDs are compared using modulo-232 arithmetic: for every XID, two billion are older and two billion are newer. On a clock, 1 comes after 12, but whether 1 is "before" or "after" depends on which half of the clock you call the past.

Where it goes wrong

Say a row was created by XID 1041, and then the database moves more than two billion transactions ahead. On the circle, 1041 now falls into the "future" half. Postgres thinks the row hasn't been created yet, so it simply isn't visible.

In the docs' words, the data is still there, but "that's cold comfort". The row is on disk, but your queries can't see it.

How VACUUM prevents it

VACUUM freezes old rows. A frozen row is permanently in the past, no matter how far the counter goes around. That's why the docs say every table in every database must be vacuumed at least once every two billion transactions.

Autovacuum does this in the background. When a table's oldest unfrozen XID gets older than autovacuum_freeze_max_age (default 200 million), autovacuum is forced to run on that table, even if autovacuum is otherwise disabled.

Postgres warns you first

When 40 million transactions are left before wraparound, Postgres starts emitting warnings:

WARNING:  database "mydb" must be vacuumed within 39985967 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.

When fewer than 3 million are left, it refuses to assign new XIDs:

ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "mydb"
HINT:  Execute a database-wide VACUUM in that database.

Read-only queries keep working. INSERT, UPDATE and DELETE all fail.

What happened at Mandrill (Feb 2019)

Mandrill is Mailchimp's transactional email service, running on a sharded Postgres setup. This timeline comes from their own postmortem:

  1. Engineers noticed XIDs "climbing to approximately half their total limit" during peak load. They judged it not an immediate threat, and the work went into the backlog.
  2. One shard (shard4) hit the XID upper limit. The database went into safety shutdown mode and stopped accepting writes on that shard.
  3. A hashing imbalance sent more writes to shard4 than to the other shards. Autovacuum "likely fell behind".
  4. A full VACUUM could take up to 40 days. Waiting wasn't an option.
  5. They truncated the large Search and Url tables (terabytes of data), then dumped and restored the database without them.
  6. Sending from the backlog resumed, about 40 hours after the shutdown. That evening they added new alerting for XID wraparound. Affected users were refunded or credited.

The warning had already shown up. A ticket was made, and it sat in the backlog.

Not the first time

The same thing happened to Sentry on 20 July 2015. Their post says they were down for "most of the US working day": writes stayed blocked until VACUUM finished, and they also had to truncate a large table.

Check your database today

1. How old is each database?

sql
SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY 2 DESC;

age is how many transactions behind the oldest unfrozen XID is. With autovacuum keeping up, it usually hovers around autovacuum_freeze_max_age (200 million). If it keeps climbing past that, autovacuum is falling behind.

2. Which tables are the oldest? (the query from the Postgres docs, sorted)

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

3. Set up an alert. Graph age(datfrozenxid) and alert on it. The right threshold depends on your write rate, but set it early enough that autovacuum has time to catch up. It's exactly what Mandrill added after the incident.

Why does autovacuum fall behind?

The Postgres docs list these things to check, because they hold on to old XIDs and stop VACUUM from freezing past them:

  • Long-running transactions: rows in pg_stat_activity with a large age(backend_xmin), especially connections stuck "idle in transaction".
  • Old prepared transactions: pg_prepared_xacts.
  • Stale replication slots: a large age(xmin) in pg_replication_slots, for example the slot of a replica that's no longer used.

And if you do hit the limit, the docs say: clear the items above, then run a database-wide VACUUM. Don't run VACUUM FULL (it needs an XID of its own), and single-user mode is no longer necessary.

Don't put your database's warnings in the backlog.

Sources