🟢 Junior Level

VACUUM — a PostgreSQL process that cleans up “dead” rows left behind after UPDATE and DELETE.

Simple analogy: Imagine a bulletin board. When you tear off a note, space remains. VACUUM is when you clean up the torn pieces of paper and free up space for new notes.

Why it’s needed:

• In PostgreSQL, UPDATE does not overwrite a row, but creates a new one. The old one remains “dead”.
• DELETE does not physically remove the row, but marks it as deleted.
• Without VACUUM, the table would grow infinitely.

Example:

-- Automatic VACUUM (runs on its own)
-- Configured in postgresql.conf:
-- autovacuum = on

-- Manual run
VACUUM users;                    -- Regular
VACUUM FULL users;               -- Full (returns space to OS)
VACUUM VERBOSE users;            -- With details

Important:

• Regular VACUUM does NOT return space to the operating system
• It only marks space as free for new data
• VACUUM FULL returns space, but locks the table

---

🟡 Middle Level

How MVCC creates dead rows

-- users table: 1000 rows

UPDATE users SET status = 'ACTIVE' WHERE id = 1;
-- Old version of the row remains (dead tuple)
-- New version is created

DELETE FROM users WHERE id = 2;
-- Row marked as deleted (dead tuple)
-- Physically remains in the file

-- After 10,000 UPDATE/DELETE:
-- Live rows: 1000
-- Dead rows: 10,000  ← taking up space!

Regular VACUUM vs VACUUM FULL

Parameter	VACUUM	VACUUM FULL
Returns space to OS	❌ No	✅ Yes
Locking	No (read/write)	AccessExclusiveLock
Speed	Fast	Slow
Space for new data	✅ Frees	✅ File shrinks
Production	✅ Safe	❌ Maintenance only

When VACUUM FULL is acceptable:

1. During a maintenance window with no load
2. On a replica temporarily removed from the load balancer
3. For a one-time “revival” after a mass DELETE

For regular cleanup — tune autovacuum, use pg_repack.

VACUUM phases

1. Scanning Heap — searching for dead rows
   → Skips "clean" pages via Visibility Map

2. Vacuuming Indexes — removing references from indexes
   → Can be parallel (PG 13+)

3. Vacuuming Heap — cleaning table pages

4. Truncating — removing empty pages at the end of the file
   → The only phase that returns space to the OS

Autovacuum

Automatic VACUUM that runs on its own:

-- Default settings
SHOW autovacuum;                        -- on
SHOW autovacuum_vacuum_threshold;       -- 50 rows
SHOW autovacuum_vacuum_scale_factor;    -- 0.2 (20%)

-- Triggers when:
-- dead_tuples > threshold + scale_factor * total_rows

-- For a table with 1000 rows:
-- 50 + 0.2 * 1000 = 250 dead rows → trigger
-- For a table with 1M rows:
-- 50 + 0.2 * 1,000,000 = 200,050 dead rows → trigger
-- Note: for large tables, scale_factor 20% means autovacuum runs
-- VERY rarely (need to wait for 200K dead tuples)!

Tuning for specific tables

-- For a large table: more frequent and aggressive
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.05,  -- 5% instead of 20%
    autovacuum_vacuum_cost_delay = 1        -- Less pause → faster
);

-- For a small table: less frequent
ALTER TABLE settings SET (
    autovacuum_enabled = false  -- Disable (if rarely changed)
);

Common problems

1. VACUUM can’t keep up

   -- Check: how many dead rows
   SELECT relname, n_dead_tup, last_autovacuum
   FROM pg_stat_user_tables
   WHERE n_dead_tup > 100000;
   
   -- Solution: tune more aggressively
   ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);
   

2. Long transactions interfere

   -- VACUUM cannot remove rows that an open transaction can see
   
   -- Find the culprit
   SELECT pid, now() - xact_start as duration, query
   FROM pg_stat_activity
   WHERE state = 'active'
   ORDER BY xact_start;
   

3. VACUUM FULL locked the table

   -- ❌ In production!
   VACUUM FULL orders;  -- All INSERT/UPDATE/DELETE wait!
   
   -- ✅ Alternative without locking
   -- pg_repack
   pg_repack -t orders -d mydb
   

---

🔴 Senior Level

Visibility Map (VM) optimization

VM — a bitmap of table pages
Each page = 2 bits:
  - all-visible: ALL rows visible to all transactions
  - all-frozen: ALL rows frozen (for wraparound)

