Transaction ID Wraparound, Freezing, relfrozenxid, and Emergency Prevention

Learning outcomes

Vacuum is also a correctness mechanism. PostgreSQL transaction identifiers (XIDs) are finite 32-bit values that are compared using wraparound-aware age semantics. If old tuple XIDs are never frozen, eventually very old data could appear to be “in the future.” PostgreSQL prevents this with freezing and anti-wraparound vacuum. The correct lesson is therefore prevention and monitoring—not manufacturing a cluster close to shutdown.

1. What does “age” mean for an XID?

Normal XIDs are compared in a circular 32-bit space. PostgreSQL interprets roughly half the space as past and half as future relative to the current transaction horizon. Freezing replaces the need to keep consulting an ancient creating transaction's status: frozen tuples are treated as older than every ordinary running transaction for visibility purposes.

SELECT datname,       age(datfrozenxid) AS xid_age,       datfrozenxidFROM pg_databaseWHERE datallowconnORDER BY xid_age DESC;SELECT c.oid::regclass AS relation,       age(c.relfrozenxid) AS xid_age,       c.relfrozenxidFROM pg_class AS cJOIN pg_namespace AS n ON n.oid = c.relnamespaceWHERE n.nspname = 'app'  AND c.relkind IN ('r','m')ORDER BY xid_age DESCLIMIT 20;

relfrozenxid summarizes the oldest normal XID that may still need consideration for a relation. datfrozenxid tracks the database-level oldest horizon derived from its relations. These are maintenance metadata, not timestamps and not identifiers to expose to applications.

2. Read the cluster's actual freeze policy

SELECT name, setting, unit, context, sourceFROM pg_settingsWHERE name IN (  'autovacuum_freeze_max_age',  'vacuum_freeze_min_age',  'vacuum_freeze_table_age',  'vacuum_failsafe_age')ORDER BY name;

autovacuum_freeze_max_age is a major prevention boundary: the server schedules anti-wraparound vacuum before relations become dangerously old. The other horizons influence when eligible tuples are frozen and when more aggressive/failsafe behavior is justified. Treat the live settings as the source of truth; do not hard-code a percentage from a blog without understanding the configured values and transaction consumption rate.

SELECT pg_current_xact_id_if_assigned() AS current_xid_if_assigned;SELECT datname, age(datfrozenxid) AS xid_ageFROM pg_databaseWHERE datname = current_database();

Read-only statements need not assign a normal XID immediately, which is one reason a safe monitoring lab does not try to “count transactions” by forcing transaction identifiers. Observe age metadata directly.

3. A safe freeze laboratory

DROP TABLE IF EXISTS app.ch09_freeze_lab;CREATE TABLE app.ch09_freeze_lab (    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    note text NOT NULL);INSERT INTO app.ch09_freeze_lab(note)SELECT 'freeze-demo-' || gFROM generate_series(1, 5000) AS g;SELECT age(relfrozenxid) AS before_age, relfrozenxidFROM pg_classWHERE oid = 'app.ch09_freeze_lab'::regclass;

VACUUM (FREEZE, VERBOSE, ANALYZE) app.ch09_freeze_lab;SELECT age(relfrozenxid) AS after_age, relfrozenxidFROM pg_classWHERE oid = 'app.ch09_freeze_lab'::regclass;

On a freshly created table, both ages are already small, so do not expect a dramatic numeric demonstration. The important evidence is that VACUUM FREEZE performs an aggressive freeze-oriented pass and advances relation metadata when there is eligible work. In production, routine autovacuum should prevent the need to run this blindly everywhere.

4. Anti-wraparound vacuum is special

Disabling ordinary autovacuum on a table does not remove PostgreSQL's responsibility to prevent transaction-ID wraparound. Protective vacuum can still be required. That is why “turn autovacuum off because it is causing I/O” is not a complete operational strategy.

SELECT c.oid::regclass AS relation,       age(c.relfrozenxid) AS xid_age,       s.last_autovacuum,       s.autovacuum_count,       s.n_dead_tupFROM pg_class AS cJOIN pg_namespace AS n ON n.oid = c.relnamespaceLEFT JOIN pg_stat_all_tables AS s ON s.relid = c.oidWHERE c.relkind IN ('r','m')  AND n.nspname NOT IN ('pg_catalog','information_schema')ORDER BY xid_age DESCLIMIT 30;

For incident triage, sort by age first. A large table with a high age is more urgent than a small table merely because its dead-tuple estimate is larger.

SELECT pid, usename, state, xact_start, backend_xmin, queryFROM pg_stat_activityWHERE backend_xmin IS NOT NULL   OR xact_start IS NOT NULLORDER BY xact_start NULLS LAST;SELECT gid, prepared, owner, databaseFROM pg_prepared_xactsORDER BY prepared;

Long-running transactions and prepared transactions are not equivalent to a high relfrozenxid, but they can hold visibility horizons and complicate cleanup. Investigate them as part of the same maintenance incident rather than vacuuming blindly.

5. Build an emergency-prevention dashboard

WITH maxage AS (  SELECT current_setting('autovacuum_freeze_max_age')::numeric AS max_age)SELECT d.datname,       age(d.datfrozenxid) AS xid_age,       round(100 * age(d.datfrozenxid)::numeric / maxage.max_age, 2) AS pct_of_configured_maxFROM pg_database AS dCROSS JOIN maxageWHERE d.datallowconnORDER BY xid_age DESC;

This percentage is a triage aid, not a universal alert threshold. Your alerting policy should also consider transaction consumption velocity, vacuum runtime on the largest old tables, worker/I/O capacity, replication/backup constraints, and how quickly an operator can intervene.

6. Production judgment: age is a trajectory, not a one-time number

A healthy operating model alerts early enough that ordinary anti-wraparound autovacuum has time to finish the largest old relations. Track both absolute age and its growth rate. A high but falling age after successful vacuum is a different incident from a lower age that is accelerating while workers are blocked. Also monitor MultiXact age in real production systems, because row-locking workloads have a separate wraparound domain even though this lesson focuses on normal XIDs.

Do not “solve” XID pressure by terminating arbitrary sessions, disabling replication safeguards, or globally forcing FREEZE without understanding consequences. Preserve business correctness, identify the oldest maintenance horizons, and give protective vacuum enough resources to make measurable forward progress.

7. Conceptual emergency runbook

If ages become unexpectedly high: confirm the actual oldest databases/relations; check whether autovacuum is running and whether long-running transactions/prepared transactions/replication slots are obstructing cleanup; protect I/O and worker capacity for anti-wraparound work; vacuum the oldest relations deliberately; avoid schema rewrites or unrelated heavy maintenance; and continue monitoring age until the trajectory reverses. If the server begins refusing commands to prevent wraparound, follow the official major-version recovery procedure rather than improvising.

DROP TABLE IF EXISTS app.ch09_freeze_lab;

Authoritative references

These mechanisms are version-sensitive. Use the documentation for the PostgreSQL major you actually operate.

• PostgreSQL 18 — Preventing Transaction ID Wraparound Failures
• PostgreSQL 18 — Autovacuum settings
• PostgreSQL 18 — VACUUM