Sharding & Partitioning

Read replicas scale reads, but the single primary still takes every write. Once writes or data size outgrow one machine, we split the data itself. In simple language — partitioning is chopping a big table into smaller pieces, and sharding is putting those pieces on different servers. Sharding is partitioning that crosses machine boundaries.

Partitioning vs sharding

• Partitioning splits one table into chunks that still live on the same database server. It’s mostly for manageability and query speed — the engine can skip whole partitions it knows are irrelevant.
• Sharding spreads those chunks across multiple servers, so each server (a shard) holds only a slice of the data and handles only a slice of the traffic. This is the one that actually scales writes and storage horizontally.

The only real difference is the boundary: partitioning stays inside one box, sharding goes across boxes.

Horizontal vs vertical partitioning

Two directions to cut a table, and the names trip people up:

• Horizontal partitioning — split by rows. Users A–M in one piece, N–Z in another. Same columns, different rows. This is what “sharding” almost always means.
• Vertical partitioning — split by columns. Put hot, frequently-read columns (id, name) in one table and rarely-touched heavy ones (bio, avatar_blob) in another. Same rows, different columns.

Think of it like a spreadsheet: horizontal slices it with a knife across the rows, vertical slices it down between the columns.

The shard key

The shard key is the column we use to decide which shard a row lives on (e.g. user_id). This single choice is the most important — and most permanent — decision in the whole design. Pick well and load spreads evenly; pick badly and one shard drowns while others nap.

A good shard key has high cardinality (lots of distinct values) and even access (no single value that’s wildly more popular than the rest).

Strategies for mapping key → shard

• Range-based — shard by ranges of the key (users 1–1M on shard A, 1M–2M on shard B). Simple, and range queries stay on one shard. But it hotspots easily: the newest users often get all the action, so the newest shard burns while old ones idle.
• Hash-based — hash the shard key and route by the hash (hash(user_id) % N). Spreads load evenly and kills hotspots. The catch: range queries now hit every shard, and changing the number of shards reshuffles almost everything (consistent hashing softens this).
• Directory-based — keep a lookup table that maps each key to its shard. Maximum flexibility — we can move a specific key anywhere — but the directory becomes a component we must keep fast and highly available, or it’s a new bottleneck and single point of failure.

Hotspots & rebalancing pain

Two things make sharding genuinely hard:

• Hotspots — one shard gets disproportionate traffic. A celebrity user, a viral product, or a range key where all new writes pile onto the latest shard. The fix is a better shard key or splitting the hot shard, neither of which is fun in production.
• Rebalancing — adding a shard usually means moving data between shards while the system is live. With plain hash % N, bumping N remaps nearly every row. This is why teams reach for consistent hashing or pre-split into many virtual shards up front, so growing means moving whole virtual shards, not reshuffling everything.

Cross-shard queries

Here’s the tax we pay. Once data is split across shards, any query that isn’t scoped to one shard key becomes painful:

• JOINs across shards basically don’t work — the rows live on different servers. We denormalize or join in the app.
• Aggregations (COUNT, SUM, “top 10 overall”) must fan out to every shard and merge the results — scatter-gather, slow and chatty.
• Transactions spanning shards need distributed-transaction machinery (two-phase commit / sagas), which is a whole different level of complexity.

-- cheap: scoped to the shard key, lands on exactly one shard
SELECT * FROM orders WHERE user_id = 42;

-- expensive: no shard key, must hit EVERY shard and merge
SELECT COUNT(*) FROM orders WHERE status = 'pending';

Interview soundbite

Partitioning splits a table into pieces on one server; sharding spreads those pieces across servers to scale writes and storage. Horizontal partitioning splits rows, vertical splits columns. Everything hinges on the shard key — pick one with high cardinality and even access. Range strategy keeps ranges together but hotspots on new data; hash spreads evenly but breaks range queries and hurts on resharding; directory is flexible but adds a lookup dependency. The price of sharding is cross-shard queries — JOINs, global aggregations, and multi-shard transactions all get hard, so shard as late as we can.