Summary
Indexes speed up queries by adding lookup structures. Denormalization duplicates or reshapes data to match read patterns.
Interview Points
- Indexes improve reads but cost storage and write overhead.
- Denormalization can remove joins and reduce query complexity.
- Denormalization creates consistency and update challenges.
- Start with indexes when relational structure still fits.
- Denormalize when access patterns are stable and performance requires it.
2-3 Minute Interview Script
“Indexes and denormalization both improve read performance, but they do it differently. An index adds a data structure that helps the database find rows faster. Denormalization changes the data model, often duplicating data so reads do less work.
Indexes are usually the first tool because they preserve the logical model. The cost is extra storage and slower writes because the index must be maintained.
Denormalization is useful when joins or aggregations are too expensive and the access pattern is known. The cost is consistency: when source data changes, all duplicated views must be updated correctly.
My interview answer: index until the model still works; denormalize when the read path and scale justify the extra write complexity.”
Follow-Ups
- What makes an index useful?
- How do you keep denormalized data consistent?