When a High-Update Table Turned into a Bloat Factory

At 08:17, the first symptom was not a crash. It was a slow shift in behavior: point lookups on *accounts* that used to return in under 10 ms started taking 200 to 800 ms, then spiked over a second during peak traffic. CPU was not the loudest signal at first; I saw rising buffer churn, increasing heap fetches, and the planner leaning harder on indexes that should have been cheap.

By 08:24, the application team was reporting elevated latency on customer reads and a measurable increase in I/O. The scary part was that nothing “looked broken” in the obvious sense. Autovacuum was running, the table was online, and there was no single runaway query dominating the top of *pg_stat_statements*. What was broken was the physical shape of the table itself.

What I saw first

The production symptom set was classic high-bloat behavior:

• Slow reads on a table with frequent updates.
• More heap pages touched per query.
• Index scans doing extra work because the underlying heap had become sparse and fragmented.
• Autovacuum activity that existed, but clearly could not keep up with the write rate.

The table in question was a multi-billion-row *accounts* table with a heavy update pattern: balance flags, last_seen timestamps, KYC state, and risk metadata were all updated repeatedly. In MVCC terms, each UPDATE didn’t overwrite a row in place; it created a new version and left the old one behind as a dead tuple until VACUUM could clean it up.

Why MVCC hurt here

MVCC is what keeps PostgreSQL concurrency sane, but it also means a busy update table can become a graveyard of dead row versions if cleanup lags. The old tuple versions still occupy heap space, still have to be skipped during scans, and still contribute to table and index bloat until vacuum reclaims them.

That mattered because the planner was now paying for all the dead weight. Sequential scans had more pages to read, index scans had more heap visits, and cache efficiency dropped because the hot working set no longer fit neatly in memory.

The checks I ran

I started with *pg_stat_user_tables* and *pg_stat_all_tables*, because I wanted to know whether the table was actually churning or whether I was just seeing a noisy query pattern.

SELECT relname,
       n_live_tup,
       n_dead_tup,
       n_tup_ins,
       n_tup_upd,
       n_tup_del,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'accounts';

The pattern was ugly: *n_tup_upd* was enormous, *n_dead_tup* was high, and *last_autovacuum* was not keeping pace with the rate of change.

I also captured execution plans before and after the slowdown with *EXPLAIN (ANALYZE, BUFFERS)*.

EXPLAIN (ANALYZE, BUFFERS)
SELECT account_id, balance, status
FROM accounts
WHERE account_id = $1;

Before the regression, the query touched a small number of buffers and completed quickly. After the bloat accumulated, the same plan had to read far more heap pages, and the buffer hit pattern got worse even though the SQL text had not changed.

Why autovacuum missed it

Autovacuum did not fail; it just lost the race. On a huge, frequently updated table, the default thresholds can be too conservative for the workload. The table was updated continuously, dead tuples accumulated faster than vacuum cycles completed, and the next cleanup cycle arrived too late to prevent bloat from compounding.

The remediation

The response had four parts:

1. Run a manual *VACUUM (ANALYZE)* on the worst table.
2. Lower autovacuum thresholds for that table.
3. Reduce fillfactor so updates have room to stay on the same page more often.
4. Revisit partitioning, because a table this hot should not have all write churn concentrated in one giant heap forever.

ALTER TABLE accounts SET (
  fillfactor = 80,
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_scale_factor = 0.02
);

Lessons for high-write systems

• A fast update path can create a slow read path if vacuum cannot keep up.
• *n_dead_tup* is a signal, not a curiosity.
• *EXPLAIN (ANALYZE, BUFFERS)* tells you whether the slowdown is really physical.
• Large, hot tables often need custom autovacuum settings instead of defaults.
• Fillfactor is part of the design on update-heavy tables.