Databases & Storage · core

Secondary Indexes

Extra indexes for non-primary-key lookups, and what they cost on writes.

databasesindexing

Mental model

A secondary index is an extra sorted lookup that maps a non-primary-key value (like email) back to the rows that match. Reads on that value get fast; writes get a little slower because the index also has to update. Every index is a tradeoff — only add the ones queries actually need.

How to study Secondary Indexes

Begin by restating the mental model in your own words, then connect it to a concrete system you have built or operated. Name the mechanism, the constraint it addresses, and the trade-off it introduces. Use Database index (Wikipedia) to check details, but close the source before writing your explanation. Retrieval is the learning step; rereading is only preparation.

Next, compare Secondary Indexes with Storage Engines. Ask what changes in correctness, latency, resource use, operability, and failure recovery. Complete Secondary index write cost and preserve the command, input, output, and one failed attempt as evidence. Finish by explaining the idea without jargon to someone who has not studied the track.

Proof of understanding

Explain the mechanism from first principles and identify the state it reads or changes.
Give one situation where the concept is the right choice and one where it is not.
Predict a realistic failure mode before running the drill, then compare the prediction with evidence.
Connect the result to a roadmap or build artifact instead of treating the concept as isolated trivia.