Database Sharding: Strategies and Implementation

When to Shard

Before sharding, try:

1. Vertical scaling (bigger machine)
2. Read replicas (scale reads)
3. Caching (reduce DB load)
4. Archival (move old data to cold storage)
5. Table partitioning (split within single DB)

Shard when: Single node can't handle write throughput OR storage exceeds practical limits.

Sharding Strategies

Range Sharding

SHARD_RANGES = [
    (0, 10_000_000, 'shard-1'),
    (10_000_000, 20_000_000, 'shard-2'),
    (20_000_000, float('inf'), 'shard-3'),
]

def get_shard(user_id: int) -> str:
    for start, end, shard in SHARD_RANGES:
        if start <= user_id < end:
            return shard

Pros: Simple, range queries efficient Cons: Hotspot risk, rebalancing hard

Hash Sharding

import hashlib

def get_shard(user_id: str, num_shards: int = 4) -> int:
    hash_val = int(hashlib.md5(user_id.encode()).hexdigest(), 16)
    return hash_val % num_shards

Pros: Even distribution, no hotspots Cons: Range queries hit all shards, adding shards requires remapping

Consistent Hashing

import bisect, hashlib

class ConsistentHashRing:
    def __init__(self, replicas=150):
        self.ring: dict = {}
        self.sorted_keys: list = []
        self.replicas = replicas

    def add_node(self, node: str) -> None:
        for i in range(self.replicas):
            key = int(hashlib.md5(f"{node}:{i}".encode()).hexdigest(), 16)
            self.ring[key] = node
            bisect.insort(self.sorted_keys, key)

    def get_node(self, key: str) -> str:
        hash_key = int(hashlib.md5(key.encode()).hexdigest(), 16)
        idx = bisect.bisect(self.sorted_keys, hash_key) % len(self.sorted_keys)
        return self.ring[self.sorted_keys[idx]]

# Only ~1/N keys remapped when adding node
ring = ConsistentHashRing()
ring.add_node('shard-1')
ring.add_node('shard-2')

Application-Level Sharding

class ShardedDatabase {
  private shards: Database[];

  private getShard(key: string): Database {
    const hash = createHash('md5').update(key).digest('hex');
    const index = parseInt(hash.substring(0, 8), 16) % this.shards.length;
    return this.shards[index];
  }

  async findUser(userId: string): Promise<User | null> {
    return this.getShard(userId).query('SELECT * FROM users WHERE id = $1', [userId]);
  }

  // Fan-out cross-shard query
  async findOrdersByDate(date: Date): Promise<Order[]> {
    const results = await Promise.all(
      this.shards.map(s => s.query('SELECT * FROM orders WHERE created_at > $1', [date]))
    );
    return results.flat();
  }
}

Shard key selection is critical. Choose based on access patterns, cardinality, and distribution.