· 7 min read
How to choose a shard key for database sharding
How to choose a shard key: hash vs range sharding, avoiding hot shards, why timestamps fail, and routing that won't move all your data when you grow.

A shard key is the column that decides which database shard a row lives on. Choosing a shard key well means picking a value that spreads writes evenly, keeps the queries you run most often on a single shard, and doesn't force you to move most of your data when you add capacity. Get it wrong and one shard runs hot while the others idle, or every request turns into a query against all of them.
It is also the hardest decision in a sharded system to change later, so it pays to think it through before the first row is written.
What is a shard key?
Sharding splits one logical table across several independent databases, called shards, so that no single machine has to hold all the data or absorb all the writes. Each row needs a deterministic home, and the shard key is the input to that decision.
The routing function that maps a key to a shard usually takes one of three forms:
- Hash-based. Hash the key and map the result to a shard. Neighbouring keys land on unrelated shards.
- Range-based. Assign contiguous key ranges to shards, such as A–F on shard 1 and G–M on shard 2. Neighbouring keys stay together.
- Directory-based. Keep a lookup table that maps each key, or each tenant, to a shard explicitly.
Most systems expose some version of this. In Citus, a PostgreSQL extension for sharding, you pick the key when you distribute the table:
SELECT create_distributed_table('events', 'tenant_id');
MongoDB lets you shard a collection on a ranged key or a hashed key. The vocabulary changes between systems, but the trade-offs don't.
How do you choose a good shard key?
A good shard key passes three tests.
- High cardinality. Cardinality is the number of distinct values a column has. If the key only has eight values, you can never usefully spread data across more than eight shards.
- Even load. Distinct values are not enough. The traffic attached to each value has to be roughly even too, or the shard holding the busiest values becomes the bottleneck.
- Query locality. The queries you run most should include the shard key, so the router can send each one to a single shard instead of all of them.
The first two are about writes and storage. The third is about reads, and it often pulls in the opposite direction. A random UUID spreads perfectly but groups nothing. A customer_id groups everything about a customer on one shard, which makes per-customer queries cheap, but only stays balanced if no single customer dominates.
A quick way to check is to look at the distribution before you commit. The script below routes 100,000 writes to 4 shards using different keys:
import hashlib, random
from collections import Counter
def shard(key, n=4):
h = int.from_bytes(hashlib.md5(str(key).encode()).digest()[:8], "big")
return h % n
rnd = random.Random(7)
users = [rnd.randrange(1_000_000) for _ in range(100_000)]
# one large tenant sends 30% of traffic, 999 small ones share the rest
tenants = [0 if rnd.random() < 0.30 else rnd.randrange(1, 1000) for _ in range(100_000)]
for name, keys in [("user_id", users),
("tenant_id", tenants),
("(tenant_id, user_id)", [(t, rnd.randrange(10**6)) for t in tenants])]:
c = Counter(shard(k) for k in keys)
print(f"{name:22} {[c[i] for i in range(4)]}")
| Shard key | Writes per shard (of 100,000) |
|---|---|
user_id | 24,794 / 25,141 / 25,202 / 24,863 |
tenant_id, one tenant at 30% | 17,609 / 17,815 / 16,489 / 48,087 |
(tenant_id, user_id) | 24,986 / 25,163 / 24,899 / 24,952 |
Hashing tenant_id is fine until one tenant is much larger than the rest. Then every row for that tenant lands on the same shard, and that shard does nearly three times the work of its neighbours. A hot shard like this caps the throughput of the whole cluster, because adding shards does nothing for the one that's overloaded.
Why do timestamps and auto-increment IDs make bad shard keys?
Keys that only ever increase, like created_at or a sequential id, look appealing for range sharding because time-range queries stay on one shard. The problem is writes. Every new row has the largest key so far, so every insert goes to whichever shard owns the top of the range.
With four shards split by date, all of today's writes go to the last shard. The other three only serve reads for old data. The system has four machines and the write throughput of one.
You have a few options:
- Hash the increasing key. Writes spread out, but a query like "everything from the last hour" now has to ask every shard. MongoDB's documentation recommends hashed sharding for exactly this case: monotonically increasing fields.
- Prefix it with something that spreads. A compound key such as
(device_id, created_at)distributes by device and still keeps each device's events ordered on one shard. - Add a bucket. Append a small random or computed bucket number to hot keys so their writes fan out over a few shards. Reads for that key then have to gather from those buckets.
What happens when a query doesn't include the shard key?
The router can't know which shard holds the matching rows, so it sends the query to all of them and merges the results. This is called a scatter-gather or fan-out query.
Fan-out isn't wrong, but its cost grows with the shard count. A query that hits 32 shards is only as fast as the slowest of the 32, and every shard pays the CPU cost even when it returns nothing. Under load, a few fan-out queries can use more capacity than thousands of targeted ones.
Before picking a key, write down your top queries by volume and check how many would include it. If the answer for your hottest read path is "none", the key is wrong for your workload, however evenly it spreads. When two important access patterns need different keys, a common fix is a second copy of the data, or a small lookup table, sharded on the other key.
For multi-tenant apps, tenant_id usually wins because almost every query is already scoped to one tenant. It also keeps joins and foreign keys inside a shard, which lines up with the argument in keeping tenant isolation in the data layer. The catch is the large tenant, which you handle by moving it to its own shard via a directory, or by sharding on a compound key.
How does the shard key affect resharding?
At some point you'll add shards. How much data moves depends on the routing function, not just the key.
Plain hash(key) % N is the trap. Going from 4 to 5 shards changes the result for about 80% of keys in a quick test, and even doubling from 4 to 8 moves half of them. Every moved row is a copy, a verification and a cutover.
| Routing approach | Data moved when adding a shard | Notes |
|---|---|---|
hash % N | Most keys | Simple, painful to grow |
| Fixed logical shards | Only the partitions you reassign | Hash into many small partitions (say 1,024), map partitions to machines |
| Consistent hashing | About 1/N of keys | See how consistent hashing works |
| Range splits | One range at a time | Split a hot range in two and move half |
The fixed logical shard approach is the one many teams settle on. You hash into far more partitions than you have machines, store the partition-to-machine map somewhere small, and move whole partitions when you grow. The shard key never changes; only the map does.
Key takeaways
- A good shard key has high cardinality, spreads load evenly, and appears in your most frequent queries.
- Check the real traffic per key value, not just the number of distinct values. One large tenant can create a hot shard.
- Timestamps and auto-increment IDs send every new write to one shard; hash them or prefix them.
- Queries without the shard key fan out to every shard, so their cost grows as you add shards.
- Avoid
hash % Nrouting. Use many logical partitions or consistent hashing so growth moves little data.
FAQ
Can you change a shard key later?
Some databases support it, but it still means rewriting and moving most of the data. Treat it as a migration project, not a configuration change, and choose carefully up front.
Should I use a UUID as a shard key?
A random UUID spreads writes evenly, which is good. It only makes sense if most queries look up rows by that UUID; otherwise most reads become fan-out queries.
How many shards should I start with?
Start with many logical partitions and few physical machines. That lets you add machines later by moving partitions instead of rehashing every row.