Indexes Are Not Free
An index accelerates matching reads, but it is a second copy of data that the engine must keep consistent. Every index carries an ongoing maintenance cost that grows with the number of indexes on a table.
Where The Cost Lands
- Writes slow down. Each insert, update of an indexed column, or delete must also update every relevant index, often touching extra pages.
- Storage rises. Wide or numerous indexes can exceed the size of the table itself.
- Memory and cache pressure increases because index pages compete for buffer space.
- The planner spends more time considering options and statistics with each added index.
Fragmentation And Bloat
Over time, page splits and deleted entries leave indexes fragmented, with partly empty pages that waste space and reads. Periodic rebuild or reorganize operations restore density. In version based engines, dead entries accumulate until cleanup reclaims them.
Finding The Balance
Audit for unused and redundant indexes, since one composite index can replace several single column ones. Drop indexes that the planner never picks; their only effect is slowing writes.
Key idea
Each index speeds some reads but slows every write, consumes storage and cache, and burdens the planner, so unused and redundant indexes should be removed.