PostgreSQL dead tuples and autovacuum

PostgreSQL updates and deletes can leave row versions that are no longer visible to any current transaction. Vacuum makes that space reusable, maintains the visibility map for index-only scans, and protects against transaction ID wraparound. Analyze, which autovacuum can invoke separately, refreshes planner statistics.

This read-only query ranks user tables by estimated dead rows and adds maintenance timestamps, modification counts, transaction ID age, and current total size.

SELECT
  s.schemaname,
  s.relname AS table_name,
  s.n_live_tup AS estimated_live_rows,
  s.n_dead_tup AS estimated_dead_rows,
  CASE
    WHEN s.n_live_tup + s.n_dead_tup > 0
    THEN round(100.0 * s.n_dead_tup /
      (s.n_live_tup + s.n_dead_tup), 1)
  END AS estimated_dead_percent,
  s.n_mod_since_analyze,
  s.last_vacuum,
  s.last_autovacuum,
  s.last_analyze,
  s.last_autoanalyze,
  age(c.relfrozenxid) AS xid_age,
  pg_total_relation_size(s.relid) AS total_bytes
FROM pg_stat_user_tables AS s
JOIN pg_class AS c ON c.oid = s.relid
ORDER BY estimated_dead_rows DESC
LIMIT 50;

Autovacuum evaluates table activity using configured thresholds and scale factors, with per-table storage settings able to override cluster settings. PostgreSQL versions and managed providers can expose different controls, and anti-wraparound work can run even when ordinary autovacuum is disabled.

There is no universal healthy dead-tuple percentage. A busy, narrow table that quickly reuses space has different needs from a large, infrequently updated table. Consider write rate, table size, maintenance duration, transaction age, and service-level impact together.

If a vacuum is already running, this read-only view reports its current phase and bounded progress counters. Values describe the active operation, not its future completion time.

SELECT
  pid,
  datname,
  relid::regclass AS relation,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count
FROM pg_stat_progress_vacuum
WHERE datname = current_database();

Long-running transactions can preserve old snapshots and prevent removal of row versions. Replication slots can also retain an xmin or catalog_xmin. Confirm ownership and downstream requirements before terminating a session or changing a slot; both actions can interrupt applications or replication.

• A high estimated dead-row count does not prove the same number of rows still consumes removable space.
• A recent autovacuum timestamp does not prove analyze also ran or that every index is compact.
• A null timestamp does not prove autovacuum is disabled; the table may be new, quiet, or the statistics may have reset.
• Standard vacuum normally reuses space internally rather than shrinking the table file.
• More aggressive settings can add I/O and maintenance load; tune from repeated evidence, not one snapshot.

The PostgreSQL health check reports bounded dead-row estimates, live-row estimates, relation size, and last maintenance timestamps. It flags substantial estimated dead-row pressure as an observation and explicitly avoids equating that estimate with bloat or an immediate manual-vacuum requirement.

Connected telemetry can preserve supported estimates and timestamps for comparison. DatabaseMeter does not run vacuum, change autovacuum settings, terminate sessions, or drop replication slots.