PostgreSQL Autovacuum Tuning: How to Stop Table Bloat

PostgreSQL autovacuum tuning is one of those tasks nobody thinks about until a 20 GB table turns into a 200 GB one, index scans slow down for no visible reason, or the log starts warning that a database "must be vacuumed within N transactions". The defaults are deliberately conservative: they work for small and medium tables, and they quietly fail on large, write-heavy ones. This guide explains how autovacuum decides when to run, which settings actually matter and how to tune it without hurting production.

Why PostgreSQL needs VACUUM at all

PostgreSQL implements MVCC by keeping old row versions. An UPDATE writes a new version of the row and a DELETE only marks the old one as deleted; neither removes it immediately, because other transactions may still need to see it. Those dead row versions (dead tuples) stay in the table and its indexes until VACUUM marks the space as reusable. The PostgreSQL documentation lists four jobs for vacuum:

• reclaim space occupied by dead rows so new rows can reuse it;
• update planner statistics (the ANALYZE part);
• update the visibility map, which makes index-only scans possible;
• freeze old rows to protect against transaction ID wraparound.

If vacuum runs too rarely, dead tuples pile up faster than space is reused. The table and its indexes grow (bloat), more pages must be read for the same data, caches become less effective and queries slow down gradually — which is why bloat often shows up first as "the database got slower over the last few months".

How autovacuum decides when to vacuum a table

The autovacuum launcher wakes up every autovacuum_naptime (default 1 minute) per database and starts up to autovacuum_max_workers workers (default 3). A table is vacuumed when its dead tuples exceed a threshold:

vacuum threshold = autovacuum_vacuum_threshold
                 + autovacuum_vacuum_scale_factor * reltuples

With the defaults — a base of 50 rows and a scale factor of 0.2 — a table is vacuumed after about 20% of its rows have become dead. For a 10,000-row table that is 2,050 rows, which is fine. For a 500-million-row table it is about 100 million dead rows before autovacuum even starts, and then the worker has to process the whole backlog in one go. That single scale factor is the root cause of most bloat problems we see.

Two more triggers exist. Since PostgreSQL 13, inserts alone can trigger a vacuum (autovacuum_vacuum_insert_threshold, default 1000, and autovacuum_vacuum_insert_scale_factor, default 0.2), which keeps append-only tables frozen and their visibility map current. And any table whose oldest unfrozen transaction ID is older than autovacuum_freeze_max_age (default 200 million transactions) gets an anti-wraparound vacuum regardless of the thresholds — even if autovacuum is switched off.

Step 1: find the tables autovacuum is losing

Start with the statistics views instead of guessing:

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
ORDER BY n_dead_tup DESC
LIMIT 20;

Tables with millions of dead tuples, a high dead percentage or a last_autovacuum from days ago are your candidates. For an exact picture of a specific table, the pgstattuple extension reports live, dead and free space precisely (it reads the whole table, so run it off-peak on large ones). Also enable log_autovacuum_min_duration (default 10 minutes since PostgreSQL 15; something like 1s is more useful while tuning) to see in the log how long each run took and how much it removed.

Dead tuples and the age of the oldest transaction belong on your monitoring dashboards permanently; we cover them among the database metrics that predict incidents.

Step 2: tune big tables individually

Lowering the global scale factor affects every table. The cleaner approach is to set storage parameters on the few large, busy tables that need it:

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

Now the table is vacuumed after roughly 1% of its rows change instead of 20%, so each run is smaller, faster and less disruptive. Some teams go further and set the scale factor to 0 with a fixed threshold (for example 100,000 dead rows), which makes vacuum frequency predictable regardless of table size. Do the same for ANALYZE: stale statistics on a big table are a classic cause of sudden plan changes.

Step 3: let autovacuum work fast enough

Autovacuum is throttled on purpose. Workers accumulate "cost" for pages they read and dirty, and once they reach the cost limit they sleep for autovacuum_vacuum_cost_delay (default 2 ms). The limit defaults to vacuum_cost_limit = 200 and is shared among all running workers — adding workers without raising the limit simply makes each one slower.

• Raise autovacuum_vacuum_cost_limit (for example to 1000–2000) on servers with SSD/NVMe storage. The default was chosen for much slower disks.
• Add workers only if many tables need vacuum at the same time. More workers help with breadth, not with one huge table.
• Give vacuum memory. maintenance_work_mem (or autovacuum_work_mem) limits how many dead tuple references a worker can hold before it has to scan the indexes again.
• Change one thing at a time and watch I/O latency: vacuum competes with your queries for disk bandwidth.

In PostgreSQL 18 the new autovacuum_worker_slots setting (applied at server start) reserves worker slots, and autovacuum_max_workers can then be changed at runtime up to that number without a restart — handy during an incident. Release 18 also adds total_autovacuum_time to pg_stat_all_tables and optional cost-delay reporting via track_cost_delay_timing, which shows how much time vacuum spends sleeping because of throttling (PostgreSQL 18 release notes).

Step 4: remove what blocks vacuum

Tuning does not help if vacuum is not allowed to remove dead rows. A row can only be removed when no transaction can still see it, so anything that holds back the oldest visible snapshot (the xmin horizon) blocks cleanup across the whole database:

1. Long-running or idle-in-transaction sessions. Check pg_stat_activity for old xact_start and the state idle in transaction; set idle_in_transaction_session_timeout so a forgotten session cannot hold the horizon for hours.
2. Abandoned replication slots. A slot whose consumer is gone keeps its xmin in pg_replication_slots and retains WAL. Drop slots nobody uses.
3. Replicas with hot_standby_feedback = on running long reporting queries — they hold back vacuum on the primary.
4. Forgotten prepared transactions in pg_prepared_xacts.

If the log shows autovacuum running often but n_dead_tup barely moves, one of these is almost always the reason. Slow queries caused by bloat are covered in our PostgreSQL slow query tuning checklist.

Transaction ID wraparound: the failure you must not reach

Transaction IDs are 32-bit, so every table must be vacuumed (frozen) at least once every roughly two billion transactions. PostgreSQL protects itself: when a database comes within 40 million transactions of wraparound it starts logging warnings, and with fewer than three million left it refuses to assign new transaction IDs — only read-only transactions work until a vacuum completes. Monitor the age directly:

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

Values that keep climbing far beyond autovacuum_freeze_max_age mean anti-wraparound vacuums are not finishing — usually because of the blockers above or throttling that is too strict. Alert well before the warnings start; recovering from the read-only state on a multi-terabyte database can take hours.

What about tables that are already bloated?

Plain VACUUM makes space reusable but rarely shrinks the files. VACUUM FULL rewrites the table and returns space to the operating system, but it takes an ACCESS EXCLUSIVE lock for the whole run and needs room for a full copy — on a busy table that is an outage. The usual production approach is an online rebuild with an extension such as pg_repack, or for indexes REINDEX CONCURRENTLY. Rebuild once, then fix the autovacuum settings so the table does not bloat again.