PostgreSQL Autovacuum and Table Bloat: Dead Tuples, Transactions That Block Cleanup, and Per-Table Tuning

When you update or delete a row in PostgreSQL, the old version does not leave the disk right away. Under MVCC some open transaction may still need to see it, so it stays on the page as a dead tuple. VACUUM is what removes dead tuples and makes their space reusable, and in most setups autovacuum does it in the background. The trouble is that autovacuum's defaults are tuned for small and medium tables. On a large, frequently updated table cleanup falls behind, the table and its indexes bloat, and queries scan more and more pages to read the same data. This post covers why bloat happens, how to measure it, and which knob helps in which situation.

When autovacuum visits a table

Autovacuum picks a table for cleanup once its dead tuple count passes this threshold:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

The defaults are 50 and 0.2, so roughly a fifth of the table has to be dead before anything happens. For a thousand-row table that is 250 rows and nobody notices. For a hundred-million-row table it means waiting for twenty million dead tuples. If that table is updated every minute, cleanup runs rarely, runs long, and runs heavy.

The second limit is speed. To avoid crushing production traffic, autovacuum uses cost-based throttling: it accumulates work units and sleeps briefly when it hits a budget. Unless you set autovacuum_vacuum_cost_limit explicitly it falls back to vacuum_cost_limit (default 200), and that budget is shared across all autovacuum workers running at the same time. That is why raising autovacuum_max_workers on its own rarely speeds things up. It splits the same budget among more workers.

Measure first

Talk about bloat with statistics, not hunches. Start with pg_stat_user_tables:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / greatest(n_live_tup, 1), 1) AS dead_pct,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

These numbers are estimates, but they are good enough to show which tables are falling behind. A table with a high dead ratio and a last_autovacuum that is very old or empty needs its own settings.

For the real picture, the pgstattuple extension reads a table end to end and reports dead tuple and free space percentages. On a big table that is a full scan, so do not run it at peak, or use the cheaper pgstattuple_approx.

To see what autovacuum is actually doing, set log_autovacuum_min_duration to a few seconds. Every long run then lands in the log with how many tuples it removed and how many it could not.

The usual culprit: an old transaction holding cleanup back

Before touching any settings, ask whether autovacuum is running fine but cannot remove anything. VACUUM can only remove tuples that died before the oldest snapshot still open in the cluster. A single transaction left open for hours freezes cleanup across the whole cluster at that point. The "dead but not yet removable" count you see in the log or in VACUUM VERBOSE output is exactly this.

Four things typically hold that horizon back:

-- Long running or "idle in transaction" sessions
SELECT pid, state, xact_start, age(backend_xmin) AS xmin_age, left(query, 60)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

-- Replication slots whose consumer went away
SELECT slot_name, active, xmin, catalog_xmin
FROM pg_replication_slots;

-- Forgotten prepared transactions (two-phase commit)
SELECT gid, prepared, owner FROM pg_prepared_xacts;

The fourth is a replica with hot_standby_feedback enabled: a long report query on the replica also holds back cleanup on the primary.

The most common source is the application. Code that opens a transaction and then waits on a network call, or returns a connection to the pool on an error path without commit or rollback, leaves the session idle in transaction. idle_in_transaction_session_timeout terminates such sessions after a set time. Give it a sane database-wide value and relax it per session for batch jobs that genuinely need long transactions. Leaving it off means one bad error path can one day stall cleanup for the whole cluster.

Per-table settings for large tables

Lowering the scale factor cluster-wide creates pointless work on every small table. The better move is to give the problem table its own threshold:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.02
);

Now cleanup triggers at one percent of the table plus ten thousand dead tuples. Lowering the analyze threshold matters too: on a fast-growing table stale statistics lead the planner to bad plans.

On speed, if you have disk and CPU headroom, raising autovacuum_vacuum_cost_limit cluster-wide is usually more effective than adding workers. After the change, watch run durations in the log together with disk latency. The goal is cleanup that keeps up with the write load, not cleanup that competes with production queries.

Also know the autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor settings added in PostgreSQL 13. Insert-only tables used to go unvacuumed for a long time because they produced no dead tuples, which meant a stale visibility map and slower index-only scans. Insert volume can now trigger vacuum as well.

Preventing bloat at the source: HOT updates

If an update touches no indexed column and the new version fits on the same page, PostgreSQL performs a HOT (heap-only tuple) update. No new index entries are written and the old version can be pruned within the page. Compare n_tup_hot_upd to n_tup_upd in pg_stat_user_tables. A low ratio has two usual causes: a frequently updated column is indexed (an unnecessary index on a "last seen" timestamp is a classic), or pages have no free room. For the second, lowering the table's fillfactor, for example to 90, leaves space on every page for updates. It only applies to pages written from then on; existing data needs a table rewrite to benefit.

Getting a bloated table back

Plain VACUUM makes space reusable but in most cases does not return it to the operating system. A table that bloated to a hundred gigabytes stays that size after cleanup. Your options for shrinking it:

VACUUM FULL rewrites the table, but holds an ACCESS EXCLUSIVE lock for the whole run, so even reads wait. It also needs free disk equal to the table's size for the new copy. Outside small tables with a maintenance window, keep it away from production.

The pg_repack extension does the same job while the table stays online: it builds the copy in the background, replays concurrent changes through a trigger, and takes only short locks at the start and the end. The table needs a primary key or a unique index on non-null columns. It still needs extra disk equal to the table size, and under heavy writes the final lock can get stuck waiting, so run it at a quiet hour.

If only the indexes are bloated, leave the table alone. REINDEX INDEX CONCURRENTLY (PostgreSQL 12 and later) rebuilds an index without blocking writes.

Queue and event tables need a different design

Tables where rows are inserted and deleted shortly afterwards (job queues, outbox tables, session records) are the worst workload for VACUUM. Changing the design beats tuning here. Partition the data by time and drop or detach old partitions: that is faster than millions of DELETEs and produces no dead tuples at all.

Do not forget transaction ID wraparound

PostgreSQL transaction IDs are 32 bits and wrap around. Freezing old rows is also VACUUM's job. Once a table's age passes autovacuum_freeze_max_age (default 200 million), autovacuum starts an anti-wraparound vacuum even if autovacuum is disabled. Unlike a regular autovacuum run it does not cancel itself on lock conflicts, and on a large table it can take hours. If it never catches up, the database eventually refuses new transactions. Add this query to your monitoring, and if ages climb well past 200 million, go back to the old-transaction checks above:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

What not to do

Do not disable autovacuum on a table or on the cluster. The load drops for a while, then bloat and wraparound protection return much harder. Do not schedule VACUUM FULL as a cron job; that is planning an outage every night. Do not write aggressive per-table settings for small tables either; the defaults are right for them. And before any tuning, always check for a blocking transaction first. Most bloat incidents come not from a wrong threshold but from a transaction that never closed.