This isn’t a scaling problem. It’s a design problem, dressed up in hardware excuses. And the knee-jerk reaction, “let’s migrate to a key-value store”, might just make things worse.
The Real Cost of “Just Add an Index”
Indexes are the duct tape of database performance. Slap one on a slow query, and it zips. Slap on thirty, and the entire write path goes into a coma.
Every index on a table is a separate data structure that must be updated on every write operation. For a table with heavy read and write load, each new index adds latency to INSERT, UPDATE, and DELETE statements. This isn’t theoretical. A table with 40 indexes can spend more clock cycles maintaining those structures than actually serving data.
The problem is cumulative. Removing one index from a set of thirty rarely solves the bottleneck. But remove the right 10-15, and the system can go from blocking to breathing.
How to Prove the Suspicion: Wait Stats and Index Usage Reports
Before you even think about removing an index, you need hard evidence. The gut feeling that “too many indexes” is the problem is a good start, but production systems demand data.
Step 1: Check Wait Statistics
If you’re on SQL Server, query sys.dm_os_wait_stats. Look for high counts of PAGELATCH waits. These indicate contention on index page updates, a classic sign of over-indexing on a hot table.
On PostgreSQL, check pg_stat_activity for waiting queries and correlate with pg_stat_user_indexes. On MySQL/MariaDB, SHOW ENGINE INNODB STATUS reveals latch contention.
The pattern is brutal and predictable: a write transaction holds a lock on a page, and all other writes pile up behind it. If your wait stats show a mountain of latch-related waits on a single table, you’ve found your suspect.
Step 2: Measure Write Latency on the Table
Run a simple benchmark. Time the duration of an INSERT or UPDATE on the hot table. Then compare that to the same operation on a table with a sane number of indexes. The difference is often 5x to 10x.
Step 3: Index Usage Reports
This is where most teams go wrong. They think they know which indexes are used. They’re wrong.
Query the engine’s usage statistics. Each engine has its own DMV or system table:
- SQL Server:
sys.dm_db_index_usage_stats, shows seeks, scans, lookups, and updates per index. - PostgreSQL:
pg_stat_user_indexes, tracksidx_scanandidx_tup_read. - MySQL/MariaDB:
sys.schema_unused_indexesorperformance_schemaequivalents.
Look for indexes with zero reads but high writes. Every one of those is a drag on write performance with zero benefit.
One veteran DBA shared a killer piece of advice: for SQL Server, use the free tool sp_BlitzIndex from Brent Ozar. It reads the same DMVs and hands you a report of unused, duplicate, and overlapping indexes. Fastest path to removal candidates.
For MySQL/MariaDB, sys.schema_unused_indexes does the same job.
The Safe Removal Strategy: Invisible Indexes and Phased Drops
You’ve identified the dead weight. Now you have to cut it without waking up at 3 AM to a pager.
The golden rule: make the index invisible before you drop it.
SQL Server supports creating indexes as INVISIBLE. PostgreSQL (15+) has the same feature. In MySQL 8.0+, you can use ALTER TABLE ... ALTER INDEX ... INVISIBLE. If your engine doesn’t support it, you can achieve the same effect by making the index unusable (e.g., disabling it in MySQL).
Monitor for at least one full business cycle, ideally one to two weeks, before dropping. This catches those monthly reporting queries that someone forgot about. The pain of restoring a dropped index from backup is much worse than the patience of waiting.
What to Look For
- Unused indexes with zero reads and high writes. Drop them.
- Duplicate indexes that cover the exact same columns. Keep one, drop the rest.
- Overlapping indexes where one index is a superset of another. Consolidate.
- Extremely wide indexes with many included columns. Narrow them. Variable-width columns in particular can cause page splits that amplify blocking.
One real-world case study from the SQL Server trenches: a table went from 40 indexes down to 23, and the blocking vanished. The DBA acknowledged 23 was still too many, but the write load was low enough, and the removal risk was too high for the remaining indexes.
That’s the trade-off. You don’t need perfect. You need better than the current hellscape.
Is This a Scale Problem or a Design Problem?
This is the point where the architecture debate gets spicy. Someone will inevitably say, “RDBMS can’t handle this load. We need to move to a key-value store.”
That’s usually cargo-cult thinking dressed up as innovation.
Most of the time, proper index hygiene fixes the problem. Elite DBA teams with adequate hardware have hit 100,000 queries per second on SQL Server. The bottleneck wasn’t the relational model. It was the 40 indexes that no one had the courage to audit.
But there’s a limit. If a table is doing close to 100k requests per second with a genuinely mixed read/write workload, and you’ve already trimmed the fat, then yes, you may be hitting the ceiling of what a traditional B-tree index structure can handle.
When Migration Makes Sense (and When It Doesn’t)
Migrate if:
– You’ve removed all dead indexes and writes are still blocking.
– The workload is overwhelmingly point lookups by primary key, with no joins or complex queries.
– You need single-digit millisecond writes at massive throughput.
Fix instead if:
– You haven’t run an index audit yet.
– The application uses joins, aggregations, or complex filtering.
– Your data has relationships that would be painful to model in a key-value store.
One make-or-break insight from a seasoned DBA: implement application caching first. If you’re hitting 100k reads per second, a Redis cache layer can slash that down to 10-20k, making your index problem far more tractable. Caching is almost always cheaper and faster than a full database migration.
If you have a genuinely mixed workload at extreme scale, in-memory OLTP (like SQL Server’s Hekaton or PostgreSQL’s in-memory extensions) is a better bet than jumping to a key-value store. The “NoSQL will save us” crowd often forgets that BASE compliance at that scale introduces consistency problems that are far harder to debug than slow indexes.
The Index-to-Table Ratio That Actually Works
The industry consensus is 5-10 indexes per table for most workloads. Beyond that, you need a really good reason.
Yet one horror story described a database with 324 indexes on a single table, inherited from an acquisition. The previous DBA had been adding indexes reactively: the app slowed down, so he added an index. When it slowed down again, he added another. He kept going until the app stopped complaining, not because it was fast, but because it was uniformly slow across all operations.
That’s the death spiral of over-indexing. Every new index makes the problem worse, but its absence would make some specific query slower, so nobody removes it.
The Harder Problem: Variable-Width Columns and Page Splits
Even after you’ve removed the dead indexes, there’s a subtle killer lurking: variable-width columns in indexes.
When a column uses VARCHAR(255) but only stores 10 characters, and then gets updated to 50 characters, the database may need to split the index page to accommodate the new value. Page splits increase the duration of write operations and amplify blocking.
Fix this by:
– Right-sizing columns. VARCHAR(50) is not VARCHAR(255).
– Avoiding NULL columns in indexes where possible.
– Monitoring index fragmentation and rebuilding on a schedule.
Neglecting index maintenance alone can degrade performance by 25% over time.
Tune or Migrate: The Decision Framework
You can frame your choice around three questions:
- Have you actually done the work? If you haven’t run index usage stats, checked wait stats, and trimmed dead indexes, you’re not at the “migrate” decision point yet. You’re at the “stop being lazy” point.
- Is the bottleneck index maintenance or query design? Often, a poorly written query is creating a full table scan that someone decided to fix with an index instead of rewriting the SQL. The correct fix is to rewrite the query, not add more indexes. This is a pattern that regularly slips through code review, we’ve covered index-related performance issues that slip through code review in depth elsewhere.
- Does your data actually have relational structure? If your workload is purely key-value lookups, and you’ve cut all dead indexes and still hit limits, then maybe a key-value store makes sense. But even then, the trend is moving the other direction. Teams are migrating from specialized systems back to relational databases because the operational complexity of running multiple systems isn’t worth the marginal performance gain.
The Real Lesson: Index Governance
The root cause of over-indexing is rarely technical. It’s organizational. No process prevents developers from adding indexes on a whim. No code review catches “this table already has 30 indexes.” No one owns the long-term health of the schema.
The fix is boring but effective:
– Enforce a maximum index count on hot tables.
– Require performance data (show the query that needs the index) before adding one.
– Run index usage reports weekly and flag dead indexes.
– Make indexes invisible before dropping them, and review the results.
Before you blame scale and start shopping for a key-value store, spend a week cleaning up your indexes.
The problem is almost never that the relational model can’t handle the load. It’s that the indexes have become a self-inflicted denial-of-service attack. Remove the dead weight, monitor the effects, and then see if you still have a problem worth migrating for.
More often than not, you won’t.
