Choosing a shard key
The one column that decides where every row lives, which queries stay cheap, and whether the load spreads at all.
5 minute lesson
One column decides everything
When a table is spread over several machines, one column is picked as the shard key. Its value decides on which shard a row is stored. It decides two more things at the same time: whether the load spreads evenly, and which queries can be answered by a single shard.
- Rowcustomer 7
- Shard keycustomer id
- Lives onshard 3
- shard key
- The column whose value decides on which shard a row is stored.
- shard
- One of the machines, or groups of machines, that each hold a part of a partitioned data set.
Key terms
Three ways a key goes wrong
- Too few valuesA key of 7 continentsallows 7 shards at most
- A few huge valuesOne customer with half the rowsthat shard is always the busiest
- Values that only growA timestamp or a counterevery new row goes to the last shard
An events table is spread over ten shards by ranges of its creation time. Each shard holds one range of dates.
Where do the new events go?
- All to the last shard, which holds the newest range
- Evenly to all ten shards, a tenth of them to each
- To a random shard, as times cannot be predicted
- To the first shard, which holds the oldest range
Show the answer
- All to the last shard, which holds the newest range
Every new event has a later time than all the events before it, so it belongs to the newest range. One shard takes all the writes while nine sit idle with old data. The table is spread over ten machines and writes as if it had one. Keys that only grow need hashing, or a different key in front of them.
A key that only grows sends every new row to one shard. Time and counters make poor shard keys by range.
A query with the key and one without
- With the keyGoes to one shardorders of customer 7
- Without the keyGoes to every shardorders above 500 euros
Without the key, the router cannot know where matching rows are. It sends the query to all shards and combines the answers. That is scatter-gather. Every shard does work for every such query, so adding shards does not make it faster.
Orders are spread over 16 shards by customer id. A query asks for all orders with a total above 500 euros.
How is the query answered?
- All 16 shards search, and the results are combined
- One shard answers, chosen by the total of 500
- The query is rejected, as it names no customer
- The shard with the most orders answers for all
Show the answer
- All 16 shards search, and the results are combined
Expensive orders can belong to any customer, so they can be on any shard. The router asks all sixteen and merges what comes back. The query is as slow as the slowest shard, and it takes a little capacity from every machine. If such a query is frequent, the data needs a second structure for it, such as a search index.
A query without the shard key is scatter-gather: every shard works, and the slowest sets the pace.
Pick the key from the main query
- List the queries, and find the value that the most frequent ones always have: usually a tenant, a customer or a user.
- Check that value against the three problems.
- Accept that the remaining queries will be scatter-gather, and keep them rare.
What makes a good shard key, and what does it decide?
Show the answer
It decides which shard holds each row, and with that how evenly the load spreads and which queries stay on one shard. A good key has many distinct values, with no value that dominates and no steady increase that sends all new rows to one shard. It should be the value that the most frequent queries always include, such as a customer id.
Recap
- The shard key decides where each row lives, how even the load is and which queries are cheap.
- Avoid keys with few values, with dominant values or with values that only grow.
- Queries without the key go to every shard. Choose the key that the main queries always have.