Skip to content

C05 — Vacuum & Autovacuum Health

Transaction ID wraparound risk, dead-tuple accumulation, and whether autovacuum is actually keeping up with the write workload. Wraparound is the one failure mode in this entire catalog that can force PostgreSQL to stop accepting writes outright if left unaddressed, which is why this category tracks database- and table-level transaction ID age as directly as PostgreSQL's own catalogs allow.

16 checks, PGHF05-001 through PGHF05-016. Every check here is PGHF-namespace and built-in — see Metadata Columns Explained for what each field below actually means, and Resetting to Defaults for why these definitions can't be hand-edited in place.

PGHF05-001 — Database TXID age

A database approaching the ~2.1 billion transaction ID wraparound limit is approaching forced-shutdown territory — PostgreSQL will eventually refuse new transactions outright to prevent data loss, and by then it's an emergency, not routine maintenance.

How to fix

  1. Find the tables driving the age (PGHF05-002) and run an aggressive freeze on them directly rather than waiting for autovacuum's normal schedule:
VACUUM (FREEZE, VERBOSE, ANALYZE) schema.big_table;
  1. Check for what's actually blocking normal autovacuum from keeping up — a long-running transaction (PGHF04-001/PGHF01-004), a disabled autovacuum table (PGHF05-005), or an autovacuum worker pool too small for cluster size (PGHF05-007).
  2. If age is already past autovacuum_freeze_max_age, autovacuum is already running FREEZE vacuums on its own — check pg_stat_progress_vacuum (PGHF05-010) rather than starting a competing manual one on the same table.

PGHF05-002 — Table TXID age

Same wraparound risk as PGHF05-001, but at table granularity — knowing which specific tables are closest to the limit is what lets a DBA target VACUUM FREEZE where it's actually needed instead of guessing.

How to fix

See PGHF05-001's remediation — run VACUUM (FREEZE, VERBOSE, ANALYZE) against the specific table(s) this check names, starting with the oldest.

PGHF05-003 — Dead tuple ratio

Dead tuples exceeding a fifth of live rows waste storage and force the planner and executor to scan past rows that no longer matter — a high ratio is a direct sign autovacuum is undersized or misconfigured for this table's write rate.

How to fix

  1. Run a manual VACUUM ANALYZE on the flagged table to reclaim space immediately.
  2. Lower autovacuum_vacuum_scale_factor for that table specifically so autovacuum triggers sooner relative to its size (see PGHF05-006):
ALTER TABLE schema.hot_table SET (autovacuum_vacuum_scale_factor = 0.05);
  1. If dead-tuple accumulation still outpaces vacuum after that, check autovacuum_max_workers (PGHF05-007) and autovacuum_vacuum_cost_delay (PGHF05-008) for cluster-wide throttling.

PGHF05-004 — Last autovacuum age

A high-write table that hasn't been autovacuumed in a week or more is accumulating bloat the whole time — this usually means autovacuum is being blocked (by a long-running transaction) or simply can't keep pace with the table's write volume.

How to fix

  1. Check for a long-running transaction holding back autovacuum's cleanup horizon (PGHF04-001) — this is the most common cause of a table going untouched despite heavy writes.
  2. If nothing is blocking it, autovacuum simply hasn't gotten to it yet — check autovacuum_max_workers (PGHF05-007) and autovacuum_naptime, and consider a per-table autovacuum_vacuum_scale_factor override (PGHF05-006) to prioritize it.
  3. Run VACUUM ANALYZE manually in the meantime to catch it up.

PGHF05-005 — Tables with autovacuum disabled

A table with autovacuum explicitly turned off gets none of the automatic dead-tuple reclamation or wraparound protection every other table gets — it's easy to disable temporarily for a bulk load and forget to re-enable it.

How to fix

Unless there's a specific, currently-active reason it's off, re-enable it:

ALTER TABLE schema.the_table RESET (autovacuum_enabled);
VACUUM ANALYZE schema.the_table;  -- catch up whatever accumulated while it was off

PGHF05-006 — autovacuum_vacuum_scale_factor

At the 0.2 default, a 10-million-row table won't be vacuumed until 2 million dead tuples accumulate — fine for a small table, but on a large one this default alone is often the root cause of bloat that looks otherwise unexplained.

How to fix

Lower it cluster-wide, or (usually better) override it per-table for large/hot tables specifically so small tables keep their more-frequent-by-proportion default:

ALTER TABLE schema.big_table SET (autovacuum_vacuum_scale_factor = 0.02);

For very large tables, also consider autovacuum_vacuum_insert_scale_factor/pairing with an absolute autovacuum_vacuum_threshold so the trigger point doesn't scale purely with table size.

PGHF05-007 — autovacuum_max_workers

Too few workers relative to the number of tables needing regular vacuuming means some tables simply wait their turn longer than their write rate can tolerate — worth sizing against actual table count, not leaving at the default.

How to fix

ALTER SYSTEM SET autovacuum_max_workers = 6;

Requires a restart. Each concurrent worker can use up to autovacuum_work_mem/maintenance_work_mem (PGHF05-011), so raise this together with a memory-budget check, not in isolation.

PGHF05-008 — autovacuum_vacuum_cost_delay

A high cost delay deliberately throttles autovacuum to reduce I/O impact — reasonable on old spinning disks, but on modern SSDs it mostly just lets bloat and dead-tuple accumulation outrun vacuum for no real benefit.

How to fix

