Track PostgreSQL transaction ID age before it becomes an incident

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

Transaction identifiers are finite, so PostgreSQL must freeze sufficiently old row versions before identifier reuse makes age interpretation unsafe. This is a maintenance obligation even for tables that rarely change. A table can have few dead tuples and still need attention because its oldest unfrozen transaction information is aging.

The operational goal is to detect a failing maintenance trend long before protective behavior limits ordinary work. Do not test this by driving a production database toward wraparound. Read-only age monitoring and a controlled review of vacuum progress provide the relevant evidence without creating the incident you are trying to prevent.

Measure age at two levels

Use PostgreSQL 18 with a role allowed to inspect the relevant catalogs. Start with databases across the cluster:

SELECT datname,
       age(datfrozenxid) AS xid_age,
       mxid_age(datminmxid) AS multixact_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Then connect to a database of interest and inspect storage-bearing relations:

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

This includes ordinary tables, materialized views, and TOAST relations. Partitioned parents do not themselves store the same kind of heap data; inspect the actual partitions. Do not silently filter out internal storage just because the application never queries it directly.

age is transaction-ID distance, not a timestamp. A database processing more transactions ages faster in wall-clock terms. A fixed “days since vacuum” alert cannot substitute for age measurement.

Read the configured safety policy

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
  'autovacuum_freeze_max_age',
  'vacuum_freeze_min_age',
  'vacuum_freeze_table_age',
  'vacuum_failsafe_age',
  'autovacuum_multixact_freeze_max_age',
  'vacuum_multixact_freeze_min_age',
  'vacuum_multixact_freeze_table_age',
  'vacuum_multixact_failsafe_age'
)
ORDER BY name;

These settings serve different purposes: when freezing can occur, when broader scans are needed, when automatic anti-wraparound work is triggered, and when failsafe behavior prioritizes completing essential maintenance. Treating them as interchangeable thresholds leads to unsafe tuning.

PostgreSQL's protective limits are not a capacity target. An operational alert should leave enough time to identify the relation, understand the blocker, obtain any required maintenance approval, and complete cleanup under realistic load. The margin depends on the measured transaction rate and maintenance duration.

Reference monitoring ladder. Observe age and trend, confirm freezing progress, and act before emergency maintenance or protective restrictions become relevant.

Estimate time cautiously

A practical dashboard can sample the oldest database age and a transaction-volume indicator over time. If age rises steadily, estimate how long the current margin would last at the recent rate. Label that estimate as a scenario, not a guaranteed deadline. Traffic bursts, batch jobs, and changes in transaction allocation can invalidate a simple extrapolation.

Do not subtract raw transaction identifiers naively or cast them into application integers and assume ordinary arithmetic across wraparound. Use PostgreSQL's supported age functions. If collecting transaction identifiers for rate analysis, understand the function's allocation and width semantics and avoid adding unnecessary transaction-ID consumption merely for monitoring.

A falling database age is encouraging, but inspect the relation-level distribution. One newly problematic large table can be masked by a cluster-wide summary. Conversely, an old small relation may be straightforward to maintain once discovered.

Investigate stalled progress

Check whether vacuum is running, waiting on locks, throttled by capacity, repeatedly interrupted, or constrained by retained horizons. Inspect long-lived transactions, prepared transactions, and replication retention as part of that investigation. A maintenance command that runs frequently without advancing the relevant age is not enough.

Large relations need time and I/O to scan. Anti-wraparound work should not first become visible to the team when the system is already near a protective limit. Record past maintenance durations and ensure that storage and worker capacity can support the next cycle.

Multixact age is a separate risk dimension associated with transaction groups such as shared row-locking activity. A healthy ordinary XID age does not prove multixact maintenance is healthy. Monitor both with the matching functions and configuration values.

Avoid dangerous shortcuts

Do not disable autovacuum to quiet an alert. PostgreSQL can invoke safety-driven maintenance even when ordinary autovacuum behavior is disabled, and suppressing routine work only makes the overall situation harder to manage. Do not edit catalogs to make ages appear smaller or use transaction-reset tools as routine maintenance.

A manual vacuum may be appropriate under an approved incident runbook, but it should target the actual cause and account for locks, I/O, and application impact. VACUUM FULL is not the standard answer to transaction age; its rewrite and locking costs solve a different physical-storage problem.

If the database emits wraparound warnings or begins refusing ordinary work, escalate through the database's version-specific recovery procedure and the hosting provider's support path. Preserve logs and current age measurements. Do not improvise by raising thresholds without understanding the remaining safe range.

Make the alert actionable

An alert should identify the database and oldest relations, current XID and multixact ages, configured trigger points, age trend, and current vacuum activity. Assign an owner who can distinguish a transient maintenance cycle from a stalled one. Include the next read-only diagnostic query rather than a destructive remediation command.

The best outcome is an uneventful graph: ages rise as transactions occur, maintenance advances the oldest frozen horizon, and operational headroom remains ample. Transaction-age monitoring is successful precisely when the team never needs to learn protective behavior during an emergency.

Sources and review scope

• Transaction ID wraparound and routine maintenance
• Vacuum configuration
• System information functions
• pg_class catalog
• pg_database catalog