VACUUM uses VM:
  - Skips pages with all-visible = 1
  → 10-100x faster for "clean" tables
  → Depends on the fraction of "clean" pages: if 95% all-visible → reads 5%.
    If the table is constantly updated — there's almost no gain.

Micro-vacuum (HOT)

This is NOT a separate process. PostgreSQL removes old versions from HOT chains during normal operations, if all versions on a page have become invisible. “Instant” cleanup without autovacuum.

-- Thanks to HOT (Heap Only Tuple) updates
-- PostgreSQL can do "mini-cleanup" during SELECT/INSERT

-- If a HOT chain of versions exists on one page:
-- - Old versions are removed immediately
-- - Without waiting for the main VACUUM

-- This reduces the load on autovacuum
-- Especially for tables with frequent UPDATEs

Cost-based VACUUM

To prevent VACUUM from overwhelming the disk, it counts the “cost” of operations:

Operation               Cost
----------------------------------
Read from cache         1.0
Read from disk          10.0
Write                   20.0

autovacuum_vacuum_cost_limit = 200  -- Limit before pausing
autovacuum_vacuum_cost_delay = 2ms  -- Pause after the limit

For large tables (TB):

-- Increase the limit → VACUUM works faster
ALTER TABLE huge_table SET (
    autovacuum_vacuum_cost_limit = 1000,  -- Instead of 200
    autovacuum_vacuum_cost_delay = 1      -- Less pause
);

Parallel VACUUM (PG 13+)

-- Parallel index cleanup
VACUUM (PARALLEL 4) orders;

-- autovacuum can also use parallelism
-- For tables with many indexes → 3-5x speedup

On PG 12 and below, VACUUM runs in a single thread. To speed up: tune cost-based VACUUM (increase cost_limit, decrease delay).

Anti-wraparound VACUUM

-- The most important task of VACUUM — preventing XID Wraparound

