postgres autovacuum falling behind bloat fix

TL;DR

Confirm bloat with pgstattuple or dead-tuple counts, then make autovacuum more aggressive: lower the scale factor, raise autovacuum_max_workers, and relax the cost delay. Falling behind is a tuning problem, not a Postgres bug.

postgres autovacuum falling behind bloat fix

Use this when

• Tables grow far beyond their live data size
• Dead tuples accumulate faster than vacuum removes them
• Queries slow down on tables with heavy update/delete churn

Not for this skill when

• You need upsert (ON CONFLICT) syntax
• JSONB queries ignore the GIN index
• Refreshing a materialized view locks reads

Steps

1. Measure the actual bloat. Do not tune on vibes:

SELECT relname, n_dead_tup, n_live_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

Expected output: dead tuple counts and the last vacuum times per table. A high dead percentage with a stale last_autovacuum is the smoking gun.

1. Make autovacuum trigger sooner on churn-heavy tables:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 1000);

Expected output: vacuum triggers at 5% dead tuples instead of the default 20%. Set this per table; the global defaults are tuned for quiet tables, not hot ones.

1. Give autovacuum more throughput globally:

ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 2;
SELECT pg_reload_conf();

Expected output: more workers vacuuming in parallel with less throttling between pages. The cost delay default (2ms) is conservative; on modern SSDs, 0-2ms is usually fine.

1. For a table that is already badly bloated, one manual aggressive vacuum to catch up:

VACUUM (VERBOSE, ANALYZE) orders;

Expected output: dead tuples reclaimed and planner stats refreshed. This reclaims space for reuse (it does not shrink the file; only VACUUM FULL or CLUSTER does that, with an exclusive lock).

1. Verify the workers are keeping up going forward:

SELECT relname, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY last_autovacuum NULLS FIRST;

Expected output: recent last_autovacuum timestamps and shrinking dead counts. Tables with NULL last_autovacuum and growing dead tuples still need attention.

Variant phrasings

postgres table bloat after deletes

Normal: deletes mark tuples dead, only vacuum reclaims them. If autovacuum is behind, bloat grows (steps 2-3).

autovacuum not running on table

Check for long-running transactions blocking it: SELECT pid, xact_start, query FROM pg_stat_activity WHERE backend_xact_start < now() - interval '1 hour'. Vacuum cannot clean tuples newer transactions might still see.

postgres vacuum full vs vacuum

Regular VACUUM reclaims space for reuse without locking. VACUUM FULL rewrites the table and shrinks the file but takes an ACCESS EXCLUSIVE lock. Prefer tuning autovacuum over periodic FULL.

Why it happens

Postgres uses MVCC: updates and deletes leave dead tuple versions behind, and only vacuum marks their space reusable. Autovacuum triggers on dead-tuple thresholds with throttled workers; on write-heavy tables the defaults trigger too late and throttle too hard, so dead tuples accumulate faster than they are cleaned.

Edge cases

• Long-running transactions (including idle-in-transaction) block vacuum from removing anything, fix the transactions first.
• Aggressive settings on every table waste IO; tune the hot tables per-table (step 2) and keep globals moderate.
• autovacuum_freeze_max_age wraparound vacuums are emergency events, not routine tuning, and they preempt normal vacuum.
• Unlogged or temp tables skip some of this; the bloat problem is mostly about permanent heap tables.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_vaqpQPER1GNNiDOnZE2y9g