Why Shard?
A single DB server has limits: max RAM, max disk, max CPU, max concurrent connections. When your data exceeds a single server's capacity or your query rate exceeds its throughput, you must shard (split data across multiple servers).
Sharding vs Replication: replication copies the same data to multiple servers (for reads and fault tolerance). Sharding splits different data to different servers (for writes and storage). Production systems use both: each shard is replicated across multiple nodes.
Horizontal vs Vertical Partitioning
Vertical partitioning: split columns across tables/databases. Put "hot" columns (frequently queried) in a separate table or DB from "cold" columns. Example: move user.profile_picture_blob to a blob store, keep user metadata in MySQL. Easy to implement but doesn't solve the row-count problem.
Horizontal partitioning (sharding): split rows across servers. Each shard has the same schema but a subset of the rows. Example: users with user_id 1-10M on shard 1, 10M-20M on shard 2. This scales row count linearly with the number of shards.
graph LR APP["App Server"] --> Router["Shard Router"] Router --> S1["Shard 1 user_id: 1-10M"] Router --> S2["Shard 2 user_id: 10M-20M"] Router --> S3["Shard 3 user_id: 20M-30M"]
Sharding Strategies
Key-based (hash sharding): shard_number = hash(shard_key) % num_shards. Simple and gives uniform distribution. Fatal flaw: adding a shard requires remapping almost every key and migrating data.
Range-based: shard 1 handles user_id 1-10M, shard 2 handles 10M-20M. Easy to understand, supports range queries. Fatal flaw: hot shards if data is skewed (new users all go to the last shard).
Directory-based: a lookup service maps each key to a shard. Flexible — shards can be added and keys moved with just a lookup table update. Adds a network hop and a dependency on the lookup service.
Consistent hashing (preferred): Map both servers and keys to positions on a virtual ring. Each key is served by the nearest server clockwise. Adding or removing a server only affects its neighbors (O(1/N) of keys remapped). Used by Cassandra, DynamoDB, Riak, and most production distributed datastores.
Common Sharding Problems
Hot spot / celebrity problem: one shard gets disproportionate traffic (e.g., all data for Beyoncé goes to one shard). Solution: add a random suffix to the shard key for hot entities, distributing them across multiple shards.
Cross-shard joins: SQL JOIN across two tables on different shards requires querying both shards and joining in application code. Very expensive. Solution: denormalize data (copy needed fields) or co-locate related data on the same shard.
Cross-shard transactions: ACID transactions across two shards require two-phase commit (2PC), which is complex and slow. Solution: design your data model to avoid cross-shard transactions (all data for a user on the same shard).
Rebalancing: when adding shards, data must be migrated. Use consistent hashing to minimize migration. Blue-green migration: new shard is populated while the old one is still serving traffic; atomic cutover when migration is complete.
Shard Key Selection
The shard key is the most important design decision. Good shard key properties: (1) High cardinality — enough distinct values to distribute data evenly. (2) Low hot spot risk — avoid keys based on time (all new data goes to one shard) or status (all active records go to one shard). (3) Access locality — queries for the same entity always go to the same shard. (4) Immutability — changing the shard key of a record requires moving it to a different shard; design keys that don't change (user_id, not email).
Common shard keys: user_id (used by Facebook, Twitter), tweet_id, product_id, tenant_id (for SaaS multi-tenancy).