Secondary Indexes

The Problem at Scale

 sharded by user_id; now: "find trips by driver_id"

 primary key routing knows NOTHING about driver_id:
 → scatter-gather ALL shards, filter locally = O(cluster) latency

 secondary index = lookup structure by a NON-key attribute.
 single-node databases make this trivial (CREATE INDEX).
 distribution makes it an ARCHITECTURE DECISION.

Local Secondary Indexes

 index lives WITHIN each shard's partition:

 shard-1: [user rows + their LSI on driver_id]
 shard-2: [user rows + their LSI on driver_id]

 query by driver → still visits EVERY shard,
 but each does an index seek instead of a full scan.

 + atomic with the row (same transaction domain)
 + no extra infrastructure
 − latency = slowest shard; every shard pays storage/IO

Global Secondary Indexes (GSI)

 the index ITSELF is partitioned by the indexed column:

 GSI(driver_id) sharded across cluster:
   driver-9's trips live together in ONE GSI partition
   regardless of which shards hold the actual trips

 query by driver → route to GSI partition → direct hit ✓
 
 DynamoDB model (the canonical implementation):
   base table PK=user_id
   GSI PK=driver_id, projected attributes copied in
   GSI updated ASYNCHRONOUSLY from base writes
 
 properties to internalize:
 + single-shard fast lookups on ANY designed dimension
 − eventual consistency (replication lag to the GSI)
 − write amplification (every write touches its GSIs)
 − GSIs have their OWN capacity/hotspot problems!

Index Design Discipline

 every index is a WRITTEN COPY you maintain forever:

 □ justify per index: which QUERY, what frequency?
 □ project MINIMUM attributes (DynamoDB: INCLUDE not ALL)
 □ watch GSI hot keys — celebrity drivers melt one partition
   (same salting/bucketing playbook applies)
 □ sparse/partial indexes for rare attributes
 □ index TTL alignment: expired rows should leave indexes too

 audit cadence: unused-index reports quarterly;
 each dead index taxes every write on its table.

The Query-Pattern Connection

 this lesson is access-pattern-driven design ENFORCED:

 pattern list from design phase → each becomes either:
 - part of primary key (free)
 - a GSI (paid: writes + staleness)
 - warehouse/analytics responsibility (routed away)

 if your design needs >3-4 GSIs on one table:
 the table is doing too many jobs — split it or rethink keys.

Interview Framing

“Support lookup by both rider AND driver” scored shape: name scatter-gather as naive cost, present GSI as the fix WITH its async-consistency caveat, note GSI hotspot risk mirroring base-table risks, and tie back to access-pattern inventory (“this is why we listed queries first”). The staleness caveat is the differentiator — GSIs that must be read-your-writes are usually a design smell.