Every database has a ceiling β a table too big to index well, a write rate one machine can't sustain, a dataset that no longer fits on one disk. Partitioning splits a table into pieces on one server; sharding splits data across many servers. They're often confused, solve different problems, and sharding especially carries a cost that makes "not yet" the right answer far more often than teams admit.
The one-line distinction. Partitioning = one database, one table broken into sub-tables (a manageability and query-pruning win). Sharding = many databases, data split across independent servers (a scaling win for writes and storage). Partition early and freely; shard late and reluctantly.
Foundations Partitioning vs sharding
Both slice a big table by a key. The difference is where the slices live β and that changes everything about the trade-offs.
Partitioning
Sharding
Data lives on
One server (sub-tables of one table)
Many servers (independent databases)
Primary win
Manageability + query pruning
Horizontal scale: writes, storage, throughput
Query routing
Automatic within the engine
Your app / a coordinator must route
Cross-piece joins
Normal β same engine
Hard β cross-network, often avoided
Operational cost
Low
High β the reason to delay it
They compose. A shard is itself a database β so each shard's big tables can also be partitioned. Real large-scale systems often partition within each shard. Master partitioning first; it's the cheaper, safer half and buys a lot of runway.
Within one DB Partitioning strategies
You split by a partition key. Three schemes cover almost everything (Postgres calls this declarative partitioning; the concepts apply to MySQL, Oracle, etc.):
π Range
Best for Time-series and any ordered key β one partition per month/year, or per id range. Makes "drop last quarter" instant and prunes date-filtered queries to one partition.
π List
Best for A discrete category β partition by region, country, or tenant_tier. Each partition holds an explicit set of values.
π² Hash
Best for Even spread when there's no natural range/list β hash the key into N partitions to balance size and write load. Range queries can't prune, though.
-- Range partitioning by month (PostgreSQL declarative syntax)CREATE TABLE orders (id bigint, created_at timestamptz, ...)
PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026_07 PARTITION OF orders
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
-- Query pruning: the planner touches ONLY the July partitionSELECT * FROM orders WHERE created_at >= '2026-07-10';
The partition key must be in every unique constraint. A primary key or unique index on a partitioned table has to include the partition key β so you can't cheaply enforce global uniqueness on an unrelated column. Choose the key knowing your constraints, and don't create thousands of partitions (planning and metadata overhead grows).
The payoff Why partition
Partitioning rarely speeds up a well-indexed point lookup β its wins are elsewhere, and they're substantial:
Instant bulk deletion.DROP/DETACH an old partition is a metadata operation β no giant DELETE, no bloat, no long-running VACUUM. This alone justifies partitioning most time-series tables.
Partition pruning. Queries filtered on the partition key skip every irrelevant partition, reading far fewer rows.
Smaller indexes. Each partition has its own index β smaller, cheaper to maintain, more cache-friendly than one monster B-tree.
Cheaper maintenance.VACUUM, ANALYZE, and reindexing run per partition, so autovacuum keeps up on a hot recent partition without rescanning history.
The classic fit: an append-heavy events/logs/orders table with a retention window. Range-partition by time, drop partitions past the window, and both your query performance and your maintenance stay flat as total data grows.
Across servers Why shard
Partitioning still lives on one machine β so it can't get past that machine's write throughput, RAM, or disk. Sharding spreads data across independent servers so total capacity scales with the number of shards. You shard when you've genuinely outgrown one box:
Limit you've hit
Why replicas/partitioning won't fix it
Write throughput
Read replicas scale reads; every write still hits the single primary
Dataset too big for one disk/RAM
Partitioning reduces per-query scan, not total storage on the node
Vertical scaling exhausted
You're already on the biggest instance and it's not enough
Sharding is a one-way door. It multiplies operational complexity β routing, cross-shard queries, rebalancing, backups per shard, schema changes across N nodes. Every alternative below (Β§9) should be exhausted first. Teams that shard prematurely spend years paying for capacity they didn't need.
The decision that matters most Choosing a shard key
The shard key (the column whose value decides which shard a row lives on) is the single most consequential and hardest-to-reverse choice in a sharded system. A good key has three properties:
1 Β· High cardinality
Many distinct values
So data can spread across many shards. A boolean or a 3-value status can't.
2 Β· Even distribution
No hotspots
Values roughly uniform, so no single shard gets most of the traffic or data.
3 Β· In your queries
Avoids scatter-gather
Present in most read paths, so a query targets one shard instead of all of them.
Common good keys: user_id, tenant_id, account_id β most queries already filter by them, so requests stay single-shard, and related data co-locates naturally.
The hotspot traps. A monotonic key (auto-increment id, timestamp) sends all new writes to one shard β a moving hot spot. And the celebrity problem: sharding a social graph by user_id still overloads the shard holding a user with 50M followers. Watch for skew that no clever key fully removes; sometimes you special-case the whales.
The mapping Sharding strategies
How does a key map to a shard? Four approaches, each with a distinct trade.
Strategy
How
Win
Cost
Range
Contiguous key ranges per shard (AβH, IβPβ¦)
Range scans hit few shards
Hotspots on skewed/monotonic keys
Hash
hash(key) % N β shard
Even distribution
Range queries scatter; resharding is painful
Directory
A lookup table maps key β shard
Total flexibility; easy rebalance
The lookup is a bottleneck & single point of failure
Geo / entity
By region or tenant
Data locality, residency compliance
Uneven regions; cross-region queries
Plain hash % N has a nasty flaw: add or remove one shard and N changes, so almost every key remaps to a different shard β a full, disruptive data migration. This is exactly the problem consistent hashing (next) is designed to solve.
The tax The hard parts of sharding
Once data spans servers, things a single database gave you for free become real engineering problems:
π Cross-shard queries & joins
A query that can't be limited to one shard becomes scatter-gather: fan out to all shards, merge results β slow and as weak as the slowest shard. Joins across shards are worst; the fix is to co-locate related data under the same shard key, or denormalize so the join isn't needed.
π³ Cross-shard transactions
A single ACID transaction can't span independent databases. You fall back to two-phase commit (slow, blocking) or sagas (eventual consistency, compensating actions). Design so a business operation touches one shard whenever possible β see concurrency design.
π Global uniqueness & IDs
Auto-increment breaks across shards. Use globally-unique IDs β UUIDs, ULIDs, or Snowflake-style ids β generated without coordination so every shard mints unique keys independently.
The golden rule of sharding: keep operations single-shard. A schema where the common queries and transactions all filter by the shard key turns sharding into "just more databases." The moment your hot paths need cross-shard joins or transactions, the design is fighting you β revisit the shard key.
Growth Rebalancing & consistent hashing
Shards fill up; you add more. The question is how much data has to move. Naive hash % N reshuffles nearly everything. Consistent hashing places shards and keys on a ring, so adding a node only moves the keys between it and its neighbour β roughly 1/N of the data, not all of it.
# hash % N β add a shard (4 β 5) and ~80% of keys remap π±
shard = hash(key) % N
# Consistent hashing β shards & keys on a ring; a key belongs to# the next shard clockwise. Adding a node moves only ~1/N of keys.# Virtual nodes (many points per shard) keep the ring balanced.
Let a system do this. Rebalancing, routing, and cross-shard coordination are why you usually adopt a sharding platform rather than hand-rolling: Citus (distributed PostgreSQL), Vitess (MySQL), or built-in sharding in MongoDB, Cassandra, and DynamoDB (partition keys). They handle virtual nodes, resharding, and query fan-out so you don't reinvent them.
Slow down When NOT to shard β the scaling ladder
Sharding is the last rung. Climb these first; most applications never reach the top, and each step is far cheaper than the next:
Tune queries & indexes. Most "we need to scale" is one missing index. Start at query performance.
Scale up (vertical). A bigger instance is trivial next to sharding. Modern hardware goes very far.
Read replicas. Offload read traffic if the load is read-heavy (most apps are).
Cache. Put hot reads in Redis/Memcached and take them off the database entirely.
Partition. Break the big tables up on one server β often enough on its own.
Then shard. Only when writes or dataset size have truly outgrown one machine.
"Don't shard until it hurts." Premature sharding buys enormous complexity to solve a problem you may not have for years, if ever. The right time is when a single well-tuned, well-provisioned, replicated, partitioned primary genuinely can't keep up β and you can prove it with numbers, not fear.
End to end Worked example: an events table that outgrows one box
An events table ingests analytics: billions of rows, 90-day retention, mostly queried by tenant_id and time. It's slow and the nightly cleanup DELETE runs for hours. Walk the ladder.
Step 1 Β· Partition by time (fixes cleanup & pruning)
CREATE TABLE events (tenant_id bigint, ts timestamptz, ...)
PARTITION BY RANGE (ts);
-- one partition per day; retention = drop day-91 instantlyDROP TABLE events_2026_04_30; -- was an hours-long DELETE, now instant
The multi-hour cleanup becomes a metadata DROP; date-ranged queries prune to a handful of daily partitions. For many teams, this is the whole fix β no sharding needed.
Step 2 Β· Exhaust single-node scaling
Add the missing composite index (tenant_id, ts), scale the instance up, and move dashboards to a read replica. Suppose ingest still saturates the primary's write path β now, and only now, sharding is justified.
Step 3 Β· Shard by tenant_id (keeps queries single-shard)
Decision
Choice
Why
Shard key
tenant_id
High cardinality, in nearly every query β single-shard reads
Strategy
Hash + consistent hashing
Even spread; cheap to add shards later
Within each shard
Range-partition by ts
Retention drops & pruning still apply per shard
IDs
Snowflake / UUID
Unique without cross-shard coordination
Big-tenant skew
Isolate whales to dedicated shards
Handles the celebrity/hotspot problem
Notice the order. Partitioning did most of the work and shipped in a day; sharding came only after single-node options were spent, used a key that keeps queries single-shard, and still partitions inside each shard. That's the healthy path: partition first, shard last, and let the shard key keep operations local.
Cheat sheet
Question
Short answer
Partitioning vs sharding?
Sub-tables on one server vs data across many servers.
Partitioning's biggest win?
Instant drop of old partitions + query pruning + cheaper maintenance.
Partition schemes?
Range (time), List (category), Hash (even spread).
When to shard?
Write throughput or dataset size outgrows one machine.
Most important shard decision?
The shard key β high cardinality, even, in your queries.
Shard-key hotspots?
Monotonic keys & the celebrity problem.
Why avoid hash % N?
Adding a shard remaps almost everything β use consistent hashing.