Detect PostgreSQL Transaction ID Wraparound Risk

calendar Apr 3, 2026 · 4 min read · postgresql administration vacuum monitoring database  ·
Share on: linkedin copy

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, which means they can only count up to about 2 billion. When the counter gets close to the limit, PostgreSQL must freeze old transaction IDs to recycle them. If this does not happen in time, the database will refuse all new connections to protect data integrity — this is called a wraparound event, and it causes complete downtime.

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: Works on PostgreSQL 9.4 and later. 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, which is the hard wraparound limit. PostgreSQL will shut down connections before this is reached.
  • age(d.datfrozenxid) — Returns how many transactions ago the database last froze its XIDs. 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 old the oldest unfrozen transaction ID is for each table.
  • > 190000000 — Filters to tables approaching the 200 million autovacuum threshold. These are the tables at risk.
  • relkind = 'r' — Limits results to ordinary tables only (not indexes, views, etc.).
  • 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. Run VACUUM FREEZE on the affected tables.
  • 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;

If a transaction has been open for hours or days, it can prevent autovacuum from freezing rows in any table. Kill idle-in-transaction sessions if safe to do so.

To manually freeze a table that is at risk, run:

1VACUUM FREEZE ANALYZE your_table_name;

For the most critical cases, you can also force a full freeze across the database:

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: Requires superuser or a role with access to pg_catalog. pg_stat_monitor and pg_database are visible to all users but age() on datfrozenxid requires superuser on older versions.
  • Replication: On replicas, autovacuum cannot freeze tables. The primary must do the freezing. Monitor hot_standby_feedback if enabled, as it can prevent vacuuming on the primary.
  • Alerts: Set up monitoring alerts when percent_towards_wraparound exceeds 50%. Most PostgreSQL monitoring tools (pganalyze, Datadog, etc.) include wraparound tracking built in.

References

Posts in this series