Selecting Database Indexes

Index sprawl: years of 'add an index, ship it' left tables with a dozen indexes each — write costs climbing, storage growing, and nobody sure which indexes were load-bearing.

Every index is a bet that read savings outweigh write costs. Nobody had been settling those bets: usage stats showed indexes unused for months still being maintained on every insert.

A quarterly index review driven by the database's own statistics: rank indexes by reads vs writes, flag never-read indexes for staged removal (disable first, drop after a clean cycle), and require new indexes to name the query they serve in the migration description.

Dropping an index is scarier than adding one — the failure mode is a slow query at peak, not a failed deploy. Staged disabling makes the bet reversible and cheap to lose.

Write-heavy tables shed a third of their indexes with no read regressions, and new indexes now arrive with a stated purpose that can be audited later.

An index without a named query is a rumor, not a design decision.

The database keeps score of every bet you made — read the stats.