Data & Storage

Sharding & Partitioning

Split data across shards so storage and write load scale beyond one primary.

Intermediate, about 75 minutes to read, 4 open questions to practise.

1Understand

The idea

Sharding (horizontal partitioning) splits one logical database into many physical shards so storage and write throughput can grow past one primary’s ceiling. Interviews probe whether you pick a partition key from access patterns, how clients route to the right shard, and what you refuse to promise (cross-shard transactions, scatter-gather on every query).

Sharding trades simplicity for scale. A bad shard key makes every request a fan-out; a hot shard from a celebrity user or a single time bucket can undo the whole layout. Strong answers say why you shard, how you reshuffle later, and what pain you accept on multi-shard reads.

Mention global secondary indexes (duplicate index per shard or lookup service) vs restricted queries, interviews prefer honest query limits over magic “query all shards” every request.

Intuition

Why shard. One primary hits limits: disk size. Write QPS, index RAM, blast radius of one giant table. Sharding splits rows across machines; each shard is still a database, but you own routing.

Partition key must match how you read and write. Shard by user_id if almost every query is “this user’s data.” Shard by tenant_id for B2B isolation. Sharding by created_at helps append-only logs but scatter-gather for “all users.”

Routing styles:

  • Hash(user_id) → shard, even spread; point lookups cheap; cross-user queries expensive.
  • Range (time), great for time-series; risk hot tail on latest range.
  • Directory service, lookup table maps key → shard; flexible resharding, extra hop and consistency for the directory.

Cross-shard pain. JOIN across shards, global COUNT, two-phase commit, avoid in interviews unless you name Saga/outbox. Prefer denormalize, aggregate async, or restrict queries to one shard key.

Hot keys. Fan-out replicas for read-heavy celebrity rows, split key into sub-keys, or cache, sharding alone does not fix skew.

Resharding story: consistent hash + background copy, or directory migration with dual-read window, never hand-wave “we will rebalance live” without steps.

Locality: co-locate data accessed together (conversation id shard) to avoid scatter-gather chat reads.

What you will be able to do

  • Pick a partition key that matches how you look data up
  • Explain routing to the right shard
  • Call out hot shards and cross-shard pain

When this comes up

  • Single DB too big
  • Write throughput ceiling
  • Partition key
  • Hot shard

2See it

Architecture sketch

Hash shard by user id

Partition key chooses the shard. Cross-shard queries are the expensive exception.

100%
ServiceOtherStore

Walkthrough

Route one write

userId → shard

1 / 3

100%

API accepts a write for user 42.

Read the walkthrough as text

Case, messages table outgrowing one Postgres.

Access: GET /threads/{userId}, POST message with sender_id; no “search all messages globally” in v1.

  1. Why shard: 2 TB and write QPS climbing; backups and vacuum hurt one node.
  2. Key: user_id (or conversation id derived from participants), every read filters by user.
  3. Routing: shard = hash(user_id) % N or consistent hash; app or sidecar picks connection string.
  4. Cross-shard: “users in two shards talking”. Pick conversation id as key if both users share one shard per conversation; else accept two-shard read rare or redesign key.
  5. Hot user: celebrity inbox, monitor shard QPS; optional read replicas on that shard, cache thread list, or dedicated shard.
  6. Resharding: directory service or consistent hash with dual-write/migrate background job, never claim zero-downtime without a story.

Range shard anti-fit here: sharding only by message_id time hurts “all messages for user U” unless secondary index fans out, say why hash-by-user wins.

  1. Global admin query: “count all users” → offline batch or aggregate table, not live scatter-gather.
  2. Shard count: start with N you can operate; doubling shards is a project.

Phrase: “Partition key = user_id; cross-shard checkout → saga, not 2PC.”

Napkin math

Shard napkin

partition key = userId (or tenantId)

shards = 8 → route = hash(key) % 8

target per shard ≤ 70% of single-box QPS

hot key → secondary split or cache

cross-shard join = last resort (fan-out)

Pick the key that keeps most queries single-shard.

3Answer it

How to answer in an interview

When “database is too big” or “writes don’t scale”:

  1. Motivation, storage, QPS, or isolation (not fashion).
  2. Partition key, from listed queries; reject bad keys.
  3. Routing, hash, range, or directory; who computes shard id.
  4. Query fit, which queries stay single-shard; which become scatter-gather or forbidden.
  5. Transactions, single-shard ACID; cross-shard = saga/outbox, not hand-waved 2PC.
  6. Hot shard / resharding, monitoring, split, consistent hash migrate.

