Data partitioning and sharding

When you need it

A single database node has a ceiling on storage, write throughput, and memory. Partitioning (sharding) splits the data into pieces spread across many nodes, so the dataset and its write load scale horizontally. You reach for it when an estimate shows the data won’t fit on one box, or one box can’t absorb the writes.

Two directions to cut

• Vertical partitioning — split by feature/column: put user profiles in one store, photos in another, messages in a third. Natural early on (one DB per service), but a single table that’s too big still won’t fit.
• Horizontal partitioning (sharding) — split rows of the same table across nodes: users A–M on shard 1, N–Z on shard 2. This is what scales a single huge table, and what “sharding” usually means.

How to choose the shard key

The partition key decides which shard a row lives on. Get it right and load spreads evenly; get it wrong and you’ve built a bottleneck. Common schemes:

• Range-based — partition by key ranges (e.g. dates, A–M / N–Z). Great for range scans, but prone to hotspots (everyone writes “today’s” range).
• Hash-based — store row at hash(key) % N. Spreads load evenly, but breaks range queries and reshuffles almost everything when N changes.
• Consistent hashing — hash-based, but adding/removing a node moves only a small fraction of keys. The standard choice for elastic clusters (its own lesson later).
• Directory-based — a lookup service maps key → shard. Flexible (you can move individual keys), but the directory becomes a component you must scale and keep available.

Pick a key with high cardinality and even access — usually a user id or object id. Avoid keys that concentrate traffic (a timestamp, a “status” flag).

The problems sharding hands you

• Hotspots — one shard gets disproportionate traffic (a celebrity’s data, the current day). Mitigate by choosing a better key or splitting the hot shard.
• Cross-shard queries — anything spanning shards (a join, a global aggregate, “search all users”) must scatter-gather across nodes and merge. Slow and complex; design your access patterns to stay within one shard when you can.
• Cross-shard transactions — atomic writes across shards need distributed transactions or sagas, which most NoSQL stores avoid entirely. Often you restructure so related data shares a shard.
• Rebalancing — adding capacity means moving data. Consistent hashing minimizes the movement; naive modulo maximizes it.

The interview move

Don’t shard reflexively. Say: “A single primary with read replicas handles this read-heavy load; I’d only shard the writes once we pass roughly X writes/sec or Y TB — and I’d shard by user id so a user’s data stays on one shard.” That ties the decision to a number and shows you know the cost.