Lv.3 IntermediatePostgreSQL

2026.05.1825 min readLv.3 Intermediate

SeriesPostgreSQL Vacuum Complete Guide · Part 2View series hub

PostgreSQL Vacuum Part 2 — Autovacuum Tuning and XID Wraparound Disaster Prevention

Autovacuum running does not mean operational risk disappears. This part dissects the Launcher and Worker architecture, three trigger conditions, the real throughput ceiling imposed by Cost-Based Throttling, scale_factor dangers for large tables and per-table override strategies, the four-tier XID Wraparound defense line, emergency response procedures, and the Vacuum improvements shipped in PostgreSQL 13–17 — everything needed to understand and tune Autovacuum from an operations perspective.

Series overview

• Part 1 — Why VACUUM Exists: MVCC and Dead Tuples
• Part 2 — Autovacuum Tuning and XID Wraparound Disaster Prevention (current)
• Part 3 — Production Monitoring, Bloat Removal, and Operations (upcoming)

Table of Contents

1. Autovacuum Architecture: Launcher and Worker
2. Autovacuum Trigger Conditions — When Does It Fire?
3. Cost-Based Throttling — Why Is Autovacuum So Slow?
4. Core Parameters — Complete Breakdown
5. Per-Table Parameter Overrides — Focused Management for Hot Tables
6. XID Wraparound — PostgreSQL's Worst-Case Disaster Scenario
7. Vacuum Improvements by PostgreSQL Version (PG 13 → PG 17)
8. Emergency Response Playbook
9. Part 2 Summary and What's Next

1. Autovacuum Architecture: Launcher and Worker

Autovacuum is not a single process. It is a two-tier structure where a Launcher and one or more Workers cooperate.

The Autovacuum Launcher wakes on every autovacuum_naptime interval (default: 1 minute), scans every database in the cluster for tables that need vacuuming, and forks a Worker process for each target.

The number of concurrent Workers is capped by autovacuum_max_workers (default: 3), meaning at most three tables can be vacuumed simultaneously. On large databases this default is frequently the bottleneck.

-- Check currently running Autovacuum Workers
SELECT pid, datname, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY duration DESC;

2. Autovacuum Trigger Conditions — When Does It Fire?

An Autovacuum Worker vacuums a table when any one of the following three conditions is met.

Dead Tuple Trigger (Standard VACUUM)

Trigger condition:
n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup

With default values, the threshold grows explosively as table size increases.

On a 100-million-row table, 20 million Dead Tuples must accumulate before Autovacuum fires. This makes the default setting intuitively dangerous for large tables.

INSERT Trigger (PostgreSQL 13+, for Freeze)

Trigger condition:
n_ins_since_vacuum > autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor × n_live_tup

Added in PostgreSQL 13, this ensures that Append-Only tables (INSERT-only workloads) also receive periodic Vacuum passes for XID Freeze purposes.

XID Age Trigger (Anti-Wraparound VACUUM — Forced Execution)

Trigger condition:
age(relfrozenxid) > autovacuum_freeze_max_age

When this condition is met, Autovacuum fires even if autovacuum = off. An Aggressive Vacuum that scans the entire table runs regardless of Dead Tuple count, which can cause unexpected I/O spikes.

-- Check how much headroom each table has before its Autovacuum trigger
SELECT
    schemaname || '.' || relname AS table_name,
    n_live_tup,
    n_dead_tup,
    (50 + 0.2 * n_live_tup)::bigint AS vacuum_threshold,
    CASE
        WHEN n_dead_tup > (50 + 0.2 * n_live_tup)
        THEN 'Immediate Vacuum needed'
        ELSE ROUND(100.0 * n_dead_tup / NULLIF(50 + 0.2 * n_live_tup, 0), 1) || '% reached'
    END AS status
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Operational note: The threshold in this query assumes the default scale_factor = 0.2. When per-table overrides are in place, check pg_class.reloptions for the actual threshold in effect.

3. Cost-Based Throttling — Why Is Autovacuum So Slow?

If Autovacuum seems far slower than expected, Cost-Based Throttling is likely the cause. PostgreSQL deliberately caps Vacuum throughput to minimize its I/O impact on user queries.

