A query that ran in two milliseconds a year ago now takes eleven. The plan is unchanged — it still uses the index you expect. The table has grown, but only by a third, and eleven milliseconds is not a third worse than two.
The index has grown by 300%. Most of that growth is empty space and entries pointing at rows that no longer exist. The lookup still works; it just reads several times as many pages to do the same job, and those pages are pushing genuinely useful data out of memory.
What changed in 2026
- Concurrent rebuilds became reliably available. Rebuilding an index without blocking writes moved from a specialist operation to something you can schedule during business hours.
- Bloat monitoring reached default dashboards. Managed database offerings started surfacing index size against estimated live size, which is the measurement most teams previously never took.
- High-churn workloads got more common. Status columns, counters, and timestamps updated constantly — the exact pattern that bloats indexes fastest — are everywhere in event-driven systems.
- Automated maintenance improved without solving it. Background cleanup keeps up with typical churn and still loses against sustained update-heavy workloads.
Where the bloat comes from
Two mechanisms, and they compound.
Dead entries from row versions. Under MVCC, updating a row creates a new version rather than editing in place. The index needs an entry pointing at the new version, and the old entry remains until cleanup determines nobody can see it. An index on a column updated frequently accumulates these continuously — and note that this happens even when the indexed value did not change, because the row moved. MVCC explained covers the underlying mechanism.
Page splits. B-tree indexes store entries in fixed-size pages. Insert into a full page and it splits into two half-full pages. Delete most entries from those pages afterwards and you are left with pages holding a handful of entries each. The pages stay allocated. A logically small index can occupy many mostly-empty pages, and ordinary cleanup reclaims the dead entries without consolidating the pages.
That second mechanism is why indexes typically bloat worse than their tables. Table cleanup can reuse freed space for new rows; index pages are ordered by key, so a page can only accept entries that belong in its key range.
Why it costs more than disk
Disk is cheap and this is not really a disk problem.
| Effect |
Consequence |
| More pages per lookup |
Each query does more I/O |
| Larger memory footprint |
Index competes for buffer cache |
| Useful data evicted |
Other queries slow down too |
| Longer maintenance |
Cleanup takes longer, falls further behind |
| Slower backups |
Backing up mostly-empty pages |
The cache row is the one that matters. A database's speed comes largely from serving reads out of memory. An index three times larger than necessary occupies three times the cache for the same information, evicting data other queries needed. The visible symptom is often not the query using the bloated index — it is unrelated queries getting slower because their working set no longer fits.
Detecting and fixing it
Measure before acting. Compare an index's actual size to a rough estimate of what its live entries require. A ratio around 1.2 is normal and healthy. Meaningfully above 2 is worth attention. Most databases have a standard query or extension for this, and managed offerings increasingly show it directly.
Also check whether the index is used at all. Unused indexes are pure cost — write overhead, space, and cache pressure — and dropping one is a bigger win than rebuilding it.
Rebuild concurrently. The concurrent form builds a fresh copy alongside the old one and swaps at the end, taking only a brief lock. The non-concurrent form takes a lock that blocks all writes to the table for the duration, which on a large index in production is an outage. The concurrent version is slower and uses more space temporarily; use it anyway.
Two caveats: a concurrent rebuild can fail partway and leave an invalid index behind, so check afterwards and drop any that failed. And a rebuild is pointless if the workload that caused the bloat continues unchanged — you will be back in three months.
Address the cause where you can. An index on a column updated on every request will bloat, permanently. Sometimes the right fix is not indexing that column, moving the high-churn field to a separate narrow table, or accepting a scheduled rebuild as ongoing maintenance for that one index.
Common mistakes
- Rebuilding without measuring. Most indexes are fine; you are spending I/O to fix nothing.
- Using the non-concurrent rebuild in production. Blocks writes for the entire operation.
- Ignoring unused indexes. Dropping beats rebuilding, and it is permanent.
- Assuming table cleanup handles indexes. It removes dead entries and does not consolidate half-empty pages.
- Not checking for failed concurrent rebuilds. An invalid index is skipped by the planner, so queries silently get slower.
- Rebuilding while a long transaction is open. Cleanup cannot reclaim anything pinned by it, so bloat returns immediately — see MVCC explained.
- Over-indexing. Every index bloats. Fewer, better-chosen indexes beat many marginal ones.
FAQ
How often should I rebuild?
Rarely on schedule, and by measurement. Track the ratio and rebuild the specific indexes that cross your threshold. A blanket monthly rebuild of everything wastes I/O on indexes that were fine.
Does this happen in every database?
The mechanisms differ by engine — where old versions live and how pages are managed varies considerably — but B-tree page splits and dead entries are near-universal. The remedies differ in name more than in substance.
Will bloat affect the query plan?
Indirectly and importantly. The planner uses index size in its cost estimates, so a sufficiently bloated index can look expensive enough that the planner stops choosing it — and a sequential scan replaces your index without anything appearing broken. See query planners.
Is a smaller index always better?
Smaller for the same coverage, yes. A partial index covering only the rows you query is often dramatically smaller and bloats less, because fewer updates touch it. That is frequently the better fix.
Where to go next
For the version churn that produces dead entries, read MVCC explained. For how the planner weighs a bloated index, query planners, and for choosing which indexes to have at all, database indexing explained.