## 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.

```text
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:

```sql
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.

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

```sql
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.

3. Give autovacuum more throughput globally:

```sql
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.

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

```sql
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).

5. Verify the workers are keeping up going forward:

```sql
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