Cost Calculation Basis

When cumulative cost reaches autovacuum_vacuum_cost_limit (default: 200), Autovacuum sleeps for autovacuum_vacuum_cost_delay (default: 2 ms).

Effective Vacuum Throughput Calculation

Cost per second = (1000 ms / cost_delay_ms) × cost_limit
               = (1000 / 2) × 200 = 100,000 cost/sec

Page size = 8 KB, cost per page ≈ 2 (assuming cache miss)
Pages per second = 100,000 / 2 = 50,000 pages/sec
Data per second  = 50,000 × 8 KB ≈ 400 MB/sec

-> On NVMe SSD, this is only 5–10% of the actual available I/O!

On NVMe storage, setting cost_delay = 0 to run Vacuum at full speed is a reasonable choice.

4. Core Parameters — Complete Breakdown

Key postgresql.conf Parameters

# === Basic activation ===
autovacuum = on                         # Never turn this off
track_counts = on                       # Prerequisite for autovacuum to work

# === Worker count and interval ===
autovacuum_max_workers = 3              # Default; 5–10 recommended for large DBs
autovacuum_naptime = 1min               # Can be reduced for small DBs (e.g., 30s)

# === VACUUM trigger thresholds ===
autovacuum_vacuum_threshold = 50        # Minimum dead tuple count
autovacuum_vacuum_scale_factor = 0.2    # Lower to 0.01–0.05 for large tables
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1   # Statistics refresh interval

# === Cost throttling ===
autovacuum_vacuum_cost_delay = 2ms      # Use 0 or 1ms on NVMe
autovacuum_vacuum_cost_limit = 200      # Higher = faster; 1000–2000 recommended

# === Freeze & Wraparound prevention ===
autovacuum_freeze_max_age = 200000000   # Aggressive Vacuum every 200M transactions
vacuum_freeze_min_age = 50000000        # Freeze tuples older than this XID age
vacuum_failsafe_age = 1600000000        # PG14+: emergency Failsafe above 1.6B

# === Memory ===
autovacuum_work_mem = -1                # -1 inherits maintenance_work_mem
maintenance_work_mem = 64MB             # 256MB–1GB recommended for large tables

Recommended Cost Settings by Storage Type

5. Per-Table Parameter Overrides — Focused Management for Hot Tables

The best practice is to leave global parameters at safe defaults and apply aggressive settings only to tables that receive frequent writes.

-- Example: an orders table receiving hundreds of writes per second
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor    = 0.01,   -- Trigger after just 1% dead tuples
    autovacuum_vacuum_threshold       = 100,
    autovacuum_analyze_scale_factor   = 0.005,
    autovacuum_vacuum_cost_delay      = 0,      -- No throttle — full speed
    autovacuum_vacuum_cost_limit      = 2000,
    autovacuum_freeze_max_age         = 100000000
);

-- Verify the settings
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

Tuning Strategy by Table Type

[Recommended scale_factor by table type]

Append-Only log table (INSERT only)
  -> vacuum_scale_factor: 0.05–0.1 (relatively relaxed)
  -> insert_scale_factor: 0.1 (PG13+, for Freeze)

General OLTP table (frequent UPDATE/DELETE)
  -> vacuum_scale_factor: 0.05–0.1
  -> cost_delay: 1–2 ms

High-frequency update table (sessions, counters, etc.)
  -> vacuum_scale_factor: 0.01
  -> cost_delay: 0
  -> freeze_max_age: 100 million or lower

Large history/archive table (rarely modified)
  -> vacuum_scale_factor: 0.2 (default is fine)
  -> freeze_max_age: must be monitored (watch XID Age)

6. XID Wraparound — PostgreSQL's Worst-Case Disaster Scenario

This section is the most important in Part 2.

Why Is It Dangerous?

PostgreSQL transaction IDs (XIDs) are 32-bit integers. After approximately 4.2 billion (2³²) transactions, the counter wraps around to zero. Because PostgreSQL uses modular arithmetic to determine past/future ordering, the practical safety limit is around 2.1 billion (2³¹).

What happens when XID Wraparound occurs?

