PostgreSQL XID Age Is Rising: Find What Autovacuum Cannot Freeze

PostgreSQL transaction IDs are a countdown, not a disk-usage gauge. Every transaction that needs an XID advances a 32-bit counter. Vacuum protects old row versions by freezing them, but a database whose oldest unfrozen XID keeps aging can eventually refuse commands that assign new XIDs rather than risk wraparound data loss.

Treat the incident as an ownership problem. Rank database age, connect to the oldest database, identify the relation that owns its oldest relfrozenxid, and decide whether anti-wraparound vacuum is advancing, waiting or repeatedly failing. Preserve a superuser maintenance path and storage runway before changing parameters. Recovery is proven only when both relation age and database age fall.

Read XID age as distance to a safety boundary

The current PostgreSQL vacuuming documentation explains the failure model: transaction IDs are 32-bit values, so an unfrozen row version cannot remain safely interpretable forever. VACUUM replaces sufficiently old visibility metadata with a frozen representation that remains in the past for normal transactions.

Two catalog fields locate the boundary. pg_class.relfrozenxid records the oldest remaining unfrozen XID for a relation after a vacuum advances it. pg_database.datfrozenxid is the minimum relation-level value for that database. Therefore database age cannot advance until its oldest relation is successfully frozen.

Run the cluster-level query from a superuser or monitoring account that can see every database:

