Horizontal Partitioning Mechanics:
- Definition: Sharding splits a large database across multiple independent physical database instances (shards) that share no memory or disk (Shared-Nothing Architecture).
1. Range-Based Sharding:
- Logic: Data is partitioned based on continuous ranges of a key (e.g., User IDs 1-1M on Shard 1, 1M-2M on Shard 2; or by creation year).
- Advantages: Range queries (WHERE date BETWEEN ...) are straightforward because data resides on a single contiguous shard.
- Disadvantages: Suffers from hot-spotting: writing current data floods the newest shard while older shards sit idle.
2. Hash-Based / Key-Based Sharding:
- Logic: Passes the shard key through a cryptographic or uniform hash function: shard = hash(key) % num_shards.
- Advantages: Distributes read and write traffic evenly across all shards, preventing write hotspots.
- Disadvantages: Range queries must scatter-gather across all shards. Adding new shards requires expensive re-hashing unless Consistent Hashing is used.
3. Shard Key Selection:
- The choice of shard key is permanent: pick high-cardinality keys that align with core query access patterns.