System Design (50 Qs)•Part 1: Foundational Distributed Systems Concepts & Trade-offs (1 - 10)
Database Sharding Strategies: Range, Hash, and Directory-Based Partitioning.
In Simple Words: Sharding is the horizontal partitioning of a large database across multiple independent physical database instances (shards) when a single database exceeds memory, storage, or connection limits.
| Sharding Strategy | Mechanism | Pros | Cons & Bottlenecks |
|---|---|---|---|
| Hash-Based (Key) | shard_id = hash(sharding_key) % N | Uniform data and traffic distribution | Resharding requires repartitioning data (unless consistent hashing is used) |
| Range-Based | Partition based on value range (e.g., A-E, F-J, or Dates) | Efficient range queries (WHERE date BETWEEN...) | Hot-spots (e.g. current month receives 99% of all write queries) |
| Directory-Based | Lookup service maps entity IDs to specific shard endpoints | Dynamic shard rebalancing with zero downtime | Lookup service is a Single Point of Failure (SPOF) and latency bottleneck |
sql
-- Example Sharding Key Selection Trade-off for Multi-Tenant SaaS
-- Sharding by tenant_id guarantees all data for a company lives on a single shard:
SELECT o.id, o.amount, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.tenant_id = 'tenant_xyz123';
-- ✅ Fast single-shard local JOIN without cross-network distributed transactions!