Scrolls
6 min read
On this page

VACUUM & Autovacuum

Why Postgres Needs Cleaning

PostgreSQL uses Multi-Version Concurrency Control (MVCC). When you update a row, Postgres does not overwrite the old version. It creates a new version and marks the old one as dead. When you delete a row, Postgres marks it as dead but does not reclaim the space. This is how concurrent transactions see consistent snapshots without blocking each other.

The consequence: dead rows accumulate. They waste disk space, slow down sequential scans (the scanner reads dead rows too), and if left unchecked, can trigger a catastrophic emergency called transaction ID wraparound. VACUUM is the process that cleans up dead rows and reclaims resources.

What VACUUM Does

VACUUM performs several critical tasks:

  1. Reclaims space from dead tuples so new rows can reuse it
  2. Updates the visibility map so index-only scans work efficiently
  3. Updates the free space map so Postgres knows where to insert new rows
  4. Freezes old transaction IDs to prevent wraparound
-- Manual VACUUM on a specific table
VACUUM orders;

-- VACUUM with verbose output
VACUUM VERBOSE orders;

INFO:  vacuuming "public.orders"
INFO:  table "orders": removed 45231 dead row versions in 1892 pages
INFO:  table "orders": found 45231 removable, 892341 nonremovable row versions
       in 23456 out of 34567 pages

Regular VACUUM does not return space to the operating system. It marks the space as reusable within the table. The table file size on disk does not shrink.

VACUUM FULL: The Nuclear Option

VACUUM FULL rewrites the entire table, reclaiming all dead space and shrinking the file on disk. But it takes an exclusive lock on the table, blocking all reads and writes for the duration.

-- Locks the table for the entire operation
VACUUM FULL orders;

For a 10 GB table with 30% bloat, VACUUM FULL might take 10-20 minutes during which the table is completely unavailable. Use it only when table bloat is severe and you can afford the downtime.

-- Check table bloat before deciding on VACUUM FULL
SELECT relname,
       pg_size_pretty(pg_relation_size(oid)) AS table_size,
       pg_size_pretty(pg_relation_size(oid) - (reltuples * (SELECT avg(avgsz)
           FROM (SELECT avg(pg_column_size(t)) AS avgsz FROM orders t LIMIT 1000) s
       )::bigint)) AS estimated_bloat
FROM pg_class
WHERE relname = 'orders';

Alternatives to VACUUM FULL that avoid long locks: pg_repack and pgcompacttable. Both reorganize tables without exclusive locks.

ANALYZE: Updating Statistics

ANALYZE collects statistics about table contents: value distributions, most common values, histograms. The query planner uses these statistics to choose execution plans.

-- Update statistics for a specific table
ANALYZE orders;

-- Update statistics for the entire database
ANALYZE;

-- Check when statistics were last updated
SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autoanalyze NULLS FIRST;

Stale statistics lead to bad query plans. If the planner thinks a table has 1000 rows when it actually has 10 million, it might choose a nested loop join instead of a hash join, turning a 50 ms query into a 50-second query.

VACUUM normally runs ANALYZE as well when autovacuum triggers it.

Autovacuum: The Background Worker

Autovacuum is the background process that runs VACUUM and ANALYZE automatically. It monitors tables for dead tuple accumulation and triggers cleanup when thresholds are crossed.

# postgresql.conf - autovacuum settings
autovacuum = on                           # Never turn this off
autovacuum_max_workers = 3                # Number of concurrent workers
autovacuum_naptime = 1min                 # How often to check for work
autovacuum_vacuum_threshold = 50          # Minimum dead tuples before vacuum
autovacuum_vacuum_scale_factor = 0.2      # Fraction of table that must be dead
autovacuum_analyze_threshold = 50         # Minimum changed tuples before analyze
autovacuum_analyze_scale_factor = 0.1     # Fraction changed before analyze

How the threshold works

Autovacuum triggers on a table when:

dead_tuples > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * table_rows)

With defaults (threshold=50, scale_factor=0.2): a table with 1 million rows triggers vacuum when dead tuples exceed 50 + 0.2 * 1,000,000 = 200,050. That means 20% of the table must be dead before autovacuum runs.

For large tables, this is too lenient. A 100-million-row table would need 20 million dead rows before autovacuum kicks in. That is an enormous amount of bloat.

Tuning Autovacuum

For large, high-churn tables

Lower the scale factor and increase workers:

-- Per-table autovacuum settings for a busy table
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,     -- 1% instead of 20%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_analyze_threshold = 500
);

With these settings, a 10-million-row table triggers vacuum at 1000 + 0.01 * 10,000,000 = 101,000 dead tuples (about 1% bloat) instead of 2 million (20% bloat).

Autovacuum cost settings

Autovacuum throttles itself to avoid consuming too many I/O resources. The defaults are conservative:

# postgresql.conf
autovacuum_vacuum_cost_delay = 2ms        # Pause between I/O operations
autovacuum_vacuum_cost_limit = 200        # Cost budget before pausing

On modern SSDs, these defaults are too conservative. Autovacuum runs too slowly and cannot keep up with write-heavy workloads:

