Autovacuum Internals: How Dead Tuples Are Reclaimed

One thing first

Autovacuum is not optional cleanup, it is the process that keeps your database able to accept writes. It does two jobs that look unrelated but share one engine: reclaiming dead-tuple space, and freezing old rows before the 32-bit XID counter wraps. Neglect either and you get runaway bloat or a cluster that refuses writes.

Launcher and workers

Autovacuum has two process types:

• The launcher wakes every autovacuum_naptime seconds and decides which databases need attention, prioritizing those closest to wraparound.
• Each worker connects to one database and selects qualifying tables and TOAST relations there; autovacuum_max_workers limits concurrent workers across the cluster.

Threshold formula

A table becomes eligible for autovacuum when its estimated dead tuples exceed:

threshold = autovacuum_vacuum_threshold
          + autovacuum_vacuum_scale_factor * reltuples

With defaults (threshold = 50, scale_factor = 0.2), a 1,000,000-row table is vacuumed after ~200,050 dead tuples. The dead-tuple estimate comes from the cumulative statistics system, updated by backends as they modify rows. The same shape of formula with analyze_* parameters governs autoanalyze.

The classic mistake on large tables: the 20% scale factor means a billion-row table waits for 200 million dead tuples before vacuuming, far too late. The fix is per-table tuning:

ALTER TABLE big_table SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold    = 10000);

What a vacuum pass actually does

lazy_scan_heap() in vacuumlazy.c runs in phases:

• Scan heap. Walk pages (skipping all-visible pages via the visibility map), collecting the line pointers of dead tuples whose xmax is older than the vacuum's cutoff XID.
• Vacuum indexes. For each index, remove entries pointing at the dead tuples. This is why many indexes make vacuum slower.
• Vacuum heap. Turn the dead line pointers into reusable space and defragment the page.
• Update the free space map and visibility map.

And here's the catch: ordinary (lazy) vacuum does not return space to the operating system, it makes space reusable within the table. Only VACUUM FULL (which rewrites the table and takes an exclusive lock) shrinks the file.

Visibility map

Each table has a visibility map (visibilitymap.c): two bits per heap page. The all-visible bit lets index-only scans skip heap fetches and lets the next vacuum skip the page entirely. The all-frozen bit lets aggressive freeze passes skip pages. Keeping these bits set is a major reason vacuum gets cheaper over time on append-mostly tables.

Freezing and wraparound

XIDs are 32-bit and wrap around after ~4 billion. To stay safe, vacuum freezes old tuples, marking them as unconditionally visible, before their age reaches autovacuum_freeze_max_age. When any table crosses that age, an anti-wraparound autovacuum is forced even if the table is otherwise quiet, and it cannot be skipped. Ignoring the warnings leads PostgreSQL to eventually refuse new writes to protect data, a production-stopping event.

-- Tables closest to wraparound trouble
SELECT relname, age(relfrozenxid) AS xid_age
FROM   pg_class
WHERE  relkind = 'r'
ORDER  BY xid_age DESC LIMIT 10;

relname        | xid_age 
-----------------------+---------
 pg_attrdef            |     425
 pg_inherits           |     425
 pg_subscription       |     425
 pg_foreign_table      |     425
 pg_authid             |     425
 pg_constraint         |     425
 pg_type               |     425
 pg_statistic_ext_data |     425
 pg_user_mapping       |     425
 pg_operator           |     425
(10 rows)

Cost-based throttling

Vacuum accumulates cost as it reads and dirties pages, then sleeps when the configured limit is reached. If workers consistently fall behind, tune the limit or delay against measured I/O headroom; SSD storage alone is not evidence that a higher limit is safe.

Making autovacuum keep up

• Per-table thresholds. On a very large high-churn table, the defaults may permit more dead tuples than the workload can tolerate. Tune scale and base thresholds from measured churn and vacuum duration.
• Tune cost throttling with evidence. Raise the limit or lower the delay only when workers are behind and storage has measured headroom.
• Concurrency needs capacity. More workers can help when many databases or tables qualify, but only if CPU, memory, and I/O can support them.
• Old horizons cap cleanup. Inspect actual backend_xmin, prepared transactions, and replication slots. Ordinary relation cleanup is database-scoped; shared catalogs use a cluster-wide horizon.
• More indexes = slower vacuum because of the per-index cleanup phase; drop unused indexes.