Sharding and Partitioning — Dev Helper Library

Intent

Sharding splits a database's data into smaller partitions (shards) spread across multiple nodes, allowing data volume and write rate to grow beyond the limit of a single machine.

A single database server has physical limits: disk capacity, RAM, and I/O bandwidth. When data volume exceeds those limits, scaling vertically (more RAM, more CPU, more disk) becomes progressively more expensive and eventually impossible. Sharding distributes the load across multiple nodes — each responsible for a slice of the dataset — turning the vertical capacity problem into a horizontal coordination problem.

Problem

Systems that grow beyond tens or hundreds of gigabytes of active data hit bottlenecks that indexing and query optimization alone can't solve:

Data volume exceeds a single node's capacity: tables with billions of rows degrade performance even with optimized indexes. The index itself becomes too large to fit in memory, forcing disk I/O on every lookup operation.
Write rate exceeds a single node's throughput: a single server has a maximum number of writes per second it can process. In high-volume systems — logs, metrics, financial transactions — that limit is reached before the storage limit is.
Read replicas don't solve the write problem: adding read replicas distributes read load, but every write still goes through the primary. The write bottleneck remains.

How it works

Horizontal vs vertical partitioning

It's important to distinguish the two types of partitioning, as the term often causes confusion:

Horizontal partitioning (sharding): splits a table's rows across multiple nodes. All shards have the same schema. E.g.: users with ID 1–1,000,000 on Shard A, ID 1,000,001–2,000,000 on Shard B. This is what "sharding" usually means in most contexts.
Vertical partitioning: splits a table's columns across different databases. E.g.: the users table with profile data in one database and payment data in another. A different technique, with a different motivation — usually used to isolate domains for security or compliance, not for volume scale.

Sharding architecture

The central component of a sharded architecture is the shard router: the layer that receives the client's request, determines which shard the data lives on, and forwards the operation to the right node.

  Client
     │
     ▼
  ┌───────────────┐
  │  Shard Router │  determines the shard based on the shard key
  └───────┬───────┘
          │
     ┌────┼────┐
     ▼    ▼    ▼
   ┌───┐┌───┐┌───┐
   │ A ││ B ││ C │   each shard is an independent database node
   └───┘└───┘└───┘
   (same schema across all shards)

The router can be implemented at the application layer (the code decides which database to connect to), in a dedicated proxy (e.g., ProxySQL, Vitess), or natively in the database (e.g., MongoDB with mongos, Cassandra with token-aware drivers).

Shard key

The shard key is the field (or combination of fields) used to determine which shard a record belongs to. It's the most critical design decision in a sharded architecture — a poorly chosen shard key creates hotspots: one overloaded shard while the others sit idle, nullifying the benefit of distribution.

Criteria for a good shard key:

High cardinality: many possible distinct values, so data spreads across many shards.
Uniform distribution: values shouldn't concentrate in a few ranges or hashes.
Alignment with access patterns: frequent operations should touch one shard at a time. Avoid shard keys that force the router to query every shard to answer a common query.

Sharding strategies

Range-based

The shard is determined by a range of shard-key values. E.g.: records with created_at in January go to Shard A, February to Shard B.

  shard key: user_id (numeric)

  Shard A: user_id  1 - 1,000,000
  Shard B: user_id  1,000,001 - 2,000,000
  Shard C: user_id  2,000,001 - 3,000,000

  Advantage: efficient range queries ("all users from 1 to 500k")
  Risk: hotspot if new records are created sequentially
        (they all go to the last shard while earlier ones sit idle)

Hash-based

The shard is determined by a hash function applied to the shard key: shard = hash(shard_key) % num_shards. Distributes data uniformly, eliminating range hotspots.

  shard key: user_id

  shard = MD5(user_id) % 3

  user_id=1001  → hash mod 3 = 2 → Shard C
  user_id=1002  → hash mod 3 = 0 → Shard A
  user_id=1003  → hash mod 3 = 1 → Shard B

  Advantage: uniform distribution, no range hotspots
  Risk: inefficient range queries (requires querying every shard)
        resharding requires remapping almost every record

Directory-based

A lookup table (the "directory") explicitly maps each shard-key value to its shard. The router consults the directory before every operation.

  Lookup table (directory):
  ┌──────────┬───────┐
  │ tenant   │ shard │
  ├──────────┼───────┤
  │ company1 │ A     │
  │ company2 │ B     │
  │ company3 │ A     │
  │ company4 │ C     │
  └──────────┴───────┘

  Advantage: flexible - can move tenants between shards without changing logic
  Risk: the lookup table becomes a single point of failure and a latency bottleneck

Resharding and consistent hashing

When a shard becomes too large or overloaded, data needs to be redistributed — an operation called resharding. With simple hash-based sharding (% N), adding a shard changes the formula and potentially invalidates the mapping of nearly every record, requiring most of the data to be moved.

