An index in a busy production database has been measured as heavily bloated. What are the ways to reclaim that space, and what does each cost in terms of locking, extra disk, duration and risk while the system keeps serving traffic?

## First: is a rebuild the right action at all? A rebuild is a large, disruptive write operation. Before scheduling one: - **Is the index used?** Per-index usage counters over a representative window (including month-end and batch jobs) may show zero scans. An unused index that enforces no uniqueness constraint should be dropped, not maintained. - **Why did it bloat?** If a long-running transaction, an idle-in-transaction session, or a stale replication slot is holding an old snapshot and blocking dead-entry reclamation, the fresh index will bloat again within days. Fix the blocker first. - **Is the design the problem?** A table where you bulk-delete a date range every month is asking for partitioning: dropping a partition removes its index segment instantly, with no bloat and no rebuild. ## Option 1 — drop the index Instant, frees all its space, and removes its ongoing write cost. The risk is dropping something that turns out to matter: a rarely-run but critical report, a constraint-backing index, or an index the planner uses only for a specific parameter range. Verify usage over a full business cycle, and know how long recreating it would take if you are wrong. ## Option 2 — in-place reorganize / compact The engine walks the existing index, compacting entries into fewer pages and improving the correspondence between key order and physical order, without materializing a second full copy. Characteristics: - **Space:** minimal extra — this is its main advantage when disk is tight. - **Locking:** typically light, often allowing concurrent reads and writes. - **Interruptibility:** usually resumable or safely abortable, keeping the work already done. - **Result quality:** lower than a full rebuild. It improves density and ordering but rarely reaches the compactness of a freshly built structure. - **Duration:** often longer per page than a rebuild, because it works incrementally against live traffic. Good choice for moderate fragmentation, or when there is not enough free space to hold two copies. ## Option 3 — online / concurrent rebuild The engine builds a brand-new index from current data while the table remains readable and writable, recording concurrent modifications and applying them to the new structure, then swaps the new index for the old one under a brief exclusive lock. Costs and hazards: - **Extra disk:** you need room for the old index, the new index, and any sort workspace, simultaneously. On a large index this is the constraint that most often blocks the plan. - **Duration:** substantially longer than an offline build — commonly several times longer — because it does more passes and yields to concurrent work. - **Write amplification:** the whole new structure is written and logged, which propagates to replicas and to backup volume. On a saturated I/O system this alone can degrade the workload. - **Waiting on transactions:** the build must reach a point where it knows all concurrent changes are captured, which typically means waiting for transactions older than a certain point to finish. One long-running reporting query can stall it for hours. - **Failure mode:** if it is cancelled or errors, some engines leave behind an invalid or partially built index that still consumes space and still costs on writes until it is explicitly dropped. Always plan the cleanup step. - **Deadlock/lock-wait risk at swap time:** the final exclusive lock is brief but real; under heavy write traffic it can queue behind or block other statements. ## Option 4 — offline rebuild Rebuild in place, holding a lock that blocks writes and often reads for the duration. Fastest wall-clock time, best resulting density, simplest failure semantics. Appropriate only in a maintenance window, on a standby that is then promoted, or on small indexes where the lock is measured in seconds. ## Related, larger hammers - **Table-level rewrite.** Rewriting the whole table (a full-table reorganize or a copy-and-swap of the table) rebuilds every index on it at once. Efficient when several indexes on the same table are bloated, but a far bigger operation and usually needs table-level locking or a full online-migration mechanism. - **Rebuild on a replica, then fail over.** Do the disruptive work on a standby, then promote. Removes production impact at the cost of a failover and careful sequencing. - **Per-partition maintenance.** On a partitioned table, rebuild one partition's index segment at a time. This is the single most effective way to make index maintenance survivable at scale: the unit of work becomes small and bounded, and old partitions can often be dropped instead of maintained. ## Choosing Unused → drop. Space-constrained or moderate damage → in-place reorganize. Heavily bloated, space available, must stay online → concurrent rebuild, scheduled during a low-write period, with the cleanup path for a failed attempt written down in advance. Maintenance window available or index small → offline rebuild. Repeatedly bloated → stop rebuilding and change the design. ## Before and after Record the size, density and the latency of the affected query shape beforehand, and again afterwards. Without that, nobody can tell whether the maintenance window bought anything — and "we rebuild everything monthly" becomes tradition instead of engineering.