Sharding in System Design

Sharding splits a dataset across independent databases so no single machine holds the writes, the disk, or the connection pool. Each shard is its own Postgres: CPU, memory, storage, connections. Together they are the dataset. Vertical scale is the first move. Sharding is what you reach for after the machine is the ceiling.

This note follows Evan's walkthrough. The schema is Data Modeling in System Design. Partitioning inside one Postgres is Core SQL Concepts. The denormalized global copy lives in Caching in System Design. Compensating steps are Message Queues in System Design. This note is the split.

Pattern Map

1. Why sharding exists

A large RDS Postgres is enough for a long time. ~70 TB. ~10k writes per second. Upgrade first. Shard after the bigger box is still angry.

One Postgres  →  bigger Postgres  →  shards
   (default)        (vertical)       (horizontal)

• Traffic grows. Reads grow. Writes grow. The first instinct is a larger instance — more CPU, more disk, 140 TB, tens of thousands of writes. Most companies never leave this box.
• The ceiling is real. Storage fills. Write throughput saturates. Backups take forever. Queries slow because the working set no longer fits. A bigger machine does not exist.
• Sharding is then the only move that adds capacity: split rows across machines. Storage and throughput scale by adding a fourth shard, a fifth, a sixth.
• The cost is operational. You now choose a key, route queries, live with hot shards, and rebalance. That complexity is why you do the math before you draw the boxes.

Failure: sharding a 2.5 TB dataset because "we always shard," while a single Postgres would have held it. The interview wanted the numbers, not the split.

2. Partitioning vs sharding

Partitioning organizes a table inside one database. Sharding is multiple databases. Same word in casual talk. Different failure domain.

• A 500M-row orders table on one node is a partition problem: huge indexes, autovacuum on the whole table, a date query that scans years. Split by month. The primary is still one machine.
• Horizontal partition: same columns, fewer rows per piece. Vertical partition: same rows, fewer columns per piece. Neither adds a second primary.
• A shard has its own CPU, memory, storage, and pool. No single box holds the dataset. That is how writes and storage scale past one machine.
• The statement model, and when a partition key actually prunes, is Core SQL Concepts. The schema that the key has to serve is Data Modeling in System Design.

Failure: calling table partitions "sharding," then discovering unique constraints, joins, and transactions still live on one WAL — or treating a second RDS instance as a partition the planner can see.

3. Shard key

Two decisions. What you group by — the shard key. How those groups land on machines — the next section. In an interview, name the column and why. The key is usually permanent.

A good key has three properties:

• High cardinality. Many distinct values, so the data can actually spread. A boolean is two groups. You have capped yourself at two shards.
• Even distribution. Values should not pile onto one machine. user_id usually spreads. Country, if 90% of users are in one country, does not.
• Query alignment. The hot path should hit one shard. GET /users/{id}/posts wants user_id. A key that does not match the APIs turns every common read into scatter-gather.

• Collocate related rows. Comments on the same shard as their post. A detail page is one database, not a join across machines.
• Tenant-centric products often shard by organization_id. The isolation story in this stack is still RLS on the row. Building a Multi-Tenant Backend with Hono, Better Auth, Drizzle, and Postgres RLS.

Failure: sharding by created_at so all writes hit today's shard, or splitting posts and comments onto different keys so every detail page is a cross-shard join.

4. How to place groups

The key groups rows. The strategy places groups. Three options. The interview default is hash plus consistent hashing.

Range

Shard 1 → user_id 0 – 10M
Shard 2 → user_id 10M – 20M
Shard 3 → user_id 20M – 30M

• Simple. A range scan that stays inside one interval hits one shard.
• Early on, only shard 1 has users. Later, new ids are monotonic — every new user, every write, lands on the highest range. That shard takes the heat.
• Range works when different tenants naturally query different ranges. It is not the production default for a growing user_id.

Hash

shard = hash(user_id) % N

user 42  → hash(42)  % 3 = shard 1
user 99  → hash(99)  % 3 = shard 2
user 123 → hash(123) % 3 = shard 0

• The hash scrambles the input. New users spread evenly. That is the win.
• N changing is the loss. % 3 to % 4 remaps almost every key. Most of the dataset moves. Operationally a nightmare.
• Consistent hashing puts keys and shards on a ring. Walk clockwise to the next shard. Add a node, and only a fraction of keys move — the neighbors, not the cluster. Virtual nodes keep the ring from clumping. This is the industry default. In an interview, "shard by user_id" already implies it unless you are mid-level and they ask how.

Directory

user_to_shard
---------------
15  → shard 1
87  → shard 4
204 → shard 2

