LabHub

Blog

DB Sharding & Partitioning Complete Guide 2025: Horizontal Scaling, Range/Hash/Directory, Resharding, Vitess

2026-04-15 · 15 min read · #database#sharding#partitioning#horizontal-scaling#vitess#citus#cockroachdb#distributed-database#2026-04#english

한국어English日本語

TL;DR

1. What is Sharding?

1.1 Limits of a Single DB

[Single DB]
  ├─ CPU: up to one machine's worth
  ├─ RAM: 256GB? 512GB?
  ├─ Disk: 30TB
  └─ Network: 25 Gbps

→ Eventually hits the limit

Vertical Scaling:

Horizontal Scaling:

1.2 Can Read Replicas Solve It?

[Primary] (writes)
   ↓ replication
[Replica 1] [Replica 2] [Replica 3] (reads)

Helps: distributes read traffic.

Limitation: writes still go through a single Primary. If writes are the bottleneck, this does not help.

→ Sharding is required.

1.3 Definition of Sharding

Sharding = distributing data across multiple independent DBs (shards).

[users table]
  ↓ shard by user_id
[Shard 1: user_id 1-1M]
[Shard 2: user_id 1M-2M]
[Shard 3: user_id 2M-3M]
...

Each shard is a completely independent DB with its own CPU, RAM, and disk.

1.4 Effects of Sharding

Single DB100 Shards
Data1 TB100 TB
Write TPS10,0001,000,000
Cost$10K/month$50K/month
ComplexityLowVery High

100x performance + 5x cost = good ROI. However, complexity is a serious problem.

2. Shard Key Selection — the Most Important Decision

2.1 Conditions for a Good Shard Key

  1. Even distribution — data spread evenly across all shards
  2. Immutable — rarely changes once set
  3. Matches query patterns — data queried together lives on the same shard
  4. High cardinality — enough unique values
  5. Not monotonically increasing — auto-increment IDs risk hot spots

2.2 Common Shard Key Candidates

user_id:

created_at:

hash(id):

country:

organization_id:

2.3 Slack's Case

Slack's shard key = workspace_id.

Why:

Downside: a massive enterprise workspace overwhelms a single shard → the "Whale shard" problem.

Solution: huge workspaces get their own dedicated shard.

3. Sharding Strategies

3.1 Range Sharding

Partition data by key range.

Shard 1: id 1 ~ 1,000,000
Shard 2: id 1,000,001 ~ 2,000,000
Shard 3: id 2,000,001 ~ 3,000,000

Pros:

Cons:

Use cases: time-series data, BigTable, HBase

3.2 Hash Sharding

Partition by the hash of the shard key.

shard_id = hash(user_id) % num_shards

Pros:

Cons:

Solution: Consistent Hashing — only some data moves when shards are added or removed.

3.3 Consistent Hashing

[Circular hash space]
        Shard A
       /
  Shard D
       \
        Shard B
       /
  Shard C

Each shard and data item is mapped to the same hash space. Data goes to the nearest shard clockwise.

When adding a shard: only the affected section is redistributed (1/N of the total).

Virtual Nodes: represent each shard with multiple virtual nodes → more even distribution.

Used by: Cassandra, DynamoDB, Riak, Memcached.

3.4 Directory-Based Sharding

Use a lookup table to explicitly map which data lives on which shard.

[Lookup]
user_1 → Shard A
user_2 → Shard B
user_3 → Shard A
...

Pros:

Cons:

Used by: Vitess (a variation of this approach)

3.5 Geographic Sharding

Shard by location:

Pros:

Cons:

3.6 Comparison Table

StrategyEvennessRange QueriesAdding ShardsQuery Pattern
RangeLowYesEasyRange
HashHighNoHardExact
Consistent HashHighNoEasyExact
DirectoryHighPartialEasyFlexible
GeographicLowPartialModerateRegional

4. The Hot Spot Problem

4.1 What is a Hot Spot?

Traffic or data concentrating on a specific shard.

Causes:

4.2 Impact

[Shard 1: 10% traffic]
[Shard 2: 5% traffic]
[Shard 3: 80% traffic] ← Hot spot! Dying
[Shard 4: 5% traffic]

The entire system is bottlenecked by the weakest shard.

4.3 Detection

-- Data volume per shard
SELECT shard_id, COUNT(*) as row_count
FROM users
GROUP BY shard_id;

