mufassall

Cheat sheet · Data & storage

Sharding

13 min · Intermediate · mufassall.dtwin.uz/en/l/sharding/

← Open the lesson

The problem

A chat service's messages table reaches 6 TB and one server can no longer hold it. We split it across shards, choose a shard key, meet a hot shard and a hot key, and add a fourth shard without moving everything.

What you learn

  • Range sharding versus hash sharding, and why time ranges make a hot shard
  • How to choose a shard key: the most frequent query decides
  • What a cross-shard query costs, and how to split a hot key
  • Why hash % n makes resharding painful, and how fixed slots fix it
Final scene
45%40%41%42%38%30%

Diagram as code

The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.

Mermaid: flowchart

flowchart LR
  n_app(["Chat app"])
  n_api["Chat API"]
  n_shard_0[("Shard 0")]
  n_shard_1[("Shard 1")]
  n_shard_2[("Shard 2")]
  n_shard_3[("Shard 3")]
  n_mover("Data mover")
  n_app --> n_api
  n_api --> n_shard_0
  n_api --> n_shard_1
  n_api --> n_shard_2
  n_api --> n_shard_3
  n_shard_2 --> n_mover
  n_mover --> n_shard_3

Mermaid: request sequence

sequenceDiagram
  participant n_api as Chat API
  participant n_db as Messages DB
  participant n_shard_2 as Shard 2
  participant n_shard_1 as Shard 1
  participant n_shard_0 as Shard 0
  participant n_mover as Data mover
  participant n_shard_3 as Shard 3
  Note over n_api,n_db: One server, 6 TB of messages
  n_api->>n_db: INSERT INTO messages (chat_id, sender_id, body)
  n_db-->>n_api: INSERT 0 1
  Note over n_api,n_shard_2: Split by date range
  n_api->>n_shard_2: route INSERT messages
  n_shard_2-->>n_api: key: created_at = 2026-11-18
  Note over n_api,n_shard_2: Every new message hits the same shard
  n_api->>n_shard_2: INSERT INTO messages (chat_id, sender_id, body)
  n_shard_2-->>n_api: ERROR: canceling statement due to statement timeout
  Note over n_api,n_shard_2: Shard by a hash of chat_id
  n_api->>n_shard_2: route INSERT messages
  n_shard_2-->>n_api: key: chat_id = c_8812
  Note over n_api,n_shard_2: A query without the shard key asks every shard
  n_api->>n_shard_2: query messages WHERE sender_id = 'u_42'
  n_shard_2-->>n_api: -#62; shard-0: 20 rows, 41 ms
  Note over n_api,n_shard_1: One huge channel heats one shard
  n_api->>n_shard_1: top keys, last 5 min (shard-1)
  n_shard_1-->>n_api: chat_id=c_1 41% of writes
  Note over n_api,n_shard_0: Split the hot chat into buckets
  n_api->>n_shard_0: route INSERT messages chat_id=c_1
  n_shard_0-->>n_api: hot chat: 8 buckets
  Note over n_mover,n_shard_3: A fourth shard, and % 4 moves 75% of the rows
  n_mover->>n_shard_3: reshard: hash % 3 -#62; hash % 4
  n_shard_3-->>n_mover: rows to check: 6.9B
  Note over n_mover,n_shard_3: Fixed slots: move only what the new shard takes
  n_mover->>n_shard_3: move slot 517: shard-2 -#62; shard-3
  n_shard_3-->>n_mover: copy: 6.8M rows

D2

direction: right

n_app: "Chat app" {
  shape: rectangle
}
n_api: "Chat API" {
  shape: rectangle
}
n_shard_0: "Shard 0" {
  shape: cylinder
}
n_shard_1: "Shard 1" {
  shape: cylinder
}
n_shard_2: "Shard 2" {
  shape: cylinder
}
n_shard_3: "Shard 3" {
  shape: cylinder
}
n_mover: "Data mover" {
  shape: rectangle
  style.multiple: true
}

n_app -> n_api
n_api -> n_shard_0
n_api -> n_shard_1
n_api -> n_shard_2
n_api -> n_shard_3
n_shard_2 -> n_mover
n_mover -> n_shard_3

PlantUML: request sequence

