Database Sharding in System Designing

advance · System Designing

While Database Replication scales your read throughput by adding mirror copies of the data, it does not solve the problem of scaling write throughput. On a single primary node, you will eventually hit limits on disk storage capacity and CPU performance. To scale writes, you must use Database Sharding . Sharding is the architectural pattern of breaking up a single massive database table and distributing the pieces horizontally across multiple completely independent database instances (called shards ). 1. Shared-Nothing Architecture Unlike replication, where every node holds a copy of the entire dataset, sharding uses a Shared-Nothing Architecture . Each shard is a distinct database engine that owns a mutually exclusive subset of the total data. 2. Shard Key Choice Is Critical A Shard Key is the specific column or attribute in your data schema that determines exactly which shard a particular row will be routed to. Once you choose a shard key, it is incredibly difficult to change without taking your entire application offline for a massive data migration. Common Sharding Strategies: Range-Based Sharding: Data is split based on a range of values (e.g., Shard 1 holds users with names A–G, Shard 2 holds H–O, etc.). The Flaw: It naturally creates uneven distributions. If you suddenly get a million new users whose names start with 'J', Shard 2 will experience a massive performance bottleneck while Shard 1 sits completely idle. Hash-Based Sharding: The system takes the shard key (like user_id ), passes it through a mathematical hash function, and uses the modulus operator against the total number of shards ( $N$ ) to find the destination: $\text{Shard ID} = \text{Hash}(\text{user\_id}) \pmod N$ The Flaw: If your system grows and you need to add an extra shard (changing $N$ to $N+1$ ), the mathematical output for almost every single key changes. This forces you to re-shard and move up to 90% of your existing data across the network. 3. The Hotspot Problem (Uneven Distribution) A Hotspot occurs when a specific shard receives a disproportionate amount of read or write traffic compared to the rest of the cluster, causing it to run out of disk space or max out its CPU. This is almost always caused by a poor choice of a shard key: The Celebrity Problem: If you shard a social media application by actor_id or user_id , a standard user's shard will handle minimal traffic. However, the shard holding a celebrity with 100 million followers will buckle under the load of millions of concurrent writes and reads, creating a critical hotspot. 4. Resolving Re-sharding: Consistent Hashing To scale a sharded database horizontally without triggering a massive data migration nightmare every time you add a new machine, systems use Consistent Hashing . How it works: Imagine a conceptual circle mapped with numeric positions from $0$ to

Careeroza — One-stop Zone for Aspirants

Study material, Careeroza mentorship, tech jobs, and career guidance on careeroza.com.

Public study materials

^{32}-1$ (The Hash Ring ). Both your database servers (shards) and your data keys are passed through a hash function that maps them to a specific point along this ring. To determine where a data row lives, you locate its key on the ring and walk clockwise until you hit the first available database server. The Elegant Benefit: When you add a new shard node to a consistent hashing ring, you do not need to reassign all your data . The new shard only intercepts a small fraction of the keys from its immediate counter-clockwise neighbor. On average, only $\frac{K}{N}$ keys need to be moved (where $K$ is the total number of keys and $N$ is the total number of shards). 5. The Massively Complex Trade-offs of Sharding Before implementing sharding, you must be prepared to handle severe application-layer complexity: No Multi-Shard Joins: You cannot easily execute a SQL JOIN query across tables that sit on different physical shards over a network. Your application code must execute multiple independent queries and stitch the data together manually in memory. Loss of Referential Integrity: Enforcing global foreign key constraints across different machines is near impossible without introducing massive network performance penalties. Complex Transactions: Standard ACID properties break down across shards. Ensuring that a transaction fully succeeds or fails across two separate databases requires slow, complex coordination algorithms like the Two-Phase Commit (2PC) protocol.

Back to System Designing

Browse all study material on Careeroza