Intermediate LevelSystem Design & Architecture
How do you choose optimal Shard Keys for horizontal database partitioning?
❓ Interview Prompt: Explain Shard Key Selection, High Cardinality, Uniform Distribution, preventing Shard Hotspotting, and Scatter-Gather queries.
Selecting a poor Shard Key causes data imbalance (Hotspotting) and forces queries to execute expensive Scatter-Gather operations across all shards:
Shard Key Selection Rules:
1
High Cardinality: Choose keys with millions of unique values (e.g. 'user_id' or 'tenant_id').
2
Even Distribution: Avoid monotonically increasing keys (like auto-increment IDs or timestamps) that route all new writes to the highest shard.
3
Query Co-location: Choose keys used in frequent filter queries ('WHERE tenant_id = 5') to execute single-shard lookups.
Code Implementation Example
json
{
"shardKey": "tenant_id",
"cardinality": "High (500,000 unique tenants)",
"distributionStrategy": "Consistent Hash Ring",
"scatterGatherPrevention": "All queries include tenant_id in filter clause"
}💡Shard Key evaluation specification payload.
🎯 Interview Key Takeaway
Select high-cardinality shard keys that distribute data evenly and co-locate frequent queries to a single shard node.
#System Design#Sharding#Shard Key#Databases#Intermediate
Back to all System Design & Architecture Questions→