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 frompg_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-tablerelfrozenxidvalues) 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_wraparoundis above 50%, investigate which tables are not being vacuumed. - If
percent_towards_wraparoundis 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_catalogviews. Run them from a role that can see all databases and tables you want to check; runningVACUUMneeds 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_feedbackis 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_wraparoundexceeds 50%, using whatever monitoring system you already run.
References
- PostgreSQL: Preventing Transaction ID Wraparound Failures — how freezing works, the warnings and the stop state
- PostgreSQL: pg_database system catalog — the
datfrozenxidcolumn - PostgreSQL: pg_class system catalog —
relfrozenxidandrelkind - PostgreSQL: vacuumdb — the shell utility with
--alland--freeze - andyatkinson/pg_scripts: detect_transaction_id_wraparound.sql — original seed script
Posts in this series
- Identify Blocking PostgreSQL Queries with pg_stat_activity
- PostgreSQL query to find columns containing only NULL values
- Analyze PostgreSQL Cache-Hit Ratio with pg_stat_statements
- How to Monitor Slow Running Queries in PostgreSQL
- Find Idle in Transaction Sessions in PostgreSQL
- Monitor PostgreSQL Index Build Progress with SQL
- Find PostgreSQL Tables That Need VACUUM FREEZE
- Detect PostgreSQL Transaction ID Wraparound Risk
- Cancel vs Terminate PostgreSQL Backends Explained
- Identify Running PostgreSQL Autovacuum Worker Sessions