Separate running maintenance from reclaimable tuples
Investigating autovacuum requires separating three questions: whether a worker is running, whether maintenance keeps up with the table’s changes, and whether old versions are eligible for removal. A recent completion timestamp does not prove that growth is under control. Equally, a quiet table with little change may not need frequent work. Combine estimated dead-tuple counts and maintenance history from table statistics with update volume, relation size and transaction age. An estimated counter is a diagnostic signal, not an exact physical inventory of every reclaimable row version.
Read the current progress phase
Pg_stat_progress_vacuum exposes the current phase and progress of backends performing vacuum. Determine whether work is scanning the heap, processing indexes or reclaiming heap space. Dividing scanned heap blocks by total heap blocks describes that scanning phase; it is not a universal percentage of total completion time. Significant index work can remain after a scan approaches its end. An empty progress view means no applicable operation is visible at that observation instant, not that maintenance has never run or will not start shortly. Record the time and compare a sequence instead of promoting one snapshot into a permanent conclusion.
SELECT pid, relid::regclass, phase, heap_blks_total, heap_blks_scanned
FROM pg_stat_progress_vacuum;
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;Connect transaction age with table statistics
An old transaction or retained snapshot may still require versions that vacuum would otherwise remove. Relate activity and replication information to table growth before assuming that a lower trigger threshold fixes the issue. If estimated dead tuples rise with updates, investigate scheduling, processing capacity, waits and retention constraints in turn. This is an investigation procedure rather than a diagnosis of a particular deployment. Ending a long transaction is an application decision with rollback and correctness consequences, so understand what it is doing and why it remains open before using interruption as a supposed maintenance optimization.
Tune while preserving cleanup and freezing goals
Vacuum supports space reuse, visibility information and protection against transaction-ID age problems. Disabling autovacuum because it consumes resources can defer essential work and worsen bloat or wraparound risk. When changing cost controls, worker capacity or table settings, observe both foreground I/O effects and maintenance throughput. Ordinary vacuum can make internal space reusable while leaving the relation’s file size largely unchanged. A file that does not shrink is therefore not sufficient evidence of failed maintenance. Judge the outcome against the actual objective: bounded growth, safe transaction age and acceptable workload latency under a sustained rate of changes.