PostgreSQL In-Depth
Module A-4·31 min read

How a stalled replication slot pinned OldestXmin for 48 hours, accumulating 800 million dead tuples — and the autovacuum mechanics that let it happen.

Written by Jatin Jain Saraf · Architect of SupraScan

Module 4 — Autovacuum: The Process Everyone Misconfigures

What this module covers: Autovacuum is not a background cleanup job you can ignore. It is the mechanism that prevents your tables from bloating to 10x their logical size, your indexes from degrading, your standbys from lagging, and — in the worst case — your entire database from shutting down due to transaction ID wraparound. This module covers how autovacuum actually works, what it does to your data, why its defaults are wrong for most production workloads, and how to tune it from first principles.

The Problem Autovacuum Solves

In Module 2, you learned that MVCC creates dead tuples. Every UPDATE marks the old tuple dead and writes a new one. Every DELETE marks the tuple dead. These dead tuples remain physically present in the heap — they occupy pages, bloat indexes, and slow down sequential scans — until something reclaims them.

That something is VACUUM.

Without VACUUM, a table with a high update rate grows without bound. Not because the number of live rows is growing, but because dead tuples accumulate. A table with 1 million live rows that gets updated 10 times per row will physically contain up to 10 million tuples on disk.

Autovacuum is the background process that runs VACUUM automatically so you don't have to schedule it manually. This sounds simple. The complexity is in the details:

  • When does autovacuum decide a table needs vacuuming?
  • How aggressively does it work while it's running?
  • What exactly does it do during a vacuum pass?
  • What does it not do that surprises most engineers?
  • How do you tune it for a specific workload?

What VACUUM Actually Does

Before tuning autovacuum, you need a precise model of what a VACUUM pass does. Most engineers think of it as "cleans up dead tuples." The reality is a multi-step process that touches heap files, indexes, the Free Space Map, and the Visibility Map.

Step 1: Scan the Heap for Dead Tuples

VACUUM reads every page of the heap file sequentially, examining tuple headers. For each tuple, it checks visibility: is this tuple dead to all current and future transactions?

A tuple is dead-to-all if:

  • Its xmax is set (it has been deleted or updated)
  • The transaction that set xmax has committed
  • That transaction's XID is older than the oldest active transaction snapshot (OldestXmin)

Any tuple older than OldestXmin that is deleted cannot be seen by any current or future transaction. It is safe to reclaim.

-- See what OldestXmin currently is
SELECT backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin
LIMIT 1;
 
-- A long-running transaction keeps OldestXmin old, preventing dead tuple reclamation
-- This is why idle-in-transaction sessions cause bloat

Step 2: Build the Dead Tuple TID List

VACUUM builds an in-memory list of tuple IDs (TIDs — page number + offset within page) for all dead tuples found in step 1. This list is bounded by maintenance_work_mem — if there are more dead tuples than fit in memory, VACUUM processes them in multiple passes.

# How much memory VACUUM can use for the dead tuple list
maintenance_work_mem = 256MB  # default is 64MB — increase for large tables

With the default 64MB, VACUUM can hold approximately 11 million dead tuple TIDs per pass (each TID is 6 bytes: 64MB / 6 bytes ≈ 11.2 million). A table with 10 million dead tuples fits in a single pass at that capacity — it's tables with more dead tuples than maintenance_work_mem can index that force multiple heap passes, each requiring a full re-scan. Increasing maintenance_work_mem to 512MB or 1GB for autovacuum workers reduces the number of heap passes on very large tables. (PostgreSQL 17+ replaced this flat TID array with a memory-efficient radix-tree TidStore, which holds far more dead tuple references per MB than the 6-bytes/TID math above — multi-pass vacuums are correspondingly rarer on PG17+.)

Step 3: Remove Dead TIDs from Indexes

For every dead tuple found, VACUUM must remove its entry from every index on the table. This is often the most expensive part of VACUUM on heavily-indexed tables.

VACUUM scans each index, finds entries pointing to dead TIDs, and removes them. Index pages that become completely empty after cleanup can be reclaimed.

This is why the number of indexes on a table directly affects VACUUM cost. A table with 10 indexes requires VACUUM to do 10 index scans per heap pass.

Step 4: Reclaim Dead Tuple Space in the Heap

With the index entries removed, VACUUM returns to the heap pages and marks the dead tuple space as free. It updates the Free Space Map (FSM) for each page to reflect the newly available space.

Critically: VACUUM does not move live tuples or compact pages. If a page has alternating live and dead tuples scattered throughout it, VACUUM marks the dead tuple slots as free but leaves the live tuples exactly where they are. The page now has holes — free space interspersed with live tuples.

New inserts can use this free space (via the FSM), but the page count of the table does not decrease. This is why VACUUM does not shrink a table's physical file. Only VACUUM FULL (which rewrites the entire table) shrinks it — but VACUUM FULL takes an exclusive lock and is rarely the right tool.

Step 5: Update the Visibility Map

The Visibility Map (VM) is a compact bitmap, one bit per heap page, that records whether all tuples on a page are visible to all transactions. If a page's VM bit is set, index-only scans can skip the heap entirely.

VACUUM sets VM bits for pages where all tuples are now visible to all transactions (all dead tuples have been reclaimed, all live tuples are committed and old enough). This is one of the most important outcomes of a VACUUM pass — without it, index-only scans cannot function correctly.

Step 6: Update pg_class Statistics

VACUUM updates pg_class.relpages and pg_class.reltuples with the current page count and live tuple estimate. The query planner uses these values. Stale statistics here cause wrong cardinality estimates and bad plans.

