Backend Study Lab

System Design

Database Sharding Explained

Learn what database sharding is, when backend systems need it, how shard keys work, and what tradeoffs sharding creates.

By CloveCode Studio Jun 29, 2026 Updated Jul 5, 2026 5 min read

Quick answer

Database sharding splits one logical dataset across multiple database instances or partitions. Each shard stores a subset of the data.

Sharding can help a backend scale beyond one database server, but it adds significant complexity around shard keys, routing, transactions, migrations, joins, and operations.

Why sharding exists

A single database can scale surprisingly far with good schema design, indexes, query tuning, caching, and read replicas. But eventually one database may hit limits:

  • Too much data for one machine.
  • Too many writes for one primary.
  • Too much storage growth.
  • Hot tables that cannot be optimized enough.
  • Operational need to isolate large tenants or regions.

Sharding spreads the load. Instead of every user living in one database, users may be split across many shards.

A simple shard example

Suppose users are sharded by user_id.

Shard 0: users where hash(user_id) % 4 = 0
Shard 1: users where hash(user_id) % 4 = 1
Shard 2: users where hash(user_id) % 4 = 2
Shard 3: users where hash(user_id) % 4 = 3

When the API needs user 123, the application computes which shard owns that user and routes the query there.

request -> shard router -> shard 3 -> query user 123

The application now needs a reliable shard routing layer.

Choosing a shard key

The shard key decides how data is distributed.

Good shard keys:

  • Have high cardinality.
  • Distribute load evenly.
  • Match common query patterns.
  • Avoid putting too much traffic on one shard.
  • Keep related data together when possible.

Common shard keys include user ID, account ID, tenant ID, organization ID, region, or a hash of a stable identifier.

Hot shards

A hot shard receives much more traffic than other shards.

This can happen when:

  • One tenant is much larger than others.
  • The shard key is time-based and all new writes hit the newest shard.
  • A celebrity user or popular object gets extreme traffic.
  • The hash function or key distribution is uneven.

Hot shards reduce the value of sharding because the bottleneck simply moves to one overloaded shard.

Range sharding vs hash sharding

Range sharding assigns ranges of keys to shards:

user_id 1-1,000,000 -> shard A
user_id 1,000,001-2,000,000 -> shard B

It can make range queries easier, but it can create hotspots if new IDs are always increasing.

Hash sharding hashes the key and spreads values across shards:

hash(user_id) % shard_count

It distributes data more evenly, but range queries and resharding can become harder.

Sharding and transactions

Transactions are simple when all related rows live on one database. They become harder when one operation touches multiple shards.

For example, transferring data from one account shard to another may require distributed coordination or a saga-like workflow. That is much more complex than a single local transaction.

Read Database Transactions Explained before designing multi-shard write workflows.

Sharding and queries

Sharding changes query design.

Easy query:

SELECT *
FROM orders
WHERE account_id = 42;

If account_id is the shard key, the router can send this to one shard.

Hard query:

SELECT *
FROM orders
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 100;

This may need to query every shard, merge results, and sort globally. Cross-shard queries are often slower and more complex.

When not to shard yet

Do not shard only because the system might grow someday.

Try simpler tools first:

  • Add or improve indexes.
  • Archive cold data.
  • Use read replicas.
  • Cache repeated reads.
  • Move expensive analytics out of the primary database.
  • Optimize the highest-cost queries.

Read Database Indexes Explained and Caching Strategies Explained before jumping to sharding.

Resharding and tenant movement

Sharding is not finished when the first shard key is chosen. Data distribution changes over time. Some tenants grow faster than expected, one shard may fill up, or a region may need to move for latency or compliance reasons.

Plan for movement early:

  • Keep a shard map that can change without redeploying every service.
  • Make routing observable so teams can see where a tenant lives.
  • Design migrations that copy, verify, and cut over data safely.
  • Freeze or serialize writes during sensitive movement windows when needed.
  • Keep rollback plans for failed moves.

A fixed formula such as hash(user_id) % 4 is simple, but it makes changing the shard count painful because many keys move at once. Directory-based routing or consistent hashing can make future movement more manageable, though they add their own operational complexity.

Operational requirements

A sharded system needs tooling beyond normal database dashboards. Operators need to answer questions such as: which shard owns this account, which shard is hottest, which backups cover this tenant, and which queries are fanning out across every shard?

Backups and restores become more complicated. Restoring one tenant may require finding all related records on the correct shard and checking whether derived systems also need repair. Schema migrations also need careful rollout because every shard must end up on a compatible version.

Sharding should be treated as an operational commitment. If the team cannot observe, move, back up, and repair shards, the system may become harder to run than the original single-database bottleneck.

Common mistakes

The first mistake is choosing a shard key that does not match access patterns. If most queries need data across all shards, the design will be painful.

The second mistake is ignoring resharding. Systems grow. A fixed shard count or poor routing strategy can become a future migration trap.

The third mistake is assuming sharding improves every query. It helps targeted queries but can make global queries harder.

The fourth mistake is hiding shard complexity without operational tooling. Teams need ways to inspect shard health, move tenants, handle backups, and debug routing.

Read Database Indexes Explained for query-level performance and Database Transactions Explained for write consistency. For stale reads and cross-service behavior, read Eventual Consistency Explained. You can also browse the System Design topic cluster for more scaling guides.

Keep reading