🍕 Database Sharding

Central Library (overwhelmed)
      ↓ Split by topic
┌─────────────┬─────────────┬─────────────┐
│ Branch A    │ Branch B    │ Branch C    │
│ Fiction     │ Non-Fiction │ Reference   │
│ 10K books   │ 10K books   │ 10K books   │
└─────────────┴─────────────┴─────────────┘

Each branch handles its own section.
Together, they hold everything.
Visitors go to the branch they need.

One database, growing (illustrative numbers):
  Year 1: smaller dataset + low QPS → Fast!
  Year 2: bigger dataset + more QPS → Slower
  Year 3: very large + very high QPS → hardware pressure
  Year 4: keeps growing → operational pain 🔥

"Just buy a bigger server!"

More RAM and bigger disks → $$$$$

Problems:
  - Expensive hardware
  - Can still be a single point of failure unless you add replication/failover
  - Eventually hits limits

"Split the data across multiple servers!"

┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐
│ Shard A  │ │ Shard B  │ │ Shard C  │ │ Shard D  │
│ data     │ │ data     │ │ data     │ │ data     │
└──────────┘ └──────────┘ └──────────┘ └──────────┘
     ↓           ↓           ↓           ↓
  load        load        load        load

Same total data, distributed load.
Add more shards as you grow.

Shard by user_id:

Shard 1: user_id 1 - 1,000,000
Shard 2: user_id 1,000,001 - 2,000,000
Shard 3: user_id 2,000,001 - 3,000,000
Shard 4: user_id 3,000,001+

User 500,000 → Shard 1
User 1,500,000 → Shard 2

shard_id = hash(user_id) % num_shards

User 12345: hash("12345") = 98765432
            98765432 % 4 = 0 → Shard 0

User 67890: hash("67890") = 54321098
            54321098 % 4 = 2 → Shard 2

Lookup Service:
┌───────────┬─────────┐
│ Key       │ Shard   │
├───────────┼─────────┤
│ user_123  │ Shard 2 │
│ user_456  │ Shard 1 │
│ user_789  │ Shard 3 │
└───────────┴─────────┘

Query: "Where is user_456?"
Answer: "Shard 1"

✓ High Cardinality
  Many unique values = even distribution
  Good: user_id (millions of unique values)
  Bad: country (only ~200 values)

✓ Even Distribution
  Data and queries spread equally
  No "hot" shards

✓ Matches Query Patterns
  Related data queried together stays together

Many multi-tenant products shard by tenant_id/workspace_id.
Many consumer products shard by user_id.

Goal: keep "data you fetch together" on the same shard.

Query: "Find all users who bought Product X"

Without sharding:
  SELECT * FROM users
  JOIN orders ON users.id = orders.user_id
  WHERE orders.product = 'X'
  → Fast, single database

With sharding:
  Query Shard 1, 2, 3, 4...
  Merge results in application
  → Slow, network overhead

Before: 3 shards with hash(key) % 3
After:  4 shards with hash(key) % 4

Problem: hash("12345") % 3 = 1
         hash("12345") % 4 = 2
         → Data needs to move!

Solutions (common approaches):
  - Consistent hashing / hash rings: reduce movement when adding/removing shards
  - Virtual shards (many small logical shards mapped to fewer physical nodes)
  - Range splitting with planned rebalancing

User transfers money from Account A to Account B

If accounts on different shards:
  - A single-shard database transaction may not be enough
  - Options include distributed transactions (e.g., 2PC) in some systems,
    or application-level patterns like sagas/compensation + eventual consistency

Partitioning:
┌─────────────────────────────────────┐
│           ONE DATABASE              │
│ ┌─────────┬─────────┬─────────┐    │
│ │ Table-A │ Table-B │ Table-C│    │
│ └─────────┴─────────┴─────────┘    │
└─────────────────────────────────────┘

Sharding:
┌───────────┐   ┌───────────┐   ┌───────────┐
│ Database 1│   │ Database 2│   │ Database 3│
│   Shard   │   │   Shard   │   │   Shard   │
└───────────┘   └───────────┘   └───────────┘

"We need to shard for scale!"
(Currently have a small user base)

PostgreSQL handles millions of rows just fine.
Shard when you have actual problems.

Shard key: created_date

Result: All new data → latest shard (hot spot)
        Old shards sit idle

Better: user_id or tenant_id (spread across shards)

"We'll just JOIN across shards"

Reality: per-shard latency adds up quickly as shard count grows
         Network calls, data transfer, result merging

Design to avoid cross-shard queries.

"4 shards should be enough forever"

Growth happens. Plan how you'll add shards.
Use consistent hashing from the start.