Autovacuum Is Running and Your Table Is Still Bloating

Postgres never updates a row in place. An UPDATE writes a new version and marks the old one dead. A DELETE marks dead without writing anything new. Those dead tuples occupy pages until vacuum reclaims them.

That design is what makes readers never block writers. The cost is that a table under heavy write load grows unless vacuum keeps pace, and when it falls behind the symptoms arrive slowly enough that nobody connects them to the cause.

Measuring bloat before you believe in it

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Anything above roughly 20 percent dead is worth investigating. Above 50 percent means vacuum is not working on that table, and you should find out why rather than running a manual vacuum and moving on.

-- is anything vacuuming right now, and how far has it got
SELECT pid, datname, relid::regclass, phase,
       heap_blks_scanned, heap_blks_total,
       round(100.0 * heap_blks_scanned / NULLIF(heap_blks_total,0), 1) AS pct
FROM pg_stat_progress_vacuum;

Tested on Postgres 16.3, Linux 6.8.

The four things that block reclamation

A vacuum can only remove a dead tuple if no transaction could still need to see it. Postgres computes a horizon, and anything newer than that horizon is untouchable. Four things hold that horizon back.

1. A long running transaction

The classic. One idle transaction holds the horizon for the entire database, and vacuum across every table becomes ineffective.

SELECT pid, state, xact_start,
       now() - xact_start AS duration,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' OR xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

idle in transaction is the state to hunt. An application that opened a transaction, did one SELECT, and then went off to call an HTTP API for ninety seconds is holding the horizon that whole time. Under load there is always one open, so the horizon never advances.

-- a safety net, not a fix
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';

The real fix is not holding a transaction open across a network call. That pattern also causes lock queues on migrations, so it is worth eliminating for two reasons.

2. Replication slots that nobody consumes

An inactive slot pins WAL and holds the horizon indefinitely. A replica that was decommissioned without dropping its slot will quietly destroy vacuum across the whole cluster, and disk fills at the same time.

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

active = false with gigabytes retained is your problem. Drop the slot if the replica is genuinely gone.

3. Abandoned prepared transactions

Rare, and catastrophic when it happens, because a prepared transaction survives restarts.

SELECT gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY prepared;

Anything here older than a few minutes is almost certainly orphaned by a two phase commit that never resolved. ROLLBACK PREPARED 'gid' clears it.

4. Hot standby feedback

With hot_standby_feedback = on, a long query on a replica holds the horizon on the primary. That is the point of the setting, and it means an analyst running a twenty minute report on the replica is preventing cleanup on the primary.

When nothing is blocking and vacuum is still behind

If the horizon is healthy, autovacuum is simply not keeping up with write volume. Three settings dominate.

The trigger threshold scales with table size and that is the trap.

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

With the default scale factor of 0.2, a table with 50 million rows waits for 10 million dead tuples before autovacuum starts. By then the damage is done and the vacuum itself is enormous.

Override per table for your large ones:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.005
);

Vacuuming a big table often and cheaply beats vacuuming it rarely and expensively.

The cost limit throttles it deliberately. Autovacuum pauses when it has accumulated autovacuum_vacuum_cost_limit of work, to avoid saturating I/O. The default is conservative for modern hardware.

ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';

Worker count caps concurrency. Three workers across two hundred tables means large tables wait. Raise autovacuum_max_workers to 5 or 6 if you have the I/O headroom, remembering that the cost limit is shared across all of them.

Bloat that vacuum will not fix

Vacuum marks space reusable inside the table. It does not return it to the filesystem, except for empty pages at the very end. A table that ballooned to 400 GB and now holds 40 GB of live data stays 400 GB on disk.

Reclaiming it requires a rewrite:

-- locks the table exclusively for the duration, avoid in production
VACUUM FULL events;

-- rewrites with only a brief lock at the end, needs the extension
pg_repack -t events -d mydb

pg_repack is what you want for a live system. VACUUM FULL takes ACCESS EXCLUSIVE for the whole rewrite, which on a large table means an outage.

Indexes bloat separately and are often the larger share:

REINDEX INDEX CONCURRENTLY idx_events_created_at;

Bloated indexes are a common reason the planner stops using an index: the index grew large enough that a sequential scan looks cheaper.

The one that will page you at 3am

Transaction IDs are 32 bit and wrap around. Postgres freezes old rows to prevent this, and if freezing falls too far behind the database refuses writes to protect itself.

SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_to_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Alert at 50 percent. At 100 percent the database stops accepting writes and recovery requires a single user mode vacuum, which is a genuinely bad day. Every cause listed above also blocks freezing, so a stuck horizon is a wraparound risk as well as a bloat problem.

Prevention

• Alert on n_dead_tup ratio per table and on age(datfrozenxid) per database. These are cheap queries and both failures are slow enough to catch early.
• Alert on the oldest transaction age. Anything over five minutes on an OLTP system is worth knowing about.
• Set idle_in_transaction_session_timeout as a backstop.
• Tune scale factors per table for anything over a few million rows. The global default is wrong for large tables by design.
• Audit replication slots when you decommission a replica. Add it to the runbook, because this one is silent until disk fills.