Database Sharding: Choosing a Shard Key Without Creating Hotspots

Learning outcome: By the end of Day 09, you will be able to explain how sharding partitions a dataset, estimate an even split across shards, and identify how a poor shard key creates a hot shard.

This lesson is part of Korshub's System Design in 30 Days series.

What is database sharding?

Sharding is a horizontal partitioning technique: one logical dataset is divided across multiple database servers called shards. Each shard stores only a subset of the data and processes a subset of the workload.

The database or routing layer uses a shard key to decide where a record belongs. A useful mental model is one giant filing cabinet divided into smaller cabinets. The shard key is the rule that selects the correct cabinet.

Sharding can increase storage and read/write capacity by adding machines, but it also introduces routing, balancing, operational, and cross-shard-query complexity. It should solve a measured bottleneck, not be a default starting point.

Worked example: 12 million users across four shards

Assume:

• 12,000,000 user records;

• four shards;

• hash(user_id) produces a sufficiently even distribution for this estimate.

The estimated records per shard are:

12,000,000 users4 shards=3,000,000 users per shard

The back-check is exact:

3,000,000×4=12,000,000

We still say about three million users per shard because real hash distributions, record sizes, and traffic are not perfectly uniform. Capacity estimates depend on their assumptions.

This formula is a teaching model. Production databases may use ranges, chunks, virtual partitions, balancers, or routing metadata rather than a direct modulo rule.

Hash-based versus range-based sharding

Suppose you shard by raw signup time. Every new signup has the latest timestamp, so the shard responsible for the highest range may receive most new writes. That server becomes a hot shard even if the older data looks balanced.

Hashing a high-cardinality user identifier can spread inserts more evenly. The trade-off is that queries such as "all users created this week" may no longer target one shard unless the data model and key include a suitable query dimension.

How to evaluate a shard key

Ask these questions before choosing the key:

1. Distribution: Will the key spread data and write traffic evenly?

2. Cardinality: Does it have enough distinct values to create useful partitions?

3. Query routing: Do common requests include the shard key so the router can target specific shards?

4. Growth pattern: Will new values always land in one range?

5. Locality: Which records need to be queried or updated together?

A key that balances storage but concentrates writes is still a poor key. A key that balances writes but forces every important request to scan every shard may also be a poor fit.

Try this today

For a multi-tenant SaaS product, compare these candidate shard keys:

• user_id;

• country;

• signup_time.

Write down how each key affects distribution, write concentration, and your two most common queries. Do not choose a key until you can explain all three effects.

Completed example you can copy

System: Consumer application with 12 million users.

Common access pattern: Fetch one user by user_id.

Candidate key: Hashed user_id.

Reasoning: The identifier has high cardinality, equality lookups can carry the shard key, and hashing reduces the risk that sequential IDs concentrate inserts on one shard.

Estimate: Four shards hold about three million users each under the uniform-distribution assumption.

Trade-off: Queries by country or signup range may require scatter/gather work or a separate index/data model.

Common mistakes

Choosing a low-cardinality key

A key such as account_type with only a few values cannot create many well-balanced partitions.

Optimizing only storage balance

Even record counts do not guarantee even CPU, I/O, or request load. One shard may hold a disproportionately active group.

Using an increasing range key without planning for hotspots

Timestamps and sequential IDs can push incoming writes to the newest range.

Ignoring queries that omit the shard key

If a query cannot be routed to a specific shard, the system may broadcast it and combine results from multiple shards.

Treating sharding as replication

Sharding partitions data for horizontal scale. Replication keeps copies for availability and read scaling. A production system may use both, but they solve different primary problems.

Knowledge check

1. With 12 million users and four evenly distributed shards, what is the estimated number of users per shard?

2. Why can a monotonically increasing range key create a hot shard?

3. What is one trade-off of hashing a shard key?

Answers

1. About 3 million users per shard: 12,000,000 / 4 = 3,000,000.

2. Most new values fall into the latest range, concentrating incoming writes on the shard that owns it.

3. Hashing can spread data and writes more evenly, but range queries on that key may need to contact multiple shards.

Continue learning with Korshub

Use this lesson as a shard-key checklist during interviews: name the access pattern, evaluate distribution, explain the hotspot risk, and state the query-routing trade-off. Continue with the Rocking System Design course for deeper architecture practice.

Navigation

• Previous: Day 08 - Database Replication

• Roadmap: System Design in 30 Days

• Next lesson: Day 10 - CAP Theorem