Partitioning and sharding

Sharding splits a large dataset across several databases (shards), each holding part of the data.

Why it matters

Even the biggest single database has limits on storage and write speed. When your data or writes outgrow it, you have to split the data across machines.

The idea

When one database can't handle the writes or the data size, you split it by a shard key. Range sharding (A–M, N–Z) keeps neighbouring keys together but can create hot spots. Hash sharding spreads data evenly but makes range queries harder. The shard key is the biggest decision: it decides balance, which queries stay on one shard, and how hard it is to add shards later.

A library too big for one building, split across several buildings: by the author's first letter, or by a code that spreads the books evenly.

Library visitors can walk between buildings. Queries that need data from many shards (joins, global counts) are slow and complicated, so you choose the shard key around the main queries.

How it works

  1. Choose a shard key that matches your main query, such as user_id.
  2. A routing rule maps each key to a shard: for example, shard = hash(user_id) % 16.
  3. The application, or a proxy, uses that rule to send each query to the right shard.
  4. Queries by user_id hit one shard and are fast. Queries across all users must ask every shard and merge the results, which is slow.
  5. To add capacity, split shards or move some keys. Consistent hashing, or many small logical shards, makes this much easier.

Example

An Instagram-like app shards user data by a hash of user_id, so one user's posts all live on the same shard and 'show my profile' touches one database.

Key points

Common mistakes

Not true: Shard key: country is a good one, since each user has exactly one.
In fact: A key with few values makes huge, uneven shards: one big country can overload its shard. A good shard key has many distinct values, spread evenly.

Not true: Time-based shard key: new writes are spread evenly across the shards.
In fact: New rows all carry the latest time, so they all land on the same shard. That shard becomes a hot spot while the others sit idle.

In the interview

Once writes or data outgrow one database, I'd shard by a key that matches the main query. Here that is user_id, hashed for an even spread, so a user's data lives on one shard. I'd use consistent hashing or many logical shards to make adding machines easier, and handle very hot users with caching.

Part of Unit 3: Data: databases, storage and scaling data in the free System Design course.