Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 65 ยท Databases at Scale

Sharding and Partitioning

Learn data partitioning and database sharding: range, hash and directory strategies, hot spots, resharding, cross-shard queries and the cost of getting it wrong.

Replication gives you copies, but every copy still holds the whole data set and takes every write. When one machine can no longer store your data or absorb your write traffic, you must split the data itself. Partitioning divides a large data set into smaller pieces. When those pieces live on different machines, each piece is called a shard, and the practice is called sharding.

Sharding is powerful and permanent: it changes how you write queries, how you do transactions and how you operate your system. This lesson shows the strategies, the traps and a way to measure them.

Vertical and horizontal partitioning

There are two ways to cut a table.

  • Vertical partitioning splits by columns. You move rarely used or large columns (a profile biography, a blob) into a separate table or store so the hot table stays small.
  • Horizontal partitioning splits by rows. Each shard has the same columns but a different subset of rows, such as users A to M on one shard and N to Z on another.

When people say "sharding" they almost always mean horizontal partitioning across machines. You can also split by function (users database, orders database, search index), which is the idea behind database per service.

Choosing a shard key

The shard key is the column that decides which shard holds a row. It is the most important decision in a sharded design. A good key:

  1. has many distinct values (high cardinality),
  2. spreads load evenly,
  3. appears in most queries, so a query goes to one shard instead of all of them.

For a social app, user_id is usually good: a user's data lives together. A poor key is a country code (a few huge values) or a creation date (all new writes hit the newest shard).

Three partitioning strategies

How to map a key to a shard

Range Keys A-F here, G-M there.
Hash hash(key) mod number of shards.
Directory A lookup table says where each key lives.

Each one trades even spread for query power.

1 / 2

Range partitioning keeps neighboring keys together, so "all orders from January" reads one shard. But sequential keys such as timestamps or auto-increment IDs send all new writes to the last shard, creating a hot spot. Hash partitioning applies a hash function and takes it modulo the shard count, which spreads keys evenly but destroys ordering, so range queries must ask every shard. A directory (lookup service) stores an explicit mapping, which lets you move any key anywhere, at the cost of an extra lookup and a component that must itself be highly available.

Python Range versus hash: how evenly do they spread?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The range layout sends most traffic to the last shard because new data is hottest. Hashing spreads that load, at the price of range scans.

Resharding: why hash mod N hurts

With hash(key) mod N, changing N moves almost every key. Adding a fifth shard to four forces roughly 80 percent of data to move, which is a huge, risky migration. Two common solutions:

  • Many small logical partitions: create, say, 1,024 partitions up front and assign them to physical servers. Adding a server moves whole partitions, not individual keys. Elasticsearch, Kafka and Cassandra-style systems work like this.
  • Consistent hashing: place servers and keys on a ring so that adding a node moves only about 1/N of the keys. Cache clusters and Dynamo-style stores rely on it.
Python How many keys move when you add a shard?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The costs of sharding

Once data is split, several things that were free become hard:

  • Cross-shard queries: a query without the shard key must scatter-gather: ask every shard and merge the results. It is slow and gets slower with every shard.
  • Joins and transactions across shards: joins need shipping data around; a transaction touching two shards needs two-phase commit or a saga (see Saga Pattern).
  • Unique IDs: auto-increment no longer works across shards; use UUIDs, a snowflake-style generator or per-shard ranges.
  • Rebalancing and hot keys: one celebrity user can overload a shard even with good hashing. Split hot keys by adding a random suffix, or cache them.
  • Operations: schema changes, backups and monitoring now run on many databases.

Shard late. First exhaust the cheaper options: indexes, query tuning, caching, read replicas and a bigger machine. Many successful products never needed more than one primary.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which shard key is most likely to create a hot spot?

    With range partitioning on time, every new write lands in the newest range, so one shard takes all the write traffic.
  2. 2. What is the main drawback of hash partitioning?

    Hashing destroys key order, so a range query cannot be answered by one shard and must be scatter-gathered.
  3. 3. Why is hash(key) mod N painful when N changes?

    Changing the divisor reshuffles almost all assignments. Fixed logical partitions or consistent hashing limit the movement.
  4. 4. Which is a cost of sharding?

    Only queries that include the shard key can go to one shard; others must ask all shards and merge.

Summary

  • Sharding splits data across machines to scale storage and writes; replication does not.
  • The shard key decides everything: high cardinality, even load and present in most queries.
  • Range keeps order but risks hot spots; hash spreads load but loses range scans; a directory is flexible but a dependency.
  • Use many logical partitions or consistent hashing so resharding moves little data.
  • Sharding makes joins, transactions and unique IDs harder: do it only when you must.

Next: Consistency Models for Designers