Vacuum Basics

UPDATE and DELETE leave dead row versions until vacuum reclaims space and updates visibility maps. PostgreSQL 18.4 does not return file bytes to the OS immediately except in special cases.

Recipe

Quick-reference recipe card - copy-paste ready.

VACUUM (VERBOSE, ANALYZE) orders;
SELECT n_dead_tup, n_live_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

When to reach for this: Table grows after deletes, seq scans slow, or index-only scans stop appearing.

Working Example

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL,
  note text
);
 
INSERT INTO orders (status, note)
SELECT 'open', 'row-' || i FROM generate_series(1, 50000) i;
 
UPDATE orders SET status = 'closed' WHERE id % 2 = 0;
DELETE FROM orders WHERE id % 5 = 0;
 
SELECT relname, n_live_tup, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';
 
VACUUM (VERBOSE) orders;
 
SELECT relname, n_live_tup, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

What this demonstrates:

• Dead tuple accumulation
• VACUUM marks space reusable inside the file
• Monitoring via pg_stat_user_tables

Deep Dive

How It Works

• MVCC keeps old row versions visible to open transactions.
• Vacuum scans pages, removes dead tuples, freezes xmin horizons, updates FSM and visibility map.
• VACUUM FULL rewrites table exclusively (downtime risk); prefer pg_repack for online shrink.
• Autovacuum launches workers based on n_dead_tup thresholds.

Vacuum Variants

SQL Notes

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE status = 'open';
-- Index Only Scan needs fresh visibility map from vacuum

Gotchas

• Expecting DELETE to shrink disk immediately - Files stay large; space reused for inserts. Fix: pg_repack or partition drop for real shrink.
• Disabling autovacuum on hot tables - Transaction id wraparound risk. Fix: Per-table tuning, not off.
• Long transactions block vacuum - Dead tuples cannot be removed. Fix: Kill idle in transaction sessions; set idle_in_transaction_session_timeout.
• Assuming vacuum fixes index bloat fully - Indexes need separate cleanup paths. Fix: REINDEX CONCURRENTLY or repack.
• Running VACUUM FULL in production peak - Access exclusive lock. Fix: Maintenance window or online tools.

Alternatives

FAQs

Related

• Autovacuum Tuning - Worker thresholds
• Transaction ID Wraparound - Freeze urgency
• Table & Index Bloat - Measurement
• MVCC basics - Why dead tuples exist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.