Horizontal partitioning — range vs hash sharding, hot spots, and cross-shard queries.
Published September 21, 2026
Sharding (horizontal partitioning) splits one logical dataset across several independent database servers, called shards. Each shard holds a subset of the rows, for example the users whose IDs hash to it. Together, the shards hold everything.
It's how systems grow beyond what one machine can handle: more data than one server can store or keep in memory, or more writes per second than one leader can absorb. Read replicas can't solve that, because every replica still has to apply every write.
Sharding is powerful, but it's also one of the most expensive architectural decisions to make, and to undo. Consider it after cheaper options are exhausted:
Every sharding design is really a shard key decision. The key determines which shard stores each row, and therefore:
A good key has high cardinality, even access frequency, and matches the dominant query pattern. For a SaaS product, tenant_id keeps each customer's data on one shard, so almost every query and transaction is single-shard. For a social app, user_id keeps a user's posts together.
shard = hash(key) mod N, or more flexibly, hash into many fixed buckets (virtual partitions) that are then assigned to shards.
user_id BETWEEN …) must hit every shard.mod N, changing N remaps almost every key. Changing from 4 to 5 shards moves about 80% of the data.The fix is to decouple keys from physical servers:
Contiguous key ranges go to each shard: A–F, G–M, or date ranges.
A mapping service records which shard holds each key or tenant.
Even distribution by key count isn't the same as even load:
Mitigations:
post_123#0 … post_123#9) to spread its writes. Reads must then gather and merge all the sub-keys.(sensor_id, day) spread time-series writes.What was simple on one database becomes hard:
WHERE email = ? when sharded by user_id) must scatter-gather across all shards: send the query to every shard in parallel and merge the results. Latency is set by the slowest shard, and the load multiplies.
email → user_id, itself sharded by email.user_id alongside users), or join in the application.Eventually shards fill up, and data must move while the system stays online. The usual approach:
Systems with virtual partitions and automatic balancing (MongoDB's balancer, Cassandra, DynamoDB, Vitess, Citus, CockroachDB) do most of this for you. That's a strong reason to prefer them over hand-rolled application-level sharding.
Q: How would you shard a multi-tenant SaaS database?
A: Shard by tenant_id, so each tenant's data and transactions stay on one shard, and use a directory so that very large tenants can be moved to dedicated shards. Cross-tenant analytics go to a separate warehouse.
Q: Why is hash(key) % N a poor choice, and what replaces it?
A: Changing N remaps nearly every key, forcing a massive data migration. Use many fixed virtual partitions assigned to servers, or consistent hashing, so adding a server moves only a small fraction of the data.
Q: How do you look up a user by email when sharding by user ID? A: Maintain a global secondary index (a lookup table from email to user ID, sharded by email). Query it first, then go to the right user shard. The alternative, scatter-gather across all shards, doesn't scale for frequent queries.
Q: What's a hot partition, and how do you fix it? A: One shard receiving disproportionate traffic, because of a popular key or a sequential key pattern. Fixes include caching, isolating hot keys on dedicated shards, adding random suffixes to split writes, and choosing keys that don't concentrate new writes in one place.
Q: When should you not shard? A: When the data fits on one well-provisioned server and the write rate is manageable. Sharding adds permanent complexity to queries, transactions, operations and schema changes, so use every simpler option first.