Database Sharding: Choosing a Shard Key, Avoiding Hotspots, and Resharding Safely

Database Sharding: Choosing a Shard Key, Avoiding Hotspots, and Resharding Safely

Database sharding is horizontal partitioning across independent database nodes. Instead of every row living on one primary server, different subsets of the dataset are owned by different shards.

The payoff can be large: storage and write capacity can grow beyond one machine. The cost is equally real: routing, cross-shard queries, rebalancing, transactions, global uniqueness, migrations, and failure handling all become harder.

Sharding should therefore solve a demonstrated scaling limit—not serve as a badge of architectural maturity.

The basic idea

Suppose one database can no longer hold or write the full tenant dataset comfortably.

A sharded design might split ownership like this:

request
  |
  v
shard router
  |
  +--> shard A: tenants 1-1000
  +--> shard B: tenants 1001-2000
  +--> shard C: tenants 2001-3000

Each shard is its own database failure and capacity domain.

The router must determine which shard owns the requested data.

That ownership decision is based on a shard key.

Sharding vs table partitioning

The terms are sometimes used loosely, but it is useful to separate them.

Table partitioning

one database system
one logical table
multiple internal partitions

Typical goals:

• partition pruning
• retention
• smaller indexes
• easier maintenance

Sharding

multiple database nodes
one logical dataset
rows distributed across nodes

Typical goals:

• horizontal storage growth
• horizontal write throughput
• tenant/workload isolation
• reducing the amount of data owned by each database

Partitioning can exist inside each shard too.

The shard key is the most important decision

A good shard key needs to balance two competing goals:

1. distribute load/data evenly
2. keep common queries local to one shard

If you optimize only for even distribution, important queries may fan out to every shard.

If you optimize only for locality, one shard may become much hotter than the others.

MongoDB's current sharding documentation describes the same fundamental trade-off: an ideal shard key distributes documents well while still supporting common query patterns.

High cardinality matters

A shard key with only a few possible values cannot spread data across many shards effectively.

Bad example:

country = IN / US / GB

If 80% of traffic belongs to one country, one shard can remain overloaded.

Higher-cardinality candidates include:

tenant_id
customer_id
account_id
device_id

Cardinality is necessary but not sufficient. A billion possible values can still produce skew if one value receives half of all traffic.

Distribution matters more than mathematical neatness

Consider a SaaS system sharded by tenant_id.

That is operationally attractive because most tenant queries stay local.

But if one enterprise tenant produces 40% of all writes, the topology becomes:

shard A: normal
shard B: normal
shard C: giant tenant, overloaded

The key has high cardinality but poor workload distribution.

Always analyze:

• rows per shard-key value
• requests per shard-key value
• writes per shard-key value
• growth rate
• largest tenants/entities

Design for the load distribution, not only the row distribution.

Range sharding

Range sharding maps contiguous key ranges to shards.

Example:

customer IDs 1-1,000,000       -> shard A
customer IDs 1,000,001-2,000,000 -> shard B

Advantages:

• range queries can remain local
• ownership is easy to reason about

Risk:

A monotonically increasing key can direct all new writes to the newest range/shard.

For example, sequential order IDs can make one shard the current-write hotspot while older shards are mostly idle.

Range sharding works best when ranges map naturally to the access pattern and write load is not concentrated at one edge.

Hash sharding

Hash sharding hashes the shard key and maps the result to a shard/bucket.

Conceptually:

shard = hash(customer_id) mod N

Benefits:

• tends to distribute keys more evenly
• avoids some monotonic-key hotspots

Trade-off:

Range queries lose locality because adjacent business values may live on unrelated shards.

If the application frequently asks for a contiguous range of the shard-key domain, hashed distribution may turn a local query into scatter/gather.

Directory-based sharding

Another pattern stores explicit ownership in a routing directory:

tenant t1 -> shard B
tenant t2 -> shard A
tenant t3 -> shard D

This is more flexible than a fixed modulo rule because individual tenants can be moved independently.

It is attractive for multi-tenant SaaS where large tenants may need dedicated placement.

But the directory becomes important infrastructure:

