Sharding and partitioning both split one logical dataset into smaller pieces so the database can route work to a narrower slice of data. They are easy to conflate; the simplest distinction is that partitioning splits one table under a single database engine, while sharding spreads ownership across multiple independent storage nodes.1

A PingCAP diagram comparing partitioned data on one database instance with sharded data split across two database instances.

Partitioning divides data inside one database engine; sharding puts shards on separate storage nodes.2

Sharding

Sharding spreads one logical dataset across multiple independent storage nodes, so storage and write load can grow past what one node can handle. Each shard is an ownership boundary with its own disk and compute path.

Choosing a shard key

The shard key decides how evenly data spreads and which queries can be served by a single shard. Range sharding keeps adjacent keys together, so range scans stay on one shard, but a busy range can overload one instance. Hash sharding scatters keys evenly and avoids hotspots from monotonic keys, but a range query then has to fan out to every shard.

Range vs Hash Splitter

The same tenant IDs can preserve range locality or scatter more evenly depending on the shard key strategy.

WHERE tenant_id BETWEEN 240 AND 260touches 1 of 3 shards: Shard A
Shard A001-333
042118240245250252260
Shard B334-666
481
Shard C667-999
817920

Adjacent tenant IDs stay together, so this range query is local.

Routing requests

Because the data lives in different places, something has to decide where each row goes. That decision comes from a shard key: the column whose value maps a row to the instance that owns it. A routing layer turns the key into a connection, either inside the application or in a proxy that speaks the database protocol, like Vitess's VTGate for MySQL or Citus's coordinator for Postgres.

The application issues a normal query, and the router sends it to the shard that owns the row.

Routing Requests To Shards

A request uses a shard key to find the database instance that owns the row.

Request router to shardsA tenant request moves from the application through a router to the shard that owns the tenant range.Apptenant_042Routerrange map-> AShard Atenant 001-299Shard Btenant 300-699Shard Ctenant 700-999
  1. tenant_042 -> Shard A
  2. tenant_481 -> Shard B
  3. tenant_817 -> Shard C
  4. tenant_225 -> Shard A

Hot shards

Even with hashing, skew can concentrate load on one shard. A hotspot is when one key value or one tenant takes most of the traffic, so a single shard saturates while the rest sit idle. A low-cardinality shard key is the usual cause.

Hot Tenant Problem

If one tenant sends most of the traffic, the shard that owns it gets hot while the other shards stay cool.

Traffic budget100% total
Shard A
10% trafficcool
Shard B
80% traffichot
Shard C
10% trafficcool

Traffic mix: 10% / 80% / 10%. The total stays at 100%; any shard at 50% or higher is hot.

Crossing shards

The hard part of sharding is any operation that spans shards. A query without the shard key becomes a scatter/gather across every shard. A transaction across shards needs a distributed commit to stay atomic, and resharding a live cluster is one of the hardest things to run. Consistent hashing lowers the rebalancing cost by moving roughly the new node's share of keys when you add capacity.

Consistent Hashing Ring

Compare the current ring with the previous node count. Key positions stay fixed; red keys are the ones whose next clockwise token changed when the latest node was added.

Moved on add5 of 28
Stayed put23 keys
Active nodes4

The ring now has 4 nodes. Red keys changed owners when the latest node was added.

Partitioning

Partitioning makes the opposite tradeoff: it keeps one logical table under one database engine and asks that engine to do the splitting. It is declarative, so you tell the engine the partition key and method and it routes rows and rewrites queries for you. PostgreSQL declarative partitioning, MySQL partitioning, and TiDB partitioned tables all offer RANGE for an ordered key like a date, LIST for an explicit set of values, and HASH for even spread when no natural range exists.

CREATE TABLE events (
  id          bigint,
  created_at  timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

Choosing a partition key

The partition key has to do two jobs that can pull against each other: match the queries you run and spread rows evenly. The Postgres docs suggest the column you most often filter on in a WHERE clause, and TiDB's partitioned-table best practices frame the same tradeoff around pruning, write hotspots, and index design. A low-cardinality key (a boolean, a three-value status) or a skewed key (a country column where most rows share one country) leaves you with one giant partition that still gets scanned in full.

Bad Partition Key

A partition key should match common queries and distribute rows. A low-cardinality or skewed key can create one giant partition.

Jan
21%
Feb
19%
Mar
20%
Apr
20%
May
20%

Balanced ranges; common date queries prune cleanly.

Partition pruning

The payoff for a good key is partition pruning. The planner reads each partition's bounds, proves a partition cannot hold any matching rows, and skips it entirely. Pruning follows the partition key, not indexes, so a query has to filter on that key to benefit.

Query Planner Pruning

A partition-aware query planner skips partitions that cannot contain matching rows.

WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'scans 1 of 5 partitions
Janorders_2026_01skip
Feborders_2026_02skip
Marorders_2026_03scan
Aprorders_2026_04skip
Mayorders_2026_05skip

Conclusion

Sharding and partitioning both route rows by key. A partition key groups rows inside one database engine so the engine can route writes and skip irrelevant chunks. A shard key chooses which independent storage owner gets the row or request.3

PatternKeyReason

Merchant SaaS

shop_id

In Shopify's shop-owned tables, shop_id identifies the shop; shard balancing moves rows for that shop to another shard.4

Multi-tenant SaaS

tenant_id

The Citus tenant example distributes tenant tables by tenant_id, keeping same-tenant joins on one worker.5

DM search

user_id

Discord reindexed direct messages by user_id, so cross-DM search can target one user's messages instead of every conversation.6

Warehouse partitioning

transaction_date

BigQuery can prune partitions when a query filters the transaction_date partition key, so date filters scan less data.7

Choose keys that appear in filters or joins, have high cardinality, avoid skew, and colocate rows queried together. Use time keys for pruning or retention; use entity keys when distributing time-series data. Avoid low-cardinality distribution keys such as a three-value status column.8

References

Footnotes

  1. See PlanetScale's Sharding vs. partitioning: What's the difference?. ↩

  2. Diagram vendored from PingCAP's Database Sharding Explained: Strategies for Scalable SQL Performance. ↩

  3. PostgreSQL's table partitioning docs describe the partition key as routing rows to partitions; Microsoft's Citus distribution-column docs describe the distribution column as assigning distributed-table rows to shards and determining how data spreads across nodes. ↩

  4. Shopify Engineering's shard-balancing post says shop_id serves as the sharding key for shop-owned tables and that moving a shop means copying rows with that shop_id to another MySQL shard. ↩

  5. Microsoft's Citus distribution-column docs include a tenant example that distributes event and page by tenant_id so same-tenant joins stay colocated. ↩

  6. Discord's message-indexing post says DM messages are sharded by user_id, which powers cross-DM search. ↩

  7. BigQuery's querying partitioned tables docs show pruning on a table partitioned by transaction_date. ↩

  8. Microsoft's Citus distribution-column docs recommend high-cardinality, evenly distributed columns used in joins or grouping, warn that skew concentrates data in some shards, and warn against timestamps as distribution columns for time-series data. BigQuery's partition-pruning docs and TiDB's partitioned-table best practices document pruning when predicates align with partition keys. ClickHouse's best-practices post warns that over-partitioning can hurt. ↩