What is table and index bloat in an MVCC database, what symptoms does it produce, and what actually returns the space — to the object for reuse versus to the operating system?

## Defining bloat Measure two things: the object's **allocated size** on disk, and the **live data size** — roughly live row count times average row width, plus index overhead. Bloat is the difference. A little is healthy: free space in pages absorbs future updates without splitting or relocating rows, which is exactly what a fill factor reserves. A lot means most of your I/O buys nothing. Bloat has two sources: 1. **Dead versions** that cleanup has not yet reclaimed, or cannot reclaim because the visibility horizon is pinned by an old snapshot. 2. **Fragmentation**: pages left partly empty after reclamation, index pages that split and never re-merge, and rows relocated by updates leaving holes behind. Even with perfect cleanup, a heavily churned object does not repack itself. ## Index bloat deserves separate attention Every version of a row generally needs index entries, and expired entries linger until cleaned. B-tree pages split under insert pressure and typically do not merge back when entries are removed, so an index can end up half empty and stay that way. It is routine to find indexes several times larger than necessary while the heap looks acceptable — and since index lookups touch a path of pages, bloat there directly raises the page count per lookup. ## Symptoms, and why they are indirect Nothing reports 'you have bloat'. You infer it: - **Size versus expectation.** Live rows × average width ≪ allocated size. - **I/O per useful row rises.** A scan that used to read 1 GB to return 5 million rows now reads 4 GB for the same result. - **Cache pollution.** The buffer pool holds pages that are largely dead versions, so the effective cache is smaller and miss rates climb — often felt as latency on *unrelated* queries. - **Degrading index performance** despite unchanged data volumes. - **Slower maintenance**: backups, rebuilds and statistics gathering scale with allocated size, not live size. - **Estimates drift**: statistics derived from sampling bloated pages can skew plans. ## Reuse versus return: the crucial distinction Routine cleanup (vacuum, purge) makes dead space **available for reuse within the object**. The file stays the same size, and subsequent inserts and updates consume the freed space rather than extending the file. For a table in steady state this is exactly right — the space would only be re-allocated anyway, and repacking would cost far more than it saves. So after a one-off deletion of 90% of a huge table, the correct expectation is: the file stays big, the table stops growing, and it will absorb roughly that much new data for free. Actually returning space to the operating system requires **rewriting the object**: - a full vacuum or `OPTIMIZE`/`ALTER TABLE … REBUILD` style operation, which writes a fresh compact copy and swaps it in; - rebuilding indexes, often the larger win since indexes fragment most; - dropping a partition or truncating the table, which unlinks whole files and is instant. Rewrites carry real costs. They typically need free disk roughly equal to the object's live size (you hold both copies briefly), they are I/O-heavy, and unless the engine offers a concurrent/online variant they take an exclusive lock for the duration — which on a large table is an outage. Concurrent rebuild variants exist on most engines and are the right default; they trade extra I/O and time for availability. ## Deciding whether to act A useful sequence: 1. **Quantify** the ratio rather than reacting to absolute size. Two-times allocated versus live on a hot, constantly-updated table is often just steady-state working space. 2. **Ask whether it will be reused.** If the table's insert rate will refill the space within days, a rewrite buys nothing but I/O. 3. **Fix the cause first.** If the horizon is pinned by a long transaction, a rewrite is treating a symptom — the table will bloat straight back. 4. **Prefer online/concurrent rebuilds**, and prefer rebuilding indexes to rewriting the whole table when indexes are the bloated part. 5. **Schedule around load** and verify you have the free space for a second copy before starting. ## Prevention beats remediation The designs that avoid bloat entirely: - **Partition by time** and drop old partitions instead of deleting rows. A drop is a metadata operation that returns space instantly with no cleanup work. - **Bound transaction lifetime** so the horizon stays young and cleanup can keep up. - **Do not store hot counters in wide rows.** Frequent updates on a wide table generate large dead versions; move volatile columns into a narrow companion table. - **Batch bulk changes** in bounded transactions. - **Tune cleanup aggressiveness** on high-churn tables so it triggers on a smaller fraction of changed rows rather than a global default suited to quiet tables. - **Trend allocated versus live size** in monitoring, so bloat is visible as a slope long before it becomes a disk alert.