Database sharding is how you scale a database beyond what a single server can handle — splitting data across multiple independent database instances. While managed databases have made sharding less common for new projects, understanding sharding is critical for system design interviews, working at scale, and architecting systems that will eventually need it. This guide covers the theory and practice of database sharding.

Database Sharding Strategies: Partitioning, Consistent Hashing, and Real-World Patterns

Sharding Strategies Compared

StrategyHow It WorksProsConsBest For
Key-Based (Hash) ShardingHash(shard_key) % N → shard numberEven distribution, simple routingAdding shards rehashes ALL data; cross-shard queries are hardEven data distribution, simple lookup patterns
Range-Based ShardingShard 1: A-M, Shard 2: N-ZIntuitive, range queries work within a shardHotspots (shard with most popular range gets overloaded)Time-series data, alphabetical/sequential data
Directory-Based ShardingLookup table maps key → shardFlexible (move data between shards easily)Lookup service is a single point of failure/ bottleneckComplex sharding needs, frequent rebalancing
Geo-Based ShardingShard by geographic region (US, EU, APAC)Low latency per region, GDPR complianceUneven distribution; cross-region queries are slowMulti-region apps, data locality requirements
Entity/Functional ShardingShard by entity type (users, orders, products)Independent scaling per entityJoins across entities are impossible in SQLMicroservices, domain-driven design

Consistent Hashing: The Key to Dynamic Sharding

# Consistent hashing minimizes data movement when adding/removing shards
# Traditional hash: hash(key) % N → changing N remaps ALL keys
# Consistent hash: hash(key) and hash(shard) both mapped to a ring
#   Adding a shard: only ~1/N keys need to move
#   Removing a shard: only that shard's keys need to move

# Simplified consistent hashing implementation
import hashlib, bisect

class ConsistentHash:
    def __init__(self, virtual_nodes_per_shard=150):
        self.ring = {}  # hash → shard_id
        self.sorted_hashes = []  # sorted list of hash positions
        self.vnodes = virtual_nodes_per_shard

    def add_shard(self, shard_id):
        for i in range(self.vnodes):
            h = self._hash(f"{shard_id}:{i}")
            self.ring[h] = shard_id
            bisect.insort(self.sorted_hashes, h)

    def get_shard(self, key):
        h = self._hash(key)
        # Find first shard hash >= key hash (clockwise on ring)
        idx = bisect.bisect_left(self.sorted_hashes, h)
        if idx == len(self.sorted_hashes):
            idx = 0  # Wrap around the ring
        return self.ring[self.sorted_hashes[idx]]

    def _hash(self, s):
        return int(hashlib.md5(s.encode()).hexdigest(), 16)

When to Shard (and When Not To)

ScenarioShould You Shard?Alternative
Single DB < 100GB, < 1K QPSNo — single instance is fineAdd read replicas for read scaling
100GB-1TB, read-heavyNo — read replicas firstRead replicas + caching (Redis)
100GB-1TB, write-heavy (>5K write QPS)Maybe — consider shardingAlso consider: better hardware, connection pooling, queue writes
>1TB, any workloadYes — single server can't hold itSharding is necessary at this scale
Multi-tenant SaaS (tenant isolation needed)Maybe — tenant-based shardingAlso consider: row-level security, separate schemas
Startup with <1K usersNo — premature optimizationSingle DB with good indexing

Common Sharding Pitfalls

PitfallProblemSolution
Choosing the wrong shard keyUneven data distribution, hotspotsAnalyze access patterns, pick high-cardinality key
Cross-shard queriesJOINs, aggregations across shards are application-levelDenormalize data, use materialized views, or avoid cross-shard queries
Resharding without downtimeMoving data between shards blocks the applicationConsistent hashing + live migration tools (Vitess, Citus)
Auto-increment IDs collideEach shard's auto-increment starts at 1Use UUIDs, Snowflake IDs, or globally unique ID service
Transactions across shardsACID transactions don't span shardsUse distributed transactions (2PC) or design around it (sagas)

Bottom line: Sharding is a last-resort scaling strategy — exhaust all other options first (indexing, caching, read replicas, connection pooling, query optimization). When you do need sharding, use key-based sharding with consistent hashing for the most flexibility. For PostgreSQL, consider Citus (distributed PostgreSQL) or Vitess (MySQL) as battle-tested sharding solutions before building your own. See also: PostgreSQL Query Optimization and System Design Interview Guide.