← Blog · Engineering · September 15, 2026 · 6 min read · By Technical Staff

You Never Start at the Table: A Reverse Decision Procedure for Postgres Autovacuum

Every autovacuum guide starts at the table. Start at the page instead: four symptoms, five look-alikes to rule out, five vacuum failure modes, and the wraparound runbook.

You Never Start at the Table: A Reverse Decision Procedure for Postgres Autovacuum

You Never Start at the Table: A Reverse Decision Procedure for Postgres Autovacuum

TL;DR: Postgres autovacuum is the background process responsible for cleaning up dead rows (tuples) to prevent table bloat and transaction ID wraparound. If you are facing a disk alert or a sudden p99 regression, use this reverse decision procedure to rule out look-alike issues and identify which of the five specific vacuum failure modes is stalling your database.

Every engineering guide for postgres autovacuum starts at the table level. They assume you have already identified a specific relation that needs tuning and now just need to adjust the autovacuum_vacuum_scale_factor. In reality, SREs and platform engineers never start at the table. They start at the page.

You arrive at a possible vacuum issue through one of four symptoms. If you don't know how to work backward from the alert to the cause, you risk changing global configurations that might make a production incident worse.

The Four Ways This Actually Reaches You

When a database begins to struggle, autovacuum is often the first suspect but rarely the first evidence. Use the table below to map your current symptom to the single query that confirms if vacuum is a live candidate.

Symptom The "Smoking Gun" Query Why it points to Autovacuum
Disk/Storage Alert SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC; High dead tuple counts suggest the vacuum daemon isn't keeping up with churn.
p99 Latency Spike SELECT * FROM pg_stat_progress_vacuum; Long-running scans over bloated tables or active vacuum workers competing for IO.
Replica Lag Climbing SELECT slot_name, restart_lsn, active FROM pg_replication_slots; A pinned xmin horizon prevents vacuum from cleaning, bloating the WAL.
Sudden Plan Flip SELECT relname, last_analyze FROM pg_stat_user_tables; If autoanalyze hasn't run, the planner is using stale statistics.

First, Rule It Out

Before diving into autovacuum tuning, you must eliminate the five look-alikes that mirror these symptoms but have nothing to do with dead tuples.

  1. Stale Planner Statistics: The query is slow not because the table is bloated, but because the planner thinks it has 100 rows when it has 1 million. Check: last_analyze timestamp.
  2. Index Bloat: The table (heap) might be fine, but the indexes are 10x the size they should be. Check: Compare pg_relation_size for indexes vs. tables.
  3. Missing Index: A new query pattern started without an index. Check: pg_stat_statements for high-scan, high-time queries.
  4. Checkpoint Storm: High IO latency caused by aggressive dirty page flushing. Check: pg_stat_bgwriter for checkpoints_timed vs checkpoints_req.
  5. Provider IO Exhaustion: You’ve run out of EBS burst credits or IOPS. Check: Cloud provider metrics for "Burst Balance."

Key Takeaway: Ruling out non-vacuum causes is 80% of incident triage; never tune autovacuum parameters until you have confirmed a blocked xmin horizon or a failed trigger.

Why n_dead_tup is a Bad First Signal

According to Coroot, relying solely on the n_dead_tup count can be misleading. This value is a statistical estimate, not a real-time count. More importantly, it doesn't tell you if the rows are removable.

Postgres uses Multiversion Concurrency Control (MVCC). A row is only "removable" if it is older than the xmin horizon—the transaction ID of the oldest currently running transaction. If a row is dead but still newer than that horizon, postgres autovacuum cannot touch it.

The Five Failure Modes

If you’ve ruled out the look-alikes, you are likely facing one of these five failure modes.

1. Autovacuum Never Triggered

Confirm: last_autovacuum is NULL or weeks old despite high churn. Refute: autovacuum_enabled is true and n_dead_tup is below the threshold. Cause: The autovacuum_vacuum_scale_factor (default 0.2 or 20%) is too high for a large table. A 1TB table needs 200GB of dead rows before vacuum kicks in.

2. The xmin Horizon is Pinned

Confirm: n_dead_tup remains high after a manual VACUUM. Refute: pg_stat_activity shows no transactions older than 5 minutes. Cause: An idle-in-transaction backend, an abandoned replication slot, or a long-running reporting query is holding the horizon open.

3. Vacuum is Running and Losing

Confirm: pg_stat_progress_vacuum shows a worker stuck in "scanning heap" for hours. Refute: IO latency is low and autovacuum_vacuum_cost_limit is set to a very high value. Cause: Throttling. By default, autovacuum is extremely polite. If your write volume is high, the default cost-based limits make vacuum too slow to win the race.

4. Vacuum Keeps Being Cancelled

Confirm: Postgres logs show "cancel autovacuum" messages. Refute: No DDL or ALTER TABLE commands have run recently. Cause: Autovacuum yields to any process requiring an AccessExclusiveLock. If you have a cron job running DDL, vacuum will restart repeatedly.

5. Vacuum Succeeded, but Disk is Full

Confirm: n_dead_tup is near zero, but pg_relation_size is still huge. Refute: pg_stat_progress_vacuum shows "vacuuming indexes." Cause: This is normal behavior. Standard vacuum returns space to the Postgres FSM (Free Space Map) for reuse by Postgres, not to the OS. Only VACUUM FULL or pg_repack returns disk space to the filesystem.

The Wraparound Case: The Real Emergency

Transaction ID wraparound is a different class of incident. Postgres uses 32-bit integers for transaction IDs. If the gap between the oldest and newest transaction reaches ~2 billion, the database will shut down to prevent data corruption.

According to PostgreSQL documentation, you must monitor age(datfrozenxid).

  1. Warning (200m xids): Postgres starts "anti-wraparound" autovacuums. These are aggressive and ignore cost limits.
  2. Danger (1b xids): You are at risk of a forced shutdown.
  3. Emergency Procedure:
    • Identify the table with the oldest relfrozenxid.
    • Increase autovacuum_work_mem to speed up the scan.
    • Cancel all non-essential long-running queries to let the freeze vacuum finish.

Safe Now vs. Needs Review

When you are on call at 2 AM, you need to know what is safe to change immediately versus what requires a PR.

Safe Now (Single Table/Session)

Needs Review (Global Config)

Note on VACUUM FULL: Avoid this during an incident. It takes an AccessExclusiveLock, blocking all reads and writes to the table. Use pg_repack or pg_squeeze if you must reclaim disk space online.

What to Write in the Postmortem

A good postmortem for a postgres autovacuum incident must include the evidence standard. Do not just say "vacuum was slow." Provide:

Why This Matters

Modern Postgres environments are too complex to debug by manual query execution alone. The reason teams skip straight to guessing is that assembling evidence across pg_stat_activity, pg_replication_slots, and pg_class takes longer than the patience of the incident allows.

Operate’s context and root cause agents are designed to collect this specific evidence automatically. By the time a human is involved, the differential diagnosis—distinguishing between a pinned horizon and a cost-limit bottleneck—is already drafted. Because Operate proposes fixes as PRs rather than taking direct write access, the split between a "Safe Now" manual vacuum and a "Needs Review" configuration change is enforced by design, ensuring auditability for even the most critical SRE teams.

Sources & Further Reading

#postgres#autovacuum#sre#database-performance#postgresql