The Slow Leak: Table and Index Bloat in Every Postgres Database

When dead tuples are reclaimed, the space they occupied is not returned to the operating system. It is marked as free for reuse within the same table or index. If write patterns do not reuse it evenly, the object keeps its enlarged on-disk footprint while holding a fraction of live data. That gap between allocated size and live size is bloat.

Bloat is not a failure of vacuum; it is a normal consequence of how vacuum works. Left unmanaged, it becomes a permanent tax on every operation that touches the object.

Storage is the least of it. A bloated table has more pages, so sequential scans read more, and shared buffers cache proportionally less useful data. Index bloat inflates the tree, adding levels and pages that every lookup must traverse. The same query does strictly more work against a bloated object than a compact one.

The degradation is gradual and compounding, which is exactly why it escapes reactive attention until it is severe.

Bloat can be measured precisely, but precise measurement is expensive: inspecting every tuple means a full scan, which is unacceptable on a busy production table. The practical approach is statistical estimation from catalog metadata, comparing the object's page count against an estimate of how many pages the live rows should occupy.

Estimates are approximate, but they are cheap enough to run continuously and accurate enough to flag the objects that warrant closer, scheduled inspection. Reserving heavy inspection for confirmed suspects keeps monitoring read-only and light.

Reclaiming bloated space is where caution matters most. VACUUM FULL compacts a table by rewriting it, but it takes an exclusive lock for the duration, making the table unavailable. Rebuilding an index with REINDEX has similar implications unless done in its concurrent form, which trades a lock for a longer, more resource-intensive run.

These operations change structure, hold locks, or consume significant resources. They are effective and sometimes necessary, but they are not routine.

Detecting bloat is read-only and continuous. Regular vacuum, which limits future bloat, is a low-risk action that can run automatically under policy. Compacting existing bloat is structural and blocking, so it is proposed with evidence and executed only with explicit DBA approval, in an appropriate window.

The system's job is to catch the leak early, keep it from growing, and hand a human a clear, well-timed decision when a heavier fix is genuinely required, never to surprise production with an exclusive lock.