PostgreSQL Autovacuum Internals & Tuning Guide

PostgreSQL autovacuum is the background worker that keeps your database from quietly falling apart. Misconfigure it and you'll deal with table bloat, degraded query plans, and in the worst case, a transaction ID wraparound that halts all writes across the cluster. Leave it at defaults on a large-table workload, and it may not run frequently enough to keep up.

At default settings, a 1 TB PostgreSQL table can accumulate up to 200 GB of dead rows before autovacuum triggers (EnterpriseDB, 2024). That's not a corner case. It's the default behavior on most PostgreSQL deployments, and it's why large-table query times can climb from 10ms to 6+ seconds without any application changes.

This guide covers how autovacuum works internally (launcher, workers, cost throttling), exactly when it fires, and how to tune it for high-traffic production tables.

Key Takeaways

• Never disable autovacuum; PostgreSQL overrides the setting near XID wraparound anyway.
• At the default 20% scale factor, a 1 TB table allows 200 GB of dead rows before vacuum runs (EDB, 2024).
• Reducing scale factor from 0.20 to 0.02 on large tables cut production query times by up to 90% (Compass, 2017).
• PostgreSQL 14 raised autovacuum disk throughput from 8 MB/s to 400 MB/s at default settings.

What is PostgreSQL Autovacuum?

Autovacuum is a background process group that PostgreSQL starts at launch. It handles three critical maintenance tasks: removing dead tuples (VACUUM), updating table statistics for the query planner (ANALYZE), and preventing transaction ID wraparound (FREEZE). These tasks keep themselves running continuously so you don't have to schedule them manually.

The process group has two parts:

• Autovacuum launcher (one per cluster): wakes up every autovacuum_naptime (default: 1 min) and identifies tables that need attention.
• Autovacuum workers (up to autovacuum_max_workers, default: 3): assigned by the launcher to run VACUUM or ANALYZE on individual tables.

$ ps -eaf | grep autovacuum
postgres  2862     1  0 Jun17  00:00:11 /usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data
postgres  2868  2862  0 Jun17  00:00:10 postgres: autovacuum launcher
postgres  2871  2862  0 Jun17  00:00:02 postgres: autovacuum worker  mydb

Two parameters must be enabled for autovacuum to function. Both default to on:

autovacuum  = on     -- enables the launcher and workers
track_counts = on    -- tracks per-table DML counts; required for threshold checks

One thing worth knowing that surprises DBAs: setting autovacuum = off does not fully disable it. PostgreSQL will still start autovacuum workers in emergency situations, specifically when a table is approaching XID wraparound. The setting is a hint, not a hard switch. PostgreSQL will override it when the wraparound risk becomes critical.

According to the PostgreSQL documentation, the XID space holds roughly 4 billion transaction IDs. The system emits warnings in the logs at 40 million XIDs from the wraparound limit and shuts down all writes at 3 million XIDs from the limit (PostgreSQL Docs). Autovacuum, when running correctly, prevents you from getting anywhere near those thresholds.

Why Does Autovacuum Matter?

Dead tuples are the root cause of table bloat in PostgreSQL. Every UPDATE or DELETE leaves an invisible dead tuple behind. This is MVCC: old row versions must stay visible to any transaction that started before the change. PostgreSQL can't delete them until all those transactions complete.

So what happens as dead tuples pile up? Tables grow physically larger. Sequential scans read more 8 KB pages. Index scans lose efficiency. The query planner works from stale statistics and picks bad execution plans. And the numbers get real fast. One engineering team documented queries on a 3-billion-row table climbing from 10ms to over 6 seconds after autovacuum fell behind at default settings (Compass True North, 2017).

Autovacuum prevents this through three operations:

1. VACUUM marks dead tuple space as reusable for future inserts and updates.
2. ANALYZE updates table statistics so the planner makes accurate cost estimates.
3. FREEZE marks old tuples immune to XID wraparound, allowing the oldest XID counter to advance safely.

