Postgres bloat is eating your p99: fix your VACUUM, not your queries

6 min read

If you run PostgreSQL in production and your p99 latency creeps up while CPU and RAM look fine, the answer is almost always PostgreSQL bloat, and the cause is almost always lazy VACUUM and autovacuum settings that were never tuned for your write rate. I have seen this on Monzo-style transaction ledgers, BBC content metadata tables, and HMRC batch jobs alike. The tables balloon, the indexes fragment, seq scans start appearing where index scans used to be, and everyone blames the ORM.

What bloat actually is, and why your VACUUM settings are killing your latency

PostgreSQL's MVCC model never overwrites a row in place. An UPDATE creates a new tuple version and marks the old one as dead. A DELETE marks the row dead. Those dead tuples sit in the heap until VACUUM reclaims them, and until that happens every sequential scan, every index scan, and every visibility check has to skip over them. The table size on disk stops reflecting useful data; it reflects dead data plus the long tail of half-dead indexes.

This is why your latency dies even when your queries have not changed. A SELECT that used to return 50ms suddenly takes 800ms because the heap is 4x larger and the planner switches to a bitmap heap scan that touches 3x more pages. The classic symptom in the UK is a quiet Monday morning where everyone assumes the previous night's batch must have run, but nobody can find it. It is bloat, and it is the direct output of autovacuum not keeping up.

The default autovacuum_vacuum_scale_factor of 0.2 means autovacuum only triggers when 20% of the rows have changed. On a 200 million row table that is 40 million dead tuples before anyone even wakes up. By the time autovacuum does run, it has so much work to do that the cost-based delay settings throttle it, and you bleed latency for hours.

The four knobs that actually matter

Stop tuning the whole autovacuum config blob. Focus on four settings per heavy table, plus one global cost knob. Everything else is noise.

For each hot table, override at the table level (never at the cluster level for a busy OLTP box):

ALTER TABLE public.transactions SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 50,
  autovacuum_vacuum_cost_limit   = 1000,
  autovacuum_analyze_scale_factor = 0.01
);

That 0.02 scale factor means autovacuum kicks in when 2% of rows are dead, not 20%. On a 200M row ledger that is 4M dead tuples, which is a small job VACUUM can chew through in a few seconds. The analyze_scale_factor of 0.01 is the unsung hero: the planner picks the right plan, so you stop getting seq scans on a 50GB table because the stats are 40 minutes stale.

The global knob is autovacuum_vacuum_cost_limit. The default of 200 with a default of 3 autovacuum workers means each worker gets 66 cost units per round, which is fine for a laptop and useless for a 64-core RDS instance. Bump it to 2000-3000, then push the per-table cost limit even higher on the tables that matter, like in the snippet above.

When to use VACUUM (not autovacuum) manually

Autovacuum is throttle-aware and runs in the background. It is the right tool 99% of the time. But after a bulk load, a large migration, or a one-off data fix on a 500M row table, you want a VACUUM (ANALYZE, VERBOSE) big_table during a quiet window. The reason is simple: autovacuum will see the 2% threshold and run a series of small vacuums over the next 6 hours. A single foreground vacuum finishes the work in 20 minutes and your latency stays clean. I have done this at Monzo on the events table and shaved 300ms off p99 within an hour.

Measuring bloat properly before you touch anything

Do not tune blind. Use the pgstattuple extension and the pg_stat_all_tables view together. The catalogue stats lie about size, but they do not lie about dead tuples.

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / greatest(n_live_tup, 1), 3) AS dead_ratio,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC
LIMIT 20;

Anything with a dead_ratio over 0.1 is a problem. Anything over 0.3 is on fire. For real on-disk bloat numbers, install pgstattuple and run SELECT * FROM pgstattuple('public.transactions'). The dead_tuple_percent column is the truth. If it is over 20% and your table is over 50GB, you are paying for storage and latency you are not using.

Index bloat is the silent killer that gets missed. Run pgstatindex on your biggest indexes. An index with 40% free space is doing 4x more random I/O than it should. REINDEX INDEX CONCURRENTLY on a Sunday morning fixes it without locking writes, and combined with the new vacuum settings, it stays fixed.

The fillfactor and vacuum strategy for write-heavy tables

For tables where UPDATEs are the norm (sessions, transactions, anything with a state column), set fillfactor = 70. This leaves 30% of each 8KB page free for HOT updates, which avoids creating index entries and keeps bloat dramatically lower. HOT updates are the single best bloat prevention feature in PostgreSQL and most UK shops have it disabled by default because they never set fillfactor.

Verify HOT is actually working with:

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(n_tup_hot_upd::numeric / greatest(n_tup_upd, 1), 3) AS hot_ratio
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY n_tup_upd DESC;

If hot_ratio is below 0.9 on a table that is supposed to benefit from HOT, you have indexed columns that are being updated. Either drop those indexes, or accept that HOT will not help and rely on aggressive autovacuum instead. The GOV.UK publishing platform team wrote about this exact trade-off when they moved to PostgreSQL 14 and tuned their content table fillfactor to 80.

Watch out for vacuum transaction ID wraparound

If your datfrozenxid age is over 200 million, you are on borrowed time. Autovacuum has a special antiwraparound mode that is much more aggressive and will happily eat your I/O budget. The fix is to prevent the situation: keep the per-table scale factor low, run manual VACUUM FREEZE on large static tables once a year, and monitor pg_stat_activity for the autovacuum launcher during business hours. If you see a wraparound vacuum running on a Tuesday at 2pm, your settings are wrong, not your workload.

The NHS Digital team famously hit this on a 2TB reference table. They now run a weekly VACUUM FREEZE job on all tables over 100GB, and the wraparound alarms have been quiet ever since. The lesson: bloat and wraparound are two sides of the same coin, and aggressive per-table autovacuum is the only thing that solves both.

FAQ

How do I know if my PostgreSQL latency problem is bloat and not the query?

Run pgstattuple on the offending table. If the dead_tuple_percent is over 20% and the table is over 10GB, bloat is contributing significantly. Also check pg_stat_user_tables for n_dead_tup: if it is growing faster than autovacuum is clearing it, you have found your culprit regardless of what EXPLAIN ANALYZE says.

Is aggressive autovacuum safe on a production OLTP database?

Yes, if you tune the cost delay. Bump autovacuum_vacuum_cost_limit to 2000-3000 cluster-wide and set per-table limits to 1000 or higher. The background workers will still yield on I/O pressure, and you will clear bloat in minutes instead of hours. The latency hit is far less than letting bloat accumulate, which costs you on every single read.

Should I use pg_repack or VACUUM FULL to fix bloat?

Use VACUUM FULL only on small tables during a maintenance window, because it takes an exclusive lock. For large production tables, use pg_repack, which rewrites the table online without blocking reads or writes. It is the standard tool for UK fintech and government workloads, and it pairs perfectly with the autovacuum tuning above to keep the table lean after the rebuild.