Maintain Table Statistics with VACUUM and ANALYZE

Why This Matters

Postgres uses table statistics to choose optimal query plans. Stale statistics lead to poor plans - the planner might choose a sequential scan when an index would be faster, or vice versa.

VACUUM and ANALYZE are two critical maintenance operations:

• VACUUM: Removes dead tuples (deleted/updated rows) and reclaims space
• ANALYZE: Updates table statistics used by the query planner
• Autovacuum: Postgres automatically runs both, but may need tuning

The Problem

Without regular ANALYZE, statistics become stale and query plans degrade:

-- Table grew from 1,000 to 1,000,000 rows
-- Statistics still show 1,000 rows
-- Planner chooses sequential scan thinking table is small

SELECT * FROM orders 
WHERE customer_id = 123;

-- EXPLAIN shows:
-- Seq Scan on orders (cost=0.00..25.00 rows=100 width=85)
--   Planner thinks: "Small table, seq scan is fine"
--   Reality: 1M rows, should use index!

The Solution

Run VACUUM ANALYZE regularly to keep statistics fresh:

-- Update statistics for a table
VACUUM ANALYZE orders;

-- Or analyze all tables
VACUUM ANALYZE;

-- After ANALYZE, planner knows table has 1M rows
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 123;

-- Now shows:
-- Index Scan using orders_customer_id_idx (cost=0.42..8.44 rows=100)
--   Planner correctly chooses index!

Autovacuum Configuration

Autovacuum runs automatically, but you may need to tune it for high-write tables:

-- Check autovacuum settings
SHOW autovacuum;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_scale_factor;

-- For high-write tables, run ANALYZE more frequently
ALTER TABLE orders 
SET (autovacuum_analyze_scale_factor = 0.01);
-- Analyzes after 1% of rows change (default is 10%)

When to Run Manually

• After bulk loads: COPY or large INSERT operations
• After bulk deletes: Large DELETE operations
• Before important queries: If you notice poor plans, run ANALYZE
• After schema changes: New indexes or columns

Monitoring Autovacuum

Check if autovacuum is keeping up:

-- Find tables that need vacuuming
SELECT 
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Common Mistakes

• Disabling autovacuum: Never disable it - it's essential for Postgres health. Tune it instead.
• Running VACUUM FULL frequently: VACUUM FULL rewrites tables and locks them - only use when absolutely necessary.
• Ignoring dead tuples: High n_dead_tup values indicate autovacuum isn't keeping up - tune thresholds.
• Not analyzing after bulk operations: Large data changes require immediate ANALYZE for accurate plans.