Note the distinction between VACUUM and VACUUM FULL. Regular VACUUM reclaims space for reuse within the same table. It does not shrink the file on disk. VACUUM FULL physically compacts the table and can return space to the OS, but it requires an exclusive lock and blocks all reads and writes during the operation. Running autovacuum frequently enough means you rarely need VACUUM FULL in the first place.

How Does Autovacuum Decide When to Run?

Autovacuum checks each table against a threshold formula on every launcher cycle. The formulas are:

VACUUM threshold  = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor  × n_live_tup)
ANALYZE threshold = autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor × n_live_tup)

A table becomes a VACUUM candidate when its dead tuple count exceeds the VACUUM threshold. It becomes an ANALYZE candidate when cumulative inserts, updates, and deletes since the last analyze exceed the ANALYZE threshold.

Example with Default Settings

For a table with 1,000 rows using PostgreSQL 16/17 defaults:

VACUUM fires when dead rows reach: 50 + (0.2 × 1,000) = 250 dead rows ANALYZE fires when row changes reach: 50 + (0.1 × 1,000) = 150 changes

These defaults work well for small tables. They don't scale to large ones. For a 1-billion-row table, autovacuum won't trigger until 200 million rows are dead. For a 1 TB table, that's up to 200 GB of dead row accumulation before cleanup starts.

Complete Parameter Reference (PostgreSQL 16/17)

Source: PostgreSQL 17 Documentation

Tuning Autovacuum for Large Tables

The global defaults work for small and medium tables. For large, high-traffic tables, they don't. PostgreSQL lets you override autovacuum settings at the table level, which is exactly what you need for mixed-size workloads.

In practice, we've seen this pattern repeatedly: a team scales a transactional table from tens of millions to hundreds of millions of rows, and query latency climbs silently. Autovacuum is still running. It just can't keep up at 20% scale factor. The fix is almost always per-table tuning, and it often takes less than a minute to apply.

-- Trigger VACUUM when dead rows exceed 1% of the table (not 20%)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor  = 0.01,
  autovacuum_vacuum_threshold     = 100,
  autovacuum_analyze_scale_factor = 0.01
);

For very large tables, use a fixed threshold and set the scale factor to zero:

-- Trigger VACUUM every 100,000 dead rows, regardless of table size
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold    = 100000
);

How do you know which tables need individual settings? The answer is in pg_stat_user_tables. This query shows tables accumulating dead tuples faster than autovacuum is clearing them:

SELECT
  schemaname,
  relname                                                          AS table_name,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2)    AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

Tables with dead_pct consistently above 20% are candidates for per-table tuning. According to pganalyze's VACUUM Advisor, dead tuple percentages above 50% often indicate the need for VACUUM FULL to reclaim disk space (pganalyze, 2023).

According to a production case study at Compass, reducing autovacuum_vacuum_scale_factor from 0.20 to 0.02 on tables with 3 billion rows cut the vacuum trigger point from 600 million changed rows to 60 million. The result: average query performance improved by ~50%, and the largest tables saw up to 90% faster query times (Compass True North, 2017). One parameter change, production-level impact.

How Many Workers Can Run at Once?

autovacuum_max_workers (default: 3) sets the maximum concurrent autovacuum workers across the entire PostgreSQL cluster, not per database. The launcher divides autovacuum_naptime by the number of databases. With 3 databases and a 1-minute naptime, each database gets checked roughly every 20 seconds.

Here's the catch that surprises most DBAs: increasing autovacuum_max_workers alone will not speed things up. In fact, it can slow each worker down.

All workers share a single autovacuum_vacuum_cost_limit budget. With 3 workers and a 200-token limit, each worker effectively gets ~67 tokens per cost cycle. Add 2 more workers and each gets ~40. More workers, less work per worker. To actually increase parallel vacuum throughput, you need to raise autovacuum_vacuum_cost_limit at the same time:

# postgresql.conf
autovacuum_max_workers       = 5
autovacuum_vacuum_cost_limit = 800    -- up from 200; budget shared across all workers
autovacuum_vacuum_cost_delay = 2ms    -- keep at default (PG 14+)

Understanding Autovacuum's IO Throttling

