# Secondary Indexes in Sharded Databases: Local vs Global

Sharding by primary key makes lookups by that key fast. But what about queries on another column, such as "find all orders for seller 42" when orders are sharded by order_id? You need a secondary index, and in a sharded system there are two ways to build one.

## Local (document-partitioned) indexes

Each shard indexes only its own rows. Writes are cheap: the row and its index entry live on the same shard, updated in one local transaction.
Reads are expensive: a query by seller_id must go to every shard (scatter-gather) and merge results. Tail latency grows with the number of shards, since you wait for the slowest one.
Used by: MongoDB, Elasticsearch, Cassandra local secondary indexes.

## Global (term-partitioned) indexes

The index itself is sharded by the indexed value: all entries for seller 42 live on one index shard. Reads hit one shard. Writes now touch two places, the data shard and the index shard, so you either need a distributed transaction or accept that the index is updated asynchronously and may briefly lag.
Used by: DynamoDB global secondary indexes (eventually consistent), Spanner (transactional).

## Choosing

- Read-heavy queries on the secondary key with large fan-out: global index.
- Write-heavy workloads, or queries that also filter by the shard key: local index.
- Alternative: denormalize into a second table keyed by the secondary attribute and maintain it via change data capture.

## Interview tip

When an interviewer adds "now also query by X", say whether your index is local or global and state the trade-off: scatter-gather reads versus cross-shard writes with possible lag.

Disclosure: I'm part of Linkjob AI, an AI assistant for technical interviews - https://www.linkjob.ai/