How to choose a shard key: an interactive sharding simulator
A shard key decides which shard each row lives on. A good one spreads the load evenly and keeps each common query on a single shard. A bad one does one or the other: it leaves one hot shard doing most of the work, or it spreads data so evenly that every query has to ask every shard.
The playground shards a social app’s posts table. Every second, users write 1,000 posts, read one user’s posts 4,000 times and ask for the latest posts from everyone 200 times. Choose the shard key and the number of shards, add a celebrity, and compare how much work each shard does against what it can take.
1,000 posts, 4,000 reads of a user’s posts and 200 “latest posts” queries a second, across 8 shards by hash(user_id). Each user’s posts live on one shard, so reading them asks a single shard, and with many users the work spreads evenly: the busiest shard does 982 a second, 1.19 times the average. The cost is queries that are not by user: “latest posts from everyone” has to ask all 8 shards and merge the answers.
What makes a good shard key
- It is in the common queries. If most requests say which user they want, sharding by user sends each to one shard. A query that does not name the key has to ask every shard.
- It spreads load, not just data. Equal row counts per shard are not enough if some keys are read or written far more than others.
- It has many distinct values. Sharding by country gives you about 200 possible shards, most of them tiny and one or two huge.
- It does not grow with time. Timestamps and auto-increment ids send every new row to the same place.
- It does not change. A row whose key changes has to move between shards.
Hash sharding vs range sharding
| Hash of the key | Range of the key | |
|---|---|---|
| Spread | Even for any key with many values | Even only if the key’s values arrive evenly |
| Range queries | Ask every shard | Ask only the shards covering the range |
| Time-ordered keys | Fine: new rows scatter | The newest range takes every write |
| Adding shards | With hash mod N most keys move; consistent hashing moves few | Split a busy range in two |
| Used by | Cassandra, DynamoDB, Redis Cluster | HBase, Bigtable, MongoDB ranged sharding |
In the playground, range(created_at) puts all 5,200 requests a second on the shard holding the newest posts while the rest sit idle. hash(user_id) keeps the busiest shard at 982, about 1.2 times the average, and each read of a user’s posts asks one shard.
Spreading evenly is not the goal
hash(post_id) gives every shard exactly the same writes, which looks ideal, but a user’s posts are now on all eight shards, so every read of them asks all eight. The shards do 34,600 units of work for 5,200 requests, and each one is far over capacity. The right key keeps the common query on one shard and accepts a little unevenness; queries that cannot use it, like “latest posts from everyone”, are better served by a separate table or index keyed for them than by asking every shard.
Hot keys and how to cool them
A celebrity breaks hash(user_id): when one user makes 30% of the posts and gets 30% of the reads, their shard does 2,247 requests a second, 2.7 times the average, and adding shards changes nothing because that user’s posts still live together. The fix depends on what is hot:
- Hot writes: salt the key. Append a suffix from 0 to 3 to the busiest users’ keys so their rows spread over four shards.
- Hot reads: cache the hot key’s results, or add read replicas. Salting makes reads worse: in the last preset every read of a salted user asks four shards, and the busiest shard still does 2,130 a second.
- Hot ranges: split them. Range-sharded stores split a busy range in two, and DynamoDB splits partitions that stay hot.
Changing the shard key later
Choosing again means moving every row: write to both layouts, backfill the new one, compare, switch reads, then stop writing the old one. It can take weeks on a large table, which is why the shard key deserves the most thought of any schema decision. Plan the shard count for growth too; with consistent hashing, adding a shard moves only the keys it takes over.
In a system design interview
Name the key and the query it serves: “Posts are sharded by hash of user id, so loading a profile is one shard. The home feed does not read this table directly; it reads precomputed timelines. For accounts with millions of followers we cache their recent posts, because one shard cannot serve those reads.” Then expect the follow-ups: the queries the key does not serve, hot keys, and how you would reshard.
Read the guide to sharding and partitioning or hot keys and skew, or watch a strong candidate design a news feed.
Questions people ask
What is a shard key?
The column, or set of columns, that decides which shard a row is stored on, either by hashing it or by assigning ranges of its values to shards. Every query that includes the shard key can go straight to one shard; a query without it has to ask all of them.
How do you choose a good shard key?
Pick a key that appears in the most common queries, has many distinct values, spreads reads and writes evenly rather than just rows, does not increase with time, and does not change once written. For a social app, user id usually fits: most queries are about one user, and there are millions of them.
What is the difference between hash and range sharding?
Hash sharding places a row by a hash of its key, which spreads any high-cardinality key evenly but turns range queries into questions for every shard. Range sharding gives each shard a contiguous range of keys, which keeps range queries on few shards but sends every new row to the last shard when keys increase over time.
What is a hot shard?
A shard that receives far more traffic than the others, usually because of a hot key such as a celebrity account, or a time-ordered key that sends every new write to the newest range. It limits the whole system, because adding shards does not spread work that belongs to one key.
How do you fix a hot key in a sharded database?
For hot writes, salt the key: add a small suffix so the key’s rows spread across several shards, and read them back from all of those shards. For hot reads, cache the key’s results or serve them from read replicas, since salting makes each read touch more shards. Range-sharded systems can also split a hot range.
Why is sharding by timestamp a bad idea?
With range sharding, every new row has a timestamp later than all the others, so every write goes to the shard holding the newest range, and reads of recent data go there too, while the other shards sit idle. Auto-increment ids have the same problem. Hash the time-based key, or lead the key with something that is spread out, such as a user id.
What is a scatter-gather query?
A query that does not include the shard key, so it is sent to every shard and the results are merged. Its latency is that of the slowest shard and its cost grows with the number of shards. Keep them rare, or serve them from a separate table, index or search system keyed for that query.