The PostgreSQL administration field guideField notes · Runbooks · Free certification
pgdba.dev
Menu

Operational runbook · Quick reference

Autovacuum is not keeping up

Determine whether scheduling thresholds, worker capacity, table churn, or old horizons prevent maintenance from advancing.

autovacuum backlogdead tuplesXID agetable bloat

Before you begin

Prerequisites

  • Confirm the target cluster, database, PostgreSQL major version, and your read-only access to the required statistics views.
  • Record the current user impact, incident owner, UTC timestamp, and a baseline before running diagnostics.

Control the blast radius

Safety boundary

Start with read-only observations. Do not restart, terminate, delete, fail over, or change configuration until ownership, blast radius, and an approved recovery path are explicit.

Triage

Establish impact

  1. Check the oldest database and table transaction ID ages before treating this only as a performance incident.
  2. Identify the tables generating the most dead rows and whether vacuum workers are active or repeatedly interrupted.
  3. Look for old transactions, prepared transactions, and replication slots that hold the cleanup horizon back.

Observe

Gather evidence

Diagnostic query

Prioritise tables by XID age and dead rows

Find the user tables with the oldest unfrozen transaction IDs and the largest dead-row estimates.

SELECT
  table_stats.schemaname,
  table_stats.relname,
  table_stats.n_live_tup,
  table_stats.n_dead_tup,
  table_stats.last_autovacuum,
  age(table_class.relfrozenxid) AS transaction_id_age
FROM pg_stat_user_tables AS table_stats
JOIN pg_class AS table_class ON table_class.oid = table_stats.relid
ORDER BY transaction_id_age DESC, table_stats.n_dead_tup DESC
LIMIT 30;
low observation cost

Interpret

Interpret the evidence

  • Tune high-churn tables from their change rate and size rather than changing global thresholds blindly.
  • A vacuum that runs but cannot remove rows may be waiting for old snapshots to disappear.

Act

Take the safest useful action

  1. Resolve old transactions and abandoned slots that prevent cleanup from advancing.
  2. Give the highest-risk tables enough worker time and I/O capacity while watching production latency.
  3. Adjust per-table thresholds for persistently high-churn tables and verify the next maintenance cycles.

Prove the outcome

Verification

  1. Repeat the baseline observation over a known interval and confirm that the measured queue or risk is moving in the intended direction.
  2. Verify the user-facing objective and every affected replica or dependency before closing or handing over the incident.

Keep recovery close

Rollback

Record the original state and rollback owner before acting. If verification worsens or the objective is missed, reverse only the bounded change, confirm the baseline is restored, and escalate with the before-and-after evidence.

Escalate

Escalate when

  • Escalate immediately when wraparound warnings appear, writes approach XID refusal, or vacuum progress repeatedly stops on the same table.

Prevent recurrence

Study the underlying system

vacuum observability

Certification competency

Refresh the assessed operating model

MVCC, vacuum, autovacuum, and bloat

Reference assurance

Version and review

Compatible versions
PostgreSQL 16–18
Content version
2026.08
Reviewed
2026-08-04

Catalog columns, wait events, and operational controls can vary by PostgreSQL major version, extensions, and orchestration layer. Verify commands against the deployed version.

Verify independently

Sources