What VACUUM Does NOT Do

  • Does not shrink the physical file — reclaimed space is reused by future inserts, not returned to the OS
  • Does not remove all dead tuples — tuples newer than OldestXmin are not dead yet; they may be visible to open transactions
  • Does not reindex — indexes accumulate bloat over time; REINDEX or VACUUM (INDEX_CLEANUP) is separate
  • Does not update column statistics — that is ANALYZE's job; VACUUM ANALYZE runs both

Autovacuum Architecture

The Autovacuum Launcher

A single autovacuum launcher process runs continuously. Its job is to monitor tables and decide when to trigger a vacuum worker. It checks each database in round-robin fashion, looking at pg_stat_user_tables to find tables that exceed the vacuum or analyze threshold.

Autovacuum Workers

When the launcher decides a table needs vacuuming, it spawns an autovacuum worker process. Workers are capped at autovacuum_max_workers (default: 3).

This is the first misconfiguration most systems have: 3 workers is almost always too few for production. If you have 50 tables all needing vacuum simultaneously, only 3 can be processed concurrently. The others wait.

# Increase for systems with many tables or high write throughput
autovacuum_max_workers = 6  # or higher for very active systems

Each worker processes one table at a time. It acquires a lightweight lock (no exclusive lock — normal reads and writes continue) and performs the VACUUM steps described above.

When Autovacuum Triggers

Autovacuum triggers a vacuum of a table when the estimated number of dead tuples exceeds:

vacuum_threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

With defaults:

autovacuum_vacuum_threshold = 50        # minimum dead tuples before considering vacuum
autovacuum_vacuum_scale_factor = 0.2    # 20% of live tuples

For a table with 1,000,000 live rows:

vacuum_threshold = 50 + 0.2 * 1,000,000 = 200,050 dead tuples

Autovacuum waits until 200,050 dead tuples accumulate before vacuuming. On a table processing 1,000 updates/second, that is 200 seconds of dead tuple accumulation before cleanup begins.

For large tables, the default scale factor is catastrophically wrong.

A table with 100 million rows requires 20 million dead tuples before autovacuum triggers. At 10,000 updates/second, that is 33 minutes of bloat accumulation per vacuum cycle. The table can grow to many times its logical size before vacuum catches up.

The fix: reduce autovacuum_vacuum_scale_factor for large, write-heavy tables. We'll cover per-table tuning shortly.

When Autovacuum Triggers ANALYZE

Autovacuum also triggers ANALYZE when:

analyze_threshold = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

Default: 50 + 0.1 * reltuples. For a 1M-row table: 100,050 row changes before statistics are updated.

Autovacuum Cost-Based Throttling

Here is where autovacuum's second major misconfiguration lives.

To avoid overwhelming the I/O subsystem, autovacuum workers throttle themselves using a cost-based delay mechanism:

autovacuum_vacuum_cost_delay = 2ms     # how long to sleep after hitting cost limit
autovacuum_vacuum_cost_limit = 200     # cost units before sleeping
 
# Cost of each I/O operation (in cost units):
vacuum_cost_page_hit   = 1    # page found in shared_buffers
vacuum_cost_page_miss  = 10   # page read from disk
vacuum_cost_page_dirty = 20   # page written (dirtied by vacuum)

How throttling works: The autovacuum worker accumulates cost as it reads and writes pages. When accumulated cost hits autovacuum_vacuum_cost_limit (200 by default), the worker sleeps for autovacuum_vacuum_cost_delay (2ms by default), then resets its cost counter and continues.

The throughput math:

In 2ms of sleep per 200 cost units:

  • If all reads are cache misses (vacuum_cost_page_miss = 10): 20 pages per cycle
  • At 8KB per page: 160KB per 2ms cycle
  • Maximum throughput: 80MB/s... but this assumes zero actual I/O time

In practice, the disk reads themselves take time. With a cost limit of 200 and page_miss = 10, the worker reads ~20 pages before sleeping. On a cold cache with SSD reads at ~100μs each, 20 pages takes ~2ms to read — then another 2ms of sleep. Effective throughput: ~40MB/s of heap scan.

Why the defaults are too conservative:

On modern NVMe storage, 40MB/s vacuum throughput is extremely slow. A table with 10GB of dead tuple bloat will take 250 seconds (over 4 minutes) of pure vacuum I/O time to clean — and that's before the multiple passes for large dead tuple lists.

For systems with fast storage:

autovacuum_vacuum_cost_delay = 0       # no sleep between cost cycles (aggressive)
# or
autovacuum_vacuum_cost_delay = 1ms     # reduce sleep by 50%
autovacuum_vacuum_cost_limit = 800     # allow 4x more work per cycle

Setting cost_delay = 0 makes autovacuum run at full I/O speed — appropriate for NVMe systems where vacuum I/O doesn't compete with query I/O significantly.

Transaction ID Wraparound: The Shutdown You Cannot Avoid

This is the most serious consequence of misconfigured autovacuum, and it deserves its own section.

How XID Wraparound Works

As covered in Module 2, transaction IDs (XIDs) are 32-bit integers. The database can have at most ~2 billion XIDs in flight before it wraps around. XID wraparound is not theoretical — every database will face it if it runs long enough.

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

Sign in & Register
PreviousModule A-3: Write-Ahead Logging: Durability, Replication, and the Price of Every WriteNext Module A-5: Index Internals: B-Tree, Hash, GIN, BRIN and the Planner's View

Discussion

0

Join the discussion

Loading comments...
PortfolioBlog

© 2026 Jatin Jain Saraf (JJS). All rights reserved.