-- Query count per shard (application metrics)

Warning signs:

4.4 Solutions

1. Change the shard key

2. Composite Key

shard_key = hash(user_id + timestamp_bucket)

Combines time-based + user-based factors.

3. Sub-sharding

4. Caching

5. Read Replicas

4.5 Example: Twitter's Celebrity Problem

Problem: a celebrity user (say, Elon Musk) tweets → must update timelines of hundreds of millions of followers.

Solution:

5. Difficulty of Distributed Queries

5.1 Single-Shard Queries

SELECT * FROM users WHERE user_id = 12345;

→ determine shard by user_id → query only that shard. Very fast.

5.2 Cross-Shard Queries

SELECT COUNT(*) FROM users WHERE country = 'KR';

→ query all shards → aggregate results. Slow + heavy load.

5.3 Difficulty of JOINs

SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.user_id
WHERE u.country = 'KR';

What if users and orders live on different shards? You need a distributed JOIN.

Strategies:

  1. Co-location: keep users and orders with the same user_id on the same shard
  2. Denormalization: copy user info into orders
  3. Distributed query engine: Vitess, Citus handle it automatically
  4. CQRS: use a separate DW (Snowflake) for queries

5.4 Distributed Transactions

def transfer(from_user, to_user, amount):
    # The two users may live on different shards
    db.update(f"... WHERE user_id={from_user}", -amount)
    db.update(f"... WHERE user_id={to_user}", +amount)

Problem: first update succeeds + second fails → money disappears.

Solutions:

1. 2PC (Two-Phase Commit):

2. Saga Pattern:

3. Keep in the same shard:

6. Resharding — the Scariest Operation

6.1 Why is Resharding Hard?

6.2 Resharding Scenarios

1. Adding shards: 4 → 8 shards 2. Splitting a shard: dividing a huge shard in two 3. Merging shards: combining small shards 4. Changing the shard key: the hardest of all

6.3 Resharding Patterns

1. Bulk Copy + Switchover:

  1. Copy data to new shards
  2. Short maintenance window
  3. Switch traffic
  4. Drop old shards

Downside: downtime.

2. CDC + Dual Write:

  1. Sync old → new shards via CDC
  2. Application writes to both old + new (dual write)
  3. Verify data parity
  4. Switch reads to new
  5. Retire old shards

Zero downtime, complex.

3. Online Migration:

6.4 Case: Slack's Sharding Evolution

Lesson: sharding is never one and done. It is a continuous evolution.

7. Vitess — a Distributed Layer on Top of MySQL

7.1 What is Vitess?

A MySQL sharding solution built by YouTube. CNCF graduated.

[Client]
   ↓
[VTGate] (query router)
   ↓
[VTTablet] [VTTablet] [VTTablet]
   ↓        ↓          ↓
[MySQL]  [MySQL]    [MySQL]

Pros:

7.2 Companies Using It

7.3 VSchema Example

{
  "sharded": true,
  "vindexes": {
    "hash": {
      "type": "hash"
    }
  },
  "tables": {
    "users": {
      "column_vindexes": [
        {
          "column": "user_id",
          "name": "hash"
        }
      ]
    }
  }
}

→ VTGate automatically shards based on user_id.

8. Other Distributed DB Options

8.1 Citus (PostgreSQL)

A PostgreSQL extension. Supports distributed queries and distributed transactions.

SELECT create_distributed_table('events', 'user_id');

Pros: PostgreSQL compatible, keep SQL as is. Cons: inherits PostgreSQL limitations (less mature than Vitess).

8.2 CockroachDB

Designed from scratch as a distributed SQL DB.

Pros:

Cons:

Used by: DoorDash, Bose, Comcast.

8.3 YugabyteDB

Similar to CockroachDB. Both PostgreSQL compatible and Cassandra compatible.

8.4 TiDB

MySQL compatible + automatic distribution. Popular in China.

8.5 Comparison Table

VitessCitusCockroachDBYugabyteDBTiDB
BaseMySQLPostgreSQLCustomCustomCustom
CompatibilityMySQLPostgreSQLPostgreSQLPG + CQLMySQL
Distributed TransactionsLimitedYesYesYesYes
Automatic ShardingManual keyManual keyAutomaticAutomaticAutomatic
Operational DifficultyModerateLowModerateModerateModerate
ValidationYouTube, SlackManyDoorDashManyChinese giants

