Monitor Last VACUUM Runs with pg_stat_user_tables

calendar Jul 12, 2026 / · 8 min read · postgresql database administration sql queries vacuum autovacuum pg_stat_user_tables dead tuples postgres dba  ·
Share on: linkedin copy

Monitor Last VACUUM Runs with pg_stat_user_tables

A table's dead tuple count climbing while last_autovacuum stays weeks old isn't noise — it's the clearest sign autovacuum is falling behind, and pg_stat_user_tables is where that gap becomes visible in a single query, without tailing logs or guessing at a schedule.

Purpose and Overview

pg_stat_user_tables is a cumulative statistics view: one row per user table, holding scan counts, tuple counts, and maintenance timestamps that accumulate for the life of the server (or since the last pg_stat_reset()). The view carries columns for two distinct maintenance operations in the same row — VACUUM and ANALYZE — but they trigger independently, run on different thresholds, and carry different operational risk. Conflating them in a single audit muddies the signal.

This query pulls only the vacuum-side columns: last_vacuum, last_autovacuum, vacuum_count, and autovacuum_count, alongside the live and dead tuple counts that explain why a table needs vacuuming in the first place. The analyze-side counters (last_analyze, last_autoanalyze, analyze_count, autoanalyze_count) live in the same table but answer a different question — whether the planner's statistics are fresh — and are deliberately left out here to keep the result focused on dead-tuple cleanup and the transaction-ID horizon that VACUUM alone manages.

That focus matters because VACUUM does something ANALYZE does not: it reclaims space from dead tuples and advances the table's frozen-transaction-ID watermark. A table that never gets vacuumed accumulates bloat and, eventually, pushes the whole database closer to a forced transaction-ID wraparound shutdown. A stale last_analyze is a planner-quality problem; a stale last_vacuum is a availability problem waiting to happen.

Sample Code

 1SELECT
 2    schemaname,
 3    relname                                                            AS table_name,
 4    n_live_tup,
 5    n_dead_tup,
 6    round(100.0 * n_dead_tup
 7          / NULLIF(n_live_tup + n_dead_tup, 0), 2)                     AS pct_dead,
 8    last_vacuum,
 9    last_autovacuum,
10    GREATEST(last_vacuum, last_autovacuum)                             AS last_vacuum_any,
11    vacuum_count,
12    autovacuum_count
13FROM
14    pg_stat_user_tables
15ORDER BY
16    pct_dead DESC NULLS LAST;

Filtered variant, for tables that have never been vacuumed at all:

 1SELECT
 2    schemaname,
 3    relname AS table_name,
 4    n_live_tup,
 5    n_dead_tup
 6FROM
 7    pg_stat_user_tables
 8WHERE
 9    last_vacuum IS NULL
10    AND last_autovacuum IS NULL
11ORDER BY
12    n_dead_tup DESC;

Notes: pg_stat_user_tables is available on every currently supported PostgreSQL release. n_live_tup and n_dead_tup are estimates maintained by the statistics collector, not exact live counts — they are accurate enough for triage but not a substitute for VACUUM VERBOSE output when precision matters. The filtered query is the fastest way to find tables that autovacuum has never touched since the statistics were last reset.

Code Breakdown

The primary query reads directly from the view and adds two derived columns; the filtered variant swaps the ORDER BY for a WHERE clause targeting the highest-risk case.

The pct_dead Expression

1round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS pct_dead

100.0 forces floating-point division so small dead-tuple counts don't collapse to zero under integer arithmetic. NULLIF(n_live_tup + n_dead_tup, 0) guards against a division-by-zero error on an empty table. The result is the fraction of a table's rows that are dead — the number that best predicts whether autovacuum's dead-tuple threshold has been crossed.

GREATEST for a Single Last-Vacuum Timestamp

1GREATEST(last_vacuum, last_autovacuum) AS last_vacuum_any

A table can be vacuumed manually (last_vacuum) or by the background autovacuum worker (last_autovacuum), and only one of the two may be populated depending on how the table has been maintained. GREATEST() collapses both into a single "most recent vacuum, however it happened" timestamp, which is usually the number an operator actually wants when triaging staleness. PostgreSQL's GREATEST() treats a NULL argument as absent rather than propagating NULL, so a table with only autovacuum history still returns a usable timestamp.

The Never-Vacuumed Filter

1WHERE
2    last_vacuum IS NULL
3    AND last_autovacuum IS NULL

Both columns are NULL until a table has been vacuumed at least once by either path. Filtering on both being NULL finds tables that have accumulated activity — inserts, updates, deletes — without ever having been cleaned up. On a table with meaningful write volume, this is the highest-priority row in any audit: it means either autovacuum's thresholds have never been crossed (unlikely on an active table) or autovacuum has been disabled or is failing silently.

Key Vacuum Statistics Columns

last_vacuum and last_autovacuum

