How would you determine whether a specific index in a production database is genuinely bloated, rather than simply large or slow for some other reason?

## Bloat is a ratio "This index is 40 GB" is not a finding. Forty gigabytes might be exactly right for a billion rows on a composite key. Bloat means **the index occupies substantially more pages than its live contents require**, so a diagnosis needs a numerator and a denominator. ## Estimating the denominator: what it should cost For each live row that the index covers, an entry costs roughly: `key bytes + row-pointer bytes + per-entry header/slot overhead` Multiply by live rows, divide by usable bytes per page (page size minus page header and any reserved space), and you get the leaf pages a freshly packed index would need. Add a small allowance for internal pages — with a fanout in the hundreds, internal levels are typically 1% or less of leaf size, so they rarely change the verdict. Be careful with the live row count: use a real count or a trusted statistic, not a stale estimate, and account for partial indexes covering only a subset of rows and for entries omitted when a key is null. ## Measuring the numerator: what it actually costs Every engine exposes the physical size or page count of an index in its catalog or system views. That is the numerator. Do not compare it against the *table* size — a small table with a wide composite index legitimately has an index larger than the heap. ## Reading the ratio - **~1.0–1.2×** — freshly built or append-only. Healthy. - **~1.2–1.5×** — normal steady state for an actively written index, since random churn settles near two-thirds density. Healthy. - **~2×** — worth watching; look at the trend rather than acting immediately. - **3× and up** — genuine bloat. Investigate the cause before rebuilding. Estimates carry error, especially with variable-length keys, so treat the number as a strong signal rather than a precise measurement. ## Better evidence, where available Most engines can report page-level statistics for an index: average leaf density, count of dead or reclaimable entries, count of empty pages, tree height, and the ratio between logical (key) leaf order and physical page order. That last one is the fragmentation measure — an index can be dense yet badly ordered, which hurts sequential scan throughput without inflating size at all. These reads scan the index, so schedule them off-peak on large objects. ## The trend beats the snapshot The cleanest evidence is time series. Record index size and table live-row count on a schedule. A healthy index's size tracks its row count. Bloat looks like size climbing while live rows are flat or falling. That divergence also dates the onset, which often points straight at the cause — a bulk delete, a new update pattern, a long-lived reporting transaction that started blocking cleanup. ## Ruling out the impostors Before blaming bloat for a slow query, check the alternatives, because rebuilding an index is disruptive and fixes none of them: - **Plan change** — the optimizer switched access paths. Compare plans, not just timings. - **Stale statistics** — cardinality estimates drifted and the planner is choosing badly. - **Lock or latch waits** — the time is spent waiting, not reading. - **Cold cache** — a restart or a competing workload evicted the index. - **Genuine growth** — the table really did get bigger. - **Wrong index** — the query needs a different key order or a covering column; a compact index that doesn't fit the predicate is still slow. A useful discriminator: bloat degrades scan-shaped and cache-sensitive work gradually, while leaving single-row lookups by unique key almost untouched. A sudden cliff, or uniform slowness including point lookups, points elsewhere. ## Find the cause, not just the symptom When the numbers do say bloat, ask why, because a rebuild only resets the clock: - Is background reclamation keeping up, or is it starved of resources? - Is a long-running transaction, an abandoned session left open inside a transaction, or a stale replication slot or standby feedback holding an old snapshot alive and preventing cleanup? - Is the workload structurally hostile — a queue table, or bulk deletes by date that would be better served by dropping a partition? - Is the index even used? An unused index bloats, costs on every write, and should be dropped rather than maintained. Most engines track per-index usage counters; check them before scheduling maintenance on something nobody reads. ## Reporting the finding A good diagnosis states: measured size versus estimated size, the trend, the affected query shape and its page-level I/O, the suspected cause, and what happens if nothing is done. That is what makes the difference between "the index is bloated" and a case for a maintenance window.