On a busy production database, how would you identify indexes that are unused or redundant, and what would you verify before dropping one?

## Why this is a real job, not housekeeping Indexes accumulate. A report needs a filter, someone adds an index; a slow query appears, someone adds another; a migration adds one "just in case". Nothing ever removes them, because removal feels risky and adding felt safe. The result is a table where every insert fans out across a dozen structures, the working set no longer fits in memory, and write latency has crept up over quarters in a way nobody can attribute to a single change. Meanwhile most of those indexes serve nothing. ## Finding unused indexes Every mainstream engine maintains per-index usage counters — how many times an index was scanned or sought, and how many rows were read through it. The method is: 1. **Snapshot or reset** the counters and record the timestamp. 2. **Wait a full business cycle.** This is the step people skip. Weekly reports, month-end closes, quarterly compliance extracts, and annual jobs all use indexes that look dead on a seven-day window. A month is a reasonable floor; a quarter is safer for financial systems. 3. **Collect from every node.** Counters are local. A read replica serving analytics uses a completely different set of indexes than the primary, and dropping based on the primary's counters alone will break the replica's workload. 4. **Check for resets.** A restart, failover, or crash zeroes the counters. A "zero uses" reading from a counter that reset yesterday means nothing. Also rank candidates by cost, not just by zero usage: a large unused index on a hot write table is worth removing far more than a tiny unused index on a static lookup table. ## Finding redundant indexes Redundancy is found by reading definitions, not counters: - **Prefix redundancy.** With `(customer_id, created_at)` present, a separate index on `(customer_id)` is almost always redundant — the composite serves every predicate the single-column one does, because a B+Tree can be probed on a leftmost prefix of its key. The narrow one is slightly smaller and thus slightly cheaper to scan, but rarely enough to justify its write cost. - **Exact duplicates.** The same column list under two names, created by two migrations. Pure waste. - **Near-duplicates.** `(a, b)` and `(a, c)`: not redundant — each serves different second-column predicates — but a candidate for consolidation into `(a, b, c)` *only* if the query patterns permit, which needs checking rather than assuming. - **Column-order duplicates.** `(a, b)` and `(b, a)` are genuinely different indexes serving different leading predicates; do not treat them as duplicates. Be careful with one exception: a narrow index is sometimes kept deliberately because it is much smaller and stays cached for a very hot lookup, or because it enables an index-only read that the wider one also enables but at a larger footprint. Those are measurable claims and should be measured, not assumed. ## The verification checklist before dropping 1. **Constraint backing.** Unique indexes enforce uniqueness; primary key indexes enforce identity. Dropping them removes a data-integrity guarantee, not just a performance path. Foreign-key relationships may also depend on an index on the referencing or referenced side, and in some engines removing it turns every referential check into a scan. 2. **Replica coverage.** Confirmed for every node in the topology. 3. **Rare-but-critical jobs.** Search the codebase and the scheduler for queries against the table. An index used twice a year by the regulatory extract is still load-bearing. 4. **Counter integrity.** Verify uptime exceeds the observation window. 5. **Reversibility.** Capture the exact creation statement, including column order, uniqueness, included columns, and any predicate. Rebuilding a large index later can take hours, so know that cost before you take the risk. ## Executing the drop - **Prefer a soft disable.** Several engines can mark an index invisible or disabled: the optimizer stops considering it while the index continues to be maintained. That gives a true production test with instant rollback. The catch is that maintenance cost continues during the trial, so it validates the read side, not the write saving. - **Drop in a low-traffic window** and prefer a non-blocking form if the engine offers one, since dropping can require a heavy lock. - **One at a time**, so that any regression has an obvious cause. - **Monitor afterwards**: plan shape and latency for the table's top queries, plus write throughput and index maintenance metrics to confirm the expected benefit actually appeared. ## Framing the payoff The benefit of a drop is not abstract tidiness. It is: fewer structures modified per write, lower log volume, less to ship to replicas, a smaller working set with a better cache hit rate, less background reclaim work, faster backups and restores, and fewer candidate paths for the optimizer to consider. On a write-heavy table, removing four dead indexes out of twelve is a measurable throughput change, and it is one of the few tuning actions that helps writes without hurting reads.