-- age(datfrozenxid) > autovacuum_freeze_max_age (200M)
-- → Aggressive VACUUM:
--    - Cannot be cancelled (CANCEL doesn't work)
--    - Will run until everything is "frozen"
--    - Blocks new transaction creation at > 1.5B

-- Monitoring:
SELECT
    datname,
    age(datfrozenxid),
    ROUND(100.0 * age(datfrozenxid) / 2000000000, 2) as wraparound_pct
FROM pg_database;

-- > 1.5B = 75% → CRITICAL!

pg_stat_progress_vacuum (PG 12+)

-- Real-time monitoring
SELECT
    pid,
    relid::regclass as table_name,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed,
    ROUND(100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 2) as progress_pct
FROM pg_stat_progress_vacuum;

-- Phase:
-- 'scanning heap'
-- 'vacuuming indexes'
-- 'vacuuming heap'
-- 'truncating heap'

On PG 11 and below, pg_stat_progress_vacuum is not available. Monitor via n_dead_tup in pg_stat_user_tables.

Index-Only VACUUM (PG 14+)

-- Skip index cleanup if they don't need it
VACUUM (INDEX_CLEANUP false) orders;

-- Emergency cleanup of Heap only
-- → Faster, but indexes are not updated
-- → Use ONLY if indexes are fine

On PG 13 and below, this parameter is not available — VACUUM always cleans both heap and indexes.

Fighting Bloat: VACUUM FULL vs pg_repack

VACUUM FULL:

-- ❌ Locks the table (AccessExclusiveLock)
-- ❌ Recreates the table and ALL indexes
-- ✅ Returns space to OS
-- ✅ Built-in, no extensions required

VACUUM FULL orders;

pg_repack:

-- ✅ No locking (brief lock at the end)
-- ✅ Returns space to OS
-- ✅ Requires extension installation

-- Installation:
CREATE EXTENSION pg_repack;

-- Run from CLI:
pg_repack -t orders -d mydb

-- How it works:
-- 1. Creates a temporary table
-- 2. Copies data
-- 3. Catches changes via triggers
-- 4. Instant swap + DROP of the old table

Edge Cases

1. hot_standby_feedback and Bloat

   -- On replica:
   hot_standby_feedback = on
   
   -- Replica asks master not to delete rows
   -- → Master accumulates dead tuples
   -- → Bloat on master!
   
   -- Solution:
   -- - Monitor Bloat on master
   -- - Regular VACUUM on replica
   

2. Partitioned Tables

   -- VACUUM for a partitioned table
   -- → Runs for EACH partition separately
   
   -- Tuning at the partition level:
   ALTER TABLE orders_y2024 SET (
       autovacuum_vacuum_scale_factor = 0.01
   );
   

3. Temporary Tables

   -- autovacuum does NOT see temporary tables!
   -- Manual VACUUM needed
   
   VACUUM ANALYZE temp_import_data;
   

Production Experience

Real scenario #1: Autovacuum can’t keep up

• E-commerce: 50M INSERT/UPDATE per day in orders
• Autovacuum: 20% scale factor → runs rarely
• Bloat: table 100 GB (should be 10 GB)
• Solution:

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

• Result: Bloat < 20%, stable performance

Real scenario #2: Wraparound emergency

• Database: autovacuum disabled for 2 years “for performance”
• age(datfrozenxid) = 1.8B (90%)
• Logs: “WARNING: database must be vacuumed within 200M transactions”
• Solution:
  • Urgent VACUUM FREEZE on the entire database
  • 18 hours of work
  • Re-enable autovacuum
• Lesson: do NOT disable autovacuum GLOBALLY. For individual tables during bulk load — temporarily OK, but re-enable immediately.

Monitoring

-- 1. Dead tuples
SELECT
    relname,
    n_dead_tup,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 2. XID age
SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age DESC;

-- 3. Progress
SELECT * FROM pg_stat_progress_vacuum;

-- 4. Table-level autovacuum settings
SELECT
    c.relname,
    c.reloptions
FROM pg_class c
WHERE c.reloptions::text LIKE '%autovacuum%';

-- 5. Bloat estimate
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_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 > 1000
ORDER BY dead_pct DESC;

Best Practices

1. Do NOT disable autovacuum GLOBALLY — tune it. For individual rarely-updated tables (reference data), you can disable locally, but monitor XID age.
2. Monitor n_dead_tup — > 10% = problem
3. age(datfrozenxid) < 1.5B — prevent wraparound
4. pg_repack instead of VACUUM FULL in production
5. Increase cost_limit for large tables
6. Decrease scale_factor for frequently updated tables
7. Parallel VACUUM (PG 13+) for tables with many indexes
8. hot_standby_feedback = on → monitor Bloat on master

Summary for Senior

• VACUUM removes dead tuples, does NOT return space to OS
• VACUUM FULL returns space, but locks the table
• Visibility Map optimizes: skips “clean” pages
• HOT does micro-vacuum during normal operations
• Wraparound — a real threat → monitor age(datfrozenxid)
• pg_repack for production without locking
• autovacuum — tune individually for hot tables
• Long transactions — VACUUM’s biggest enemy

---

🎯 Interview Cheat Sheet

Must know:

• VACUUM: removes dead tuples (marks space as free), does NOT shrink files
• VACUUM FULL: recreates the table → returns space to OS, but AccessExclusiveLock
• Autovacuum: triggers when dead_tuples > threshold + scale_factor × total_rows
• Visibility Map: skips “clean” pages → VACUUM 10-100x faster
• HOT (Heap Only Tuple): micro-vacuum — removing old versions on the same page
• Cost-based VACUUM: operation costs → pause to avoid overwhelming the disk
• Anti-wraparound VACUUM: age > 200M → aggressive, CANCEL doesn’t work
• pg_stat_progress_vacuum (PG 12+): real-time monitoring
• pg_repack: VACUUM FULL without locking (triggers + swap)
• Parallel VACUUM (PG 13+): parallel index cleanup
• Index-Only VACUUM (PG 14+): VACUUM (INDEX_CLEANUP false) — Heap only

Frequent follow-up questions:

• “Why can’t autovacuum keep up?” → scale_factor 20% is too much for hot tables
• “How to fight Bloat without locking?” → pg_repack
• “What happens if age(datfrozenxid) = 1.9B?” → Critical! Urgent VACUUM FREEZE
• “Why do long transactions interfere with VACUUM?” → VACUUM can’t remove rows the transaction can see

Red flags (do NOT say):

• ❌ “VACUUM returns space to the OS” (no, only marks as free)
• ❌ “autovacuum can be disabled” (Wraparound → READ ONLY!)
• ❌ “VACUUM FULL is safe in production” (locks ALL operations!)
• ❌ “hot_standby_feedback has no consequences” (Bloat on master!)

Related topics:

• [[How does MVCC work in PostgreSQL]] → dead tuples, Hint Bits, XID Wraparound
• [[Why is ANALYZE needed]] → Autovacuum triggers ANALYZE
• [[How to optimize slow queries]] → Bloat affects performance