Database Sharding: Making Data Placement Part of the Architecture

When one database cannot hold or serve a workload efficiently, data can be divided across several independent database nodes.

This is sharding.

Sharding is a data-placement decision that becomes part of queries, transactions, recovery, and application architecture.

A good shard key keeps related work together and spreads expensive work evenly.

A concrete example: customer orders

The system computes a shard from customer_id.

All orders for one customer live on one shard, making account history local. A global sales report must query every shard and combine results.

The key improves one common access pattern while making another distributed.

Horizontal partitioning

Sharding usually divides rows:

• customers A–F on shard 1,
• G–M on shard 2,
• or hash ranges across many nodes.

Each shard has the same schema for its subset.

Vertical partitioning instead separates columns or responsibilities.

Shard key

The shard key determines placement.

It should consider:

• distribution,
• common queries,
• transaction boundaries,
• growth,
• tenant size,
• and migration.

Changing it later can require moving most data.

Range sharding

Ranges assign ordered key intervals:

• dates,
• customer IDs,
• geography.

Range queries are efficient, but sequential new keys can send all writes to one current shard.

Split and move ranges as they grow.

Hash sharding

A hash spreads keys across buckets.

It often balances random customer IDs well but destroys natural range locality. Queries without the full key may contact many shards.

Consistent hashing can reduce movement during membership changes.

Directory-based routing

A directory maps each key or tenant to a shard.

It supports custom placement and moving large tenants, but the directory becomes critical infrastructure requiring availability and consistency.

Cache directory lookups carefully.

Geographic sharding

Data can be placed by region for:

• latency,
• residency,
• ownership,
• and failure isolation.

Global users and cross-region collaboration complicate placement. Moving a user's home region becomes a data migration.

Hot shards

Even record counts do not guarantee load balance.

One celebrity, enterprise tenant, or current date range can dominate traffic. Detect:

• CPU,
• storage,
• query rate,
• locks,
• and growth by shard.

Large tenants may need dedicated placement or sub-sharding.

Routing

The application or a routing layer determines the target shard.

Every request needs enough routing context. Losing tenant ID deep in a service call can force a scatter query or make the operation impossible.

Routing rules must be consistent across writers and readers.

Scatter-gather

A query without a shard key is sent to many shards.

Each shard returns candidates, and a coordinator merges:

• sorting,
• aggregation,
• pagination,
• and limits.

Fan-out latency follows the slowest shard and consumes cluster-wide capacity.

Cross-shard joins

Traditional joins are difficult when tables live on different shards.

Options include:

• colocate related data,
• duplicate reference data,
• application join,
• analytical warehouse,
• or distributed query engine.

Model frequent transactional joins into the same shard where possible.

Cross-shard transactions

Updating several shards atomically requires distributed transactions or a workflow with compensation.

This adds latency and failure complexity. Design shard boundaries around business transactions to keep most writes local.

Global uniqueness may also need a central service or structured identifiers.

Secondary indexes

A local index finds records within one shard.

A global secondary index maps a field to shard locations but becomes another distributed data structure that must stay consistent.

Search systems often provide a separate eventually consistent global view.

Resharding

When a shard grows, data must move.

A safe migration may:

1. create destination,
2. copy historical data,
3. stream ongoing changes,
4. verify,
5. switch routing,
6. drain old writes,
7. remove old copy.

The process must tolerate retry and rollback.

Virtual shards

Create many logical buckets mapped onto fewer physical databases.

Adding capacity moves selected buckets rather than redefining the entire hash function.

Virtualization provides flexibility but adds mapping metadata.

Shard metadata

Routing metadata is part of the data system.

It needs strong consistency, backups, versioning, cache invalidation, and an owner. A stale router that sends a write to the former shard after migration can create split ownership even when every database is healthy.

Tenant movement

Moving one tenant should use an explicit migration state:

• source only,
• copying,
• dual or captured changes,
• destination verified,
• routing switched,
• source retired.

Every retry must be idempotent, and support tools should reveal which location is authoritative.

Backups

Backups across shards occur at different moments unless coordinated.

For cross-shard consistency, record a common log position or workflow checkpoint. Restore routing metadata as well as data.

Test restoration of one shard and the full fleet.

Schema changes

Migrations must reach every shard.

Track version per shard, handle partial rollout, and keep application code compatible during the migration window.

One forgotten shard can fail only for a subset of customers.

Operational isolation

Shards can contain failure:

• one shard outage affects one subset,
• one noisy tenant stays local.

Shared routers, deployment, identity, and configuration can still create global failure.

When not to shard

Before sharding:

• optimize queries,
• add indexes,
• archive cold data,
• scale vertically,
• add replicas,
• separate analytics,
• and cache suitable reads.

Sharding is valuable when data and write scale justify permanent distributed complexity.

Knowledge check

1. How does a shard key affect queries and transactions?
2. Why can range sharding create a write hot spot?
3. What makes scatter-gather expensive?
4. How can virtual shards simplify adding capacity?
5. Why should sharding usually follow simpler scaling options?

The one idea to remember

Sharding partitions records across independent databases using a placement rule. The shard key determines locality, balance, fan-out, transactions, and migration, so choose it from real access patterns and introduce it only when simpler capacity options are insufficient.