Secondary indexes when sharded

Data is sharded by its primary key, which makes lookups by that key fast. Other queries face a choice. Either every shard indexes its own rows and each query visits all shards, or the index gets its own shards and writes become more expensive. This page compares the two.

The big idea

Picture a school that groups students into classrooms by grade. To find every cello player, you can ask each classroom for its cello players, or keep one master list of cello players in the office. The classroom approach is cheap to update but slow to search. The office list is quick to search but must be updated every time someone joins.

The office list is the global index. It can fall briefly out of date, as covered below.

Walking through it

Find all red cars in a table sharded by car ID, with four shards.

Local index. Each shard indexes its own cars. The query goes to all four shards at once, and the answer waits for the slowest one. Load is multiplied by four. Writes are cheap, because a new car touches only its own shard.

Global index. The index is sharded by colour. All color = red entries live on one index shard. The query touches one index shard, then fetches the cars. A new red car updates the car's shard and the red index shard. Those may be different machines, so the index update is usually asynchronous.

Rule of thumb: use local indexes for queries that include the shard key or are rare. Use global indexes for frequent queries on another attribute, if a little staleness is acceptable.

Common mistakes

• Scatter-gather by accident. A new "search orders by SKU" endpoint on a table sharded by customer_id fans out to every shard.
• Expecting a global index to update with the row. It is usually updated asynchronously.
• Making a global index on a low-cardinality field. Every red car then lands on one index partition, which becomes a hot spot. The same warning applies to any value with few distinct options.
• Ignoring tail latency. A scatter-gather query is only as fast as its slowest shard.

Check yourself

1. Why does a local index on a non-key column get slower as you add shards?

Answer: The query must ask every shard, so its latency follows the slowest one and its load grows with the shard count.

2. Why can a global index on color become a hot spot?

Answer: All entries for one colour are stored on one index partition, so every red-car query and update lands on it.

Key takeaways

• Local indexes make writes cheap and reads scatter across all shards.
• Global indexes make reads hit one partition, but writes can touch another machine.
• Global index updates are usually asynchronous, so they can be briefly stale.
• Use local indexes unless a query on another field is frequent.

Further learning

Next, see how to move data between shards when the layout changes, in resharding without downtime.