Consistent hashing solves this: shards and keys are positioned on a circular ring, and each key belongs to the nearest shard clockwise. When adding or removing a shard, only the keys adjacent to it on the ring need to move — on average K/N keys (where K is the total number of keys and N the number of shards), instead of nearly all of them.

Cross-shard joins

Joins between data that live on different shards aren't executed by the database — each shard is an independent database. The application needs to fetch data from each shard separately and combine the results in memory. Depending on volume, this can be slower than the original join and introduces merge logic at the application layer.

The most common solution is to denormalize: duplicate the necessary data within the same shard to avoid the cross-shard join. E.g.: instead of joining with the users table on another shard, copy the user's name into the order record at creation time.

When to use

Data volume exceeds a single node's capacity: when scaling vertically (more RAM, more disk) becomes cost- prohibitive or technically infeasible.
Write rate exceeds a single node's throughput: high-volume systems such as real-time analytics, IoT time series, or large-scale e-commerce platforms.
The data has a natural partitioning dimension: by user (social networks, multi-tenant SaaS), by geographic region, or by time period. The shard key emerges naturally from the domain.

When to avoid

Exhaust the simpler alternatives first: correct indexing, query optimization, caching, and read replicas can delay or eliminate the need for sharding for a long time. Sharding adds permanent operational complexity.
Cross-shard joins are frequent in your workload: if the system's access pattern frequently requires combining data across multiple dimensions, sharding turns every simple operation into a distributed coordination problem.
No natural shard key: if there's no key that distributes data uniformly and aligns with access patterns, the system will create hotspots. Sharding without a good shard key is worse than not sharding at all.

Pros and cons

Pros

Real horizontal write scale: ingestion capacity grows linearly with the number of shards.
Unlimited data volume: the dataset can grow beyond the limit of any individual machine.
Failure isolation: a shard's failure affects only the data in that shard, not the whole system.
Localized queries get faster: with a good shard key, most queries touch only one shard — a smaller dataset, with smaller indexes that fit entirely in memory.

Cons

High operational complexity: monitoring, backing up, restoring, and upgrading N independent databases instead of one.
Cross-shard joins: need to be implemented at the application layer, with manual merge logic and multiple network round trips.
Cross-shard transactions: ACID doesn't apply across shards. Two-phase commit or patterns like Saga are needed, with significant consistency trade-offs.
Resharding is costly: redistributing data with the system in production requires careful coordination to avoid inconsistency and downtime.
Hotspots are hard to diagnose: distribution can look uniform in the data but be extremely uneven in access.

Common pitfalls

1. Shard key that creates a hotspot

The classic example is using created_at (a timestamp) as the shard key in a logging or event system. All new traffic goes to the most recent period's shard — older shards sit idle while the current shard gets hammered. The sharded system performs worse than the unsharded one because the load, instead of being distributed, is concentrated on a single node.

Rule of thumb: before adopting a shard key, simulate the distribution with real production data (not with uniform test data) and with the projected future access pattern, not just the current one.

2. Cross-shard joins in a loop

The application fetches a list of IDs from Shard A and, for each ID, queries Shard B for related data — the N+1 problem at distributed scale. Instead of an efficient database join, there are N serialized network queries. The result is worse in latency and load than before sharding.

Solutions: denormalize the necessary data into the same shard at write time, or use fan-out (query every shard in parallel and aggregate the results), accepting the cost of multiple connections.

3. Unplanned resharding

The system's growth exceeds the existing shards' capacity and new shards need to be added. With simple hash-based sharding, this requires moving most of the data. With the system in production, the consistency window during migration needs to be managed — data being moved can't be accessed by the old router, and the new router doesn't yet know the final destination. Planning resharding before you need it is part of the architecture.

4. Cross-shard transactions

An operation that needs to modify data on two different shards has no ACID guarantee. If the system commits the write on Shard A but fails before writing to Shard B, the data ends up in an inconsistent state. Two-phase commit (2PC) solves the problem but introduces distributed locks and degrades availability. The Saga pattern replaces the distributed transaction with a sequence of local transactions with compensations on failure.

Related architectures and patterns

Replication and sharding are complementary techniques often used together: sharding distributes writes across multiple primary nodes, while replication adds read replicas to each shard to increase read throughput and availability. The resulting architecture has both horizontal write scale and fault tolerance.

NoSQL databases like Cassandra, DynamoDB, and MongoDB were designed with native sharding — the distribution logic is part of the database, not the application. In relational SQL databases, sharding is normally implemented at the application layer or with tools like Vitess (MySQL) or Citus (PostgreSQL). The choice between SQL and NoSQL is influenced by how central sharding is to the design from the start.

Caching is often the first line of defense against database overload: by reducing the number of reads that reach the database, caching delays the moment sharding becomes necessary. Evaluating caching before sharding is the correct sequence of architectural decisions.