Don’t Let PostgreSQL Kill Itself: A Guide to Transaction ID Wraparound

Database is shut down due to risk of transaction wraparound data loss

At that point, you’re in emergency mode. The good news: it’s entirely preventable with a simple monitoring query.

The Problem

Postgres uses 32-bit transaction IDs (XIDs) to track changes. That gives about 2 billion transactions before wraparound. To prevent corruption, Postgres must “freeze” old IDs.
If freezing doesn’t keep up, the system forces a shutdown. No warning dialog, no partial service, just a hard stop.

The Query

This SQL acts as an early-warning system. It highlights tables with high transaction age, excessive dead tuples, or unusual modification rates:

WITH table_stats AS (
   SELECT
       schemaname,
       stats.relname,
       n_tup_ins + n_tup_upd + n_tup_del as total_modifications,
       age(rel.relfrozenxid) as xid_age,
       pg_size_pretty(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(stats.relname))) as table_size,
       current_setting('autovacuum_freeze_max_age')::int8 as max_age,
       last_vacuum,
       last_autovacuum,
       n_dead_tup,
       n_live_tup,
       ROUND((n_dead_tup::float / NULLIF(n_live_tup, 0) * 100)::numeric, 2) as dead_tuple_ratio
       FROM pg_stat_user_tables stats
   JOIN pg_class rel ON rel.relname = stats.relname
   WHERE schemaname NOT IN ('pg_toast', 'pg_catalog')
)
SELECT
   schemaname as schema_name,
   relname as table_name,
   table_size,
   xid_age as transaction_age,
   ROUND((xid_age::float / max_age * 100)::numeric, 2) as age_pct,
   total_modifications,
   ROUND(total_modifications::numeric /
       NULLIF(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - COALESCE(last_vacuum, last_autovacuum)))::numeric, 0)
   ) as mods_per_sec,
   dead_tuple_ratio as dead_pct,
   COALESCE(last_vacuum, last_autovacuum) as last_vacuum_time,
   CASE
       WHEN xid_age >= max_age * 0.75 THEN 'CRITICAL'
       WHEN xid_age >= max_age * 0.50 THEN 'WARNING'
       WHEN dead_tuple_ratio >= 20 THEN 'WARNING'
       WHEN total_modifications > 1000000 THEN 'MONITOR'
       ELSE 'OK'
   END as risk,
   CASE
       WHEN xid_age >= max_age * 0.75 THEN 'Immediate vacuum required'
       WHEN xid_age >= max_age * 0.50 THEN 'Vacuum soon'
       WHEN dead_tuple_ratio >= 20 THEN 'High dead tuple ratio, vacuum recommended'
       WHEN total_modifications > 1000000 THEN 'High modification rate, monitor closely'
       ELSE 'Normal'
   END as recommendation
FROM table_stats
WHERE xid_age >= max_age * 0.25 
   OR total_modifications > 100000 
   OR dead_tuple_ratio > 10
ORDER BY risk, xid_age DESC
LIMIT 20;

Why It Works

• xid_age → how close the table is to wraparound
• age_pct → normalized risk relative to system limits
• mods_per_sec → write activity intensity
• dead_pct → dead tuple ratio
• risk + recommendation → converts raw stats into actionable output

Only the top 20 risky tables are shown to keep results readable.

Using It in Production

Automate the Check

Schedule the query daily with cron or your monitoring stack. Example with a Bash script:

#!/bin/bash
psql -h db -U user -d appdb -f wraparound_check.sql -t | grep CRITICAL > critical.txt
if [ -s critical.txt ]; then
  mail -s "PostgreSQL wraparound risk detected" you@company.com < critical.txt
fi

Interpreting Results

• CRITICAL → Run VACUUM FREEZE immediately
• WARNING → Vacuum soon, adjust autovacuum if needed
• MONITOR → Heavy churn, consider partitioning or tuning
• OK → No action required

Performance Notes

The monitoring query is lightweight, pulling only from system catalogs. The heavy part is the fix: VACUUM. For large tables:

• Run vacuum during low-traffic periods
• Use VACUUM VERBOSE to track progress
• Partition very large, high-churn tables
• Tune autovacuum per table instead of relying on defaults

Beyond Wraparound

This query doubles as a maintenance aid:

• High dead tuples → detect table bloat
• High modification rates → spot candidates for partitioning
• Age percentage trends → forecast when wraparound thresholds will be hit

‍

Conclusion

Transaction ID wraparound is one of the rare cases where Postgres will simply stop. The monitoring query above gives you a simple way to see trouble before it hits, automate alerts, and keep your database alive.

Preventing wraparound is not optional. Run the query, schedule it, and vacuum before Postgres decides for you.