Transaction ID Wraparound

PostgreSQL reuses 32-bit transaction IDs. Vacuum must freeze old rows before the cluster approaches wraparound shutdown on PostgreSQL 18.4.

Recipe

Quick-reference recipe card - copy-paste ready.

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

When to reach for this: Logs mention wraparound, autovacuum runs with autovacuum (to prevent wraparound), or age(datfrozenxid) exceeds hundreds of millions.

Working Example

CREATE TABLE ledger (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);
 
INSERT INTO ledger (amount)
SELECT (random() * 100)::numeric(12,2) FROM generate_series(1, 100000);
 
-- Churn creates tuples needing freeze
UPDATE ledger SET amount = amount + 1;
 
SELECT
  c.relname,
  age(c.relfrozenxid) AS table_frozen_age,
  s.n_dead_tup,
  s.last_autovacuum
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relname = 'ledger';
 
SELECT datname, age(datfrozenxid) AS db_frozen_age
FROM pg_database
WHERE datname = current_database();

What this demonstrates:

• age(relfrozenxid) per table
• Database-level datfrozenxid age
• Link between churn, vacuum, and freeze

Deep Dive

How It Works

• Every row has xmin/xmax transaction IDs for visibility.
• Vacuum marks old committed transactions frozen so IDs can be reused safely.
• autovacuum_freeze_max_age (default 200M) forces aggressive vacuum.
• Emergency mode stops connections if wraparound horizon violated (rare with healthy autovacuum).

Monitoring Thresholds

SQL Notes

SELECT relname, last_autovacuum, last_vacuum
FROM pg_stat_user_tables
ORDER BY age(relid) DESC NULLS LAST
LIMIT 15;
 
VACUUM FREEZE VERBOSE ledger;

Gotchas

• Long open transactions - Block freezing; age climbs. Fix: idle_in_transaction_session_timeout, monitor pg_stat_activity.xact_start.
• Replication slots without consumption - Prevent vacuum cleanup on primary. Fix: Drop stale slots; monitor pg_replication_slots.
• Disabled autovacuum - Fast path to shutdown risk. Fix: Re-enable immediately; manual VACUUM FREEZE.
• Only watching database age, not table age - One bad table can drag horizon. Fix: Per-table age(relfrozenxid) dashboards.
• Deferring vacuum on append-only archives - Even append tables need occasional freeze. Fix: Scheduled light vacuum.

Alternatives

FAQs

Related

• Autovacuum Tuning - Prevent lag
• Vacuum Basics - Freeze role
• Monitoring Basics - Alerting
• Vacuum Best Practices - Never disable autovacuum

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.