A team proposes a nightly job that rebuilds every index in the database to "keep things fast." How would you evaluate that policy, and what would you propose instead?

## What the policy actually buys Rebuilding an index restores page density and physical ordering. That helps only where density or ordering has actually degraded. For a large fraction of a typical schema — indexes on ever-increasing keys, indexes on read-mostly reference tables, indexes on tables that are inserted into and never updated — density is already near optimal and the rebuild is a no-op with a very large bill. ## What it costs **I/O and time.** A rebuild reads and writes the entire index. Doing that for every index means rewriting most of the database's index footprint every night. As the database grows, the job's duration grows with it until it no longer fits the window, and a maintenance job that overruns into business hours is worse than no job at all. **Write-ahead log volume.** Every rebuilt page is logged. That log is shipped to every replica and captured by every incremental or differential backup. A nightly full-index rewrite can multiply replication traffic and backup size by an order of magnitude, and replica lag becomes a nightly spike. **Cache displacement.** The rebuild pulls every index page through the buffer cache. By morning, the cache holds the tail of last night's maintenance rather than the working set, so the first business hour is slow — a self-inflicted cold-cache period every day. **Lock and failure exposure.** Whatever locking model the rebuild uses, it is exercised across the entire schema nightly. Concurrent rebuilds can stall on long transactions or fail partway, leaving leftovers; offline rebuilds block traffic. Multiplying the number of operations multiplies the number of chances to hit one of those. **Masked causes.** The most damaging cost is epistemic. If an index bloats badly every week, that is a signal: cleanup is blocked, or the table is a queue, or a column that is updated constantly should not be indexed. Nightly rebuilds erase the signal and let the underlying defect persist for years. ## The alternative: measure, target, fix causes **1. Instrument.** Record per-index physical size and per-table live row count on a schedule. Healthy indexes track their row counts. Compute the ratio of actual size to expected size and watch its trend. Alert on divergence, not on absolute size. **2. Remove before maintaining.** Per-index usage counters, sampled over a full business cycle including month-end and batch jobs, reveal indexes nothing reads. Dropping them removes their bloat, their maintenance and their write cost in one step. Redundant indexes whose leading columns duplicate another index are the same story. This is almost always the largest single win, and it makes every subsequent maintenance decision smaller. **3. Unblock reclamation.** Background cleanup cannot remove dead entries while any snapshot might still need them. Long-running transactions, sessions left idle inside a transaction, stale replication slots, and standbys with feedback enabled all hold that line back. Bound transaction lifetimes, alert on idle-in-transaction age, and monitor slot lag. Fixing this often eliminates the perceived need for routine rebuilds entirely. **4. Change the structures that guarantee bloat.** A table whose old rows are deleted in bulk by date should be partitioned by that date: dropping a partition removes its data and index segments instantly, with no bloat and no maintenance. A queue table processed and emptied continuously may deserve dedicated handling. An index on a column updated on every request is a design question, not a maintenance question. **5. Target the survivors.** After the above, the set of indexes that genuinely need periodic rebuilding is usually small and stable. Maintain those on evidence, choosing the method by available space and tolerance for locking, and schedule them so their write and replication volume lands off-peak. Prefer per-partition work so each unit is bounded, and consider doing the disruptive work on a standby that is then promoted. **6. Prove the benefit.** Capture size, density and the latency of the affected query shape before and after each maintenance action. If a rebuild does not move a metric anybody cares about, that index should come off the list. This is what keeps the policy from decaying back into ritual. ## How to have the conversation The team is not wrong that bloat is real, and dismissing the proposal outright loses the useful instinct behind it. Frame the counter-proposal as strictly more effective for less cost: the same protection against bloat, applied where bloat exists, plus visibility into which indexes are drifting and why. Offer a transition — keep a weekly targeted job while the monitoring is built, then narrow it as data arrives. Agree on the trigger that would justify maintenance (a size-to-expected ratio threshold sustained over time, plus a query-level symptom) so the decision is a rule rather than an argument each month. ## The residual case for scheduled maintenance Some environments genuinely warrant a fixed schedule: small databases where a full rebuild takes minutes and the simplicity is worth more than the efficiency, or systems with a guaranteed quiet window and no replication cost. Judgment means recognising when the simple policy is cheap enough to be right — and when the database has outgrown it.