Lesson 3. Bloat Diagnosis and Autovacuum Tuning

Mission link: This is stage 2's capstone: lesson 2 explained why dead tuples exist at all; this lesson is diagnosing when they've become a real problem, and finding which of several distinct causes is actually responsible before touching a setting.
Primary source: Wiki: "Show database bloat", PostgreSQL Wiki
Prerequisites: Lesson 2, Write-ahead log (WAL)

Warm-up

1. ▢ Why does a table under heavy UPDATE and DELETE traffic accumulate dead tuples, and why doesn't this resolve on its own?

2. ▢ What does ordinary VACUUM do to a table's reclaimed space, and how does that differ from VACUUM FULL?

Know this

Bloat is a gap that's normal in small amounts and a problem when it grows

Bloat is the gap between a table's actual live data and the storage it occupies on disk, caused by dead tuples accumulating faster than vacuum reclaims them. Some bloat is expected and harmless: space naturally builds up between vacuum runs and gets reclaimed on the next pass. The concern isn't bloat existing at all; it's bloat that keeps growing because vacuum genuinely isn't keeping pace with it.

A quick smell test, then a real measurement

pg_stat_user_tables gives a fast, rough signal: n_dead_tup divided by n_live_tup + n_dead_tup is a directional bloat ratio, not an exact size measurement, but useful as a first check alongside last_autovacuum, when autovacuum last actually ran on that table. For an actual size-based estimate, a dedicated bloat-estimation query (this lesson's primary source provides one) measures the real gap between a table's live data size and its size on disk, which the dead-tuple ratio alone can't precisely capture.

Three distinct causes, in a diagnostic order

A table with growing bloat has one of a few distinct root causes, and finding which one it is means checking in order, not tuning several settings at once:

1. Is autovacuum even running and keeping pace? Check last_autovacuum and whether the dead-tuple ratio is trending up over time or holding roughly steady. If autovacuum hasn't run recently at all, that's the first thing to fix.
2. Is something holding back what vacuum can reclaim, even though it's running? A single long-running transaction, an idle-in-transaction session, an uncommitted prepared transaction, or a stalled replication slot all hold back the oldest snapshot vacuum has to respect: vacuum cannot reclaim any dead tuple newer than that snapshot, no matter how many workers are configured or how aggressive the thresholds are. This is the case that looks the most confusing from the outside: autovacuum logs show it ran, on schedule, and bloat still climbs, because it genuinely couldn't reclaim anything held back by that older transaction. Checking pg_stat_activity for long-running or idle-in-transaction sessions, and pg_replication_slots for a stalled slot, is how this gets found.
3. Is autovacuum triggering too late or running too slowly for this table's actual write rate? The default trigger, autovacuum_vacuum_scale_factor, fires once roughly 20% of a table's rows have changed since the last vacuum. That percentage scales badly for a very large table: 20% of a million-row table is 200,000 dead tuples before autovacuum even considers it, by which point real bloat has already accumulated. Lowering the scale factor, or setting an absolute row-count threshold instead, for specifically large or high-churn tables is the fix once the first two causes are ruled out.

Practice

1. ▢ A table shows n_live_tup = 800,000 and n_dead_tup = 200,000. Compute the bloat ratio, and say whether this figure alone is alarming or merely worth watching.

2. ▢ A table's n_dead_tup is climbing steadily, but last_autovacuum shows it ran recently, meaning autovacuum is actually running. What's a likely cause that has nothing to do with autovacuum's own tuning, and how would you check for it?

3. ▢ Why does a single long-running transaction block vacuum from reclaiming dead tuples newer than it, regardless of how many autovacuum workers are configured or how aggressive the trigger thresholds are?

4. ▢ A large, high-churn table's default autovacuum_vacuum_scale_factor (roughly 20%) is too coarse to keep bloat under control. What's the fix, and why does a percentage-based trigger scale badly specifically for very large tables?

5. ▢ Which claim is true of diagnosing table bloat?

   • a) A high dead-tuple ratio always means autovacuum's thresholds are misconfigured
   • b) A long-running transaction can prevent vacuum from reclaiming dead tuples even while autovacuum is running exactly on schedule
   • c) Autovacuum's default scale factor works equally well regardless of table size
   • d) The bloat ratio computed from pg_stat_user_tables is an exact measurement of disk space wasted

Real-world reps

• [ ] On a Postgres instance you can access, compute the dead-tuple ratio for a few tables from pg_stat_user_tables, and check pg_stat_activity for any long-running or idle-in-transaction sessions that might be holding back vacuum.
• [ ] Run the primary source's bloat-estimation query against a table you suspect is bloated, and compare its estimate to the rough dead-tuple ratio.
• [ ] Tomorrow: find one table's autovacuum_vacuum_scale_factor setting (its own, or the server default) and decide whether it's appropriate for that table's actual size and churn rate.

Going further

• Wiki: "Show database bloat", PostgreSQL Wiki
• Docs: "Routine Vacuuming", PostgreSQL
• Resources

Not landing? Reread the primary source at the top, since this lesson compresses it and compression is where understanding leaks. Check the glossary for any term that felt slippery.

If the lesson itself is unclear rather than the material, that is a defect: open an issue.