Tune Autovacuum for Bloat

Your PostgreSQL database was snappy last month. Today, a simple UPDATE on a 10 GB table takes five seconds, disk usage has doubled, and your nightly VACUUM job is barely keeping up. The culprit is almost certainly table bloat — dead tuples piling up faster than autovacuum can reclaim them. The good news? You don't need to rebuild the table. You need to tune autovacuum for table bloat, and this lesson will show you exactly how.

The problem this lesson solves

Every UPDATE and DELETE in PostgreSQL leaves behind a dead tuple — an old version of a row that is no longer visible to any transaction. Dead tuples are not immediately removed; they remain in the table until a VACUUM operation sweeps them away. This is how MVCC works, and it's what makes PostgreSQL's concurrency model so powerful.

But if dead tuples accumulate faster than they're cleaned, you get table bloat. The table grows physically, even though the number of live rows stays the same. Queries slow down because the database must scan through more pages. Indexes bloat too, making lookups slower. Disk space fills up. And if autovacuum is too aggressive, it can cause I/O storms that hurt performance even more.

The challenge: autovacuum's defaults are conservative. They work fine for small tables or low-write workloads, but they are rarely optimal for production systems with heavy updates. This lesson teaches you how to tune autovacuum for table bloat — not by disabling it (a common mistake), but by adjusting its thresholds, costs, and scheduling so it runs when it should, and doesn't run when it shouldn't.

By the end, you'll be able to diagnose bloat, measure it, and set autovacuum parameters that keep your tables lean without overwhelming your server.

Core concept / mental model

Think of autovacuum as a garbage collector for your database. It runs in the background, periodically scanning tables, removing dead tuples, and updating statistics. It's like a janitor who comes in at night to clean the office. If the janitor comes too rarely, papers pile up (bloat). If the janitor comes too often, they disrupt everyone's work (I/O overhead).

Key parameters at a glance

Autovacuum is controlled by a handful of settings that you can tune at the server level (postgresql.conf) or per-table (via ALTER TABLE). The most important ones:

• autovacuum_vacuum_threshold: The minimum number of dead tuples before a vacuum is triggered on a table.
• autovacuum_vacuum_scale_factor: A fraction of the table size that gets added to the threshold. Together, they define the trigger point: threshold + (scale_factor * reltuples).
• autovacuum_vacuum_cost_delay: The sleep time between vacuum I/O operations, in milliseconds. A higher delay reduces I/O impact but slows down the vacuum.
• autovacuum_vacuum_cost_limit: The maximum I/O cost that a vacuum can accumulate before it sleeps. Lower limits make vacuum less aggressive.
• autovacuum_naptime: The minimum delay between two runs of autovacuum on the same database.

The bloat formula

Bloat is essentially the ratio of dead tuples to live tuples. A table with 1 million live rows and 500,000 dead rows has a bloat percentage of roughly 33%. Autovacuum's job is to keep that percentage low.

Here's the mental model in words: autovacuum monitors each table's pg_stat_user_tables counters. When the number of dead tuples (n_dead_tup) exceeds the threshold, it schedules a vacuum for that table. The vacuum scans the table, removes dead tuples, and updates the statistics. The cost mechanism throttles the vacuum so it doesn't starve other queries.

How it works step by step

Let's walk through the lifecycle of a vacuum and how tuning alters each step.

1. Monitoring

PostgreSQL's statistics collector tracks live and dead tuples per table. You can see this with:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

This is your first diagnostic step. If you see a table with a large n_dead_tup and no last_autovacuum timestamp, autovacuum either hasn't been triggered or hasn't run recently.

2. Triggering

Autovacuum wakes up every autovacuum_naptime (default 1 minute) and checks each table. For each table, it compares n_dead_tup against the trigger point:

autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * reltuples)

For example, with default settings (threshold = 50, scale_factor = 0.2), a table with 100,000 rows triggers vacuum at 50 + 0.2 * 100,000 = 20,050 dead tuples. That means autovacuum waits until 20% of the table is dead. For a large table, that can be a lot of bloat.

3. Executing

Once triggered, autovacuum runs a VACUUM (not VACUUM FULL). This scans the table, removes dead tuples, and updates the free space map. It does not rebuild the table or compact it — that's VACUUM FULL, which locks the table and should be run manually.

During execution, the cost-based delay throttles I/O. The vacuum accumulates cost based on operations (reading pages, writing pages, etc.) and when it reaches autovacuum_vacuum_cost_limit, it sleeps for autovacuum_vacuum_cost_delay milliseconds. Lowering the delay (or raising the limit) makes the vacuum faster but more intrusive.

4. Updating statistics

Finally, autovacuum updates the table's statistics (unless it's VACUUM only, not ANALYZE — but autovacuum runs both by default). This helps the planner make better decisions.

Tuning levers

• To trigger more often: lower the threshold or scale factor.
• To trigger less often (e.g., for a small, frequently accessed table): raise the threshold.
• To reduce I/O impact: raise cost_delay (up to 100ms or more) or lower cost_limit.
• To make autovacuum finish faster: lower cost_delay to 0 (no sleep) or raise cost_limit.

Per-table overrides

You can override global settings per table. This is crucial for mixed workloads — a large orders table may need aggressive autovacuum, while a tiny config table should never be vacuumed at all.

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE config SET (autovacuum_vacuum_threshold = 10000);

These settings override the global ones for that table.

Hands-on walkthrough

Let's put this into practice. First, we'll set up a test scenario with simulated bloat, then tune autovacuum, and finally verify the improvement.

Step 1: Create a test table and generate bloat

Connect to your PostgreSQL instance (or use a sandbox like DB Fiddle). Run:

-- Create a table with some rows
CREATE TABLE bloat_test (id serial PRIMARY KEY, data text);
INSERT INTO bloat_test (data) SELECT repeat('x', 100) FROM generate_series(1, 100000);

-- Simulate heavy update activity (60% of rows)
UPDATE bloat_test SET data = repeat('y', 100) WHERE id % 3 = 0;

Now check the dead tuple count:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'bloat_test';

You should see a n_dead_tup around 33,000 (the updated rows) and last_autovacuum likely NULL if autovacuum hasn't triggered yet (with default threshold 50 and scale 0.2, it needs 50 + 0.2*100,000 = 20,050 dead tuples, so it should trigger). If it hasn't run, wait a minute or manually trigger it for comparison.

Step 2: Measure bloat

Bloat isn't directly visible in pg_stat_user_tables, but you can estimate it using the pgstattuple extension:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('bloat_test');

Look at dead_tuple_percent — that's your bloat percentage. With the above updates, it should be around 20-30%.

Step 3: Tune autovacuum for that table

Let's make autovacuum more aggressive for this table to prevent bloat from accumulating:

ALTER TABLE bloat_test SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE bloat_test SET (autovacuum_vacuum_threshold = 1000);

Now the trigger point becomes 1000 + 0.05 * 100000 = 6000 dead tuples — much lower than the default 20,050. Also, you might want to reduce the cost delay to speed up the vacuum:

ALTER TABLE bloat_test SET (autovacuum_vacuum_cost_delay = 5);

Step 4: Verify the effect

Trigger another round of updates and watch how autovacuum responds:

UPDATE bloat_test SET data = repeat('z', 100) WHERE id % 2 = 0;

-- Wait a moment, then check
SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'bloat_test';

You should see last_autovacuum populated much sooner than before, and n_dead_tup staying closer to 0.

You can also check the bloat again:

SELECT * FROM pgstattuple('bloat_test');

dead_tuple_percent should be dramatically lower (likely 0 or near 0).

Expected output

After tuning, your pgstattuple output might look like:

 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
    3.6 MB  |      100000 |    10 MB  |        90.0   |                0 |              0 |                  0  |       0.4MB |        10.0

Notice the dead_tuple_percent is now zero — the bloat is gone.

Compare options / when to choose what

Tuning autovacuum is not one-size-fits-all. Here's a comparison of common strategies:

When to choose what

• For OLTP systems with frequent updates: use per-table tuning with a low scale factor (0.01–0.05) for hot tables.
• For large tables (1M+ rows): lower the scale factor to avoid waiting for 20% dead. Use ALTER TABLE to set autovacuum_vacuum_scale_factor = 0.01.
• For small tables (under 10k rows): keep the default threshold (50) — it's fine.
• For batch processing that causes mass updates: consider running VACUUM (ANALYZE) manually after the batch, and keep autovacuum disabled during that window to avoid I/O contention.

Alternatives to autovacuum tweaking

• pg_repack — rebuilds tables online to reclaim space, but requires a maintenance window.
• Table partitioning — splits large tables into smaller ones, each with its own autovacuum settings.
• Fillfactor — lowering fillfactor on a table (e.g., to 70%) leaves spare space for in-place updates, reducing the need for additional bloat.

Each has trade-offs; tuning autovacuum is usually the first line of defense.

Troubleshooting & edge cases

Autovacuum never runs

If last_autovacuum is NULL on a busy table, check:

• Is autovacuum enabled? SHOW autovacuum; should return on.
• Is the table excluded? ALTER TABLE ... SET (autovacuum_enabled = false) can disable it.
• Is the trigger point too high? For a table with 10M rows, default threshold is 50 + 0.2 * 10M = 2M dead tuples — it might take weeks to trigger.

Autovacuum runs too aggressively

If your I/O is spiking, increase autovacuum_vacuum_cost_delay (e.g., to 20ms) or lower autovacuum_vacuum_cost_limit. You can also set a per-table higher delay for critical tables.

Vacuum still doesn't remove bloat

Sometimes bloat is caused by long-running transactions that hold snapshots. A vacuum can't remove dead tuples that are still visible to an open transaction. Check:

SELECT pid, state, now() - xact_start AS duration
FROM pg_stat_activity
WHERE state = 'idle in transaction' OR state = 'active';

Kill or commit those transactions, then run VACUUM manually.

Autovacuum runs but table still grows

This can happen if the vacuum isn't completing. Check logs for errors, or if the table has a high fillfactor, updates create new tuples anyway. Consider VACUUM FULL — but beware of the lock.

Common pitfalls

• Setting autovacuum_vacuum_scale_factor = 0 without also setting a reasonable threshold can cause autovacuum to run on every tiny change, increasing overhead.
• Copying settings from another server without understanding your workload. Always test per-table first.
• Forgetting that ALTER TABLE changes take effect only for new vacuums — the next autovacuum cycle picks them up, but if the table is currently being vacuumed, it won't be interrupted.

What you learned & what's next

You've learned how to tune autovacuum for table bloat: how to monitor dead tuples, trigger autovacuum precisely, and adjust cost-based throttling to match your workload. You can now:

• Explain the core idea: autovacuum removes dead tuples based on threshold and scale factor, while cost delays control its I/O footprint.
• Apply the exercise: you created a table, generated bloat, measured it with pgstattuple, tuned autovacuum per-table, and verified the bloat was reclaimed.
• Connect to the next lesson: bloat is one symptom of deeper issues — the next step is learning about VACUUM FULL and when to use it, or moving on to index maintenance to prevent index bloat.

Remember: tuning autovacuum is an ongoing process. Monitor your tables regularly with pg_stat_user_tables, and adjust as your workload evolves.

Now, go ahead and tune your own tables — you have the tools to keep them lean and fast.