These are two separate timestamps for two separate trigger paths. last_vacuum records the most recent time a session issued VACUUM directly; last_autovacuum records the most recent run launched by the autovacuum daemon. A production table with last_vacuum populated and last_autovacuum empty usually means someone ran a manual vacuum once and autovacuum has never fired since — worth investigating whether the table's autovacuum settings are too permissive for its write volume.

vacuum_count and autovacuum_count

These are running totals since the last statistics reset, not just the most recent run. A vacuum_count of zero alongside heavy write activity confirms a table has never been manually vacuumed; a low autovacuum_count relative to a table's age and update rate suggests the autovacuum thresholds (autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor) may be set too high for that table's actual churn.

n_dead_tup and the Bloat Signal

Dead tuples are rows that UPDATE or DELETE has logically removed but that still occupy physical space until VACUUM reclaims them. A rising n_dead_tup with a stale last_vacuum_any is the direct evidence that cleanup is falling behind write volume. Left unaddressed, this shows up as index and heap bloat, slower sequential scans, and — because VACUUM is also what advances a table's frozen-transaction-ID watermark — increasing exposure to transaction ID wraparound on the busiest tables.

Practical Applications

Vacuum-history auditing sits at the intersection of routine health checking and pre-incident triage — the same query answers both a daily monitoring question and an active-firefighting one.

Autovacuum Starvation Detection

Run the primary query on a schedule and alert on any table whose pct_dead climbs past a threshold appropriate to its size while last_vacuum_any stays old. This is the earliest, cheapest signal that autovacuum is losing ground to write volume — well before query latency or disk usage make the problem visible elsewhere.

Pre-Bulk-Load Planning

Before a large COPY or backfill job, check the target table's current pct_dead and last_vacuum_any. A table already carrying a high dead-tuple ratio going into a bulk load compounds the problem; scheduling a manual VACUUM first, or tightening the table's autovacuum scale factor temporarily, avoids a much larger cleanup after the load completes.

Never-Vacuumed Table Triage

The filtered query surfaces any table that has never been vacuumed by either path. On an actively written table this is a red flag worth immediate investigation — check whether autovacuum is enabled cluster-wide (autovacuum = on), whether it is disabled specifically for that table (autovacuum_enabled = false in the table's storage parameters), or whether autovacuum workers are being starved by longer-running vacuums elsewhere.

Tuning Per-Table Autovacuum Settings

Tables with very different write patterns often need different thresholds. A large, slowly changing table can tolerate the default scale factor; a small, high-churn queue-style table needs a much lower autovacuum_vacuum_scale_factor set via ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ...) so it gets vacuumed proportionally to its own size rather than the cluster-wide default tuned for larger tables.

Correlating with Live Vacuum Progress

When pct_dead is high and last_vacuum_any is genuinely old, cross-reference pg_stat_progress_vacuum to check whether a vacuum is currently running on that table and how far along it is. A table that appears stale in the statistics view but has an active, progressing vacuum run is a different situation than one with no vacuum activity at all.

Version Compatibility

pg_stat_user_tables and its vacuum-tracking columns have been part of PostgreSQL's statistics collector infrastructure since autovacuum statistics matured into a standard, always-on subsystem, and the column set used in this query has been stable across the currently supported release range. Because the view is cumulative rather than point-in-time, its numbers reset whenever pg_stat_reset() is called for the database or on server restart in some configurations — a fresh reset produces NULL timestamps and zero counts everywhere, which looks identical to a genuinely unvacuumed table until enough time has passed for real activity to accumulate.

For visibility into a vacuum that is actively running right now, rather than one that already finished, pg_stat_progress_vacuum is the companion view — it was added in PostgreSQL 9.6 and reports the current phase, scanned/total heap pages, and dead-tuple counters for any in-progress VACUUM. pg_stat_user_tables and pg_stat_progress_vacuum are complementary: one shows history, the other shows what's happening this second.

Best Practices

  • Alert on pct_dead trend, not a single snapshot — a table sitting at 15% dead tuples steady-state may be normal for its workload; a table climbing from 2% to 15% over a week is the actionable signal.
  • Check both vacuum_count and autovacuum_count — a table with only manual vacuum history and zero autovacuum runs points to a configuration problem, not a healthy table.
  • Investigate every row in the never-vacuumed filter — on any table with real write activity, last_vacuum and last_autovacuum both NULL deserves a same-day look, not a backlog ticket.
  • Tune scale factor per table, not just globally — small high-churn tables need a lower autovacuum_vacuum_scale_factor than the cluster-wide default tuned for larger tables.
  • Pair with pg_stat_progress_vacuum before assuming staleness — a table that looks overdue in the history view may already have an active vacuum running against it.
  • Remember the reset caveat — a recent pg_stat_reset() or restart can make every table look freshly unvacuumed; check server uptime and reset history before treating a NULL timestamp as an incident.

References

Posts in this series