On SSD/NVMe-backed storage, lower it (PostgreSQL 12+ defaults to 2ms, which is already reasonable for most modern hardware — this is a check for a legacy value carried over from spinning-disk-era tuning):

ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
SELECT pg_reload_conf();

PGHF05-009 — Table bloat estimate

A table whose physical size has grown far past what its live row count would predict is wasting disk space and slowing every scan against it — routine VACUUM reclaims space for reuse but doesn't shrink the file; only VACUUM FULL or pg_repack does.

How to fix

Routine VACUUM won't fix this — the space is already marked reusable internally but the file itself hasn't shrunk. To actually reclaim disk:

VACUUM FULL schema.bloated_table;  -- takes an ACCESS EXCLUSIVE lock for the duration

For a table too large or too busy to lock like that, use pg_repack instead — it rebuilds the table online with only a brief final lock. Either way, also fix whatever's driving the bloat in the first place (see PGHF05-003/PGHF05-006) or it recurs.

PGHF05-010 — In-progress vacuum age

A vacuum still running after several hours is unusual enough to warrant a look — it may be legitimately working through a huge table, or it may be stuck behind a lock or an inefficient index, either of which is worth knowing about while it's happening rather than after.

How to fix

  1. Check pg_stat_progress_vacuum for which phase it's in and how far through — a slow but genuinely progressing index vacuuming phase on a huge table with many indexes is normal, not stuck.
  2. If it's not progressing at all, check pg_stat_activity/pg_locks for something blocking it.
  3. If it needs to be stopped, pg_cancel_backend() its pid — this is safe, VACUUM is fully resumable and won't corrupt anything if interrupted.

PGHF05-011 — autovacuum_work_mem

Left at -1 (fall back to maintenance_work_mem), every concurrent autovacuum worker can claim that full amount — if maintenance_work_mem is sized generously for manual DDL, several simultaneous workers can consume far more memory than intended.

How to fix

Set it explicitly to bound per-worker memory independently of maintenance_work_mem:

ALTER SYSTEM SET autovacuum_work_mem = '256MB';
SELECT pg_reload_conf();

Multiply by autovacuum_max_workers (PGHF05-007) to get the real worst-case total, and check that against available RAM.

PGHF05-012 — Multixact wraparound age

Multixact IDs track row-level locks, and exhausting their ID space causes table corruption and a forced shutdown — just as dangerous as ordinary TXID wraparound (PGHF05-001) but far less commonly monitored.

How to fix

Same remediation shape as PGHF05-001: run VACUUM (FREEZE, VERBOSE, ANALYZE) on the oldest tables, and check for a blocking long-running transaction. Heavy multixact usage specifically comes from SELECT ... FOR SHARE/foreign-key-checking row locks — if this recurs frequently, also look at whether the application is taking more row-level shared locks than it needs to.

PGHF05-013 — Oldest prepared transaction age

An old uncommitted two-phase-commit transaction holds locks, retains WAL, and prevents TXID age from advancing — it can silently block autovacuum cluster-wide, often left behind by a crashed application that never called COMMIT PREPARED or ROLLBACK PREPARED.

How to fix

  1. List them: SELECT * FROM pg_prepared_xacts;
  2. For each one that's clearly abandoned (its owning application is gone/crashed), resolve it directly:
ROLLBACK PREPARED 'the_gid';  -- or COMMIT PREPARED, if you know the intended outcome
  1. If this happens repeatedly, check whether the application actually needs two-phase commit (max_prepared_transactions > 0) — most single-database applications don't, and disabling it entirely (max_prepared_transactions = 0) removes the failure mode altogether.

PGHF05-014 — Autovacuum worker saturation

When every autovacuum worker slot is occupied, newly-eligible tables simply wait for a slot to free up — sustained full saturation means the worker pool is undersized for the cluster's actual vacuum workload.

How to fix

See PGHF05-007's remediation — raise autovacuum_max_workers (restart required), and re-check saturation over a representative period afterward. If a handful of very large tables are monopolizing workers for a long time each, per-table autovacuum_vacuum_cost_delay/autovacuum_vacuum_cost_limit overrides can free up slots faster for the rest.

PGHF05-015 — Table statistics staleness

Stale statistics on a heavily-modified table lead the planner to misjudge selectivity and choose worse plans — index vs. sequential scan decisions are only as good as the last ANALYZE that informed them.

How to fix

ANALYZE schema.the_table;

If this recurs on the same tables repeatedly, autovacuum's analyze phase (governed by autovacuum_analyze_scale_factor, the ANALYZE counterpart to PGHF05-006's vacuum setting) may need the same per-table tightening.

PGHF05-016 — pg_stat_io eviction pressure by context

PGHF13-002 only measures buffer-pool evictions for backend_type = 'client backend' — it structurally cannot see autovacuum's own ring-buffer thrashing, a different problem: heavy VACUUM/COPY activity evicting pages other backends still need directly causes query-latency spikes during maintenance windows, with zero visibility today despite this project already querying the exact view that reports it.

How to fix

  1. Check whether eviction pressure lines up with a known maintenance window (a large VACUUM, COPY, or index build) — if so, this may be the expected cost of that operation rather than a misconfiguration.
  2. If shared_buffers (PGHF03-001) is undersized for the working set, autovacuum's ring buffer and ordinary client backends compete harder for the same limited space — raising it can reduce pressure on both sides at once.
  3. For a specific heavy maintenance operation, consider running it in a lower-traffic window rather than tuning the server around accommodating it at peak load.

Continue to C06 — Index Health.