The transaction counter hit two billion

December 21st. 14:37. Three workdays before Christmas, when everyone has already mentally checked out and the engineering Slack is full of “OOO starting the 24th” messages.

The monitoring alert that came in was a 503 rate spike on the application tier. Not database alerts, not disk alerts, not memory. Application 503s. The load balancer was healthy. The application servers were running. I logged into the first app node and ran a quick test query against the database.

psql: error: connection to server at "db.internal" (10.0.0.5), port 5432 failed: ERROR:  database is not accepting commands to avoid wraparound data loss in database "production"
HINT:  Stop the postmaster and vacuumdb --all --freeze as a standalone backend.

There it is. I had seen that message once before, four years earlier at a different company, and I recognized it immediately. The hint says to stop the postmaster. You do not actually have to stop the postmaster. The message is old and the situation is recoverable while the database is running. But the next few hours were going to be slow and uncomfortable regardless.

What PostgreSQL does with transaction IDs

PostgreSQL assigns every write transaction an integer ID: the XID. These start at 3 (a few values are reserved) and increment monotonically. Rows in the database store the XID of the transaction that inserted them. Visibility rules use these IDs to determine which transactions can see which rows: a transaction can see any row whose inserting XID is older than its own.

The problem is that XIDs are 32-bit integers. The maximum value is about 4.3 billion. PostgreSQL uses modular arithmetic: any XID more than 2^31 (roughly 2.1 billion) steps before the current XID is considered “in the future,” meaning no running transaction can see it. If you let the counter advance far enough without freezing old rows, rows that were perfectly visible last week become invisible. They have not been deleted. They are still on disk. But they look like they were written by a future transaction and the visibility system hides them. That is data corruption without any actual data loss, which is somehow worse.

The mechanism to prevent this is freezing. When PostgreSQL VACUUMs a table, it can mark old rows as “frozen”: the stored XID is replaced with a special frozen value (XID 2) that is always considered to be in the past, regardless of where the counter is. Frozen rows are permanently visible. The pg_class catalog stores relfrozenxid per table: the oldest non-frozen XID in that table. The pg_database catalog stores datfrozenxid: the minimum relfrozenxid across all tables in the database.

age(datfrozenxid) tells you how far the current XID counter is ahead of the oldest non-frozen transaction. At around 200 million, autovacuum starts aggressively freezing tables. At around 1.6 billion (PostgreSQL 14+), autovacuum enters failsafe mode and freezes regardless of its normal cost throttling. At roughly 2.1 billion, PostgreSQL refuses writes entirely to prevent the counter from wrapping past visibility.

On December 21st at 14:37, we were at 2.1 billion.

Finding out how bad it was

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

  datname   |  xid_age
------------+-----------
 production | 2141872304
 template1  |  208441920
 postgres   |  208441920

The production database was at 2.14 billion. The threshold for read-only mode is 2^31 minus 3 million, which is 2,144,483,647. We were eleven million transactions away from that. We had crossed into read-only mode because the check fires before the actual wrap point, but we were not at genuine data-corruption territory yet. That was the good news.

The bad news was I needed to find which table or tables were the problem. The database-level datfrozenxid is the minimum of all table-level relfrozenxid values. One table with a very old relfrozenxid can pin the entire database’s age.

SELECT schemaname, relname, relfrozenxid, age(relfrozenxid) AS xid_age
FROM pg_class
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
WHERE relkind = 'r'
ORDER BY xid_age DESC
LIMIT 20;

 schemaname |     relname     | relfrozenxid |  xid_age
------------+-----------------+--------------+------------
 public     | event_log       |    142847523 | 2141872304
 public     | event_log_2024  |    142847525 | 2141872302
 public     | sessions        |    523901847 |  831411480
 public     | users           |    983741201 |  430872122

The event_log table and its 2024 partition. Two tables, both with the same ancient frozen XID, pinning the entire database at 2.14 billion.

Why autovacuum did not catch this

Autovacuum is supposed to prevent exactly this. It runs in the background, picks tables with old relfrozenxid, freezes them, and advances the table’s frozen XID forward. The default threshold is 200 million, which gives you roughly 1.9 billion transactions of headroom before you hit trouble, assuming autovacuum can keep up.

We had 2.14 billion. That means either autovacuum had not touched event_log in over two billion transactions, or it had been prevented from doing so.

SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT NULL AND relkind = 'r'
ORDER BY relname;

   relname    |              reloptions
--------------+--------------------------------------
 event_log    | {autovacuum_enabled=false}
 event_log_2024 | {autovacuum_enabled=false}

There it was. Someone had explicitly disabled autovacuum on event_log.

event_log was a 380-gigabyte table that received about 40,000 inserts per minute at peak. Autovacuum on a table that large causes real I/O: it reads every live row, freezes the old ones, writes the updated versions. On this table, during business hours, that generated enough disk pressure to cause noticeable latency. Someone, at some point in the past, had decided the vacuum I/O was not acceptable and had run:

ALTER TABLE event_log SET (autovacuum_enabled = false);

No comment. No issue filed. The git history on the migration file that contained this command attributed it to a developer who had left the company eighteen months earlier. The migration message was perf: disable autovacuum on event_log to reduce write latency.

The performance problem it solved was real. The solution was wrong in a way that took two years and four months to matter.

Fixing it

The fix is straightforward: run VACUUM FREEZE on the offending tables. Re-enable autovacuum so this cannot happen again. Do both before Christmas.

The complication: VACUUM FREEZE on a 380-gigabyte table takes time. I ran it at 14:58, with verbose output to a log file so I could track progress.

VACUUM (FREEZE, VERBOSE, ANALYZE) event_log;