SELECT datname,
       age(datfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint AS forced_vacuum_age,
       round(100.0 * age(datfrozenxid)
             / current_setting('autovacuum_freeze_max_age')::bigint, 1) AS pct_of_forced_age
FROM pg_database
WHERE datallowconn
ORDER BY age(datfrozenxid) DESC;

pct_of_forced_age compares age with the cluster-level anti-wraparound trigger; it is not a universal emergency score. Per-table storage parameters can request an earlier forced vacuum, while a busy cluster can consume the remaining distance much faster than a quiet cluster. Record the result with a UTC timestamp and sample it again after a known interval to learn XID velocity.

MultiXact IDs have a separate wraparound safeguard for row-lock membership. The queries in this article measure ordinary transaction-ID age through relfrozenxid and datfrozenxid; they do not replace monitoring of relminmxid, datminmxid and autovacuum_multixact_freeze_max_age. Treat a MultiXact warning as its own measured counter rather than assuming the XID percentage covers it.

PostgreSQL launches anti-wraparound autovacuum even when ordinary autovacuum is disabled. That safeguard does not make the system self-healing: a worker can be blocked by a conflicting lock, cancelled, starved of storage or repeatedly exit on a relation error. Worker presence is not completion proof.

Follow the oldest database down to its relation

Connect directly to the database with the greatest xid_age; relation catalogs are database-local. Rank ordinary tables, materialized views and TOAST relations without hiding system schemas:

SELECT n.nspname,
       c.relname,
       c.relkind,
       age(c.relfrozenxid) AS xid_age,
       c.reloptions,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 25;

The first row is the current ownership candidate, not automatic permission to vacuum blindly. Note whether it is a user table, pg_toast relation or catalog; capture its size; and compare its age with nearby relations. A single outlier suggests one local failure, while many similarly old relations point toward a broader maintenance deficit.

Include partitions, TOAST and catalogs in the search

Partitioned parents do not store tuples, so the leaf relations matter. Large values can also sit in TOAST storage under a different relation name. Catalogs remain critical because datfrozenxid cannot move past them; PostgreSQL specifically warns that a non-superuser vacuum can miss system catalogs and fail to advance database age.

Resolve a TOAST owner before acting:

SELECT toast.oid::regclass AS toast_relation,
       main.oid::regclass AS owning_relation
FROM pg_class AS main
JOIN pg_class AS toast ON toast.oid = main.reltoastrelid
WHERE toast.oid = 'pg_toast.pg_toast_REPLACE_OID'::regclass;

Replace the placeholder with the exact TOAST relation returned by the ranking query. If name resolution is unclear, use the OID from pg_class rather than guessing. Vacuuming the owning table normally covers its associated TOAST relation.

Separate XID pressure from a storage incident

Wraparound age and free bytes are different measurements, yet they can collide. VACUUM needs enough filesystem and WAL runway to complete; another PostgreSQL mechanism may be consuming that runway. When pg_wal is growing, inspect the separate replication-slot WAL retention workflow instead of assuming old XIDs created the files.

Check the PostgreSQL data directory and relevant filesystems before an aggressive pass. When evidence points to full thin-pool data or metadata, follow Voxfor’s LVM thin-pool recovery workflow before retrying database maintenance. Deleting pg_xact, WAL or relation files is not space recovery; it risks cluster damage.

Decide whether vacuum is progressing or merely present

An old worker can be healthy when it is scanning a large relation. Killing it discards work and shortens the remaining safety margin. Begin with PostgreSQL’s live progress view:

SELECT pid,
       relid::regclass AS relation,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed,
       index_vacuum_count
FROM pg_stat_progress_vacuum
ORDER BY pid;

The vacuum progress reference defines each phase and counter. Save two samples several minutes apart. Increasing heap_blks_scanned or a legitimate phase transition shows progress even if completion is slow. A static sample proves nothing by itself.

A wait event needs an owner before a cancellation

Join activity and lock evidence when the counters do not move:

SELECT a.pid,
       a.backend_type,
       a.wait_event_type,
       a.wait_event,
       now() - a.query_start AS runtime,
       pg_blocking_pids(a.pid) AS blocking_pids,
       left(a.query, 120) AS query
FROM pg_stat_activity AS a
WHERE a.backend_type = 'autovacuum worker'
   OR a.query ILIKE 'autovacuum:%'
ORDER BY a.query_start;

A nonempty blocker list turns the incident into a lock-ownership decision. Identify the session, application and operation behind each PID; PID alone is not termination authorization. A DDL migration may be safer to cancel than anti-wraparound vacuum, while a critical transaction may need an orderly application pause. Preserve evidence and use the smallest reversible intervention.

Connection storms can also consume operator attention and maintenance access. If normal application admission must be reduced deliberately, use Voxfor’s PgBouncer connection-control guide as a separate pooling boundary; keep one tested superuser path outside an overloaded application pool.

Old snapshots explain cleanup limits, not every freeze failure

Long transactions, prepared transactions and logical or physical replication horizons can retain dead tuples and amplify bloat. Inspect them because they change maintenance cost and may reveal a broader incident:

SELECT pid, usename, application_name,
       now() - xact_start AS transaction_age,
       backend_xid, backend_xmin, state
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
ORDER BY prepared;

SELECT slot_name, slot_type, active, xmin, catalog_xmin
FROM pg_replication_slots
ORDER BY slot_name;

An old snapshot does not by itself prove anti-wraparound freezing cannot advance. It can prevent removal of dead tuples and hold other horizons, so close only abandoned or safely recoverable work under application ownership. Never delete a replication slot or resolve a prepared transaction merely because its age looks large.

Repeated worker disappearance needs log evidence. Search PostgreSQL logs for cancellation, lock timeout, I/O error, corrupt page, no-space or crash messages at the worker’s timestamp. When kernel or cgroup pressure repeatedly kills database processes, follow Voxfor’s Linux OOM attribution workflow to identify the actor before changing PostgreSQL memory settings.

Recover the owner without broadening the incident

Once the oldest relation and its blocker are understood, protect the maintenance window: pause nonessential write-heavy jobs, preserve superuser access, confirm storage runway and record the before-age query. Avoid a version upgrade, cluster restart or global parameter sweep while the XID margin is shrinking.

When the owning relation has already reached its effective aggressive-vacuum age, or a failed anti-wraparound worker was processing it, run standard vacuum against that qualified table from the affected database:

VACUUM (VERBOSE) schema_name.table_name;

Replace both identifiers with the exact quoted-safe relation selected from the catalog. For generated commands, use format('%I.%I', nspname, relname) rather than hand-building unquoted SQL. Standard VACUUM is normally the right freeze path and can coexist with ordinary reads and writes; the current VACUUM reference contrasts it with VACUUM FULL, which rewrites the table and requires ACCESS EXCLUSIVE.

Treat VACUUM FULL as outside wraparound recovery because its exclusive lock, rewrite space and longer change boundary do not make tuple freezing more valid. Below the effective vacuum_freeze_table_age, ordinary vacuum may skip pages that a deliberately early freeze needs to visit; an approved, measured maintenance action may therefore use VACUUM (FREEZE, VERBOSE) against the exact relation after checking I/O and storage runway. FREEZE is not a default emergency ritual merely because the word sounds safer.

Near write refusal, use the safeguard PostgreSQL preserves

Current PostgreSQL documentation says the server refuses commands that assign new XIDs when fewer than three million transactions remain before wraparound. The safety margin exists so an administrator can run maintenance. Perform the recommended vacuum as a superuser, including catalogs, instead of assuming the cluster must be started in single-user mode.

Single-user mode is now described as usually unnecessary and riskier because single-user operation disables wraparound safeguards. PostgreSQL reserves that path for the narrow case where an administrator must TRUNCATE or DROP unneeded tables instead of vacuuming them. That is an outage-level data decision: follow the exact documentation for the installed major version, take a verified backup where possible, and require human change approval.

Raising autovacuum_freeze_max_age does not freeze existing tuples. The current autovacuum settings reference shows that it moves a future forced-vacuum trigger and increases the history PostgreSQL must retain in pg_xact and, when enabled, pg_commit_ts. Changing the ruler does not repair the oldest relation.

Close only after the counter moves

Rerun the relation ranking in the affected database after vacuum completes. The targeted relation should no longer own the oldest age, or its xid_age should fall materially. Next rerun the cluster database query. datfrozenxid may remain unchanged when another relation is now the minimum; continue through evidence, not through an indiscriminate VACUUM loop.

Record four acceptance facts:

1. Oldest relation age decreased or ownership moved to a known next relation.
2. Database age(datfrozenxid) decreased after every required oldest relation completed.
3. No new wraparound warning, vacuum error or unexplained worker exit appears during the observation window.
4. A representative application write, commit and read succeeds after traffic is restored deliberately.

Recovery does not require reclaiming operating-system disk space. Standard vacuum makes reusable space inside relations and advances freeze metadata; those are different outcomes. If filesystem pressure remains after XID age is safe, investigate it as a separate capacity incident.

Build an age budget from velocity, not folklore

Monitor age(datfrozenxid) for every database and the configured autovacuum_freeze_max_age. Alert before the forced threshold with enough time for the largest relation to complete under real I/O. A second alert should detect positive age velocity while the oldest relation remains unchanged across samples.

Cardinality matters in monitoring too. Export database-level age broadly, but add per-relation series only for a bounded oldest-N set or through scheduled reports; Voxfor’s Prometheus label-cardinality guide explains why relation names across every database can create an avoidable series explosion.

Track the largest freeze duration, worker cancellations, lock waits, free-space floor, XID consumption per hour and the exact runbook owner. After the incident, tune autovacuum only from these measurements. Continue with Voxfor’s database operations library for adjacent reliability work, but keep wraparound prevention accountable as its own availability control.