Day 04 · System Design
Database Sharding & Horizontal Partitioning
Split one logical dataset across many databases when a single primary can no longer keep up with storage or write volume.
01Overview & core concept
When a dataset exceeds the physical storage limits of a single machine, or write throughput saturates the primary database node, replication is no longer sufficient. Database sharding (horizontal partitioning) splits a single logical dataset across multiple independent physical database instances (shards). Each shard holds a subset of the total data and operates as an autonomous database with identical table schemas but distinct rows.
Shard key
The column used to decide which shard owns a row. Choosing it well determines balance, query cost, and hotspots.
Routing layer
A component (e.g., Vitess, Citus) that maps each query to the right shard based on the shard key.
02Architecture & request flow
┌───────────────────────────┐
│ Application / API │
└─────────────┬─────────────┘
│ SQL Query (with Shard Key)
▼
┌───────────────────────────┐
│ Sharding / Routing Layer │ (e.g., Vitess, Citus)
└──────┬──────┬──────┬──────┘
hash(user_id) % 3 │ │ │
┌─────────┘ │ └─────────┐
▼ ▼ ▼
┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│ Shard 0 │ │ Shard 1 │ │ Shard 2 │
│ (Users 0-3M) │ │ (Users 3-6M) │ │ (Users 6-9M) │
└───────────────┘ └───────────────┘ └───────────────┘Partitioning approaches
Hash-Based (Modulo) Sharding
Computes hash(shard_key) % num_shards to pick the target shard. Pros: even data distribution. Cons: adding shards requires migrating a large percentage of data (addressed by Consistent Hashing).
Range-Based Sharding
Partitions by ranges of values (e.g., user_id 1–1,000,000 on Shard 1, or by date). Pros: simple range queries across contiguous keys. Cons: hotspotting if recent ranges receive all write traffic.
Directory / Lookup-Based Sharding
A centralized lookup service maps entity IDs to shard locations. Pros: maximum flexibility for dynamic shard relocation. Cons: the lookup service can become a bottleneck and single point of failure (requires aggressive local caching).
03Real-world example: Slack Sharding by Workspace ID
Entity Grouping (Workspace as Shard Key)
Communication predominantly happens within isolated teams, so Slack shards its MySQL clusters using workspace_id.
Co-location of Data
All channels, messages, and member profiles belonging to a given workspace live on the same physical shard.
Eliminating Cross-Shard Overhead
Because 99%+ of queries (e.g., a channel's message history or member list) touch data within a single workspace, complex distributed joins and cross-shard transactions are avoided entirely.
04Architectural trade-offs
| Dimension | Single / Replicated Database | Sharded Database Cluster |
|---|---|---|
| Write Scalability | Bound by the single primary's I/O and CPU. | Scales horizontally; each new shard adds write throughput. |
| Storage Capacity | Bound by single-node disk capacity. | Virtually unlimited aggregate storage. |
| Query Complexity | Supports full relational joins, transactions, and foreign keys. | Cross-shard joins and distributed transactions are prohibitively slow or unsupported. |
| Operational Complexity | Low; standard backups and migrations. | High; schema migrations, re-sharding, and rebalancing require custom tooling. |
Failure modes & mitigations
The "Celebrity" / Hotspot Problem
A high-traffic entity (e.g., a massive Slack workspace or a celebrity user with millions of followers) exhausts a single shard's capacity while other shards sit idle. Mitigation: isolate outlier tenants onto dedicated hardware or sub-shard their data using secondary keys (e.g., workspace_id + channel_id).
Scatter-Gather Queries
Queries that lack the shard key in their WHERE clause must be broadcast to every shard in parallel, then merged in memory. Mitigation: enforce routing rules in the API layer or build secondary global search indexes (e.g., Elasticsearch).
05Interview question & approach
How would you choose a shard key and design the storage tier for a global peer-to-peer payment application (like Venmo or PayPal) where users both send and receive money?
The Dual-Relationship Challenge
A transaction involves two users (sender_id and receiver_id). If you shard by user_id, a single payment inevitably spans two different shards.
Design Options
Option A: shard by transaction_id. It distributes writes evenly, but retrieving a user's history requires scatter-gather queries across all shards. Option B: dual-write / denormalize by user_id. When user A sends money to user B, record an outbound transaction on A's shard and an inbound transaction on B's shard via an asynchronous message queue (e.g., Kafka).
Consistency & Idempotency
Use an idempotent transaction coordinator or the Saga pattern with unique idempotency keys so neither sender nor receiver is double-credited or debited across shard boundaries.