When data is sharded by a primary key (e.g., user_id), querying by a secondary key (e.g., email) requires a secondary index. There are two main strategies.

Local secondary index:

• Each shard maintains an index for its local data only
• Query by email must be sent to ALL shards (scatter-gather)
• Simple to implement but expensive for queries

Example:

Request:  {"type": "secondary_index_query", "msg_id": 1, "index": "email", "value": "alice@example.com"}
Response: {"type": "secondary_index_query_ok", "in_reply_to": 1, "results": [{"user_id": "u42", "email": "alice@example.com"}], "shards_scanned": 3}

Global secondary index:

• A separate index shard that maps email → primary_key (user_id)
• Query by email first looks up the index shard, then fetches from the data shard
• Faster queries but more complex architecture

Example with global index:

Request:  {"type": "secondary_index_query", "msg_id": 2, "index": "email", "value": "bob@example.com", "use_global": true}
Response: {"type": "secondary_index_query_ok", "in_reply_to": 2, "results": [{"user_id": "u99", "email": "bob@example.com"}], "shards_scanned": 1}

Write amplification:
When a user updates their email:

1. Update primary record on shard hash(user_id)
2. Update secondary index (either local or global)
3. Two network round-trips instead of one

Implementation:

• Maintain an index map: Map<index_name, Map<index_value, primary_key>>
• For local indexes: each shard has its own index map
• For global indexes: a dedicated index shard
• On write: update both the record and all secondary indexes