Lesson 27 · Senior PostgreSQL backend engineering

Bloat, HOT updates, and autovacuum

The write-heavy side of PostgreSQL: dead tuples, cleanup pressure, and why “it still works” can still be expensive.

Your win: explain how write-heavy tables accumulate cleanup debt, why bloat happens, and how HOT updates and autovacuum affect real backend cost.

In plain English Plain English: every UPDATE can leave an old row version behind. If cleanup cannot keep up, the table gets heavier even when the logical number of rows barely changes.

The problem: yesterday’s writes become today’s tax

Write-heavy services often pay for yesterday’s updates today: larger tables, worse cache behavior, more vacuum work, and slower scans. That is why storage behaviour is not just an internals curiosity. It turns into visible backend cost.

The mental model MVCC trades overwrite-free concurrency for cleanup work later. Bloat is the bill that arrives when too many old versions stay around too long.

Three ideas to keep straight

Bloat — too much dead or wasted space relative to live data
HOT update — an update that can avoid touching indexes under the right conditions
Autovacuum — the background system that reclaims dead tuples and refreshes planner stats

The senior explanation is not just “vacuum cleans things.” It is “this write pattern creates this kind of cleanup pressure, and here is why the table keeps getting more expensive to read or maintain.”

Anchor — where this shows up here Soft deletes and repeated updates to tenant-scoped rows make this especially relevant here. Even if the application never hard-deletes, the storage system still has to cope with the churn.

Common mistake Thinking “the row count is stable, so the table is stable.” MVCC storage cost depends on version churn, not just on the number of currently visible rows.

Routine vacuuming docs

The official docs are the best source for how VACUUM and ANALYZE keep MVCC practical at scale.

→ Routine Vacuuming

Check yourself (from memory)

Q1. Bloat usually comes from…

MVCC churn creates dead tuples that vacuum must eventually reclaim.

Why does a write-heavy table get slower even when the app logic did not change?

recall, then click to reveal

Because repeated updates and deletes create dead tuple versions, increase storage and vacuum work, and make scans and caching less efficient over time.

Want a plain-English HOT update walkthrough tied back to indexed columns and soft deletes? Ask me.

Sources. Routine Vacuuming.