• must be highly available
• routing changes must be consistent
• caching must not use stale ownership indefinitely
• migrations need cutover coordination

Flexibility creates control-plane complexity.

Consistent hashing can reduce remapping—but does not solve everything

Consistent hashing is often used when nodes are added or removed because it can reduce how much key ownership changes compared with a simple hash(key) mod N scheme.

Virtual nodes/buckets can improve balance further.

However, consistent hashing does not automatically solve:

• hot keys
• query locality
• cross-shard joins
• cross-shard transactions
• uneven tenant sizes

It is a placement technique, not a complete sharding architecture.

Avoid scatter/gather on the hot path

A query that lacks the shard key may have to ask every shard:

router -> shard A
       -> shard B
       -> shard C
       -> shard D

Then results must be merged.

This is called scatter/gather or broadcast querying.

MongoDB's official sharding docs explicitly warn that queries without the shard key/prefix can require broadcasting across shards and become expensive.

A sharded schema should make the common request path shard-addressable.

For example, this is easy to route:

GET /tenants/t42/orders/o900

because tenant_id = t42 identifies ownership.

A global search across every tenant is a different workload and may belong in a search/analytics system.

Co-locate data that is transacted together

Cross-shard transactions are more expensive than local transactions because they require coordination among multiple independent nodes.

A strong shard key keeps business invariants local.

For multi-tenant SaaS, sharding by tenant often keeps:

• tenant users
• orders
• products
• configuration

on the same shard.

Then most tenant transactions stay local.

If orders are sharded by order ID while inventory is sharded by product ID, one checkout can require several shards and distributed coordination.

Choose boundaries from transaction locality, not just data volume.

Global unique IDs need an explicit strategy

A single database sequence provides easy global uniqueness.

Independent shards cannot safely rely on separate local sequences if IDs must be globally unique.

Options include:

• UUID/ULID-style IDs
• IDs containing shard/time components
• globally coordinated ID service
• compound IDs such as (tenant_id, local_id)

Do not discover ID collisions only after the second shard is live.

Foreign keys become more complicated

A foreign key inside one shard can remain straightforward.

A foreign key pointing to data on another shard usually cannot rely on normal local database enforcement in the same way.

Cross-shard relationships may require:

• application-level validation
• denormalized identifiers
• asynchronous integrity checks
• reference data copied locally

This is one of the costs of turning one relational database into multiple independent ownership domains.

Secondary indexes become local unless you build something global

A local shard index can answer:

find email inside tenant t42

But a system-wide lookup such as:

find account by email across every shard

needs another strategy.

Options include:

• a global directory table/service
• a search index
• a mapping cache/database
• broadcast query for rare admin workflows

Global secondary indexes are possible in some distributed databases, but they create their own coordination and write costs.

Hot keys defeat horizontal scale

Even with excellent overall distribution, one record can become a serialization point.

Examples:

• one viral post counter
• one global inventory item
• one celebrity account
• one massive tenant

If all updates for the hot entity must reach the same shard and same row, adding unrelated shards does not help.

Possible redesigns include:

• striped counters
• append-and-aggregate
• sub-sharding large tenants
• isolating heavy tenants onto dedicated shards
• changing consistency requirements where safe

Sharding distributes independent work. It cannot make inherently single-owner state parallel without a data-model change.

Rebalancing is normal, not exceptional

A shard topology that fits today will not stay perfectly balanced forever.

You need a plan for:

• adding shards
• moving ranges/buckets/tenants
• draining a shard
• replacing hardware
• correcting skew

Modern platforms may automate parts of this, but data movement still consumes:

• network bandwidth
• disk I/O
• CPU
• replication capacity

MongoDB's documentation notes that range/chunk migration and resharding are resource-intensive operations. The same operational reality applies broadly: moving live data is work.

Online shard migration needs a cutover protocol

Suppose tenant t42 must move from shard A to shard B.

A safe migration generally needs phases such as:

1. create/copy destination data
2. capture ongoing changes
3. catch destination up
4. stop or coordinate conflicting writes briefly
5. switch routing ownership
6. verify
7. retire old copy after safety window

The exact mechanism might use:

• logical replication
• change data capture
• dual writes with reconciliation
• database-native resharding

