Sharding & Partitioning

Overview

When a database becomes too large to fit on a single machine, or when the write throughput exceeds what one node can handle, the data must be split into smaller, independent pieces called shards or partitions. Each piece is hosted on a separate node, allowing the database to scale horizontally almost indefinitely.

🧠 Mental model: Sharding is like splitting a massive library into several smaller buildings by author's last name. Building A holds authors A-F, Building B holds G-M, etc. You can handle much more traffic, but if someone wants books by both Asimov and Tolkien, they must visit two buildings.

Key Concepts

The core challenge of sharding is determining which row goes to which node. This requires a shard key (or partition key), a column chosen to distribute the data. Common sharding strategies include:

Hash Sharding: The shard key is hashed, and the hash value determines the node. This ensures a very even distribution of data and traffic, preventing hotspots, but makes range queries impossible since contiguous keys are scattered randomly.
Range Sharding: Data is split by contiguous ranges of the shard key (e.g., User IDs 1-10000 go to Shard A, 10001-20000 to Shard B). This supports efficient range queries but can cause massive hotspots if traffic concentrates on a specific range (e.g., sorting by timestamp where all new writes hit the last shard).
Directory/Lookup Sharding: A dedicated lookup service maintains a mapping of which shard holds which keys. This allows ultimate flexibility but introduces a single point of failure and an extra network hop.

Sharding Challenges

Challenge	Description	Mitigation
Cross-shard Joins	Joining tables across different nodes is extremely slow and complex.	Denormalize data, or perform the join in application code.
Data Hotspots	One shard receives vastly more traffic (the "Justin Bieber problem").	Choose a high-cardinality shard key and use hash sharding.
Rebalancing	Moving data when adding or removing shards is disruptive.	Use Consistent Hashing to minimize data movement.
Distributed Transactions	Transactions spanning shards require 2PC (Two-Phase Commit).	Design schema so transactions stay within a single shard.

Trade-offs

Sharding allows virtually infinite horizontal scaling for writes and storage, avoiding the strict ceiling of vertical scaling. However, it severely increases operational complexity. Backups, schema migrations, and rebalancing become distributed systems problems. Furthermore, application logic must become "shard-aware" or rely on a smart proxy layer to route queries. Because of this complexity, sharding should be a last resort after indexing, caching, replication, and vertical scaling are exhausted.

Interview Tips

Never default to sharding on day one. Always start with a single primary and read replicas. Propose sharding only when the capacity math (e.g., storage limits or write throughput) demands it.
Be prepared to defend your shard key. If you shard a social network by user ID, how do you handle celebrity users with millions of followers? (Hint: caching at the edge, or custom routing).
Mention Consistent Hashing as the standard technique to handle adding/removing shards without moving all your data.

Summary

Sharding splits data across multiple nodes to scale storage and write throughput horizontally.
Hash sharding distributes evenly but breaks range queries; range sharding supports ranges but risks hotspots.
Choosing the right shard key is critical to avoid unbalanced load (hotspots).
Cross-shard joins and transactions are prohibitively expensive and usually avoided.
Sharding adds immense operational complexity and should be treated as a last resort.