Diagnosing PostgreSQL Autovacuum Lag: A Step‑by‑Step Guide to Table Bloat and Performance Issues

Recognizing the Problem

When a PostgreSQL database starts to grow faster than expected, queries that once ran in milliseconds begin to take seconds or minutes, and the logs are flooded with autovacuum: VACUUM messages that never complete, you’re likely dealing with autovacuum lag. The core symptom is a steady increase in pg_total_relation_size() for one or more tables, combined with a high number of n_dead_tup entries in pg_stat_user_tables.

Typical signs:

• Table size grows by 10‑30% per day despite no bulk inserts.
• Query latency spikes, especially on UPDATE/DELETE heavy tables.
• Log entries show autovacuum: VACUUM but pg_stat_progress_vacuum never shows progress.

Root Cause Table

Possible Cause	Typical Indicator
Autovacuum disabled	SHOW autovacuum; → off
Too few vacuum workers	SHOW autovacuum_max_workers; → low (e.g., 1)
High vacuum cost delay	SHOW autovacuum_vacuum_cost_delay; → > 200ms
Long‑running transactions blocking vacuum	SELECT pid, age(clock_start, now()) FROM pg_stat_activity WHERE state = 'idle in transaction';
Update/delete rate exceeds vacuum capacity	High n_dead_tup in pg_stat_user_tables

Ordered Diagnostic Checks

1. Verify autovacuum status

   Run on any database connection with postgres role:

   SHOW autovacuum;
   SHOW autovacuum_max_workers;
   SHOW autovacuum_vacuum_cost_delay;
   SHOW autovacuum_vacuum_cost_limit;

   Check that autovacuum is on and that autovacuum_max_workers is at least 3 for medium‑traffic clusters.

2. Identify bloated tables

   Use the following query to list the top 10 tables with the most dead tuples and their current size:

   SELECT schemaname,
          relname,
          pg_total_relation_size(schemaname || '.' || relname) AS size_bytes,
          n_dead_tup
     FROM pg_stat_user_tables
    ORDER BY n_dead_tup DESC
    LIMIT 10;

   High n_dead_tup (> 10,000) and a large size_bytes relative to the table’s row count indicates bloat.

3. Check vacuum progress

   If a vacuum is running, pg_stat_progress_vacuum will report its phase and progress:

   SELECT * FROM pg_stat_progress_vacuum;
   

   Look for a row with phase = 'vacuuming' and confirm that heap_blks_total changes over time.

4. Find blocking long‑running transactions

   Long transactions can hold tuple locks and prevent vacuum from cleaning. Check for idle‑in‑transaction sessions:

   SELECT pid,
          age(clock_start, now()) AS txn_age,
          query
     FROM pg_stat_activity
    WHERE state = 'idle in transaction';

   If any session has txn_age > 10 minutes, consider terminating it or adjusting the application to commit sooner.

Applying Fixes

• Enable autovacuum

  If autovacuum is off, enable it and reload the configuration:

  ALTER SYSTEM SET autovacuum = on;
  SELECT pg_reload_conf();

  This change requires a reload, not a full restart.

• Increase worker count

  For high‑throughput workloads, raise autovacuum_max_workers (default 3):

  ALTER SYSTEM SET autovacuum_max_workers = 10;
  SELECT pg_reload_conf();

  Risk: too many workers can increase I/O contention.

• Tune vacuum cost settings

  If autovacuum_vacuum_cost_delay is set high (e.g., 200 ms), reduce it or increase the cost limit:

  ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 20;
  ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 200000;
  SELECT pg_reload_conf();

  These changes make vacuum more aggressive, but can increase CPU usage.

• Address long‑running transactions

  Terminate offending sessions:

  SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND age(clock_start, now()) > INTERVAL '10 minutes';

  Alternatively, modify application code to wrap transactions in BEGIN ... COMMIT blocks that close promptly.

• Force a full vacuum during maintenance windows

  When bloat is extreme, schedule a VACUUM (FULL, ANALYZE) or use pg_repack to rebuild the table without exclusive locking. Example:

  VACUUM (FULL, ANALYZE) my_schema.my_table;
  

  Risk: VACUUM FULL locks the table exclusively; schedule during low traffic.

When to Escalate

If after applying the fixes the following persist, consider escalating to a DBA or infrastructure team:

• Table size growth exceeds 30% of its current size within a week.
• Query latency SLA is breached for critical paths.
• Autovacuum never progresses (pg_stat_progress_vacuum shows no change for >10 min).
• Hardware I/O is saturated (check iostat or database metrics).

Potential actions at this stage include partitioning large tables, scaling storage, or adding read replicas to offload query traffic.

Practical Monitoring Tips

• Create a daily cron job that logs pg_stat_user_tables to a CSV for trend analysis.
• Use PostgreSQL’s built‑in pg_stat_statements or an external APM to track query latency spikes correlated with vacuum activity.
• Set alerts on n_dead_tup thresholds (e.g., > 100,000) and on pg_stat_progress_vacuum inactivity.
• Regularly run pg_repack on tables that exceed 1 GB in size to keep bloat manageable without full vacuums.

Limitations and Caveats

• Changes to autovacuum settings should be tested in a staging environment first; aggressive settings can cause high I/O and CPU spikes.
• VACUUM FULL locks the table; use pg_repack or schedule during maintenance windows.
• Long‑running transactions are often application‑side; terminating them can cause lost work if not handled properly.
• Hardware constraints (disk throughput, CPU) can still limit vacuum speed even after configuration tuning.

After applying any change, monitor pg_stat_user_tables.n_dead_tup and pg_total_relation_size() over several autovacuum cycles. A successful fix should show a gradual decrease in dead tuples and a stabilization of table size growth.