Partitioning and sharding

Step 1 Understand it

The problem it solves. 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.

Watch it happen

Picture it

flowchart TD
  Q["Query for user_id 42"] --> RT{"shard = hash of user_id, mod 4"}
  RT --> S0[("Shard 0")]
  RT --> S1[("Shard 1")]
  RT -->|"user 42 lives here"| S2[("Shard 2")]
  RT --> S3[("Shard 3")]

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.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

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.

Must-know points

• A good shard key: many distinct values, an even spread, and it matches the main access pattern.
• Hash sharding: even load, poor for ranges. Range sharding: good for ranges, risk of hot spots.
• Hot keys (a celebrity) can overload one shard: split them further, or cache them.
• Cross-shard joins and transactions are hard: design so you don't need them.
• Resharding is painful; consistent hashing or many small logical shards make it easier.
• Shard only when needed: try caching, read replicas and a bigger machine first.

Common mistakes

• A shard key with few values (like country), which creates huge, uneven shards.
• A time-based key that sends every new write to the same shard.
• Designs whose common requests need joins or transactions across shards.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and extra practice.

• Watch

  Distributed Systems lecture series: partitioning

  Martin Kleppmann, University of Cambridge · free on YouTube

• Watch

  ByteByteGo: database sharding

  ByteByteGo · YouTube

• Read

  The System Design Primer

  Donne Martin · GitHub, free

Step 3 Practise

Try each exercise yourself first: on paper, out loud or in code. Then open the answer and compare it with yours.

1. ExplainSay it in plain words in under a minute, with no notes.

   Explain sharding and the shard key with the library-buildings picture.

   Show answer

   The library has grown too big for one building, so the books are split across several buildings (shards). The shard key is the rule for which building a book goes in: by the author's first letter (A–F in building 1: range sharding), or by a code that spreads the books evenly (hash sharding).

   If you know a book's key, you go straight to the right building: a query by the shard key touches one shard. But a question like "every book published in 2020" has to be asked in every building and the answers combined, which is slow. So you choose the shard key to match the questions asked most often, and you choose one that spreads books evenly, so no single building is overwhelmed.

2. DrawSketch the diagram (or class diagram) from memory.

   Draw 4 shards with hash(user_id) % 4 routing, and show what happens when you add a fifth.

   Show answer

   With hash % N, the shard depends on N, so changing N from 4 to 5 changes the answer for most keys: about 80% of the data has to move (here 7 of 10 users). The fix is to decouple keys from physical machines: consistent hashing, or a fixed number of logical shards (say 1,024) that are mapped to machines in a table, so adding a machine moves a few whole logical shards and nothing else.

   textCopy

   shard = hash(user_id) % 4        (say the hash is the id)
   
   user_id:  1  2  3  4  5  6  7  8  9  10
   % 4:      1  2  3  0  1  2  3  0  1  2
   % 5:      1  2  3  4  0  1  2  3  4  0
   moves?    .  .  .  ✓  ✓  ✓  ✓  ✓  ✓  ✓
   
   Shard 0: 4, 8      Shard 1: 1, 5, 9
   Shard 2: 2, 6, 10  Shard 3: 3, 7
   
   Add a fifth shard → 7 of 10 users move.
   In general, about 4 in 5 keys move.

3. ApplyUse it in a real design question and say exactly where it fits.

   Choose a shard key for: a chat app's messages, an online shop's orders, a URL shortener. Justify each.

   Show answer

   • Chat messages: conversation_id. The main query is "the latest messages in this chat", so all of a conversation's messages should live together, sorted by time inside the shard. There are millions of conversations, so the spread is even. (One giant group chat can be a hot spot; split it by time bucket if needed.)
   • Online shop orders: customer_id. "My orders" is the most common query and stays on one shard. To look an order up by order_id alone, put the shard number inside the order ID, or keep a small lookup table from order_id to shard. Reports across all orders go to a separate analytics copy.
   • URL shortener: the short code (hashed). Every redirect is a lookup by code and nothing else, so hashing the code gives a perfectly even spread and one shard per request.

4. Trade-offsSay what it costs, when not to use it, and the alternative.

   Range or hash sharding for sensor readings that are always queried by time range?

   Show answer

   Neither on its own is ideal.

   • Range sharding by time alone: a time-range query reads one or two shards (good), but every new reading has the newest timestamp, so all writes land on the newest shard (a hot spot), while the old shards sit idle.
   • Hash sharding: writes spread evenly (good), but neighbouring times are scattered, so every time-range query has to ask every shard and merge the results.

   The usual answer is both: shard by a hash of the sensor ID (spreading the writes across sensors), and within each shard store each sensor's readings sorted by time, often in one partition per sensor per day. "Sensor 17 between 09:00 and 10:00" then goes to one shard and reads one contiguous range. Queries across all sensors for a time range are rarer; they scatter to every shard, or run on a pre-aggregated copy (time-series databases such as TimescaleDB do this partitioning for you).

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud. Practise it until it sounds like you, not like notes.

Once writes or data outgrow one database, I'd shard by a key that matches the main query — here 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.

Follow-up questions

Tick each check when you can do it without notes