Postgres Table Bloat: Why the Default Autovacuum Settings Are Too Passive

Nobody gets paged for table bloat. That is exactly why it hurts. There is no error, no failed query, no red line on a dashboard the day it starts. A table that was snappy at launch gets a little slower each week, disk usage creeps up faster than your data actually grows, and one day a query that used to take 20 milliseconds takes 800 and someone finally asks what happened. The answer is usually the same: dead tuples piled up faster than autovacuum was configured to remove them, and the defaults let it happen quietly for months.

Here is the opinion I will defend for the rest of this article: Postgres's default autovacuum settings are tuned to protect a small, idle database from wasting CPU, not to keep a busy, write-heavy table healthy. They are safe defaults, not good defaults for your hottest tables, and leaving them untouched on a high-churn table is a slow-motion decision to accept bloat.

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

The formula nobody reads until it is too late

Autovacuum does not run on a timer per table. It wakes up regularly, looks at each table, and vacuums the ones whose accumulated dead tuples cross a threshold computed like this:

vacuum runs when:
  n_dead_tup > autovacuum_vacuum_threshold
             + autovacuum_vacuum_scale_factor * reltuples

The two knobs, with their default values:

Plug in a one-million-row table: 50 + 0.2 * 1,000,000 = 200,050 dead tuples. Postgres will not autovacuum that table until roughly a fifth of it is dead. On a ten-million-row table the trigger point is over two million dead tuples. The scale factor is a percentage, so the bigger the table, the more absolute garbage it tolerates before acting, which is precisely backwards from what a large hot table needs.

Why dead tuples cost you even before you notice

Postgres uses multiversion concurrency control. When you UPDATE or DELETE a row, the old version is not overwritten or removed immediately; it is marked dead and left in place so transactions that started earlier still see a consistent snapshot. Vacuum is the process that later reclaims those dead versions once no transaction can see them. Until it runs, the dead tuples are still physically in the table's pages.

That has three costs, all invisible until they are not:

• Scans read more pages. A sequential scan or an index scan has to walk past dead tuples to find live ones. A table that is 20% dead is doing roughly 20% more page reads to return the same rows. Your query plan did not change; the amount of dead weight it drags through did.
• Indexes bloat too. Dead tuples leave dead index entries. Index scans get slower and the indexes grow on disk, sometimes faster than the table.
• Freezing is postponed. Vacuum also freezes old row versions to protect against transaction ID wraparound. A table that rarely vacuums accumulates unfrozen rows, which is how a bloat problem quietly becomes a wraparound emergency. I wrote the runbook for the day that bill comes due in the transaction ID wraparound runbook.

None of this throws an error. The table keeps answering queries. It just does more and more work to do so, and the disk keeps filling with rows that logically do not exist.

Step 1: Measure the bloat you already have

Before tuning, see the current state. pg_stat_user_tables tracks live and dead tuple estimates and the last time each table was vacuumed:

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 3) AS dead_ratio,
  last_autovacuum,
  last_analyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_ratio DESC NULLS LAST
LIMIT 20;

The tables at the top of that list, high dead_ratio and a stale or null last_autovacuum, are the ones the defaults are failing. A dead ratio consistently above about 0.1 to 0.2 on a table you write to often is your signal that the trigger point is set too high for that table's churn.

Step 2: Tune the hot tables individually

Do not reach for the global postgresql.conf first. Lowering autovacuum_vacuum_scale_factor cluster-wide makes every quiet lookup table vacuum more often for no benefit and can starve autovacuum workers. Instead, set storage parameters on the specific tables that churn:

-- A high-write table: vacuum at ~5% dead instead of 20%
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 1000
);

-- A very large, very hot table: pin to an absolute count with a tiny scale factor
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.0,
  autovacuum_vacuum_threshold = 50000
);

Setting scale_factor to 0.0 with a fixed threshold is the trick for huge tables: it decouples the trigger from the row count entirely, so a 50-million-row table vacuums every 50,000 dead tuples instead of every ten million. That keeps the dead ratio low and each vacuum run small and cheap, rather than rare and enormous.

Match the analyze settings too, because stale statistics degrade plans just like bloat does:

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

Step 3: Make autovacuum's work visible

