Oldest Autovacuum Age (hours), PostgreSQL

​

At a glance

This card reports how long it has been, in hours, since your most overdue busy table was last vacuumed. PostgreSQL relies on autovacuum to clean up dead row versions and, critically, to advance the transaction-ID horizon that keeps the database from hitting wraparound. When a hot table has not been vacuumed in more than 24 hours, autovacuum is starved: it is not keeping up, has been disabled, or is being blocked. This is a PostgreSQL-distinctive risk with no exact equivalent in other databases, and at its extreme it ends in the single scariest PostgreSQL failure mode: transaction-ID wraparound, where the database forces itself into a read-only shutdown to protect your data. For a DBA this is a slow-burning, high-consequence alarm.

​

Calculation

for each user table:
   age_hours = now() - greatest(last_autovacuum, last_vacuum)
   is_busy   = (n_tup_ins + n_tup_upd + n_tup_del) since last vacuum > 0
oldest_autovacuum_age = max(age_hours) over busy tables
fire when: oldest_autovacuum_age > 24

1. Bloat. Every UPDATE and DELETE leaves a dead row version behind. Vacuum reclaims that space for reuse. Without it, the table and its indexes grow, sequential scans read more pages, and the buffer cache holds more dead weight. This shows up next in Top Tables by Dead Tuples.
2. Transaction-ID wraparound. PostgreSQL uses a 32-bit transaction counter. Vacuum “freezes” old rows so their transaction IDs can be safely reused. If freezing falls far enough behind (age(relfrozenxid) approaching autovacuum_freeze_max_age, default 200 million), PostgreSQL escalates to an aggressive anti-wraparound autovacuum, and if that still cannot keep up, it shuts the database into read-only mode to prevent data loss. The age-in-hours is the early, human-readable warning that this process is not keeping pace.

​

Worked example

1. The hottest table is the starved one. order_events takes 4 million writes a day and has not been vacuumed in over two days. Every one of those updates and deletes left a dead tuple behind that has not been reclaimed. That is a lot of bloat building quietly.
2. The frozen-XID horizon is drifting. age(relfrozenxid) of 121 million is over half of the 200-million default autovacuum_freeze_max_age. It is not an emergency yet, but it is heading the wrong way, and on the busiest table. Left alone, an anti-wraparound autovacuum will eventually kick in hard.
3. Autovacuum is not disabled globally, just losing on this table. Other busy tables (order_line_items at 1.1h) are being vacuumed fine. So the cause is table-specific: either order_events exceeds the cost-limit budget autovacuum will spend per run, or something is blocking it.

Diagnosis:
  1. Is autovacuum even running on it?
     SELECT relname, last_autovacuum, autovacuum_count, n_dead_tup
     FROM pg_stat_user_tables WHERE relname = 'order_events';
  2. Is a long transaction holding back the cleanup horizon?
     SELECT pid, state, now() - xact_start AS tx_age, query
     FROM pg_stat_activity
     WHERE xact_start IS NOT NULL
     ORDER BY xact_start ASC LIMIT 5;
     -> a transaction open for hours pins the XID horizon for the WHOLE DB,
        so vacuum cannot remove dead tuples newer than that snapshot.
  3. Is the cost limit throttling it on a big table?
     Check autovacuum_vacuum_cost_limit / cost_delay.
  4. Per-table tuning to make autovacuum trigger sooner and run harder:
     ALTER TABLE order_events SET (
        autovacuum_vacuum_scale_factor = 0.02,
        autovacuum_vacuum_cost_limit  = 2000
     );
  5. If urgent, run a manual VACUUM (VERBOSE) order_events now.

1. Age-in-hours is the early warning; wraparound is the catastrophe. You will never want to read this card at the wraparound stage. 24 hours on a busy table is the nudge to look now, while the fix is a one-line ALTER TABLE and not an emergency single-user-mode VACUUM.
2. A single starved table usually points to a single blocker. When one busy table lags while others are clean, suspect a long-lived transaction pinning the snapshot, or per-table autovacuum settings that are too conservative for that table’s write rate.
3. This is PostgreSQL-specific. Engines without PostgreSQL’s MVCC-plus-32-bit-XID design do not have this exact failure mode. Treat autovacuum health as a first-class operational signal, not an afterthought.

​

Sibling cards

​

Reconciling against the source

Run SELECT relname, last_autovacuum, last_vacuum, n_dead_tup, n_live_tup, autovacuum_count FROM pg_stat_user_tables ORDER BY last_autovacuum ASC NULLS FIRST;. The top rows are your oldest vacuums; ignore the static tables with no dead tuples. Check the wraparound horizon with SELECT relname, age(relfrozenxid) AS xid_age FROM pg_class WHERE relkind = 'r' ORDER BY xid_age DESC LIMIT 10; and compare against SHOW autovacuum_freeze_max_age;. See whether an autovacuum is running right now with SELECT pid, query, now() - xact_start AS running_for FROM pg_stat_activity WHERE query LIKE 'autovacuum:%';. On a managed service, the provider surfaces a MaximumUsedTransactionIDs metric (RDS / Aurora) or an equivalent XID-age chart (Cloud SQL, Azure), which is the same wraparound horizon the card watches.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre