Fighting Table Bloat: Mastering VACUUM and autovacuum in PostgreSQL

PostgreSQL's MVCC (Multi-Version Concurrency Control) architecture gives you powerful concurrent access, but it comes with a hidden cost: dead tuples. Every UPDATE or DELETE leaves behind a tombstone that must be cleaned up before the storage can be reused. Without proper management, these dead tuples accumulate into table bloat—a silent performance killer that inflates disk usage, degrades index scans, and forces the query planner to overestimate row counts. The weapon against this entropy is VACUUM, and mastering both its manual invocation and its automated cousin, autovacuum, is essential for any PostgreSQL DBA who wants predictable performance and manageable storage.

Understanding Table Bloat: Causes and Consequences

Table bloat occurs when dead tuples consume space that could otherwise be reused by new rows. Every UPDATE in PostgreSQL creates a new version of the row (a new tuple) while marking the old version as dead. DELETE operations similarly mark tuples as dead. These dead tuples remain in the table's heap pages until VACUUM reclaims their space. The bloat ratio—the percentage of dead space relative to live data—can quickly reach 20-50% on busy tables, and in extreme cases, exceed 90%.

The consequences are measurable. Indexes become larger because they point to dead tuples. Sequential scans waste I/O reading pages that are mostly empty. The visibility map becomes stale, causing index-only scans to fall back to heap fetches. Most critically, autovacuum's own activity can cause I/O spikes that interfere with production workloads. Understanding the root causes—long-running transactions that prevent VACUUM from advancing the visibility horizon, massive batch updates, or simply misconfigured autovacuum thresholds—is the first step toward prevention.

How VACUUM Works: Dead Tuples and Storage Reclamation

When you run VACUUM on a table, PostgreSQL scans all heap pages to identify dead tuples. For each dead tuple that is no longer visible to any active transaction, VACUUM marks the space as reusable. It updates the free space map (FSM) so that future INSERTs and UPDATEs can reuse that space. It also updates the visibility map (VM), which tracks which pages contain only tuples visible to all transactions—a prerequisite for index-only scans.

Critically, VACUUM does not return space to the operating system. It only makes space available within the table file for reuse. The file size remains unchanged. This is why a heavily updated table can stay large even after VACUUM—the dead space is simply recycled. The following query shows you the bloat ratio for a table:

SELECT
  schemaname,
  tablename,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_pct DESC;

If dead_pct exceeds 20%, your autovacuum settings likely need tuning or the table has a long-running transaction blocking cleanup.

Configuring autovacuum: Thresholds, Scale Factors, and Tuning

Autovacuum is controlled by two main parameters per table: autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor. The threshold is a fixed number of dead tuples that must exist before autovacuum triggers. The scale factor multiplies the current live tuple count. The trigger condition is: dead tuples > threshold + (scale_factor * live tuples). Defaults are 50 and 0.2 respectively, meaning a table with 100,000 live tuples will trigger autovacuum when dead tuples exceed 50 + 0.2*100,000 = 20,050.

For large tables, the scale factor can be too aggressive. A table with 10 million rows would wait until 2 million dead tuples accumulate—by which point bloat is severe. Tune per-table using storage parameters:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 1000);

This triggers autovacuum after just 1% dead tuples (or 1000, whichever is larger). For very large tables, consider disabling the scale factor entirely and using a fixed threshold: ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0); combined with a threshold like 50000. Also tune autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay to control how aggressively autovacuum runs—higher cost limits allow faster cleanup but more I/O impact.

Rule of thumb: For tables larger than 10 GB, set autovacuum_vacuum_scale_factor to 0.01 or lower. For tables over 100 GB, consider 0.001 or a fixed threshold. Always monitor autovacuum worker logs for "still waiting" or "skipped" messages.

Monitoring Bloat: Queries and Tools for Detection

Bloat detection requires estimating how much space dead tuples consume versus live data. The pgstattuple extension provides exact per-table dead tuple counts and free space, but it locks the table. For production, use sampling-based estimates. The following query estimates table bloat using pg_class statistics:

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  round(100.0 * (pg_total_relation_size(schemaname||'.'||tablename) -
    pg_relation_size(schemaname||'.'||tablename, 'main')) /
    nullif(pg_total_relation_size(schemaname||'.'||tablename), 0), 2) AS bloat_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY bloat_pct DESC;

For more precise estimates, use the pg_bloat_check script from the PostgreSQL wiki or the check_postgres tool. The pg_stat_user_tables view gives you n_dead_tup and last_autovacuum timestamps—if n_dead_tup stays high and autovacuum hasn't run recently, investigate why.

Advanced Strategies: Manual VACUUM, VACUUM FULL, and WAL Considerations

When autovacuum falls behind, manual intervention is needed. A simple VACUUM (without FULL) reclaims space within the table file but does not shrink it. Use VACUUM VERBOSE to see exactly how many dead tuples were removed and how much free space was made available. For tables that have grown due to bloat and need physical size reduction, VACUUM FULL rewrites the entire table, compacting it and returning space to the OS. However, VACUUM FULL requires an exclusive lock and generates significant WAL—it should be scheduled during maintenance windows.

An alternative is pg_repack, which can rebuild tables online with minimal locking. For tables under heavy write load, consider partitioning: older partitions can be dropped or vacuumed aggressively without affecting current data. Also be aware that VACUUM itself generates WAL, and on busy systems you may need to increase wal_level or tune max_wal_size to prevent checkpoints from interfering with vacuum progress.

Common Pitfalls and Best Practices for Bloat Prevention

The most common pitfall is ignoring long-running transactions. A single idle-in-transaction session can prevent VACUUM from removing dead tuples for the entire duration. Always set idle_in_transaction_session_timeout to a reasonable value (e.g., 5 minutes). Another mistake is setting autovacuum too aggressively on small tables—frequent vacuums cause overhead without benefit. Use per-table tuning: small tables (<1 GB) can keep default scale factors; large tables need custom thresholds.

Best practices include: enabling track_counts (it's on by default), monitoring pg_stat_all_tables weekly, scheduling VACUUM ANALYZE after bulk operations, and using pg_stat_user_tables.n_dead_tup as an early warning. For tables that never see UPDATEs or DELETEs, consider setting autovacuum_enabled = off to avoid unnecessary scans. Finally, remember that autovacuum runs in the background—if your workload is write-heavy, ensure autovacuum_max_workers is at least 3 and autovacuum_naptime is 1 minute or less.

The short version

Table bloat is dead tuple accumulation from MVCC. Autovacuum cleans it up, but default settings fail on large tables. Tune per-table scale factors to 0.01 or lower for tables over 10 GB. Monitor n_dead_tup and use VACUUM VERBOSE to verify cleanup. Avoid VACUUM FULL in production; use pg_repack instead. Kill long-running transactions. Set idle_in_transaction_session_timeout. Always test changes in staging. Bloat is preventable—don't let it become your next fire drill.