Explain database sharding and the different sharding strategies available. How do you choose the right shard key, and what are the consequences of a poor choice?

Question

Accepted Answer

Database sharding is a horizontal scaling strategy that distributes data across multiple independent database instances, called shards, to handle workloads that exceed the capacity of a single server. Each shard holds a subset of the total data, and together they form the complete dataset. Sharding enables a database to scale write throughput, storage capacity, and query performance beyond what vertical scaling can achieve.

There are several sharding strategies, each with distinct characteristics. Range-based sharding divides data by ranges of the shard key value. For example, customers with IDs 1 through 1,000,000 go to shard one, 1,000,001 through 2,000,000 to shard two, and so on. This supports efficient range queries on the shard key but can create hotspots if data access is concentrated in recent ranges, which is common with time-based or sequential keys.

Hash-based sharding applies a hash function to the shard key and uses the result to determine the shard. This distributes data more evenly across shards, avoiding the hotspot problem of range-based sharding. However, it makes range queries across the shard key expensive because they must query all shards. Consistent hashing is a refinement that minimizes data movement when shards are added or removed.

Directory-based sharding maintains a lookup table that maps each shard key value to a specific shard. This provides maximum flexibility because the mapping can be arbitrary, but it introduces a single point of lookup that must be highly available and performant. This approach is useful when data placement needs to follow business rules, such as placing a customer's data in a region-specific shard.

Geographic sharding places data based on the geographic origin or primary access location. This reduces latency for users by keeping their data physically close and can help with data sovereignty compliance.

Choosing the right shard key is the most critical sharding decision. The ideal shard key has high cardinality to distribute data across many shards evenly, is present in most queries to enable single-shard operations, distributes write load evenly to avoid creating a hot shard, and rarely changes because changing a shard key value requires moving data between shards.

A poor shard key choice has severe consequences. If the key has low cardinality, data concentrates on few shards, negating the scaling benefit. If the key is not included in common queries, every query becomes a scatter-gather operation that hits all shards. If writes concentrate on one shard, that shard becomes a bottleneck while others sit idle. These problems are difficult to fix after deployment because resharding typically requires significant downtime or a complex online migration.

Common shard key choices include tenant ID for multi-tenant applications, user ID for user-centric applications, and composite keys combining geography with a hash for workloads that need both locality and distribution. I always recommend load testing with production-representative data before committing to a sharding strategy, because theoretical analysis often misses real-world access pattern skew.