PostgreSQL autovacuum: diagnose cleanup lag and wraparound risk

Inside the database under investigation, rank stored tables and materialized views, including catalogs. TOAST stores large values separately and can be older than the visible table. Keep the schema and relation identity; an unqualified name can select the wrong object.

The first ranking prioritizes XIDs. Inspect both heap and TOAST multixact ages as well, and rerank by those columns when MXIDs drive the incident. Partition parents have no ordinary heap here; examine the actual stored partitions. System catalogs or multiple old relations need a database-wide DBA plan, beyond the single-table action below.

SELECT n.nspname, c.relname,
       age(c.relfrozenxid) AS heap_xid_age,
       age(t.relfrozenxid) AS toast_xid_age,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) AS oldest_xid_age,
       mxid_age(c.relminmxid) AS heap_mxid_age,
       mxid_age(t.relminmxid) AS toast_mxid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       c.reloptions
FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
LEFT JOIN pg_class t ON t.oid=c.reltoastrelid
WHERE c.relkind IN ('r','m') AND c.relfrozenxid <> '0'::xid
ORDER BY greatest(age(c.relfrozenxid),age(t.relfrozenxid)) DESC
LIMIT 30;

Use table statistics to distinguish ordinary churn from the freeze-age finding. n_dead_tup is an estimate, not an exact count of removable rows or bytes. A high count can persist because old snapshots still need those row versions; a small count does not mean freezing is unnecessary.

Compare repeated samples with workload and statistics-reset history. Insert-heavy tables can also need vacuum. These counters do not prove a missed schedule, and they do not justify VACUUM FULL.

SELECT schemaname,relname,n_live_tup,n_dead_tup,
       last_vacuum,last_autovacuum,vacuum_count,autovacuum_count,
       n_ins_since_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 20;

Vacuum must retain a row version that an old snapshot can still need. The oldest retained need therefore constrains cleanup. Old open transactions, prepared transactions, replication slots and standby feedback are candidates to investigate. Start with age and ownership metadata rather than dumping SQL text, which can contain private data.

Discuss an old transaction with its application owner. Prepared transactions belong to a two-phase commit coordinator; resolving one arbitrarily can break business consistency. A replication slot may protect an offline consumer. Deleting it can force reinitialization or lose the consumer’s required history. Do not terminate sessions, roll back prepared transactions or drop slots from a generic age ranking.

SELECT pid,usename,application_name,backend_type,state,
       clock_timestamp()-xact_start AS transaction_duration,
       age(backend_xid) AS xid_age,age(backend_xmin) AS xmin_age,
       wait_event_type,wait_event
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC NULLS LAST LIMIT 30;
SELECT gid,prepared,owner,database,age(transaction) AS xid_age
FROM pg_prepared_xacts ORDER BY age(transaction) DESC;
SELECT slot_name,slot_type,database,active,active_pid,
       age(xmin) AS xmin_age,age(catalog_xmin) AS catalog_xmin_age,
       wal_status
FROM pg_replication_slots ORDER BY age(catalog_xmin) DESC NULLS LAST;

Inspect current vacuum work in this database. A nonempty blocking_pids list points to a lock investigation. No row can simply mean there is no vacuum at this instant; a short run may finish between samples.

The heap scan is only one phase. Scanned blocks are not a percentage of total elapsed work, and index passes can repeat. Compare phase, waits and repeated observations; do not cancel an anti-wraparound worker merely because it is expensive. Capacity or I/O pressure needs its own evidence.

SELECT p.pid,p.datname,p.relid,p.phase,
       p.heap_blks_total,p.heap_blks_scanned,p.heap_blks_vacuumed,
       p.index_vacuum_count,a.backend_type,a.wait_event_type,a.wait_event,
       pg_blocking_pids(p.pid) AS blocking_pids
FROM pg_stat_progress_vacuum p
LEFT JOIN pg_stat_activity a USING(pid)
WHERE p.datid=(SELECT oid FROM pg_database WHERE datname=current_database())
ORDER BY p.pid;

Apply the public sample values to select one ordinary application table or materialized view, then record its current identity and freeze ages. The defaults are placeholders for an already existing table; the guide creates no example table. Keep this read-only result beside the maintenance request.

Before the optional next action, agree acceptable I/O/WAL/replication impact, available disk, the table owner and a maintenance window. First resolve any horizon holder through its owning workflow. A single table vacuum will not advance the database horizon while an older catalog, partition or other database object remains.

\set target_schema 'public'
\set target_table 'orders'
SELECT c.oid,c.relkind,n.nspname,c.relname,c.reloptions,
       age(c.relfrozenxid) AS xid_age,mxid_age(c.relminmxid) AS mxid_age,
       age(t.relfrozenxid) AS toast_xid_age,
       mxid_age(t.relminmxid) AS toast_mxid_age
FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
LEFT JOIN pg_class t ON t.oid=c.reltoastrelid
WHERE n.nspname=:'target_schema' AND c.relname=:'target_table';

Only continue when the owner has approved this specific relation and the cluster is not in an emergency stop state. Open a second terminal for the maintenance profile, verify that it reaches the same database and primary, and keep the observation terminal available.

The profile name does not prove the destination or grant permissions. If the only available account is read-only, keep the diagnostic findings and hand the action to the DBA.

/usr/lib/postgresql/18/bin/psql -X -v ON_ERROR_STOP=1 'service=academy_vacuum_maintain'