While it ran, the database was still read-only. The application was still throwing 503s. This was the choice: wait for the freeze to complete (and get full service back when it was done), or find a way to get writes back faster.

There is no faster option. The read-only mode exists because age(datfrozenxid) is too high. Reducing that age requires freezing rows in the table that is pinning it. Freezing rows requires a full sequential scan of the table plus page writes for every row that needs updating. It is I/O-bound and you cannot rush it.

I re-enabled autovacuum on the table immediately:

ALTER TABLE event_log RESET (autovacuum_enabled);
ALTER TABLE event_log_2024 RESET (autovacuum_enabled);

This does not help right now, since autovacuum does not take priority over the manual VACUUM already running and would not run concurrently on the same table anyway. It matters for afterward.

At 16:22, about eighty-four minutes into the freeze, pg_database showed:

SELECT datname, age(datfrozenxid) FROM pg_database WHERE datname = 'production';

  datname   | age
------------+----------
 production | 41872304

The manual VACUUM had frozen enough rows in event_log to advance datfrozenxid past the critical threshold. PostgreSQL came out of read-only mode on its own. The application tier started handling writes again. The 503 rate dropped to zero within two minutes as connection pools recovered.

The VACUUM itself ran until 19:41, about two hours after writes resumed. The final age(datfrozenxid) for the production database was 38,921,044, which is comfortably below the 200-million autovacuum trigger threshold.

The monitoring that was not there

This should have paged at 1.5 billion, not 2.14 billion. At 1.5 billion you have time to schedule a maintenance window and run the vacuum at a controlled rate during low-traffic hours. At 2.14 billion you are already in read-only mode and the choice is “vacuum it now under pressure” or “stay down.”

PostgreSQL does not expose XID age as a metric you can scrape without querying the database directly, which means this does not show up in default node exporter output. You have to instrument it yourself.

The query that should be in your monitoring:

SELECT max(age(datfrozenxid)) AS max_xid_age FROM pg_database;

Alert at 1 billion. Page at 1.5 billion. Anything over 1.8 billion means someone is on call right now whether they know it or not.

The per-table check is also worth running periodically, because a single table can dominate the database-level age:

SELECT schemaname, relname, age(relfrozenxid) AS xid_age
FROM pg_class
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
WHERE relkind = 'r' AND age(relfrozenxid) > 500000000
ORDER BY xid_age DESC;

A table older than 500 million transactions means autovacuum has not frozen it in a long time. That is either because autovacuum is disabled, or because the table is so large and busy that autovacuum cannot finish a pass before the cost throttle kicks in and puts it to sleep. Both situations need addressing before they get to 2 billion.

The autovacuum_enabled = false trap

Disabling autovacuum on a large, high-write table is a reasonable response to a real problem. Autovacuum on a 380-gigabyte table during peak traffic hours genuinely causes latency. The correct response is to tune autovacuum so it runs more slowly and during off-peak hours, not to disable it entirely.

ALTER TABLE event_log SET (
  autovacuum_vacuum_cost_delay = 20,
  autovacuum_vacuum_scale_factor = 0.01
);

autovacuum_vacuum_cost_delay adds a sleep between pages scanned, reducing I/O pressure at the cost of more elapsed time. autovacuum_vacuum_scale_factor controls how full the table needs to be with dead tuples before vacuum triggers. Tuning these is work. Disabling autovacuum is one SQL statement and makes the problem go away immediately.

The problem does not go away. It defers. The XID counter keeps advancing. The relfrozenxid for that table stays where it was. Every transaction that touches any other table advances the counter while event_log sits frozen in place. Two years and four months later, you are on the phone at 14:37 three days before Christmas explaining to someone why the entire application is down.

This is structurally similar to the inode exhaustion I wrote about in September: a counter you are not watching creeps toward a hard limit, everything looks healthy until it does not, and then you are in an incident. The load average and D-state situation has the same shape. The metric that would tell you there is a problem is not df -h or top. It is the metric nobody added to the monitoring because nobody knew to look for it.

What I changed afterward

I added XID age monitoring to the Prometheus setup the next day. Two queries: the database-level max age and the per-table max age. Alert threshold 1 billion, page threshold 1.5 billion.

I wrote a small runbook entry for “PostgreSQL XID wraparound” covering the diagnosis commands and the VACUUM (FREEZE) procedure. It lives next to the alert in the monitoring docs. The next person who sees that alert should be able to diagnose and fix it without recognizing the error message from prior experience.

I also added a comment to the migration file that re-enabled autovacuum, explaining what had happened and why autovacuum_enabled = false is not a safe optimization. It will not help the developer who wrote the original migration, who left eighteen months ago. It might help the next person who gets the idea.

The performance problem that motivated disabling autovacuum in the first place was still real. We addressed it properly in January: moved event_log to a partition scheme by month, which kept individual partitions small enough for autovacuum to complete quickly without significant I/O impact. The latency spikes that had prompted the original disable went away. The XID age on the table now stays under 100 million.

The transaction counter

The counter does not care what time of year it is. It does not care that it is December 21st and your team is partially OOO. It does not care that the last person who understood why autovacuum was disabled is gone. It just increments. Every write transaction, one more. Every COMMIT, one more. The number was at 142,847,523 when someone ran that migration in July 2024 and it had been climbing since.

Two billion transactions is a lot. It is also about two years of normal operation on a busy table with autovacuum disabled. The clock started the day the migration ran.

SELECT max(age(datfrozenxid)) FROM pg_database; takes about four milliseconds. Run it. If the number is over 500 million and you do not have a monitoring alert for it, add one before you do anything else today.

The hint in the error message tells you to stop the postmaster. Ignore that part. The rest of the message is accurate.