A write-heavy OLTP table has accumulated fourteen indexes, one added for each new report over three years, and insert latency has been climbing. How would you decide what the right number of indexes for that table is?

## Why "how many indexes is too many?" has no numeric answer Asking for a number invites a rule of thumb, and rules of thumb here are wrong because the cost of an index depends on write rate, key distribution, row width, whether the working set fits in memory, and replication topology — while the benefit depends on query frequency, selectivity, and how much latency those queries owe their users. Two tables with identical index counts can be perfectly healthy and completely broken. The right framing is a **budget**: the table has a write service level, index maintenance consumes it, and each index must buy its share of that consumption with read value. ## Establishing the constraint Start from what the write path owes: peak insert/update rate, the latency percentile that matters, and headroom for growth and for failover scenarios where one node carries more. That is the denominator. Without it, every subsequent argument is aesthetic. ## Measuring the current cost Quantify rather than assert: - Structures modified per write, and index maintenance as a share of total write time. - Write-ahead log volume per transaction, which determines both local I/O and replication lag. - Total index size versus table size versus available memory. The single most important threshold is whether the combined hot pages of the table and its indexes still fit in RAM. Past that point each additional index causes physical reads on the write path, and the latency curve bends sharply. - Background reclaim work, which scales with index count on update-heavy tables. Then get the actual shape of the curve: clone to a test environment, replay a representative write workload at 14, 10, 6, and 3 indexes. The result is usually not linear, and knowing where the knee sits converts the debate into arithmetic. ## Attribution: every index needs a claimant Run the usage-counter exercise across every node for a full business cycle — long enough to include monthly and quarterly jobs. For each index produce: which queries use it, how often, what latency they owe, and who owns them. Then classify: - **Constraint-bearing** (primary key, unique): not negotiable, they are data integrity. - **Hot-path**: serving frequent, latency-sensitive, selective queries. Keep. - **Rare but critical**: the quarterly extract. Keep, or accept a slower scan for it and drop — an explicit tradeoff, not an accident. - **Unclaimed**: nothing uses it, or its only user is a query nobody owns anymore. Drop. - **Redundant/overlapping**: prefix-redundant pairs, exact duplicates, or sets consolidatable into one wider index. The attribution step is where fourteen usually becomes six or seven without anyone losing anything they care about. ## The architectural read The detail that matters most in this scenario is *why* there are fourteen: one per report. That is analytical demand being served from a transactional table, and adding indexes is the wrong answer to it. Every report index taxes every insert forever, so the write path subsidises reporting queries permanently. The structural fixes: - **Read replica for reporting.** Indexes needed only by reports can live on a replica — though note the replica still applies the primary's writes, so this helps only where indexes can differ per node, which depends on the engine. - **A separate analytical store** fed by change data capture or a periodic extract, modelled for the reports rather than for the transactions. This is the real fix when reporting demand is genuinely growing, and it also removes long analytical queries from the OLTP node. - **Materialised summaries** for the handful of reports that are aggregations, refreshed on a schedule, which removes both the index and the query from the hot path. - **Partitioning** where reports are time-bounded, so each report touches a small number of partitions and needs less index support. ## Making the decision durable Without process, the count grows back. What holds: - **A rule that a new index requires a named query, an expected frequency, a selectivity estimate, and an owner** — reviewed like any other schema change. - **Index review inside schema change review**, where adding a fifteenth index requires arguing against the write budget explicitly. - **Telemetry that shows cost next to benefit**: index size, maintenance share, and usage counters on one dashboard, so a dead index is visible rather than inferred. - **A periodic sweep** — quarterly — using the same attribution method, so drift is caught while it is cheap. ## Executing the reduction Do it incrementally, largest-cost-first, one index at a time, with an invisible/disable trial where the engine supports it, the exact creation statement scripted for rollback, and monitoring on both sides: the queries that might regress and the write metrics that should improve. Report the outcome in the terms the budget was set in — "p99 insert latency fell from 18ms to 7ms, index maintenance dropped from 61% to 24% of write time" — because that is what makes the next reduction easy to authorise. ## What separates a strong answer Refusing the number, naming the write budget as the real constraint, insisting on measurement over assertion, and recognising the architectural smell that fourteen report indexes on an OLTP table represents. A weaker answer picks a number like five and defends it.