Draw at least two shards and one example query path.

Say: “Dominant query filters shard key.” · “Hot shard metrics.” · “Resharding via directory or consistent hash migrate.”

Answer checklist

1. Why shard (storage, QPS, blast radius)? 2. Choose partition key from access patterns. 3. Routing: lookup service, hash, range. 4. Cross-shard queries and transactions, avoid or compensate. 5. Resharding / hot keys plan.

Tradeoffs & numbers

  • Hash sharding: even spread; painful global secondary indexes.
  • Range sharding: range scans local; tail hotspot on “now.”
  • Directory: operational flexibility; directory is its own SPOF/scale problem.
  • More shards: smaller blast radius; more connections and ops overhead.
  • FK across shards: usually impossible, use application-level ids and async reconciliation.
  • Shard count N: plan growth; resharding is a project, not a config flip.
  • When NOT to shard: single primary under ~1 TB and moderate write QPS, replicas + cache first.
  • Range on time: hot tail on “now” shard, mitigate with rotation/archival.

Reshard cost: doubling shard count is a multi-week project with dual-write, say when you would choose directory vs hash. Scatter-gather admin COUNT(*) → offline aggregate table, not live query. - When NOT to shard: one primary still under ~1 TB with indexed point lookups, replicas + cache first; sharding is last resort.

Kinds of questions (4)

Same pattern, different interview surfaces. Recognise which variant you are in before you answer.

  • Hash sharding by user id

    Example. Social app: every API filters `WHERE user_id = ?` for profile, DMs, and settings; no cross-user joins in v1. Defend the shard key from access patterns, call out hot shards and resharding, and say which queries stay single-shard vs scatter-gather.

    How. Application computes `shard_id = hash(user_id) % N`; connection pool per shard; point reads/writes local; explain cross-user chat may need conversation-id key to co-locate both parties.

  • Range sharding by time

    Example. Compliance audit log: inserts are append-only; investigators query `BETWEEN start AND end` for a month. Defend the shard key from access patterns, call out hot shards and resharding, and say which queries stay single-shard vs scatter-gather.

    How. Shard by `YYYY-MM`; writes hit current month shard (hot tail); archival job moves cold months to object storage; scatter-gather only for rare cross-month admin reports.

  • Directory / lookup service

    Example. Multi-tenant B2B: enterprise tenants added weekly; you reshuffle without changing app hash function daily. Defend the shard key from access patterns, call out hot shards and resharding, and say which queries stay single-shard vs scatter-gather.

    How. Registry table `tenant_id → shard_id` cached in app; dual-read during migration; directory itself is HA SQL or consensus; resharding background job copies tenant rows then flips pointer.

  • Hot-key fan-out

    Example. Celebrity user id maps to one shard doing 80% of read QPS while other shards idle. Defend the shard key from access patterns, call out hot shards and resharding, and say which queries stay single-shard vs scatter-gather.

    How. Monitor per-shard QPS; read replicas on hot shard; Redis cache for profile; optional dedicated shard or sub-key split; do not only “add shards” without fixing skewed key.

Common mistakes

Failure modes the AI coach watches for when you practise open questions.

Hot shard

Avoid. Shard by created_at day, today is on fire

Prefer. Shard by userId, load spreads

Weak sketch

Bad

100%

Stronger sketch

Good

100%
  • Bad shard key

    Why it happens. Every query becomes scatter-gather across all shards.

    How it fails. Shard by `message_id` but every API filters by `user_id`.

    Fix. Pick key from dominant access pattern; co-locate data that is read together.

  • Ignores hot shard

    Why it happens. Skewed keys overload one shard while others sit idle.

    How it fails. Celebrity user on same hash shard as everyone else with no cache or split.

    Fix. Monitor per-shard QPS; fan-out reads, dedicated shard, or sub-key splitting for hot ids.

  • Hand-waves cross-shard TX

    Why it happens. Distributed ACID across shards is expensive and rarely needed in interviews.

    How it fails. “Two-phase commit across 10 shards for checkout” with no saga or outbox.

    Fix. Keep transactions single-shard; use sagas, idempotent steps, or redesign partition key.

4Practise

Concept check

A short quiz on this lesson before open questions. Optional, and it does not gate practice.

Open questions

Ready to practise?

Write an answer, get AI coaching, then revise. You are not building a real system.

Practise with an open question

All systems lessons