Avoid naive “copy rows, change router” migrations. Writes that happen during the copy create divergence.

Dual writes are dangerous without reconciliation

During migrations or denormalized architectures, teams sometimes write the same change to two shards/services.

If the application performs:

write A
write B

and crashes between them, the copies diverge.

Safer approaches include:

• source-of-truth + CDC/outbox
• idempotent replay
• database-native migration tools
• explicit reconciliation

Two writes in application code are not automatically one transaction.

Resharding should be designed before you need it

Shard-key mistakes happen.

Modern MongoDB supports changing/refining shard keys and resharding collections, but its documentation still emphasizes choosing the key carefully because poor keys create uneven distribution and performance problems.

In custom sharding architectures, changing the key can be much harder.

Before the first production shard, write down:

• how a tenant/range can move
• how routing changes propagate
• how consistency is maintained during copy
• how rollback works
• how progress is monitored

A system that can add shards but cannot move existing data is not truly elastic.

Cross-shard transactions should be rare and intentional

Distributed transactions can preserve strong consistency across shards, but they add:

• coordination round trips
• more failure states
• potentially longer lock/transaction lifetimes
• harder recovery

If most normal operations require distributed transactions, the shard boundary may be wrong.

Try to structure ownership so the common invariant lives on one shard.

Use distributed transactions for genuinely cross-domain operations, not as compensation for poor data locality.

Cross-shard joins move complexity somewhere else

A relational database can join tables locally because the optimizer owns all data.

Across shards, joins may require:

• fan-out queries
• application-side merging
• data duplication
• precomputed projections
• analytics/search systems

For example, global reporting across every tenant often belongs in a warehouse/OLAP system fed from shards rather than on the synchronous application request path.

Availability changes from global to partial

Sharding can improve failure isolation.

If shard C is unavailable, tenants on shards A/B/D may continue operating.

But the application must support partial availability deliberately:

• route errors only to affected ownership ranges
• avoid global dependencies that make one shard failure global
• expose shard health
• avoid retry storms against the failed shard

A sharded system that routes every request through one fragile central coordinator can reintroduce a global single point of failure.

Backups and restores get harder

With one database, a point-in-time backup has one timeline.

With many shards, a globally consistent restore may require coordination across several timelines.

Ask:

• can each shard be restored independently?
• do cross-shard invariants exist?
• how is a cluster-wide point in time defined?
• how are routing metadata/configuration backed up?

Sharding affects disaster recovery, not just request routing.

Observability must become shard-aware

Cluster-wide averages hide skew.

Monitor per shard:

• CPU
• storage
• memory/cache hit ratio
• connections
• transactions/sec
• write latency
• lock waits
• replication lag
• dataset size
• top tenants/keys
• cross-shard query rate
• migration/rebalancing progress

Also monitor distribution:

largest shard / average shard size
hottest shard / average request rate
hottest tenant / shard capacity

The system is constrained by the hottest shard, not the average shard.

When sharding is justified

Sharding is a strong candidate when:

• one node cannot economically hold the dataset
• write throughput has hit a demonstrated single-primary limit
• tenants/domains have natural ownership boundaries
• failure isolation is valuable
• vertical scaling is no longer practical

Do not shard when:

• slow queries simply need indexes
• connection pooling is broken
• large analytics should be moved off OLTP
• a read cache/replica would solve the actual bottleneck
• the current primary has huge unused headroom

Complexity should follow evidence.

A shard-key checklist

Before choosing a shard key, ask:

1. Does it have high enough cardinality?
2. Is traffic roughly distributed across values?
3. Can one value become extremely hot?
4. Do common queries include the shard key?
5. Do transactions stay within one shard?
6. Do range queries need locality?
7. Can a large tenant/entity be split later?
8. How will data be rebalanced?
9. How will global lookups work?
10. How will uniqueness and IDs work?

A good shard key is not merely one that spreads rows evenly. It spreads work while preserving locality for the operations the product performs most often.

References

• MongoDB — Sharding
• MongoDB — Shard Keys
• MongoDB — Change a Shard Key
• MongoDB — Troubleshoot Shard Keys