1---
2title: Maintain Table Statistics with VACUUM and ANALYZE
3impact: MEDIUM
4impactDescription: 2-10x better query plans with accurate statistics
5tags: vacuum, analyze, statistics, maintenance, autovacuum
6---
7 
8## Maintain Table Statistics with VACUUM and ANALYZE
9 
10Outdated statistics cause the query planner to make poor decisions. VACUUM reclaims space, ANALYZE updates statistics.
11 
12**Incorrect (stale statistics):**
13 
14```sql
15-- Table has 1M rows but stats say 1000
16-- Query planner chooses wrong strategy
17explain select * from orders where status = 'pending';
18-- Shows: Seq Scan (because stats show small table)
19-- Actually: Index Scan would be much faster
20```
21 
22**Correct (maintain fresh statistics):**
23 
24```sql
25-- Manually analyze after large data changes
26analyze orders;
27 
28-- Analyze specific columns used in WHERE clauses
29analyze orders (status, created_at);
30 
31-- Check when tables were last analyzed
32select
33  relname,
34  last_vacuum,
35  last_autovacuum,
36  last_analyze,
37  last_autoanalyze
38from pg_stat_user_tables
39order by last_analyze nulls first;
40```
41 
42Autovacuum tuning for busy tables:
43 
44```sql
45-- Increase frequency for high-churn tables
46alter table orders set (
47  autovacuum_vacuum_scale_factor = 0.05,     -- Vacuum at 5% dead tuples (default 20%)
48  autovacuum_analyze_scale_factor = 0.02     -- Analyze at 2% changes (default 10%)
49);
50 
51-- Check autovacuum status
52select * from pg_stat_progress_vacuum;
53```
54 
55Reference: [VACUUM](https://supabase.com/docs/guides/database/database-size#vacuum-operations)
56