Auto Vacuum in PostgreSQL Explained

What is Autovacuum?

Autovacuum is PostgreSQL's background maintenance process responsible for:

• Removing dead tuples
• Preventing table and index bloat
• Updating optimizer statistics
• Freezing old transaction IDs
• Preventing transaction ID wraparound

Without Autovacuum, PostgreSQL databases would gradually consume more storage, queries would become slower, and eventually the database could stop accepting writes due to transaction ID exhaustion.

How the autovacuum daemon actually runs

Autovacuum isn't one process — it's a small fleet. A launcher process wakes up periodically (governed by autovacuum_naptime, default 1 minute), checks every database, and spins up worker processes to handle whichever tables have crossed their dirty-tuple threshold. By default, Postgres allows up to autovacuum_max_workers (3) workers running concurrently across the whole instance.

Each worker does one of two jobs on a table:

Why it never shrinks tables on disk

When does a table actually qualify?

Postgres tracks dead tuple counts per table in pg_stat_user_tables. A table becomes eligible for autovacuum once its dead tuple count crosses this threshold:

vacuum threshold = autovacuum_vacuum_threshold +
                    (autovacuum_vacuum_scale_factor × n_live_tup)

-- defaults: threshold = 50, scale_factor = 0.2 (20% of the table)

The ANALYZE trigger works the same way, with its own threshold and scale factor (autovacuum_analyze_threshold = 50, autovacuum_analyze_scale_factor = 0.1).

The parameters worth knowing by name

9 of 9 parameters shown

VACUUM vs. autovacuum vs. VACUUM FULL

All three share the word "vacuum," and DBAs new to Postgres often assume they're interchangeable — they're not. They differ in who triggers them, what lock they take, and whether they actually shrink the file on disk.

Common VACUUM commands

1. Basic VACUUM

Removes dead tuples and makes space available for reuse.

blogs=# VACUUM;

2. Vacuum a specific table

blogs=# VACUUM employees;

3. VACUUM with ANALYZE

Removes dead tuples and updates planner statistics.

blogs=# VACUUM ANALYZE employees;

4. Analyze only

Updates optimizer statistics without removing dead tuples.

blogs=# ANALYZE employees;

5. VACUUM FULL

Rewrites the table and returns unused disk space to the operating system. Requires an exclusive lock on the table.

blogs=# VACUUM FULL employees;

6. VACUUM FREEZE

Freezes old tuples to prevent transaction ID wraparound.

blogs=# VACUUM FREEZE employees;

7. VACUUM VERBOSE

Displays detailed information about the vacuum process.

blogs=# VACUUM VERBOSE employees;
INFO:  vacuuming "public.employees"
INFO:  finished vacuuming "public.employees"

8. VACUUM FULL ANALYZE

Reclaims disk space and updates statistics in one pass.

blogs=# VACUUM (FULL, ANALYZE) employees;

Table-level autovacuum settings

Some tables need more aggressive vacuuming than the rest of the cluster. Rather than loosening these globally in postgresql.conf — which affects every table, including small, quiet ones — override them on the specific tables that need it:

blogs=# ALTER TABLE code.transactions SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_vacuum_cost_delay = 0
);

Should you ever disable Autovacuum?

For production OLTP tables, disabling Autovacuum is generally not recommended, as it can quickly lead to table bloat, outdated statistics, and transaction ID wraparound risks.

blogs=# ALTER TABLE staging.bulk_import SET (autovacuum_enabled = false);

-- Revert to instance-wide defaults for any parameter:
blogs=# ALTER TABLE staging.bulk_import RESET (autovacuum_enabled);

Catching it falling behind and watching it work

blogs=# SELECT schemaname, relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

blogs=# SELECT
  pid,
  datname,
  relid::regclass AS table_name,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  max_dead_tuples,
  num_dead_tuples
FROM pg_stat_progress_vacuum;

blogs=# SELECT
  pid,
  usename,
  query_start,
  state,
  wait_event_type,
  wait_event,
  query
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

blogs=# SELECT
  relname,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables;

Best practices

Conclusion

Autovacuum is one of PostgreSQL's most important self-maintenance features. It quietly removes dead tuples, refreshes planner statistics, prevents table and index bloat, and safeguards the database from transaction ID wraparound. Although the default configuration works well for many workloads, high-traffic systems often benefit from tailored settings at the server or table level.