Autovacuum is intentionally throttled. It uses a token-based cost model so it doesn't overwhelm your storage during peak transaction hours. Each I/O operation consumes a certain number of tokens. When the combined cost hits the cycle budget, every worker sleeps for autovacuum_vacuum_cost_delay milliseconds before resuming.

With a 2ms delay, autovacuum runs up to 500 cost cycles per second. The resulting throughput ceiling at default settings:

• Shared buffer reads: 500 cycles/s × (200 / 1) × 8 KB = 800 MB/s
• Disk reads: 500 cycles/s × (200 / 2) × 8 KB = 400 MB/s
• Writes: 500 cycles/s × (200 / 20) × 8 KB = 40 MB/s

Before PostgreSQL 12, the default autovacuum_vacuum_cost_delay was 20ms. That capped disk throughput at roughly 8 MB/s. PostgreSQL 12 dropped it to 2ms. PostgreSQL 14 then reduced vacuum_cost_page_miss from 10 tokens to 2 tokens, pushing throughput even higher.

What does this mean practically? If you're still running PG 11 or earlier, upgrading to PG 14+ alone will dramatically improve autovacuum effectiveness on large tables, with no configuration changes.

To raise the throughput ceiling further, increase autovacuum_vacuum_cost_limit. The tradeoff is higher I/O pressure on your workload during peak hours. Start with 400-800 and monitor I/O utilization.

Logging Autovacuum Activity

Autovacuum runs silently unless you tell it otherwise. Set log_autovacuum_min_duration to surface slow runs in your PostgreSQL log:

# Log any autovacuum run taking longer than 250ms
log_autovacuum_min_duration = '250ms'

# Log every autovacuum run (useful during tuning, noisy in production)
# log_autovacuum_min_duration = 0

A typical log entry looks like this:

automatic vacuum of table "mydb.public.orders": index scans: 1
  pages: 0 removed, 15432 remain, 2 skipped due to pins
  tuples: 182000 removed, 4500000 remain, 0 are dead but not yet removable
  avg read rate: 38.2 MB/s, avg write rate: 2.1 MB/s
  elapsed: 4823.112 ms

If autovacuum is taking minutes on the same table repeatedly, that table needs per-table settings or a scheduled VACUUM during an off-peak window. The combination of log_autovacuum_min_duration and periodic snapshots of pg_stat_user_tables gives you a complete picture of which tables autovacuum is keeping up with and which it isn't.

Frequently Asked Questions

Why is autovacuum running but my table still has lots of dead tuples?

The most common cause is the 20% scale factor being too high for a large table. A 500M-row table needs 100M dead rows before vacuum triggers. Set autovacuum_vacuum_scale_factor = 0.01 on the table, or use a fixed threshold with autovacuum_vacuum_scale_factor = 0 and autovacuum_vacuum_threshold = 50000.

Can I disable autovacuum for a specific table?

Yes, using a storage parameter:

ALTER TABLE archive_log SET (autovacuum_enabled = false);

Only do this for truly static tables that never receive writes. Any table with regular DML needs autovacuum for dead tuple removal and statistics maintenance.

Why does adding more workers slow each worker down?

All workers share one autovacuum_vacuum_cost_limit token budget. Adding workers splits that budget further, so each worker processes fewer pages per cycle. Increase autovacuum_vacuum_cost_limit proportionally when raising autovacuum_max_workers.

What happens if autovacuum can't keep up with transaction wraparound?

PostgreSQL logs warnings at 40M XIDs from the wraparound limit and blocks all writes at 3M XIDs from the limit. Recovery requires downtime for a forced VACUUM run. Prevent it by keeping autovacuum_freeze_max_age at its default (200M transactions) and ensuring autovacuum is not throttled on tables with heavy UPDATE/DELETE workloads.

How do I verify autovacuum is actually keeping up?

Query pg_stat_user_tables for n_dead_tup as a fraction of n_live_tup, and check last_autovacuum timestamps on active tables. Enable log_autovacuum_min_duration = '250ms' to see which tables are taking the longest. Dead tuple percentages consistently above 20% on active tables mean autovacuum isn't keeping pace.

Tested versions: PostgreSQL 11, 12, 13, 14, 15, 16, 17.