@startuml
participant "Chat API" as n_api
database "Messages DB" as n_db
database "Shard 2" as n_shard_2
database "Shard 1" as n_shard_1
database "Shard 0" as n_shard_0
participant "Data mover" as n_mover
database "Shard 3" as n_shard_3
note over n_api, n_db : One server, 6 TB of messages
n_api -> n_db : INSERT INTO messages (chat_id, sender_id, body)
n_db --> n_api : INSERT 0 1
note over n_api, n_shard_2 : Split by date range
n_api -> n_shard_2 : route INSERT messages
n_shard_2 --> n_api : key: created_at = 2026-11-18
note over n_api, n_shard_2 : Every new message hits the same shard
n_api -> n_shard_2 : INSERT INTO messages (chat_id, sender_id, body)
n_shard_2 --> n_api : ERROR: canceling statement due to statement timeout
note over n_api, n_shard_2 : Shard by a hash of chat_id
n_api -> n_shard_2 : route INSERT messages
n_shard_2 --> n_api : key: chat_id = c_8812
note over n_api, n_shard_2 : A query without the shard key asks every shard
n_api -> n_shard_2 : [duplicate] query messages WHERE sender_id = 'u_42'
n_shard_2 --> n_api : -> shard-0: 20 rows, 41 ms
note over n_api, n_shard_1 : One huge channel heats one shard
n_api -> n_shard_1 : top keys, last 5 min (shard-1)
n_shard_1 --> n_api : chat_id=c_1 41% of writes
note over n_api, n_shard_0 : Split the hot chat into buckets
n_api -> n_shard_0 : route INSERT messages chat_id=c_1
n_shard_0 --> n_api : hot chat: 8 buckets
note over n_mover, n_shard_3 : A fourth shard, and % 4 moves 75% of the rows
n_mover -> n_shard_3 : reshard: hash % 3 -> hash % 4
n_shard_3 --> n_mover : rows to check: 6.9B
note over n_mover, n_shard_3 : Fixed slots: move only what the new shard takes
n_mover -> n_shard_3 : move slot 517: shard-2 -> shard-3
n_shard_3 --> n_mover : copy: 6.8M rows
@enduml

PlantUML: components

@startuml
left to right direction
agent "Chat app" as n_app
component "Chat API" as n_api
database "Shard 0" as n_shard_0
database "Shard 1" as n_shard_1
database "Shard 2" as n_shard_2
database "Shard 3" as n_shard_3
collections "Data mover" as n_mover
n_app --> n_api
n_api --> n_shard_0
n_api --> n_shard_1
n_api --> n_shard_2
n_api --> n_shard_3
n_shard_2 --> n_mover
n_mover --> n_shard_3
@enduml

Recap

Sharding spreads one table across servers when it no longer fits on one. Everything depends on the shard key.

1) Pick the key your most common query filters on. 2) Avoid keys that grow over time: hash them. 3) Queries without the key fan out to every shard; keep them rare. 4) Watch for hot keys and split them. 5) Route through fixed slots so adding a shard moves only its share.

Numbers: at the start and at the end

MetricStartEnd
Query p951 400 ms28 ms
Hottest shard load98%42%
Shards per query11
Largest shard6 TB2.0 TB

Steps

  1. Problem One server, 6 TB of messages

    Every chat message goes into one messages table.

  2. Fix Split by date range

    We split the table into three shards, each a separate database: 2024 on shard 0, 2025 on shard 1, 2026 on shard 2.

  3. Problem Every new message hits the same shard

    New messages are all from today, so every write lands on shard 2.

  4. Fix Shard by a hash of chat_id

    The new shard key is chat_id, and the shard is hash(chat_id) % 3.

  5. Case A query without the shard key asks every shard

    A new feature: "all messages I sent".

  6. Problem One huge channel heats one shard

    A public channel c_1 with 2 million members goes viral.

  7. Fix Split the hot chat into buckets

    For chats marked hot, the key becomes (chat_id, bucket), where bucket is msg_id % 8.

  8. Problem A fourth shard, and % 4 moves 75% of the rows

    Shards grow to 2.6 TB, so we add shard 3.

  9. Fix Fixed slots: move only what the new shard takes

    We route in two steps: hash % 1024 picks a slot, and a small map assigns each slot to a shard.

  10. Recap Recap

    Sharding spreads one table across servers when it no longer fits on one.

Terms

Sharding
Splitting a table that no longer fits on one server across several servers (shards) by rows. The shard key decides where each row lives.
Shard key
The value that decides which shard holds a row. Pick the column your most common query filters on, and hash keys that grow over time or one shard runs hot.
Hot key
A single key with far more load than the rest, such as a huge channel or a product on sale. It overheats one shard or cache node, so it is split into buckets.