# Aggressive settings for SSD-backed servers
autovacuum_vacuum_cost_delay = 0          # No delay (or 2ms if concerned about I/O)
autovacuum_vacuum_cost_limit = 1000       # Higher budget
autovacuum_max_workers = 5                # More concurrent workers

Monitoring autovacuum

-- Check autovacuum activity right now
SELECT relname, last_autovacuum, last_autoanalyze,
       autovacuum_count, autoanalyze_count,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Watch autovacuum workers in real time
SELECT pid, datname, relid::regclass AS table_name,
       phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

Transaction ID Wraparound: The Emergency

Every transaction in Postgres gets a 32-bit transaction ID (XID). That gives roughly 4 billion unique IDs. When IDs wrap around, Postgres can no longer determine which transactions are in the past and which are in the future. Data becomes invisible or corrupted.

VACUUM prevents wraparound by "freezing" old transaction IDs: marking them as definitively in the past so they no longer consume XID space.

-- Check how close you are to wraparound
SELECT datname,
       age(datfrozenxid) AS xid_age,
       2147483647 - age(datfrozenxid) AS xids_remaining,
       round(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_consumed
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

 datname | xid_age   | xids_remaining | pct_consumed
---------+-----------+----------------+--------------
 myapp   | 150000000 |     1997483647 |         6.99

Alert when xid_age exceeds 500 million. Panic when it exceeds 1 billion. At approximately 2 billion, Postgres enters emergency shutdown mode, refusing to process any new transactions until a manual VACUUM is performed.

-- Check per-table XID age (tables closest to forced vacuum)
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

When autovacuum_freeze_max_age (default 200 million) is reached, Postgres launches an "anti-wraparound" vacuum that cannot be cancelled. This vacuum may cause noticeable I/O impact because it is urgent.

When Autovacuum Cannot Keep Up

Signs that autovacuum is falling behind:

  • Dead tuple counts growing over time
  • Table sizes growing despite stable row counts (bloat)
  • XID age increasing steadily
  • Autovacuum workers always busy

Solutions, in order:

# 1. Increase autovacuum aggressiveness
autovacuum_vacuum_cost_delay = 0
autovacuum_vacuum_cost_limit = 1000
autovacuum_max_workers = 5

# 2. Lower per-table scale factors for large tables
ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.01);

# 3. Schedule manual VACUUM during low-traffic periods

# Cron job for off-peak manual vacuum on the biggest table
0 3 * * * psql -d myapp -c "VACUUM (VERBOSE, PARALLEL 4) orders;" >> /var/log/vacuum.log 2>&1

-- Monitor vacuum progress
SELECT relid::regclass AS table_name,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed,
       index_vacuum_count,
       round(100.0 * heap_blks_vacuumed / greatest(heap_blks_total, 1), 1) AS pct_complete
FROM pg_stat_progress_vacuum;

Common Pitfalls

  • Disabling autovacuum. Never do this. Some people disable it because it "uses too many resources." The resources it uses preventing bloat and wraparound are far less than the emergency when it catches up.
  • Not tuning autovacuum for large tables. Default scale_factor of 0.2 means a 100-million-row table needs 20 million dead rows before cleanup. Lower the scale factor for large tables.
  • Running VACUUM FULL during business hours. VACUUM FULL takes an exclusive lock. On a busy table, it blocks all reads and writes. Schedule it during maintenance windows or use pg_repack instead.
  • Ignoring transaction ID wraparound warnings. When xid_age exceeds 1 billion, you are in danger zone. Postgres will eventually refuse to accept writes. This is recoverable but painful.
  • Killing autovacuum to "fix" performance. If autovacuum is slow, the fix is to make it faster (increase cost_limit, decrease cost_delay), not to kill it. Killing it makes the underlying problem worse.
  • Not monitoring vacuum progress. pg_stat_progress_vacuum tells you exactly how far along a vacuum is. Without this, you have no idea if a vacuum will finish in 5 minutes or 5 hours.
  • Forgetting that idle-in-transaction connections block VACUUM. A long-running transaction prevents VACUUM from cleaning up any rows created after the transaction started. Find and kill idle-in-transaction sessions.

Key Takeaways

  • VACUUM reclaims dead tuple space, updates the visibility map, and freezes transaction IDs. It is essential for Postgres health.
  • Autovacuum runs VACUUM and ANALYZE automatically. Never disable it. Tune it for your workload instead.
  • For large tables, lower autovacuum_vacuum_scale_factor to 0.01-0.05. The default of 0.2 allows too much bloat to accumulate before cleanup.
  • On SSD-backed servers, reduce autovacuum_vacuum_cost_delay and increase autovacuum_vacuum_cost_limit so autovacuum can keep up with write-heavy workloads.
  • Transaction ID wraparound is a real emergency that happens when VACUUM does not run. Monitor xid_age and alert well before it becomes critical.
  • VACUUM FULL reclaims space to the OS but locks the table. Use pg_repack for online table reorganization when possible.
  • Idle-in-transaction connections are the enemy of VACUUM. Set idle_in_transaction_session_timeout to kill them automatically.