Indexes Tax Writes
An index makes matching reads faster, but it is a second copy of the data that the database must keep in sync. Every insert, update, and delete that touches an indexed column must also update the index. Ten indexes mean a single insert does roughly ten extra writes.
The Hidden Costs
- Write amplification: more indexes, more work per write, lower write throughput.
- Storage: indexes can rival or exceed the size of the table data itself.
- Cache pressure: index pages compete with table pages for memory.
- Update churn: changing an indexed column can require removing and reinserting the index entry, not just an in place edit.
Fragmentation And Bloat
Over time, deletes and updates leave dead entries and partly empty pages, called fragmentation or bloat. This makes the index larger and slower than it should be. Databases reclaim this with background vacuum or periodic rebuild and reindex operations, which themselves cost IO.
The Balance
Add indexes for queries that genuinely need them, and remove unused ones. An index that no query uses is pure overhead, slowing every write for no read benefit.
Key idea
Each index is a synced second copy that taxes every write, consumes storage and cache, and fragments over time, so add only the indexes queries need and drop unused ones.