Distributedpartitioningshardingshard keyhash shardingrange sharding

Partitioning and Sharding

Partitioning splits one table across pieces of one database; sharding splits data across separate database nodes — and everything that crosses a shard boundary (joins, transactions, uniqueness, aggregates) becomes the application’s problem.

▶ InteractiveInterview questionSee how this works internally →
Progress

The difference

Partitioning divides one logical table into physical pieces within a single database. Same machine, same transactions, same joins, same constraints — the planner just prunes the partitions a query does not need and each piece stays small. It solves *operability* of a huge table: small indexes, fast VACUUM, instant retention by dropping a partition. It does not add capacity.

Sharding divides data across independent database nodes, each a full database serving a slice of the keyspace. It is the only thing that adds write capacity beyond one machine. The price is that anything spanning shards — a join, a transaction, a unique constraint, a count(*) — now happens in application code, if it can happen at all.

Choosing the shard key

The shard key decides which node holds a row, and the goal is that almost every query already knows it. Hash sharding (hash(user_id) mod N) spreads load evenly regardless of the key’s distribution, at the cost of range queries hitting every shard and resharding remapping almost everything (consistent hashing or virtual nodes soften that). Range sharding (A–F, G–M) keeps ranges on one shard but skews with the data — and with a monotonically increasing key, every new row lands on the last shard, the classic hot shard. Geographic sharding gives locality and data-residency compliance and wildly uneven shard sizes. Tenant sharding (all of one account on one shard) keeps almost every query shard-local for B2B SaaS, until one whale tenant outgrows its shard.

The failure to design for is the hot shard: a key whose distribution concentrates traffic on one node, which then gets a majority of the load on a minority of the hardware, and adding shards does not help because the key is the problem.

What crosses the boundary

A query that names the shard key touches one shard and is fast. A query that does not is scatter-gather: sent to every shard, results merged in the application — slow, and it gets slower as you add shards. Joins across shards are done in application code or not at all. Transactions across shards need two-phase commit (slow, and a coordinator failure leaves locks held) or a saga (application-level compensation). Global uniqueness — a unique email across all shards — needs a separate lookup service, because no single shard can enforce it. count(*) over everything is scatter-gather.

This is why sharding is last on the ladder. It is not a config change; it is a re-architecture that trades a whole category of database guarantees for horizontal write scale. Exhaust vertical scaling, replicas, caching and partitioning first, genuinely.

Partitioning (one database) is not sharding (many databases)
1-- PARTITIONING: one database, planner prunes, retention is instant
2CREATE TABLE events (occurred_at timestamptz, ...) PARTITION BY RANGE (occurred_at);
3CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
4 
5-- SHARDING: many databases, routing in the application, no cross-shard join
6--   shard = hash(tenant_id) % 4  →  connect to that node, query only there
7--   "list all tenants over quota"  →  ask all 4 nodes, merge in code (scatter-gather)

Key points

  • Partitioning: one database, smaller pieces, no new capacity. Sharding: many databases, real write scale, lost cross-shard guarantees.
  • The shard key must be in almost every query; hash spreads evenly, range and tenant skew into hot shards.
  • Cross-shard joins, transactions, uniqueness and aggregates move into the application or disappear.
  • Shard last, after vertical scaling, replicas, caching and partitioning are exhausted.

Four ways to shard

Four ways to shard 400 users
Same rows, four shard keys. The bars are how many rows each shard holds — and the skew is what decides whether a strategy survives contact with real data.
Shard 1141 rows
Shard 289 rows
Shard 390 rows
Shard 480 rows
Largest / smallest shard
1.8×
Perfect balance would be
100 rows each
Hot shard. Shard 1 holds 141 rows — 1.8× the smallest. It gets 35% of all traffic on 25% of the hardware. Adding more shards does not help unless the key distributes; the key is the problem. Here the cause is that names are not uniform across the alphabet. With a monotonically increasing id as the range key it is worse: every insert lands on the last shard.

Shard 1 gets A–F, shard 2 G–M, and so on. Easy to reason about and range queries stay on one shard.

Scans over a range of the key touch one shard.
Real data is not uniform in the key. Names cluster; ids grow monotonically so every new row lands on the last shard. This is the hot-shard problem.
Which shards does this query touch?
Shards touched
1 of 4
Partitioning vs sharding: partitioning splits one table into pieces inside one database — same machine, same transactions, same joins. Sharding puts pieces on different machines, and everything that crosses the boundary (a join, a transaction, a unique constraint, a count) becomes your application’s problem. Partition first; shard when one machine’s write capacity is measurably the limit.

When to use — and when not

Use it when
  • Partitioning: any table growing past hundreds of millions of rows with a natural time or tenant key. Sharding: a measured single-machine write ceiling.
Avoid it when
  • Sharding for read scaling (use replicas) or before vertical scaling is exhausted.
  • Partitioning by a column queries do not filter on.

Failure modes

  • A shard key that concentrates load — hot shard.
  • Scatter-gather queries that scale with shard count.
  • Cross-shard transactions bolted on after the fact.
  • Resharding a hash scheme that remaps every key.

See how this works internally →

Descend one layer: the same topic explained from the machinery up.

Key → partition function → node, and what rebalancing moves
↓ Database Internals → Partitioning Internals: Key → Partition Function → Node
← Replication and Read ScalingDistributed Consistency: CAP, Quorums, Consensus →