What are the main database sharding strategies, and how do you pick a shard key?

#Why shard at all

Replication solves read scaling and availability, but it doesn't solve write scaling — every follower still has to apply every write the leader does, so the whole dataset (and its write load) still has to fit on one leader. Sharding (horizontal partitioning) splits the data itself across multiple independent database nodes, each holding a subset of the rows, so that both storage and write throughput scale by adding machines.

The hard part was never mechanically splitting a table — it's picking a shard key that determines which shard a given row lives on, because that one decision shapes every query pattern, every hotspot, and every future resharding operation for the life of the system.

#The main strategies

Range-based sharding assigns contiguous ranges of the shard key to each shard — for example, users with IDs 1–1,000,000 on shard A, 1,000,001–2,000,000 on shard B. This makes range queries (WHERE created_at BETWEEN ...) cheap, because a range query usually only touches one or two shards. The failure mode is hotspotting: if the shard key is something monotonically increasing like an auto-increment ID or a timestamp, all new writes land on the single "last" shard while older shards sit idle.

Hash-based sharding runs the shard key through a hash function and uses the result (typically hash(key) mod N, or a bucket derived from it) to pick a shard. This spreads writes evenly and kills the hotspot problem, at the cost of range queries becoming a scatter-gather across every shard, because consecutive keys are scattered randomly.

Directory-based (lookup) sharding keeps an explicit mapping table (key → shard) in a separate service, giving full control over placement — including deliberately isolating specific noisy tenants — at the cost of an extra lookup on every request and that lookup service becoming its own availability and scaling concern.

Geo/attribute-based sharding places data by a business attribute — region, tenant, or customer plan — so that European user data lives in an EU shard for latency and regulatory reasons (data residency), independent of hashing or ranges.

#Picking a shard key: the checklist

• High cardinality — a shard key with only 3 possible values (like a boolean flag) can never spread data across more than 3 shards, no matter how you hash it.
• Even access distribution — cardinality isn't enough; a customer_id shard key is fine on cardinality, but if one enterprise customer generates 40% of all traffic, that customer's shard becomes a hotspot regardless of hashing (a "hot key," not a hot shard, but it manifests the same way).
• Query alignment — pick a key that most of your queries already filter on. If 90% of queries are WHERE user_id = ?, shard by user_id, so those queries hit exactly one shard instead of fanning out to all of them.
• Avoid monotonic keys for hash-adjacent range needs — if you need both even writes and range scans, consider hashing a prefix while keeping a sortable suffix, or bucketing by a coarser attribute (day, region) combined with a hash.
• Resharding cost — plan for growth before you need it; moving from hash(key) mod 4 to hash(key) mod 8 reshuffles nearly every key, whereas consistent hashing only moves roughly 1/N of the keys when a shard is added.

#Consistent hashing, in code

import bisect
import hashlib

class ConsistentHashRing:
    def __init__(self, shards, virtual_nodes=150):
        self.ring = {}
        self.sorted_keys = []
        for shard in shards:
            for v in range(virtual_nodes):
                h = self._hash(f"{shard}:{v}")
                self.ring[h] = shard
                self.sorted_keys.append(h)
        self.sorted_keys.sort()

    def _hash(self, key: str) -> int:
        return int(hashlib.md5(key.encode()).hexdigest(), 16)

    def get_shard(self, key: str) -> str:
        h = self._hash(key)
        idx = bisect.bisect(self.sorted_keys, h) % len(self.sorted_keys)
        return self.ring[self.sorted_keys[idx]]

ring = ConsistentHashRing(["shard-a", "shard-b", "shard-c"])
print(ring.get_shard("user:48213"))  # -> deterministic shard for this key

Each shard gets many virtual nodes on the hash ring (not just one point), which keeps the load balanced across shards even though the hashing is random — without virtual nodes, one shard could randomly land a disproportionate slice of the ring. Adding a fourth shard only steals virtual nodes (and their keys) from their immediate neighbors on the ring, instead of reshuffling the entire keyspace the way mod N would.

🧠 Check your understanding
0/1 · 0/1 answered
1. You shard a table by an auto-incrementing order ID using pure range-based sharding. What's the most likely problem in production?
Resharding becomes impossibleAll new writes land on the single highest-range shard, creating a hotspotThe shard key has too low cardinality to be usableRange queries become slow scatter-gathers