Database State: Transaction-ID wraparound & freeze-age monitoring

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

Proposal

Extend the Database Diagnostics page (/admin/database_diagnostics) with transaction-ID (and MultiXact) wraparound monitoring: show how close tables and databases are to the freeze thresholds that force aggressive anti-wraparound autovacuums - and, ultimately, to the wraparound limit that would force the database into read-only protection mode. This is the safety-critical end of autovacuum: bloat costs performance, but unaddressed wraparound risk can take the database down.

Background

PostgreSQL uses 32-bit transaction IDs and MultiXact IDs, which wrap around. To prevent old rows from appearing "in the future", autovacuum must freeze old tuples. As a table's oldest unfrozen xid age approaches autovacuum_freeze_max_age, PostgreSQL launches an anti-wraparound autovacuum - which cannot be skipped and won't auto-cancel. If freezing still can't keep up and age approaches ~2 billion, the database refuses new write transactions to protect itself. The references below flag wraparound prevention as one of autovacuum's core jobs; surfacing freeze age early turns a potential incident into a routine observation, and explains why an aggressive anti-wraparound vacuum (see #601284 (closed)) is running.

What to surface

Per database (from pg_database):

• age(datfrozenxid) and the MultiXact equivalent mxid_age(datminmxid)
• Each as a percentage of autovacuum_freeze_max_age / autovacuum_multixact_freeze_max_age, and of the ~2-billion hard wraparound limit
• Effective autovacuum_freeze_max_age, autovacuum_multixact_freeze_max_age, vacuum_freeze_min_age, vacuum_freeze_table_age for context

Per table (top offenders, from pg_class joined to pg_stat_user_tables):

• age(relfrozenxid) and mxid_age(relminmxid), with the same percentage-of-threshold framing
• last_autovacuum, table size, and any per-table *_freeze_* reloptions overrides
• Sorted by age descending so the closest-to-threshold tables surface first

Checks / warnings to flag

• 🟡 Table or database age ≥ autovacuum_freeze_max_age - anti-wraparound autovacuum is (or should be) running.
• 🟠 Age at a high fraction of the freeze threshold and not decreasing across runs - freezing isn't keeping up (often points back to an xmin-horizon blocker or starved workers).
• 🔴 Age approaching the hard wraparound limit (e.g. > ~1.5 billion) - escalate; risk of forced read-only mode.

Apply the same checks to MultiXact age, which can be the binding constraint on workloads heavy in row locking / SELECT ... FOR SHARE/UPDATE / subtransactions.

Technical notes

• If needed, follow the Worker + Cache + Vue pattern of the collation / schema checkers.
• Run across all decomposed databases via the SharedModel pattern (each database has its own datfrozenxid / datminmxid).
• Use age() / mxid_age() in SQL rather than computing in Ruby, to get correct modular-arithmetic distances.
• Severity thresholds should be constants, easy to adjust.
• Read-only / informational - no freezing or vacuum triggered from this page.

References

• Postgres.fm - Autovacuum
• PostgreSQL docs - Preventing Transaction ID Wraparound Failures
• EDB - Autovacuum Tuning Basics
• pganalyze - Visualizing & Tuning Postgres Autovacuum

Related

• Part of the Database State Framework epic &20270
• Complements #601284 (closed) (auto-vacuum current status) - explains anti-wraparound autovacuums seen there - and the auto-vacuum backlog / vacuum-urgency and configuration-audit work items.