XID 1,000,000 (old data)
  -> XID counter wraps past ~2.1 billion
  -> XID 1,000,000 is now interpreted as a "future transaction"
  -> Those rows become invisible to all transactions (data loss!)
  -> PostgreSQL detects this and switches the DB to read-only

ERROR: database is not accepting commands to avoid
       wraparound data loss in database "mydb"

Wraparound Defense Lines — 4 Tiers

XID Age (in number of transactions)

  0              200M              1.5B   1.6B    2B
  |--------------|-----------------|------|--------|
  Normal range   Tier 1: Aggressive  Alert  Failsafe  PANIC
                 VACUUM fires              (PG14+)   DB refuses writes

XID Age Monitoring Queries

-- Database-level XID Age
SELECT
    datname,
    age(datfrozenxid)                          AS xid_age,
    2000000000 - age(datfrozenxid)             AS xid_remaining,
    ROUND(age(datfrozenxid) / 20000000.0, 1)  AS danger_pct
FROM pg_database
ORDER BY xid_age DESC;

-- Table-level XID Age (top 20 most at-risk tables)
SELECT
    n.nspname || '.' || c.relname                   AS table_name,
    age(c.relfrozenxid)                             AS xid_age,
    pg_size_pretty(pg_total_relation_size(c.oid))   AS size,
    CASE
        WHEN age(c.relfrozenxid) > 1500000000 THEN 'CRITICAL'
        WHEN age(c.relfrozenxid) > 1000000000 THEN 'WARNING'
        WHEN age(c.relfrozenxid) > 500000000  THEN 'CAUTION'
        ELSE 'OK'
    END AS status
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY xid_age DESC
LIMIT 20;