9. In Practice — Designing E-commerce Sharding

9.1 Scenario

9.2 Analysis

Tables:

9.3 Sharding Decisions

1. users:

2. orders:

3. products:

4. reviews:

9.4 Query Patterns

-- User orders (same shard)
SELECT * FROM orders WHERE user_id = 12345;
-- Fast

-- User info + orders (same shard, JOIN OK)
SELECT u.*, o.*
FROM users u JOIN orders o ON o.user_id = u.user_id
WHERE u.user_id = 12345;
-- Fast

-- Product reviews (different shard)
SELECT * FROM reviews WHERE product_id = 67890;
-- Fast

-- All reviews written by a user (cross-shard)
SELECT * FROM reviews WHERE user_id = 12345;
-- Slow — scans every reviews shard

-- Solution: denormalize. Store review summaries on the users shard too.

9.5 Infrastructure

[Application]
    ↓
[ProxySQL / Vitess]
    ↓
[16 user shards]
[16 order shards (co-located)]
[1 products DB + 5 read replicas]
[8 reviews shards]

9.6 Monitoring

10. Sharding Pitfalls and Lessons

10.1 Sharding Too Early

Wrong decision: sharding at 1000 QPS of traffic.

Reality:

→ Try every other optimization first before sharding.

10.2 Wrong Shard Key

GitHub's first sharding attempt failure case:

Lesson: the shard key is extremely hard to change. Be careful up front.

10.3 Avoiding Distributed Transactions

Distributed transactions really are hard. Whenever possible, keep transactions within a single shard.

While designing: shape the data model so that every write transaction fits in a single shard.

10.4 Operational Complexity

A sharded system means:

→ Automation is essential. Have enough operations staff.

10.5 Is NewSQL the Answer?

NewSQL systems like CockroachDB and YugabyteDB offer automatic sharding + distributed transactions. None of the complexity of manual sharding.

When to go NewSQL:

When to go manual sharding:

Quiz

1. Why can sharding not be replaced by read replicas?

Answer: read replicas only distribute reads. Writes still go through a single Primary. A system where writes are the bottleneck (e.g. 1 billion INSERTs per day) cannot be fixed with read replicas. Only sharding can spread writes across multiple machines. That said, sharding brings enormous complexity, so first check whether read replicas + caching + index optimization can solve the problem.

2. What are the pros and cons of hash sharding?

Answer: Pros: even distribution (with a good hash). Safe even with monotonically increasing keys. Cons: no range queries (WHERE id BETWEEN ...). Adding a shard redistributes nearly all data (because % num_shards changes). Solution: Consistent Hashing — only a fraction (1/N) of the data moves on add/remove. Used by Cassandra, DynamoDB, and Riak. Virtual nodes give more even distribution.

3. How do you solve the hot spot problem?

Answer: (1) Change the shard key — best, but requires resharding (hard), (2) Composite key — hash(user_id + timestamp_bucket) to spread across time/users, (3) Sub-sharding — split hot shards finer ("whale shard"), (4) Caching — move hot data to Redis, (5) Read replicas — distribute read load. The most common hot spots: popular users, popular products, monotonically increasing keys. Always review hot spot scenarios when choosing a shard key.

4. Why did Vitess become the standard for MySQL distribution?

Answer: (1) Born at YouTube — proven at massive scale, (2) MySQL compatible — existing code/tools keep working, (3) Automatic sharding — key-based routing, (4) VReplication — zero-downtime migrations and resharding, (5) CNCF graduated — cloud-native standard, (6) Proven users — Slack, GitHub, Square. Downside: distributed transactions are relatively weak (the Saga pattern is recommended). PostgreSQL users use Citus for a similar role.

5. When should you use NewSQL (CockroachDB) vs. manual sharding?

Answer: Pick NewSQL (CockroachDB, YugabyteDB) when: new project, strong consistency required (distributed ACID), limited operations staff, PostgreSQL compatibility is acceptable. Pick manual sharding (Vitess + MySQL) when: existing MySQL system, extreme performance needed, worried about NewSQL maturity, want more control. Reality: new systems increasingly go NewSQL; existing huge systems stick with Vitess. Both are valid — decide based on trade-offs.

References

Read the original: https://www.youngju.dev/blog/culture/2026-04-15-database-sharding-partitioning-strategies-guide-2025.en

Comments

No comments yet.

Sign in to leave a comment