When do you reach for partitioning versus sharding to scale MySQL?

Question

IT Interview Practice · Accepted Answer

Both split a big table, but at different levels. **Partitioning** splits one table across multiple files **inside a single MySQL server**. **Sharding** splits data across **multiple independent servers**. Partitioning helps a table that's too big for one file; sharding helps a workload too big for one machine.

## Partitioning (one server)

```sql
CREATE TABLE events (
  id BIGINT, created_at DATETIME, payload JSON,
  PRIMARY KEY (id, created_at)          -- partition key must be in every unique key
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027)
);
-- Queries filtering on created_at hit ONE partition (partition pruning);
-- dropping old data is an instant ALTER TABLE ... DROP PARTITION p2024.
```

Great for time-series and easy archival, but it's still one server — no more total CPU, RAM, or write throughput.

## Sharding (many servers)

Choose a **shard key** (e.g. `user_id`) and route each row to a shard by hash or range:

```
shard = hash(user_id) % N
  user 42 -> shard 2 (server B)     user 99 -> shard 0 (server A)
```

- **Pros** — near-linear scaling of writes, storage, and connections.
- **Cons** — cross-shard JOINs and transactions are hard or impossible; a bad shard key creates hotspots; **resharding** when you add servers is painful. Pick a high-cardinality, evenly-distributed key and avoid queries that must fan out to all shards.

## Order of operations

Exhaust cheaper wins first: indexing, query tuning, read replicas, caching, then partitioning. Reach for sharding only when a single primary can no longer absorb the **write** volume — it's the biggest jump in operational complexity.

## Why it matters

Scaling decisions are largely irreversible and expensive. Knowing that partitioning is a single-server organizational tool while sharding is a distributed-systems commitment — with its shard-key and cross-shard costs — keeps teams from sharding prematurely or expecting partitioning to solve a write-throughput ceiling it can't.

Partitioning (one server)

Sharding (many servers)

Order of operations

Why it matters

Related questions