-- Long-running transactions (Vacuum's biggest enemy)
SELECT
    pid,
    usename,
    state,
    now() - xact_start   AS tx_duration,
    LEFT(query, 80)      AS query_snippet
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
ORDER BY xact_start
LIMIT 10;

Operational warning: Long-running transactions completely prevent Vacuum from cleaning up Dead Tuples. Setting idle_in_transaction_session_timeout = '30min' automatically terminates abandoned transactions. Note that this setting will also terminate long-running batch connections, so batch sessions should use SET idle_in_transaction_session_timeout = 0 to opt out individually, or run under a dedicated role with the timeout disabled.

7. Vacuum Improvements by PostgreSQL Version (PG 13 → PG 17)

PostgreSQL has improved Vacuum in every major release. The available features and performance characteristics vary significantly depending on the version in use.

PostgreSQL 13 (2020)

• Parallel index Vacuum introduced (VACUUM (PARALLEL n) option): index cleaning distributed across parallel Workers for a single table
• B-tree index deduplication → reduced index bloat
• INSERT-based Autovacuum trigger added: Freeze support for Append-Only tables

PostgreSQL 14 (2021)

• vacuum_failsafe_age parameter introduced: when XID Age exceeds the threshold (default 1.6 billion), Cost Throttling is disabled and index Vacuum is skipped to complete Freeze as fast as possible
• Enhanced index cleaning deferral (Defer) during Autovacuum
• General Vacuum performance improvements for large tables

PostgreSQL 15 (2022)

• Internal VACUUM statistics processing improvements
• autovacuum_vacuum_max_threshold parameter added: allows setting an absolute upper bound on the Dead Tuple threshold

PostgreSQL 16 (2023)

• Vacuum Freezing performance improvement: pages eligible for Freeze are processed proactively during non-Freeze passes → reduces the frequency of full-table Freeze Vacuums
• BUFFER_USAGE_LIMIT option added: controls how much Shared Buffer Vacuum may consume → prevents cache eviction
• SKIP_DATABASE_STATS / ONLY_DATABASE_STATS options added → improves vacuumdb performance
• pg_vacuum_all_tables / pg_analyze_all_tables predefined roles added

PostgreSQL 17 (2024) — The Biggest Vacuum Overhaul

• TidStore new memory structure: a data structure optimized for storing Dead Tuple IDs, reducing memory usage by up to 20x (benchmark-based) → improved Vacuum throughput on large tables + reduced Shared Buffer contention
• Index Vacuum progress added to pg_stat_progress_vacuum
• pg_maintain role and MAINTAIN privilege introduced: Vacuum authority can be delegated to non-superuser roles
• Incremental VACUUM: only pages changed since the previous run are processed selectively → eliminates unnecessary full-table scans on large tables

-- PG17: Real-time monitoring of index Vacuum progress
SELECT
    p.pid,
    p.relid::regclass                                             AS table_name,
    p.phase,
    p.heap_blks_scanned,
    p.heap_blks_total,
    ROUND(100.0 * p.heap_blks_scanned / NULLIF(p.heap_blks_total, 0), 1) AS heap_pct,
    p.index_vacuum_count,
    p.num_dead_item_ids
FROM pg_stat_progress_vacuum p;

8. Emergency Response Playbook

Scenario A: "Autovacuum Is Running but Dead Tuples Keep Accumulating"

1. Check for long-running transactions → look for old xact_start values in pg_stat_activity
2. Check whether a Replication Slot is holding an old XID — an inactive Slot retains XID references indefinitely, preventing Autovacuum from cleaning up Dead Tuples at all. If Dead Tuples keep growing despite Workers running, suspect this first.

-- Is a Replication Slot blocking Vacuum?
SELECT slot_name,
       catalog_xmin,
       age(catalog_xmin) AS catalog_xmin_age,
       xmin,
       age(xmin)         AS xmin_age
FROM pg_replication_slots
WHERE NOT active
ORDER BY age(xmin) DESC;

3. Drop unnecessary inactive Replication Slots

SELECT pg_drop_replication_slot('slot_name'); -- Verify carefully before running

Scenario B: "XID Age Has Exceeded 1 Billion"

-- Run manual VACUUM FREEZE immediately
VACUUM (FREEZE, ANALYZE, VERBOSE) target_table;

-- For the entire database (raise maintenance_work_mem first)
SET maintenance_work_mem = '1GB';
VACUUM FREEZE;

Scenario C: "ERROR: database is not accepting commands..." Has Occurred

PostgreSQL has switched the database to read-only to prevent Wraparound data loss. This is the worst-case outcome.

# 1. Start in single-user mode (requires full service outage)
postgres --single -D /var/lib/postgresql/data mydb

# 2. Run VACUUM in single-user mode
VACUUM;

# 3. Restart normally

Alternatively, after a normal PostgreSQL startup, let autovacuum run its emergency pass or force it with vacuumdb.

# Force execution with vacuumdb
vacuumdb --all --freeze --jobs=4 -U postgres

Prevent this before it happens. Set a monitoring alarm to fire immediately when XID Age exceeds 1 billion (1B).

Scenario D: "VACUUM Is Too Slow and Consuming I/O During Peak Hours"

-- Cancel a running Autovacuum Worker (that Worker only; it will restart later)
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
  AND query LIKE '%my_hot_table%';

-- Avoid peak hours: schedule manual VACUUM via cron + temporarily disable autovacuum
ALTER TABLE big_table SET (autovacuum_enabled = false);

-- Run during off-peak hours, then re-enable immediately
VACUUM ANALYZE big_table;
ALTER TABLE big_table SET (autovacuum_enabled = true);

9. Part 2 Summary and What's Next

Part 2 Key Concepts

Part 3 Preview: Production Monitoring, Bloat Removal, and Operations

The final part provides a practical toolkit of real-world queries and tools ready for immediate use in production.

• Measuring production Bloat: pgstattuple usage
• pg_repack / pg_squeeze: rewrite tables and indexes without downtime
• Live VACUUM progress dashboard (pg_stat_progress_vacuum)
• Vacuum strategy for mixed OLTP + OLAP environments
• FAQ: "Should I ever use VACUUM FULL?"
• Operations checklist — monthly and quarterly Vacuum review items

References

• PostgreSQL 17 Documentation — Autovacuum Parameters
• PostgreSQL 17 Documentation — Routine Vacuuming
• InfoQ — PostgreSQL 17: Improved Vacuum Process
• Percona — Importance of PostgreSQL Vacuum Tuning
• CYBERTEC — Autovacuum Wraparound Protection