Sharding

Overview

Sharding splits a dataset horizontally across multiple database servers (shards), each holding a subset of rows, so that data volume and write traffic can grow beyond one machine. A shard key (such as user_id or tenant_id) decides which shard owns each row, and a routing layer sends each query to the right shard.

Sharding is powerful but costly: cross-shard queries, joins, and transactions become hard; resharding when shards fill up requires moving data; and a poor shard key creates hot shards. It is usually the last step after indexing, caching, replicas, and vertical scaling are exhausted.

When to use it

• Write throughput or data size exceeds a single primary.
• Data naturally partitions by a key (tenant, user, region).
• Most queries include that key.
• Isolation between tenants or regions is valuable.

Where it shows up in interviews

Where it is used in real software

Key terms

Shard key

Column(s) that decide which shard owns a row.

Range sharding

Contiguous key ranges per shard; good for range scans, prone to hot spots.

Hash sharding

hash(key) mod N or consistent hashing; even spread, no range scans.

Directory sharding

A lookup table maps keys to shards; flexible but the directory is critical.

Logical shards

Many small virtual shards mapped to fewer physical nodes, easing rebalancing.

How it works, step by step

1. 1
   Choose the shard key

   High cardinality, even load, present in most queries, and keeps related data together.

2. 2
   Choose the strategy

   Hash for even spread, range for scans, directory for flexibility.

3. 3
   Create many logical shards

   For example 1,024 logical shards on 8 servers, so rebalancing moves whole logical shards.

4. 4
   Route queries

   Application library, proxy (Vitess, ProxySQL), or the database itself (Citus).

5. 5
   Handle cross-shard needs

   Scatter-gather for rare queries, denormalized global indexes, and sagas instead of distributed transactions.

Choosing a shard key for a social app

Candidate keys and their effects

The best key matches the dominant access pattern. Celebrities with huge data may still need special handling (splitting or dedicated shards).

Implementation

1import { createHash } from "node:crypto";2 3const LOGICAL_SHARDS = 1024;4// Mapping of logical shard ranges to physical databases (kept in config or a directory service)5const physical = [6  { from: 0, to: 255, pool: shardPool0 },7  { from: 256, to: 511, pool: shardPool1 },8  { from: 512, to: 767, pool: shardPool2 },9  { from: 768, to: 1023, pool: shardPool3 },10];11 12function logicalShard(userId: string) {13  return createHash("md5").update(userId).digest().readUInt32BE(0) % LOGICAL_SHARDS;14}15 16function poolFor(userId: string) {17  const shard = logicalShard(userId);18  return physical.find((p) => shard >= p.from && shard <= p.to)!.pool;19}20 21export const getPosts = (userId: string) =>22  poolFor(userId).query("SELECT * FROM posts WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20", [userId]);23 24// Rare cross-shard query: scatter-gather with a limit per shard25export async function recentGlobal(limit = 20) {26  const results = await Promise.all(physical.map((p) => p.pool.query("SELECT * FROM posts ORDER BY created_at DESC LIMIT $1", [limit])));27  return results.flatMap((r) => r.rows).sort((a, b) => b.created_at - a.created_at).slice(0, limit);28}

Complexity and performance

Trade-offs

Variants and related techniques

Common mistakes

• Sharding too early.

  Fix: Exhaust indexes, caching, replicas, and vertical scaling first.

• hash(key) mod N with physical shards.

  Fix: Changing N remaps everything; use logical shards or consistent hashing.

• Global auto-increment IDs.

  Fix: Use Snowflake-style IDs or UUIDs that embed or allow shard routing.

Interview questions

Practice problems