• A lookup says where the key lives. Move Messi to his own shard by updating a row. Rebalance without rehashing.
• Every request is now two hops: directory, then shard. The directory is a single point of failure. If it is down, healthy shards are unreachable — you do not know where the data is.
• Flexibility you can afford in production for a handful of hot keys. Almost never the interview answer. It invites a derail about the lookup's HA.

Failure: hash % N with no plan for N+1, or leading with a directory so the rest of the hour is the lookup service.

5. Hotspots

A good key still has outliers. Hashing spreads keys, not traffic. One key can be most of the load.

• Celebrity problem. Shard by user_id. Messi lands on shard 1. Every profile view, like, comment, and DM hits that shard. The others sit cold. The cluster looks fine. One primary is on fire.
• Time-range is the other shape: all new writes go to the newest shard. Same incident, different cause.
• Detect it from shard metrics — latency, CPU, RPS — not from "the keyspace is even."

Two responses:

• Compound key. Hash user_id + n, or user_id + date, so one celebrity's posts spread across shards. Reads that used to be one shard now fan out. You bought write spread with a smaller scatter-gather on that user.
• Dedicated celebrity shard. Detect the hot keys. Move them onto their own hardware. A small directory overlay: if celebrity, that shard; else hash. Most systems never need this. A social graph with Messi does.

Caching a hot read is the other half. Redis in front of the profile does not make the write path infinite, but it stops the read storm from also being a database storm. Caching in System Design.

Failure: hashing user_id and calling the cluster balanced because each shard has the same number of users, while one of those users is the product.

6. Cross-shard work

Once rows live on many machines, any query that needs more than one shard is a fan-out: query N, wait, merge. The hot path should not be that.

GET /users/123            → one shard
GET /posts/trending       → all shards, then merge

• Alignment is the first defense. If most queries are user-scoped, shard by user_id. Global top-10 is then the exception, not the feed.
• You cannot eliminate global queries. Trending, leaderboards, "how many users." The first expensive scatter-gather caches. Five minutes stale is the product for a trending page. Precompute with a job so the request never fans out. Caching in System Design.
• Denormalize so related facts live together. Copy the fields the hot read needs onto the shard that already has the user. Writes go to two places. Reads stay on one. The schema trade is Data Modeling in System Design.
• Rare admin totals can hit every shard. A common user-facing path that always scatter-gathers means the key is wrong.

Failure: "we'll query all shards and aggregate" as the plan for the homepage. That is a signal to change the key, cache the merge, or precompute — not to accept N round-trips as the product.

7. Consistency

One Postgres makes a transfer one transaction. Two shards make it two databases that do not know each other. ACID does not span them.

-- one database
BEGIN;
  UPDATE accounts SET balance = balance - 5 WHERE id = bob;
  UPDATE accounts SET balance = balance + 5 WHERE id = alice;
COMMIT;

• Bob on shard 3, Alice on shard 1: deduct can succeed and credit can fail. The money is gone. The inverse is a double credit. There is no ROLLBACK that covers both boxes.
• Two-phase commit asks every shard to prepare, then commit. Correct, slow, fragile. A coordinator or a shard dying mid-flight leaves locks you cannot easily unwind. Production avoids it.
• The golden rule: do not have the distributed transaction. Keep a user's balance, history, and profile on that user's shard. Collocation is the consistency strategy. The key was the design.
• When two users on two shards must move money, a saga: deduct Bob, credit Alice; if credit fails, refund Bob. Compensation is not undo. It is a new, durable, retryable write with its own audit. Message Queues in System Design.
• Follower counts and denormalized tallies can be eventually consistent. A few seconds of disagreement is cheaper than 2PC. Money is not a tally.

Failure: 2PC as the default because the textbook named it, or treating a saga compensation as a rollback — the money already moved; the refund is a second business event.

8. What to say

Sharding shows up in the deep dive, when you are satisfying a scaling non-functional. Do the math first. A well-tuned single Postgres is a long way.

• Storage. 500M users × 5 KB = 2.5 TB. One instance holds that. Say so. Shard if you 10× or 100×.
• Write throughput. 50k writes/s at peak is past a comfortable single primary. Now you have a reason.
• Read throughput. Replicas take you far. 100M DAU with several queries each may still need the read load split. Prove it.

If the numbers require the split:

1. Key from access patterns. Social app, user-centric reads — posts, followers, likes scoped to one user — shard by user_id.
2. Distribution. Hash-based with consistent hashing. Even spread. Adding a shard moves a fraction, not the cluster. Mid-level: say it. Senior: it is the default.
3. Trade-off. Global queries get expensive. Trending is cache or a precompute job, not scatter-gather on every request.
4. Growth. Start with enough shards to grow. Consistent hashing is how you add more without moving everything.

Failure: drawing shards before the bottleneck, or naming a key with no access pattern, or skipping the global-query cost so the interviewer has to pull it.

Recap Q&A