beginnerintermediateadvanced

System Design · Section 23

Sharding and Partitioning

Level: advanced
Read: 20 min
Concepts: 3

Partitioning splits one table's rows into smaller physical pieces within a single database instance — good for manageability and query performance, but it never exceeds one machine's capacity. Sharding distributes data across multiple independent database instances, which is what actually scales storage and write throughput past a single machine. The shard key — the field that decides which shard a row belongs to — is the single most consequential and hardest-to-change decision in a sharded design, and sharding brings recurring operational costs (hot shards, cross-shard queries, resharding) that a design has to plan for from the start.

This section

System Design: Horizontal Partitioning or Sharding | Data Partitioning | Scalable SystemsUdit Agarwal

What is true here

Partitioning splits data within one instance; sharding distributes it across multiple instances, which is what adds capacity.
A good shard key spreads load evenly and matches the application's dominant query pattern.
Sequential IDs or timestamps as range-based shard keys create a hot shard — new writes all land on the same one.
Sharding brings ongoing costs — hot shards, cross-shard queries, expensive resharding — that a design has to plan for upfront.

What you will be able to do

Distinguish what partitioning and sharding each actually solve
Choose a shard key that avoids the sequential-key hotspot trap and matches real query patterns
Anticipate the operational costs sharding introduces before committing to it

What each one actually solves

Partitioning's scope vs sharding's scope, and the shard-key decision that follows from choosing to shard.

Partitioning splits data within a database; sharding distributes it across instances

coreintermediate

Partitioning divides one table's rows into smaller physical pieces that still live on the same database server — it helps manageability and query performance but does not add capacity beyond one machine. Sharding goes further: it splits data across multiple separate database instances (often on separate machines), which is what actually lets storage and write throughput scale past what a single instance can hold.

Think of it as

Partitioning is organizing one huge filing cabinet's drawers by year, so finding "2024 records" doesn't mean searching every drawer — but it's still one cabinet, in one room, with one capacity. Sharding is buying five separate filing cabinets and putting them in five different rooms, each holding a different slice of the records — now the total capacity is five cabinets' worth, but finding something means first knowing which room to walk into.

text

partitioning:  one DB instance  → orders_2024, orders_2025, ...
sharding:      many DB instances → shard-0, shard-1, shard-2, ...
                                    (each with its own partitions)

What we're doing: Show a table that outgrows partitioning alone and needs sharding to keep scaling.

partition-then-shard.txttext

Year 1: orders table has 50M rows on one Postgres
instance. Partitioned by month for query speed and
easy archival — still one instance, comfortably
within its disk and CPU limits.

Year 3: orders table has 4B rows. The single instance
is now disk-full and write-saturated even with
partitioning — partitioning only reorganized the rows,
it never added capacity beyond this one machine.

Fix: shard by customer_id across 8 database instances.
Each instance now holds ~500M rows (still partitioned
internally by month) — total capacity scales with the
number of shards, which partitioning alone could not do.

8: This is the ceiling partitioning cannot break through — it never added a second machine's worth of capacity.
11: Sharding is what actually adds capacity here — 8 instances, each independently handling its own slice.

Why this works: A very common mistake is reaching for "more partitions" to solve a capacity problem that only sharding can actually fix — the two solve genuinely different problems and are not interchangeable.

Adding more partitions to solve a single-instance capacity problem

Wrong

text

"The database is out of disk space — let's add
more partitions to spread the load."

Better

text

"The database is out of disk space on one
instance — more partitions won't add capacity,
since they're still all on the same machine.
We need sharding: distributing data across
multiple instances, or moving to managed
storage that scales independently."

What you see: A team repartitions an already-partitioned table to try to fix disk space or write-throughput pressure, sees no real improvement, and is confused why — because partitioning never touches the underlying single-machine ceiling that sharding exists to break.

Why: Partitioning reorganizes data for manageability and query performance within a fixed amount of hardware; it cannot exceed that hardware's limits no matter how the rows are split, because every partition still lives on the same disk and competes for the same CPU.

Partitioning vs. sharding

Partitioning

+One database instance, split into pieces
+Helps manageability and query speed
+Never adds capacity beyond one machine

Sharding

−Multiple independent database instances
−Each shard handles its own reads and writes
−Actually adds capacity by adding machines

Partitioning vs sharding

Partitioning vs sharding
Property	Partitioning	Sharding
Scope	Within one database instance	Across multiple database instances
Adds capacity beyond one machine	No	Yes
Typical goal	Query performance, manageable table sizes, easy archival	Total storage and write-throughput scale
Cross-piece queries	Usually still simple (one instance, one query engine)	Often requires application-level fan-out or a routing layer

Remember: Partitioning reorganizes data within one instance for manageability and query speed; sharding distributes data across multiple instances to actually add capacity beyond what one machine can hold.

Choosing a shard key for distribution, locality and query patterns

coreadvanced

The shard key is the field used to decide which shard a row lives on — it is the single most consequential decision in a sharded design, because it is expensive to change afterward. A good shard key spreads data and load evenly across shards, keeps data that is usually queried together on the same shard, and matches how the application actually reads and writes.

Think of it as

Choosing a shard key is like deciding how to split a company's filing cabinets across several storage rooms. Split by employee last name and everything about one employee stays together, easy to find — but if hiring is uneven (lots of "S" surnames this year), one room fills up faster than the others. Split badly — say, by the date the file was created — and every new hire's paperwork piles into whichever room holds "this week," overloading it while the others sit half-empty.

text

shard_index = hash(shard_key) % number_of_shards
-- e.g. hash(user_id) % 8 → routes this user's rows
--      consistently to the same one of 8 shards

