Table and Index Bloat

Dead tuples from UPDATE and DELETE operations accumulate in PostgreSQL heap pages and index pages when autovacuum cannot reclaim them fast enough, causing table and index storage to grow well beyond the live data size and degrading query performance through wasted I/O on dead pages.

PostgreSQL's MVCC implementation never overwrites an existing row. An UPDATE creates a new tuple version in the heap and marks the old version as dead. A DELETE marks the row as dead without removing the physical space. Dead tuples remain in the heap pages, occupying disk space and participating in sequential scans until VACUUM reclaims them.

VACUUM marks dead tuple space as reusable but does not compact pages or return disk space to the OS: that requires VACUUM FULL, which acquires ACCESS EXCLUSIVE lock and is operationally risky on large tables. Dead index entries pointing to dead tuples are also only cleaned up by VACUUM.

Bloat occurs when the dead tuple accumulation rate exceeds VACUUM's processing rate. The primary causes: autovacuum is triggered by dead tuple count thresholds (autovacuum_vacuum_threshold = 50 rows + autovacuum_vacuum_scale_factor × table size, default scale_factor = 0.2 meaning 20% of the table must be dead before vacuum triggers). For a 100M-row table, vacuum triggers after 20M dead tuples accumulate. A 10,000 updates/second workload generates 10,000 dead tuples/second and 864M dead tuples/day: far exceeding the threshold.

Long-running transactions prevent vacuum from advancing the oldest transaction horizon (relfrozenxid). Even if autovacuum runs, it cannot remove dead tuples visible to any open transaction. A single read-heavy analytics query holding a snapshot for 2 hours prevents all vacuum progress on any table written to in those 2 hours.

The operational consequence: table size grows without a corresponding growth in row count. Index size grows without new entries. Sequential scans read dead pages. Index scans return dead index entries that require heap fetches to validate, wasting I/O. Storage costs increase. Cache efficiency drops as the buffer pool caches dead pages at the expense of live data. Query performance degrades gradually over weeks as bloat grows.

This entire mechanism, heap dead tuples plus dead index entries reclaimed by VACUUM, is specific to PostgreSQL's storage model, and "run VACUUM" does not generalize to every engine. PostgreSQL uses a heap table that is not clustered by its primary key by default: every index, including the primary key index, is a separate structure pointing at heap tuple locations (ctid), so an UPDATE leaves dead space in both the heap and every index, and both need VACUUM.

InnoDB is structurally different: it is a clustered index engine, meaning the primary key index IS the table, rows are stored in primary-key order inside the B-Tree leaf pages themselves, there is no separate heap. "Bloat" in the PostgreSQL sense (a dead row occupying heap space until VACUUM) does not apply the same way, because InnoDB reclaims old row versions via its own undo log purge thread, not VACUUM (PostgreSQL has no purge thread and InnoDB has no VACUUM; the two are not interchangeable names for the same process). What InnoDB does experience is secondary-index fragmentation: every secondary index stores the indexed column plus the primary key value (not a physical row pointer), so a row that moves within the clustered index, or a page split from insert/update pressure, leaves secondary-index pages fragmented and requires an extra clustered-index lookup by primary key to fetch the full row. The operational fix is also different: OPTIMIZE TABLE rebuilds the table and its indexes, there is no InnoDB VACUUM to tune the way autovacuum_vacuum_scale_factor is tuned.

An LSM (log-structured merge-tree) engine, such as Cassandra's or ScyllaDB's SSTable storage, has no "bloat" in this sense at all, because it has no in-place update to leave dead space behind. A DELETE writes a tombstone, a marker that a key is deleted, rather than removing anything, and an UPDATE writes a new immutable SSTable entry rather than modifying one in place; older versions of a key and its tombstones persist across however many SSTables they landed in until compaction merges those SSTables and drops the obsolete versions. There is no VACUUM-equivalent command to run: reclamation happens only as a side effect of compaction, on compaction's schedule, not an operator-triggered one. The operational signal is also different: instead of a bloat ratio on a table, the signal is tombstone and SSTable accumulation, and a workload with heavy deletes and infrequent compaction can fail reads outright (Cassandra's tombstone_failure_threshold aborts a read that scans too many tombstones) rather than merely degrading, which has no PostgreSQL or InnoDB analogue. See lsm_compaction_debt for the compaction-backlog failure this produces.