Swe Database Sharding Partition Strategies

Sharding questions appear in the majority of senior and staff-level system design interviews once a design crosses roughly 10 million users or 1TB of primary data, according to aggregated interview debrief data from 2026 candidate cohorts at FAANG and high-growth startups. The failure mode isn’t usually “candidate doesn’t know what sharding is” — it’s candidates picking a sharding key without justifying the tradeoff, or forgetting that sharding breaks cross-shard transactions and joins. This piece covers the strategies and the tradeoffs interviewers actually probe.

What Sharding Solves (and What It Doesn’t)

Sharding (horizontal partitioning) splits a single logical dataset across multiple physical database instances so that no single machine holds all the data or absorbs all the write load. It solves storage-capacity limits and write-throughput limits that a single primary database can’t scale past, even with read replicas. It does not solve: complex cross-entity joins (these become expensive or impossible across shards), global uniqueness constraints (need a separate ID-generation strategy), or ACID transactions spanning multiple shards (need distributed transaction protocols or eventual-consistency workarounds).

Candidates who jump straight to “let’s shard” without first exhausting vertical scaling, read replicas, and caching lose points — interviewers want to see you justify sharding as a last resort given its operational complexity cost.

The Three Core Strategies

Hash-based sharding: Apply a hash function to a shard key (e.g., user_id) and mod by shard count to route to a physical shard. Distributes load evenly, but resharding (adding a shard) requires rehashing and moving a large fraction of data unless you use consistent hashing, which limits data movement to roughly 1/N of keys when adding the Nth shard.

Range-based sharding: Partition by contiguous key ranges (e.g., user IDs 1-1M on shard 1, 1M-2M on shard 2). Simple to reason about and supports efficient range queries, but is prone to hot-spotting — if IDs are assigned sequentially and recent users are more active, the newest shard absorbs disproportionate load.

Directory-based (lookup) sharding: A separate lookup service/table maps shard keys to physical shards explicitly. Most flexible — supports arbitrary rebalancing and non-uniform shard sizing — but introduces a single point of failure/bottleneck at the lookup service unless it’s heavily cached and replicated.

# Consistent hashing sketch — minimizes data movement on rescale
import hashlib

class ConsistentHashRing:
    def __init__(self, nodes, replicas=150):
        self.ring = {}
        self.sorted_keys = []
        for node in nodes:
            for i in range(replicas):
                key = self._hash(f"{node}:{i}")
                self.ring[key] = node
                self.sorted_keys.append(key)
        self.sorted_keys.sort()

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

    def get_node(self, key):
        h = self._hash(key)
        for k in self.sorted_keys:
            if h <= k:
                return self.ring[k]
        return self.ring[self.sorted_keys[0]]

Comparison Table

Choosing a Shard Key: The Question Interviewers Actually Ask

The shard key decision dominates system design tracing rubrics because it determines almost every downstream tradeoff. Good shard keys have high cardinality (avoid a handful of values absorbing all traffic), align with your most common query pattern (shard by tenant_id in a multi-tenant SaaS so per-tenant queries never cross shards), and avoid monotonically increasing values as the sole key component (timestamp-only keys create write hot-spots on the newest shard). A strong interview answer explicitly walks through 2-3 candidate keys, states the query patterns each optimizes for, and names the resulting cross-shard cost (e.g., “if we shard by user_id, a query for ‘all orders in region X’ becomes a scatter-gather across every shard”).

Handling Cross-Shard Queries and Joins

When a query must touch multiple shards (scatter-gather), the application layer or a query-routing proxy fans the query out to all relevant shards in parallel and merges results — this adds tail latency (bounded by the slowest shard) and complexity around partial failures. Common mitigations discussed in 2026 interviews include denormalizing frequently-joined data into the shard itself (trading storage for query simplicity) and maintaining a separate analytics store (data warehouse) fed by CDC (change data capture) for cross-shard aggregate queries rather than serving them from the sharded OLTP layer directly.

For a structured walkthrough of sharding alongside the rest of the system design interview loop — including how to pair sharding decisions with caching and replication strategy — see The 0-to-1 SWE Interview Playbook: https://www.amazon.com/dp/B0H256Z1MF?tag=sirjohnnymai-20.

FAQ

Q: When should I NOT recommend sharding in a system design interview? A: When the interviewer’s stated scale doesn’t justify it — e.g., under a few million users and sub-TB data, read replicas plus vertical scaling plus caching usually suffice, and proposing sharding prematurely signals over-engineering rather than judgment.

Q: How do auto-incrementing primary keys work across shards? A: They generally don’t work unmodified — teams use strategies like Snowflake-style IDs (timestamp + shard ID + sequence number embedded in a single 64-bit integer) or UUID/ULID generation to guarantee global uniqueness without a centralized counter.

Q: Is resharding something candidates are expected to design in detail? A: Senior+ interviews expect you to at least name the problem (data movement cost, downtime risk) and propose a mitigation (consistent hashing, dual-write during migration, or a directory service) — full step-by-step migration choreography is usually only expected at staff level.