Database sharding — when to shard, choosing a shard key, and the problems it creates

Question

Accepted Answer

116
   Database sharding — when to shard, choosing a shard key, and its trade-offs
  Level: Very Hard — The ultimate scaling question; the shard-key choice is a near-irreversible decision that makes or breaks the system.

  
     Scenario: Your orders table has grown to 5 billion rows and 4 TB. A single MySQL server can't handle the write throughput or storage anymore — even with replicas (which only scale reads). The interviewer asks: "How do you scale writes? Walk me through sharding, how you'd pick the shard key, and what breaks."
  

   What Sharding Is (and isn't)
  Sharding = horizontal partitioning across multiple servers. Each shard holds a subset of the rows. Unlike replication (which copies all data to scale reads), sharding splits data to scale writes and storage.

  
     Shard only as a last resort: Sharding adds enormous complexity. Exhaust simpler options first — indexing, query tuning, read replicas, caching, vertical scaling, partitioning. Shard only when a single node genuinely can't handle the write volume or data size.
  

   Sharding Strategies
  
    
      
        StrategyHowWeakness
      
      
        
          Range-based
          Shard by id/date ranges (1–1M → shard A)
          Hotspots (newest shard gets all writes)
        
        
          Hash-based
          hash(shard_key) % N
          Re-sharding requires rehashing everything
        
        
          Directory/lookup
          A lookup table maps key → shard
          Lookup service is a bottleneck/SPOF
        
        
          Consistent hashing
          Hash ring; adding nodes moves minimal data
          More complex to implement
        
      
    
  

   Choosing the Shard Key — The Critical Decision
  A good shard key must satisfy three properties:
  
     Even distribution: Spreads data/load uniformly (avoid hotspots).
     Query locality: Most queries should hit a single shard (avoid cross-shard scatter-gather).
     Stable: The value shouldn't change (moving rows between shards is painful).
  

   Example — Sharding by customer_id
-- Shard key = customer_id, hashed across 4 shards
-- shard_number = customer_id % 4

-- Customer 1001 -> 1001 % 4 = 1 -> Shard 1
-- All of a customer's orders live on ONE shard (great locality)

-- Query for one customer -> single shard, fast:
-- (routed by app/proxy to Shard 1)
SELECT * FROM orders WHERE customer_id = 1001;

-- PROBLEM query -> "all orders today" must hit ALL shards (scatter-gather):
-- SELECT * FROM orders WHERE order_date = CURDATE();  -- fan-out to every shard

  
     Why customer_id often wins: Most queries are "show me this customer's orders" — all on one shard. Sharding by order_id instead would scatter a customer's orders across shards, making their order history a cross-shard query. Shard by the dominant access pattern's key.
  

   The Hard Problems Sharding Creates
  
    
      
        ProblemWhy It's Hard
      
      
        Cross-shard JOINsCan't JOIN across servers; must gather in app or denormalize
        Cross-shard transactionsNo single ACID transaction; need 2-phase commit or sagas
        Unique IDsAUTO_INCREMENT collides across shards; need UUID/Snowflake
        Aggregations"Total revenue" must query every shard and merge
        Re-shardingAdding shards means migrating data live — very risky
        HotspotsA "celebrity" key can overload one shard
      
    
  

   Solving Unique IDs Across Shards
-- AUTO_INCREMENT would collide (each shard starts at 1). Options:

-- Option A: interleaved auto-increment (offset per shard)
-- Shard 0: 1,5,9...  Shard 1: 2,6,10...  (auto_increment_increment=4)
SET @@auto_increment_increment = 4;   -- number of shards
SET @@auto_increment_offset    = 1;   -- this shard's offset

-- Option B (preferred): globally-unique time-sortable IDs (Snowflake)
-- 64-bit: [timestamp][shard/machine id][sequence] -> unique & ordered

  
     Mitigate re-sharding pain: Start with more logical shards than physical servers (e.g., 1024 logical shards mapped onto 4 servers). To scale, you move whole logical shards to new servers without rehashing keys — the row-to-shard mapping never changes, only shard-to-server placement.
  

  
     Interviewer follow-up: "You sharded by customer_id, but now the analytics team needs 'top products across all customers.' How?" → That's an inherently cross-shard aggregation. Don't force it through the sharded OLTP DB — stream the data to a separate analytics store (data warehouse like BigQuery/Redshift, or a columnar DB) via CDC/ETL. Keep the sharded MySQL for OLTP; do cross-cutting analytics elsewhere.

Strategy	How	Weakness
Range-based	Shard by id/date ranges (1–1M → shard A)	Hotspots (newest shard gets all writes)
Hash-based	`hash(shard_key) % N`	Re-sharding requires rehashing everything
Directory/lookup	A lookup table maps key → shard	Lookup service is a bottleneck/SPOF
Consistent hashing	Hash ring; adding nodes moves minimal data	More complex to implement

Problem	Why It's Hard
Cross-shard JOINs	Can't JOIN across servers; must gather in app or denormalize
Cross-shard transactions	No single ACID transaction; need 2-phase commit or sagas
Unique IDs	AUTO_INCREMENT collides across shards; need UUID/Snowflake
Aggregations	"Total revenue" must query every shard and merge
Re-sharding	Adding shards means migrating data live — very risky
Hotspots	A "celebrity" key can overload one shard

Database sharding — when to shard, choosing a shard key, and the problems it creates

Answer with Explanation

Database sharding — when to shard, choosing a shard key, and its trade-offs

What Sharding Is (and isn't)

Sharding Strategies

Choosing the Shard Key — The Critical Decision

Example — Sharding by customer_id

The Hard Problems Sharding Creates

Solving Unique IDs Across Shards