Reading transaction age

Part VIII will deal with wraparound as a failure mode. This lesson is the measuring instrument you need first, because “transaction age” is reported at three different levels that answer three different questions, and mixing them up is the usual reason an alert either never fires or fires constantly.

Three levels, three questions

They are not interchangeable. The database number is a consequence; the session number is a cause.

Database level

$ psql -U postgres -c "SELECT datname, age(datfrozenxid) AS xid_age, round(100.0*age(datfrozenxid)/current_setting('autovacuum_freeze_max_age')::numeric,2) AS pct_to_wraparound_vacuum FROM pg_database ORDER BY age(datfrozenxid) DESC"

  datname  | xid_age | pct_to_wraparound_vacuum
-----------+---------+--------------------------
postgres  |  682948 |                     0.34
template1 |  682948 |                     0.34
template0 |  682948 |                     0.34
(3 rows)

datfrozenxid is the oldest unfrozen transaction id anywhere in the database, so age(datfrozenxid) is how far behind the oldest table has fallen. The percentage is against autovacuum_freeze_max_age, default 200,000,000 — the point at which autovacuum stops being optional and launches anti-wraparound work whether or not the table met any other threshold.

Table level

When the database number rises, the next question is which table.

SELECT c.oid::regclass                       AS relation,
       age(c.relfrozenxid)                   AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
       s.last_autovacuum,
       s.autovacuum_count
  FROM pg_class c
  LEFT JOIN pg_stat_all_tables s ON s.relid = c.oid
 WHERE c.relkind IN ('r', 'm', 't')          -- tables, matviews, TOAST
 ORDER BY age(c.relfrozenxid) DESC
 LIMIT 20;

Two details in that query are there for a reason.

relkind includes 't'. TOAST relations have their own relfrozenxid and age independently. A TOAST relation can be the oldest thing in the database while its parent table looks fine, and a query filtered to relkind = 'r' will never show it.

The size column matters for planning, not for diagnosis. When the oldest table is 4 TB, the aggressive vacuum that will eventually be forced on it is a long operation you would rather schedule than have scheduled for you.

Session level, and the four sources

This is the query from lesson VII-03, and it is the one that answers “why is the number rising”.

SELECT 'oldest running xact'     AS source,
       coalesce(max(age(backend_xmin))::text, 'none') AS xid_age
  FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'oldest prepared xact',
       coalesce(max(age(transaction))::text, 'none') FROM pg_prepared_xacts
UNION ALL
SELECT 'oldest replication slot',
       coalesce(max(age(xmin))::text, 'none') FROM pg_replication_slots
UNION ALL
SELECT 'oldest replica feedback',
       coalesce(max(age(backend_xmin))::text, 'none') FROM pg_stat_replication;

$ psql -U postgres -f horizon.sql

         source          | xid_age
-------------------------+---------
oldest running xact     |       9
oldest prepared xact    | none
oldest replication slot | none
oldest replica feedback | none
(4 rows)

Distinguish none from 0. none means there are no rows in that source at all — no prepared transactions exist. 0 would mean one exists and is current. An alert that treats them the same will either page you about an empty table or miss a brand-new holder.

The thresholds that matter

Four numbers govern what happens as age rises. All are documented defaults for PostgreSQL 18.

The gap between 200 million and 2 billion is the safety margin, and it is generous: on a cluster consuming a thousand transactions per second, that gap is roughly three weeks. Wraparound outages are not caused by the margin being too small. They are caused by nothing having worked for a very long time and nobody having looked.

What to take from this

• Three levels: database (datfrozenxid), table (relfrozenxid), session (backend_xmin). The first two are consequences; the third is a cause.
• Include relkind = 't' when hunting for the oldest relation, and do not exclude template0.
• Alert on the percentage of autovacuum_freeze_max_age, and if you can, on the trend rather than the level.
• A sawtooth with stable peaks is health. A monotonic climb is an outage in preparation.
• none and 0 are different answers. Handle both.

Cross-course references

• Observability for Production Sysadmins — Part XIII (Rates and counters) covers turning transaction age into a rate, which is what converts a number into a date, and Part XVIII (Alerting rules) covers expressing the resulting deadline as an alert.

Quiz