You cannot tell whether vacuum is keeping up if you never see it run. Turn on autovacuum logging so every run records its duration and how many tuples it removed. In postgresql.conf (or per-session for testing):

# Log every autovacuum that takes longer than 0ms, i.e. all of them
log_autovacuum_min_duration = 0

Reload and watch the log. You will see lines like this for each run:

LOG:  automatic vacuum of table "shop.public.orders": index scans: 1
  pages: 0 removed, 41230 remain
  tuples: 48901 removed, 998233 remain, 402 are dead but not yet removable
  elapsed time: 2.14 s

Two things to read here. tuples: N removed confirms vacuum is actually reclaiming dead rows. dead but not yet removable is the number it could not reclaim because an old transaction or replication slot still needs them, and if that number is large and growing, no amount of autovacuum tuning will help until you deal with the long-running transaction holding it back.

Verification

After tuning, prove the dead ratio actually comes down and stays down. Re-run the measurement query from Step 1 over the following days on the tables you changed:

SELECT relname, n_dead_tup, n_live_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 3) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'events')
ORDER BY relname;

Success looks like a dead_ratio that now oscillates in a low band (say under 0.05 for a table you tuned to 5%) with a last_autovacuum timestamp that keeps advancing. Before, the ratio climbed steadily and last_autovacuum was hours or days stale. After, it sawtooths: dead tuples accumulate, autovacuum fires, they drop, repeat. That sawtooth is the shape of a healthy table.

To recover space from a table that is already badly bloated, tuning alone will not shrink it; autovacuum reclaims tuples for reuse but does not usually return disk to the OS. Use VACUUM (VERBOSE) orders to see the state, and for a one-time reclaim consider pg_repack, which rebuilds the table without the long exclusive lock that VACUUM FULL takes.

What people get wrong

Running VACUUM FULL on a live table to fix bloat. This one is dangerous. VACUUM FULL takes an ACCESS EXCLUSIVE lock for its entire duration, which blocks every read and write on the table until it finishes rewriting the whole thing. On a large busy table that is an outage. It does reclaim disk to the OS, which regular vacuum does not, but reach for pg_repack for that instead, and fix the trigger settings so you never need the emergency reclaim again.

Disabling autovacuum because it "causes load". People see autovacuum in pg_stat_activity during a slow period and turn it off. This is the single worst thing you can do. Autovacuum load is the price of not having a wraparound shutdown and a table that is 60% dead. If autovacuum is too disruptive, tune its cost delay and worker count, do not switch off the thing keeping your tables alive.

Cranking the global scale factor to a tiny value. Setting autovacuum_vacuum_scale_factor = 0.01 cluster-wide feels aggressive and correct, but it makes every tiny lookup table vacuum constantly and can leave your handful of autovacuum workers permanently busy on trivial tables while the giant hot one waits in line. Tune per table. The cluster default should stay conservative.

When it is still broken

1. Check for a long-running transaction or stale replication slot. Vacuum cannot remove dead tuples that a still-open transaction might need. Query pg_stat_activity for old xact_start values and pg_replication_slots for inactive slots. A single forgotten BEGIN in a psql session can hold back vacuum across the whole database.
2. Confirm you have enough autovacuum workers. The default autovacuum_max_workers is 3. On a cluster with many churny tables they queue up. Raise it, and raise autovacuum_vacuum_cost_limit so each worker is allowed to do more work per round before pausing.
3. Look at index bloat separately. A table can vacuum well while its indexes stay bloated from before you tuned it. Rebuild them concurrently with REINDEX INDEX CONCURRENTLY to avoid locking.
4. Correlate with the slow query itself. If a specific query is slow, confirm bloat is actually the cause and not a missing index or a bad plan. The same "read the real cost, not the symptom" method applies here as in diagnosing a slow SELECT when disk reads look low; check the plan and the pages read, not just the wall-clock time.

Bloat is the rare production problem that gives you no warning shot, which is exactly why it deserves a setting change up front rather than a heroic recovery later. Lower the scale factor on your write-heavy tables, watch the dead ratio settle into a sawtooth, and turn on autovacuum logging so the next time someone asks why a query got slow, you already have the answer on a graph.