What we're doing: Show a sequential-ID shard key creating a hot shard, and hashing the fix.

shard-key-hotspot.txttext

Sharding an events table by range on a sequential,
auto-incrementing event_id, split into 4 shards by ID
range (0-1M, 1M-2M, 2M-3M, 3M+):

  All NEW events get the highest IDs, so every new
  write lands on the SAME shard (3M+) — the other 3
  shards receive zero new writes. One hot shard,
  three idle ones.

Fix: shard by hash(event_id) % 4 instead. Now new
events distribute evenly across all 4 shards, because
a hash scrambles sequential input into an even spread
— write load balances across the whole cluster.

6: This is the hotspot: a range-based key on a monotonically increasing value always routes new writes to one shard.
12: Hashing breaks the sequential pattern, which is exactly what spreads writes evenly.

Why this works: A shard key that looks reasonable in isolation can still create a severe hotspot depending on how its values are actually generated — sequential IDs and timestamps are the most common trap.

Sharding by a sequential ID or timestamp with range-based shards

Wrong

text

-- range shard by auto-increment id
shard = (id < 1_000_000) ? 0
      : (id < 2_000_000) ? 1
      : (id < 3_000_000) ? 2
      : 3;

Better

text

-- hash the key before assigning a shard
shard = hash(id) % num_shards;
-- or shard by a field that isn't monotonically
-- increasing in write order, e.g. user_id

What you see: One shard consistently runs hotter (higher CPU, higher write latency, growing disk faster) than the others, and the gap keeps widening over time rather than staying constant — a strong sign new writes are concentrating on a single shard by construction, not by chance.

Why: Range-based sharding on a monotonically increasing key means every new row's key is numerically larger than the last, so it always falls in the same (highest) range — the shard boundaries never spread new writes, only old, static data.

Range shard vs. hashed shard, on a sequential ID

Range shard (by id)

+New IDs are always the highest values
+Every new write lands on the same shard (3M+)
+One hot shard, three idle ones

Hashed shard (by hash(id))

−A hash scrambles sequential input evenly
−New writes spread across all 4 shards
−Write load balances across the whole cluster

Common shard key choices and their trade-offs

Common shard key choices and their trade-offs
Shard key	Good for	Risk
user_id (hashed)	Per-user queries stay on one shard; even distribution	Queries spanning many users need fan-out
tenant_id	Multi-tenant isolation; all of one tenant's data together	A very large tenant can overload its single shard
Geographic region	Data locality, regional compliance	Uneven population across regions creates hot shards
Sequential ID / timestamp	Simple, ordered	All recent writes hit the same shard — a hot shard by construction

Remember: A shard key must spread load evenly (avoid sequential/monotonic keys with range sharding), match the dominant query pattern to avoid cross-shard fan-out, and is expensive to change later — get it right before data volume makes resharding painful.

The ongoing operational cost

What sharding keeps costing after the initial split — hot shards, cross-shard queries, resharding.

Hot partitions, cross-shard queries and resharding

standardadvanced

Sharding trades single-instance capacity limits for a new set of ongoing costs: a shard can still get "hot" if its data or traffic skews unevenly, queries that need data from multiple shards require extra coordination the single-instance case never needed, and growing or shrinking the shard count means physically moving data — none of which is free.

Think of it as

Sharding is like splitting one busy restaurant into five smaller branches across town. It solves the original capacity problem — five kitchens can serve more customers than one — but now creates new ones: one branch might get unexpectedly popular (a hot shard), a customer wanting an item from two different branches' menus needs someone to coordinate between them (a cross-shard query), and opening a sixth branch means physically relocating some existing customers to keep things balanced (resharding).

What we're doing: Show why adding shards later is not a simple, cheap change.

resharding-cost.txttext

System starts with 4 shards, keyed by hash(user_id) % 4.
Growth requires moving to 8 shards.

Naive re-hash: hash(user_id) % 8 assigns most users
to a DIFFERENT shard than they were on before — nearly
all data has to move, essentially a full migration
while the system stays live.

Better: use consistent hashing (or a lookup-table-based
shard map) from the start, so growing from 4 to 8 shards
only moves roughly 1/8 of the data — not nearly all of it.

5: This is the operational cost this concept is about: a plain modulo hash reassigns almost every key on a shard-count change.
9: The mitigation has to be designed in from the start — retrofitting consistent hashing onto an already-modulo-sharded system is itself a migration.

Why this works: Resharding cost is decided by a design choice made at the very first sharding decision — plain modulo hashing looks identical to consistent hashing on day one, but diverges sharply the first time the shard count needs to change.

Sharding's recurring operational costs

Hot shard

uneven real-world distribution

Cross-shard queries

no single shard has it all

Resharding

data must physically move

Secondary indexes

need their own distributed index

Sharding's recurring operational costs

Sharding's recurring operational costs
Cost	Why it happens	Common mitigation
Hot shard	Real-world data/traffic distribution is uneven regardless of key choice	Sub-sharding a hot key, or a dedicated shard for outliers
Cross-shard queries	No single shard holds the full dataset for a non-shard-key query	Application-side fan-out, or a separate search/analytics index
Resharding	Data must physically move when shard count changes	Consistent hashing to minimize moved keys; online migration tooling
Secondary indexes	A query by a non-shard-key field can't use shard routing	Global index service, or accept a scatter-gather query

Remember: Sharding trades single-instance limits for hot shards, cross-shard query complexity, and expensive resharding — plan the shard-count-change story (consistent hashing or a shard map) before the first shard split, not after.