Archprimer
ConceptIntermediate 8 min read

Sharding

Horizontal partitioning that spreads rows of one logical dataset across many independent nodes, using a shard key to route each request to the node that owns the data.

scalabilitydatabasesdistributed-systemspartitioning

By Ratish Jain · Updated

Diagram with 5 components: Client, Shard Router, Shard 1, Shard 2, Shard 3. Connections: Client → Shard Router (1 query); Shard Router → Shard 1 (A–H); Shard Router → Shard 2 (I–P); Shard Router → Shard 3 (Q–Z).
ClientServiceDatabase
A router hashes the shard key and sends each query to the owning shard.

Definition

Sharding is horizontal partitioning of a single logical dataset across multiple independent database nodes, called shards. Each shard holds a disjoint subset of the rows and runs on its own machine with its own CPU, memory, and disk.

A shard key (for example user_id) determines which shard owns a given row. A routing layer maps that key to a shard so reads and writes go to exactly one node instead of the whole cluster.

Why It Exists

A single database server eventually hits a ceiling: the working set no longer fits in memory, disk IOPS saturate, and vertical scaling gets exponentially expensive. Replication helps read throughput but every replica still stores the full dataset and absorbs every write.

Sharding removes that ceiling by splitting both data volume and write load across N nodes. Ten shards mean roughly one-tenth the data and one-tenth the writes per node, so the system scales close to linearly with hardware.

How It Works

You choose a shard key and a strategy for mapping keys to shards. Hash sharding applies hash(key) % N (or better, a consistent-hashing ring) to spread keys uniformly and avoid hotspots, at the cost of losing range-scan locality. Range sharding assigns contiguous key ranges (e.g. dates) to shards, keeping range queries fast but risking a hot shard when recent data gets most traffic.

A router — a proxy, a client library, or a lookup table — resolves the shard for each request. Queries that touch a single key are fast. Cross-shard queries, joins, and aggregations must scatter to many shards and gather results, which is slower and harder to make transactional.

What It's Used For

  • Scaling write-heavy OLTP databases beyond one node (user data, orders, messages).
  • Multi-tenant SaaS: shard by tenant_id so each customer's data is isolated.
  • Time-series and event stores partitioned by time range.
  • Geo-sharding: keep data physically near the users who own it for latency and compliance.
  • Large key-value and NoSQL stores (Cassandra, MongoDB, DynamoDB) that shard transparently.

Routing by Shard Key

Diagram with 5 components: Client, Shard Router, Shard 1, Shard 2, Shard 3. Connections: Client → Shard Router (1 · key); Shard Router → Shard 2 (2 · route); Shard Router → Shard 1; Shard Router → Shard 3.
ClientGatewayDatabase
The router hashes the key to exactly one shard; ranges are disjoint.

Tradeoffs

Pros
  • Near-linear horizontal scaling of storage and write throughput.
  • Smaller working set per node improves cache hit rate and query latency.
  • Blast radius of a node failure is limited to one shard's data.
Cons
  • Cross-shard joins, aggregations, and transactions are complex and slow.
  • A poor shard key causes hotspots that concentrate load on one node.
  • Resharding to add capacity is operationally painful without consistent hashing.
  • Loss of global secondary indexes and easy referential integrity.

When to Use / Avoid

Use when
Data or write volume exceeds a single node and access is dominated by single-key lookups on a good shard key.
Avoid when
The dataset fits comfortably on one node, or the workload needs frequent cross-entity joins and multi-row transactions.
Real-world
Instagram shards Postgres by user ID; Discord shards messages by channel; DynamoDB auto-shards by partition key.

Interview Questions

How do you pick a good shard key?

Pick a key with high cardinality and uniform access so load spreads evenly, that appears in most queries (so requests hit one shard), and that rarely changes. Avoid low-cardinality or monotonically increasing keys, which create hotspots.

How do you handle a query that spans multiple shards?

Scatter-gather: fan the query to all relevant shards in parallel and merge results in the router. To avoid it, denormalize or maintain a secondary index sharded by the second access pattern; for cross-shard writes, use a saga or two-phase commit.

60-Second Revision
  • →Sharding = horizontal split of one dataset across independent nodes, each owning disjoint rows.
  • →Shard key + strategy (hash vs range) decides routing; hash spreads evenly, range keeps locality.
  • →Scales writes and storage near-linearly; single-key access stays fast.
  • →Cross-shard joins, transactions, and resharding are the main costs.
  • →Consistent hashing minimizes data movement when adding or removing shards.

Related

Everything on Archprimer is connected — follow the graph.