Detect PostgreSQL Transaction ID Wraparound Risk

Detect PostgreSQL Transaction ID Wraparound Before It Causes Downtime

PostgreSQL assigns a transaction ID (XID) to every write transaction. These IDs are 32-bit integers, so the counter wraps around after about 4 billion transactions. Because IDs are compared modulo 2^32, each ID has two billion IDs that count as older and two billion that count as newer, which is why every table must be vacuumed at least once every two billion transactions. PostgreSQL freezes old rows to recycle IDs. If that does not happen in time, the server first logs warnings and then, with fewer than three million transactions left, refuses to assign new IDs. Reads and VACUUM still run, but every write fails until a database-wide vacuum finishes. That is a wraparound stop, and for a write-heavy application it is an outage.

Autovacuum handles freezing automatically, but it can fall behind on large or heavily written tables. These queries let you monitor how close each database and table is to the wraparound limit so you can act before it becomes a crisis.

Sample Code

Database-level wraparound status:

 1WITH max_age AS (
 2    SELECT 2000000000 AS max_old_xid
 3        , setting AS autovacuum_freeze_max_age
 4    FROM pg_catalog.pg_settings
 5    WHERE name = 'autovacuum_freeze_max_age'
 6),
 7per_database_stats AS (
 8    SELECT datname
 9        , m.max_old_xid::int
10        , m.autovacuum_freeze_max_age::int
11        , age(d.datfrozenxid) AS oldest_current_xid
12    FROM pg_catalog.pg_database d
13    JOIN max_age m ON (true)
14    WHERE d.datallowconn
15)
16SELECT max(oldest_current_xid) AS oldest_current_xid
17    , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
18    , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac
19FROM per_database_stats;

Table-level risk detection:

1SELECT
2    relname,
3    age(relfrozenxid),
4    pg_size_pretty(pg_relation_size(oid)) AS size
5FROM pg_class
6WHERE age(relfrozenxid) > 190000000
7  AND relkind = 'r';

Notes: Tested for PostgreSQL 9.4 and later; age(), pg_database and pg_settings also exist in much older releases. Run both queries regularly on production systems. The table-level query flags tables with an XID age over 190 million — close to the default autovacuum threshold of 200 million.

Code Breakdown

Database-level query:

  • autovacuum_freeze_max_age — A PostgreSQL setting (default: 200 million) that controls when autovacuum is forced to freeze a table. Read from pg_settings.
  • max_old_xid — Set to 2 billion, the interval within which every table must be vacuumed. The server stops assigning new IDs slightly before this point (about three million transactions short).
  • age(d.datfrozenxid) — Returns the number of transactions from the database's oldest unfrozen XID (datfrozenxid, the minimum of the per-table relfrozenxid values) to the current transaction. A larger number means more transactions are unfrozen.
  • percent_towards_wraparound — How far the database has progressed towards the 2 billion hard limit, as a percentage.
  • percent_towards_emergency_autovac — How far the database has progressed towards triggering emergency autovacuum, as a percentage.

Table-level query:

  • age(relfrozenxid) — Returns how many transactions old the table's oldest unfrozen transaction ID is.
  • > 190000000 — Filters to tables approaching the 200 million autovacuum threshold. These are the tables at risk.
  • relkind = 'r' — Limits results to ordinary tables. Indexes, views, TOAST tables and materialized views are excluded.
  • pg_size_pretty — Shows the table size in a readable format so you can prioritize larger tables.

Key Points

  • If percent_towards_wraparound is above 50%, investigate which tables are not being vacuumed.
  • If percent_towards_wraparound is above 75%, treat it as urgent and vacuum the affected tables now. These two thresholds are rules of thumb, not figures from the PostgreSQL manual.
  • The table-level query shows which specific tables are closest to the limit. Start with the largest tables, as they take the longest to vacuum.
  • Autovacuum should handle most cases automatically, but it can be blocked by long-running transactions or misconfigured settings.

Insights and Explanations

To understand why a table is not being frozen, check if there are long-running transactions blocking autovacuum:

1SELECT pid, now() - xact_start AS duration, query
2FROM pg_stat_activity
3WHERE xact_start IS NOT NULL
4ORDER BY duration DESC;

The PostgreSQL manual lists ending long-running open transactions as a step when recovering from wraparound risk; look for a large age(backend_xid) or age(backend_xmin). Commit, roll back or terminate such sessions if it is safe to do so.

To manually freeze a table that is at risk, run the following. If the server has already stopped assigning IDs, the manual advises a plain database-wide VACUUM instead, because VACUUM FREEZE does more work than the minimum needed to restore normal operation.

1VACUUM FREEZE ANALYZE your_table_name;

For the most critical cases, you can also run a freeze across every database from the shell with the vacuumdb program:

1vacuumdb --all --freeze

Monitor autovacuum activity to confirm it is running regularly:

1SELECT relname, last_autovacuum, last_autoanalyze
2FROM pg_stat_user_tables
3ORDER BY last_autovacuum ASC NULLS FIRST;

Additional Considerations

  • Permissions: Both queries read pg_catalog views. Run them from a role that can see all databases and tables you want to check; running VACUUM needs ownership of the table or a role with the right privilege.
  • Replication: A standby is read-only, so freezing happens on the primary. If hot_standby_feedback is on, a long-running standby query can cause bloat on the primary for some workloads, so watch for it.
  • Alerts: Set up monitoring alerts when percent_towards_wraparound exceeds 50%, using whatever monitoring system you already run.

References

Posts in this series