← All tutorials
Sharding & Partitioning

Database sharding & partitioning

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 on one server vs sharding across servers Partitioning breaks one table into sub-tables inside a single database server; sharding distributes data across multiple independent database servers behind a router. Partitioning β€” one server single database orders (parent, logical) 2024part. 2025part. 2026part. Sharding β€” many servers router / coordinator Shard ADB server Shard BDB server Shard CDB server each shard is an independent database one engine sees all sub-tables; queries prune to the right one
PartitioningSharding
Data lives onOne server (sub-tables of one table)Many servers (independent databases)
Primary winManageability + query pruningHorizontal scale: writes, storage, throughput
Query routingAutomatic within the engineYour app / a coordinator must route
Cross-piece joinsNormal β€” same engineHard β€” cross-network, often avoided
Operational costLowHigh β€” 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 partition
SELECT * 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 hitWhy replicas/partitioning won't fix it
Write throughputRead replicas scale reads; every write still hits the single primary
Dataset too big for one disk/RAMPartitioning reduces per-query scan, not total storage on the node
Vertical scaling exhaustedYou'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.

StrategyHowWinCost
RangeContiguous key ranges per shard (A–H, I–P…)Range scans hit few shardsHotspots on skewed/monotonic keys
Hashhash(key) % N β†’ shardEven distributionRange queries scatter; resharding is painful
DirectoryA lookup table maps key β†’ shardTotal flexibility; easy rebalanceThe lookup is a bottleneck & single point of failure
Geo / entityBy region or tenantData locality, residency complianceUneven 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:

  1. Tune queries & indexes. Most "we need to scale" is one missing index. Start at query performance.
  2. Scale up (vertical). A bigger instance is trivial next to sharding. Modern hardware goes very far.
  3. Read replicas. Offload read traffic if the load is read-heavy (most apps are).
  4. Cache. Put hot reads in Redis/Memcached and take them off the database entirely.
  5. Partition. Break the big tables up on one server β€” often enough on its own.
  6. 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 instantly
DROP 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)

DecisionChoiceWhy
Shard keytenant_idHigh cardinality, in nearly every query β†’ single-shard reads
StrategyHash + consistent hashingEven spread; cheap to add shards later
Within each shardRange-partition by tsRetention drops & pruning still apply per shard
IDsSnowflake / UUIDUnique without cross-shard coordination
Big-tenant skewIsolate whales to dedicated shardsHandles 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

QuestionShort 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.
Hardest sharding problems?Cross-shard joins & transactions β€” keep operations single-shard.
Global IDs?UUID / ULID / Snowflake β€” no auto-increment.
Before sharding?Index β†’ scale up β†’ replicas β†’ cache β†’ partition β†’ then shard.

Knowledge check

Six questions. Pick an answer for each, then check your score.

1. What's the core difference between partitioning and sharding?

2. Which is partitioning's clearest, biggest win?

3. Read replicas do NOT solve which problem?

4. A good shard key is…

5. Why is plain hash(key) % N risky for sharding?

6. What keeps a sharded system healthy?

Next steps

🐘 Query performance

Rung 1 of the ladder β€” most "we must scale" is a missing index.

🧹 VACUUM & autovacuum

Why partitions help β€” per-partition maintenance and instant drops.

🧡 Concurrency design

Cross-shard reality β€” transactions, sagas & capacity.

☸️ Cloud native

Where shards run β€” replicas, scaling & observability.