The Cost of Indexes: Storage and Write Performance
What you'll learn: You'll understand that indexes aren't free — they consume disk space and slow down data modification operations.
The Hidden Price of Speed
In the previous lesson, you learned that indexes dramatically speed up queries. But they come with real costs that you must balance against their benefits.
Storage Overhead
Every index you create is essentially a separate data structure (usually a B-tree or hash table) that must be stored on disk. This means:
- Each index duplicates some of your data in a different organization
- Multiple indexes multiply your storage requirements
- A table with 5 indexes might use 2-3x the disk space of the base table alone
Analogy: Think of a library with multiple card catalogs — one by author, one by title, one by subject. Each catalog takes up physical space, and the more catalogs you maintain, the more shelf space you need.
Write Performance Penalty
Here's the critical trade-off: every time you INSERT, UPDATE, or DELETE a row, the database must update every index on that table.
INSERTa new row? Every index gets a new entry.UPDATEan indexed column? The index must be reorganized.DELETEa row? Every index must remove that entry.
This means writes become slower as you add more indexes. A table with 10 indexes might take 10x longer to insert into than one with no indexes.
The Balancing Act
Good database design means:
- Index queries you run frequently
- Avoid indexing rarely-queried columns
- Consider your read-to-write ratio (read-heavy systems benefit more from indexes)
- Monitor actual usage and remove unused indexes
Key Takeaway: Indexes trade storage space and write performance for faster reads — design thoughtfully based on your application's actual query patterns and write frequency.