Sharding
matureSummary
Horizontally partition a dataset across multiple independent database nodes by a shard key, distributing both storage and query load so that each node handles only a fraction of the total data.
Problem
A single database primary reaches its write throughput, storage, or connection ceiling and cannot be vertically scaled further without disproportionate cost or risk. Read replicas distribute read load but do not increase write capacity.
Description
A single database node has hard ceilings on storage (typically 10–50TB before operational complexity becomes prohibitive), write throughput (limited by CPU, WAL bandwidth, and I/O), and connection count. Sharding breaks the dataset into N independent partitions called shards, each of which is a full, self-contained database node. No shard communicates with another during normal query execution.
Shard assignment is determined by a shard key: a column or composite of columns whose value deterministically maps a row to a shard. Two common strategies: hash-based sharding (shard = hash(key) % N) distributes rows uniformly but makes range queries cross-shard; range-based sharding (shard = key / range_width) keeps sorted ranges on one shard but risks hotspots if traffic is concentrated in a key range.
The routing layer (application code, a proxy like Vitess or Citus, or the database's native partitioning) resolves the shard for each query and directs it to the correct node. Queries that require data from multiple shards: joins, aggregates, or range scans across shard boundaries: must scatter to all shards and gather results, incurring N × single-shard latency.
Resharding: changing the number of shards or the key distribution: is the most operationally expensive operation in a sharded system. It requires moving data between nodes while serving live traffic, typically taking hours to days depending on dataset size and acceptable throughput impact.
Tradeoffs
Near-linear write throughput scaling with shard count
Storage scales linearly; each shard holds 1/N of total data
Scatter-gather required; latency is N × single-shard latency
Schema migrations, resharding, and cross-shard transactions multiply complexity
ACID across shards requires 2PC or saga; local shard transactions are ACID
Poor key selection concentrates load on one shard while others idle
When to use
Dataset exceeds 5–10TB on a single node and growth continues
Above this range, single-node storage costs, backup times, and vacuum overhead become operationally problematic
Write throughput exceeds what a single primary can sustain
PostgreSQL on high-end hardware handles ~50,000–100,000 simple writes/second; beyond this, writes must be distributed across nodes
Access pattern has a natural high-cardinality partition key
A good shard key (user_id, tenant_id, region) distributes load uniformly; poor key selection (status, boolean) produces hot shards
Most queries are scoped to a single shard
Sharding only improves performance when queries stay within one shard; cross-shard scatter-gather eliminates the benefit
When not to use
Primary bottleneck is read throughput, not write or storage
Read replicas solve read scaling at far lower operational cost and complexity than sharding
Query patterns frequently join across the shard key boundary
Cross-shard joins require scatter-gather across all N shards; latency scales with shard count and query complexity
Dataset size is below 1TB and growth rate is moderate
Vertical scaling and read replicas cover this range; sharding complexity is premature
Operational Requirements
Choose shard key before initial deployment: it is extremely expensive to change
Changing the shard key requires a full data migration; simulate query patterns with production-like data before committing
Monitor per-shard write rate and storage to detect hotspots early
A hot shard will hit single-node limits while others are underutilised; detect via per-shard QPS and disk metrics
Plan resharding strategy before reaching 80% of single-shard capacity
Resharding under pressure is a multi-day operation; begin planning at 60% utilisation and execute before crossing 80%
Test schema migrations across all shards in staging before production rollout
A migration failure on shard 3 of 8 leaves the system in an inconsistent state; automate migration orchestration and rollback
Characteristics
Technologies
Canonical
Alternatives
Relationships
Evolves from
Evolves to
Complements
Basis
Extensively deployed at scale; hotspot and resharding failure modes are well-documented; operational complexity is real and must not be minimised
Related Architecture Knowledge
Outbound: this entity affects
Sharding by a specific key makes queries that omit the shard key require fan-out across all shards, degrading latency and consuming connection pool slots proportional to shard count.
Full relationship →Sharding distributes data across partitions, but poor shard key selection concentrates traffic on a small number of shards. A hot partition receives disproportionate load, becomes a bottleneck, and degrades performance for all data on that shard.
Tradeoffs
- ·Hash-based shard keys (good distribution) eliminate range query capability: all range queries become scatter-gather
- ·Detecting hot partitions requires per-shard metrics: aggregate metrics mask the problem
- ·Resharding to fix a hot partition is expensive: requires data migration across shards
Sharding is only effective when the shard key distribution is uniform; monotonic or low-cardinality keys produce hot shards that eliminate the write throughput benefit of sharding.
Full relationship →Inbound: affects this entity
DynamoDB automatically partitions tables across internal shards using the partition key as a shard discriminator. Adaptive capacity redistributes throughput across partitions automatically, but partition key design remains critical for avoiding hot partitions.
Tradeoffs
- ·Write sharding for hot keys requires scatter-gather reads to reassemble results across partitions
- ·Adaptive capacity does not eliminate hot partition limits: it only allows temporary bursting
- ·DynamoDB partition splits are transparent but irreversible: a split table has permanent overhead for low-traffic keys
Elasticsearch implements sharding natively: every index is divided into primary shards distributed across nodes, with replica shards providing redundancy. Sharding is not optional: all Elasticsearch data exists within a shard.
Tradeoffs
- ·Fixed shard count at creation requires capacity planning before data ingestion begins
- ·Cross-shard queries fan out to all shards and merge results on the coordinating node: too many shards add merge overhead
- ·Replica shards double storage and I/O for writes: fewer replicas during bulk indexing, restore after load