Sharding and Partitioning

Splitting data within one instance vs across many, and the shard-key decision that's hard to undo

Partitioningwithin one instance
Shardingacross many instances
Shard keydistribution + query pattern
Reshardingexpensive if not planned for

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

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.

Key features

Partitioning: splitting one table's rows into physical partitions inside a single database instance (e.g. by date range or hash)
Sharding: splitting data across multiple independent database instances, each holding a subset of the overall data
Partitioning alone does not increase total capacity — it is still one machine's CPU, memory and disk
Sharding increases capacity by adding more machines, each independently handling its own shard's reads and writes
Many systems use both together: each shard is itself internally partitioned for query performance within that shard

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

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

Command

text

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

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.

2.Choosing a shard key for distribution, locality and query patterns

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.

Key features

The shard key determines which shard a given row belongs to — usually via a hash or range of that field's value
A good shard key distributes data and traffic roughly evenly across all shards — an uneven key creates a hot shard
Choosing a shard key that matches the most common query pattern avoids cross-shard fan-out for everyday queries
A shard key with high cardinality (many distinct values) spreads better than one with few distinct values
Changing a shard key after data is distributed usually means a full resharding migration — get this decision right early

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

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

Command

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

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.

3.Hot partitions, cross-shard queries and resharding

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.

Key features

A hot shard receives disproportionately more traffic or data than the others, even with a reasonable shard key, if the underlying real-world distribution is itself uneven (e.g. one celebrity user, one huge tenant)
Cross-shard queries — joins or aggregations spanning more than one shard — usually require the application or a query layer to fan out and merge results, since no single shard has the full picture
Resharding (changing the number of shards) requires moving data between shards, which is operationally risky and typically done gradually, not as a single cutover
Global secondary indexes (querying by a field other than the shard key) often need their own separate distributed index, since the primary shard layout doesn't help
Every one of these costs is the price of the capacity sharding buys — a system should only pay it once single-instance approaches genuinely cannot meet the requirement

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

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

Signature

sharding costs: hot shards, cross-shard queries, expensive resharding

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.

Spotted a mistake on this page? Tell us.

Read Sheet