Technologies referenced in this case study: PostgreSQL · DynamoDB · Cassandra · ZooKeeper & etcd · Apache Kafka

Related: Sharding & Partitioning · Consistent Hashing · Scaling Writes · Database Selection · Replicated Data Store

How to Use This Case Study#

Organized for interview use first, reference second. Read front-to-back once. Return to individual sections for targeted review.

Without sharding (single PostgreSQL primary, 64 vCPU, 4 TB):
t=0:       Write QPS crosses 18K sustained; WAL generation at 250 MB/s
t=+2wk:    Replica lag spikes to 40s during nightly batch; reads go stale
t=+1mo:    Autovacuum can no longer keep up on the orders table (1.8B rows)
t=+6wk:    Largest instance class already in use — nowhere to scale up
t=+2mo:    Black Friday: primary CPU 100%, p99 write latency 1.2s, checkout errors
t=+2mo+4h: Incident closes with "we need to shard" — now as an emergency project

With sharding (planned 9 months earlier, 32 logical shards on 8 hosts):
t=0:       Same 18K write QPS spread ~2.3K per host
t=+2mo:    Black Friday: hottest host at 55% CPU; p99 write 14ms
t=+2mo:    Shard 17 runs hot (large merchant) — moved to its own host in 40 min
t=+1yr:    Hosts doubled to 16 by moving logical shards — zero application changes

Executive Summary

If you only read one section, read this. Everything in the case study flows from the contrast below.

What This Interview Actually Tests#

Sharding is not a partitioning-algorithm question. Everyone can draw consistent hashing.

It is an access-pattern and data-movement ownership question that tests:

• Whether you derive the shard key from the top 3 queries, not from the schema
• Whether you know which queries you are about to make expensive (or impossible)
• Whether you have a plan for moving data while it is being written
• Whether you can name who owns the router, the directory, the resharding runbook and the cross-shard invariants

The key insight: The shard key decides which queries stay single-shard for the next five years. Staff engineers pick it by listing the queries that must never fan out, then design resharding before they need it — because the first resharding is always done under duress.

The L5 vs L6 Contrast — Start Here#

The Staff Positions#

The Three Intents#

Three intents drive every design decision. Each leads to a different architecture.

🎯 Staff Move: "I'll assume OLTP write scaling for a marketplace, because that's where shard-key choice and cross-shard transactions collide. If this were multi-tenant SaaS I'd key on tenant_id with a directory; if it were time-series I'd key on time buckets — and those are three different designs with different failure modes."

The Five Fault Lines#

In the Wild: Real Production Systems#

Why this section belongs here: Citing specific production systems demonstrates you've studied operational reality, not textbook designs.

YouTube / Vitess — Sharding Proxy over MySQL#

Vitess was built at YouTube to scale MySQL horizontally and is now a CNCF project used by Slack, GitHub and others. Applications talk to VTGate, a stateless proxy that parses SQL and routes by a vindex (the sharding function) defined in a VSchema. Resharding is performed online by the VReplication-based workflows (Reshard, MoveTables): copy, catch up via binlog streaming, verify with VDiff, then switch reads and writes.

Staff insight: Vitess productizes exactly the steps an interviewer wants to hear — copy, catch-up, verify, cutover, reverse replication for rollback. Name those phases and you've shown you understand resharding as a workflow, not an algorithm.

Instagram — Logical Shards in PostgreSQL Schemas#

Instagram's well-known engineering post describes mapping thousands of logical shards to a much smaller number of physical PostgreSQL servers, with each logical shard as a PostgreSQL schema. IDs are generated in-database and embed the logical shard ID alongside a timestamp, so any ID can be routed without a lookup.

Staff insight: Encoding the logical shard in the ID makes routing stateless and lets you move logical shards between hosts without rewriting IDs. That's the "logical ≫ physical" position in production.

Notion — Sharding PostgreSQL by Workspace#

Notion publicly described sharding its PostgreSQL fleet into 480 logical shards across 32 physical databases, partitioned by workspace ID so a workspace's blocks stay together, and later re-sharding onto more hosts using a dual-write/backfill/verify approach.

Staff insight: Workspace is the transactional and access boundary, so it's the shard key. 480 was chosen because it has many divisors — letting the logical shards spread evenly across many physical host counts. Numbers like that are what an L6 answer sounds like.

What Interviewers Probe#

System Architecture Overview#

Reading the diagram: The router is on every request but holds no durable state — it reads a cached, versioned shard map. The directory in etcd is on the control plane and can be down for minutes without taking out the data plane, as long as the cached map is valid. Everything that is not keyed by the shard key — lookups by buyer, analytics, search — is fed asynchronously by CDC and is explicitly allowed to lag.

Quick-Reference: The 30-Second Cheat Sheet#

Key Numbers Worth Memorizing#

Interview Walkthrough

The most common mistake: Candidates spend 20 minutes on hash functions, ring diagrams and replica counts, then get asked "how do you add capacity?" with 8 minutes left. Compress the basics to ~10 minutes; the level is decided in the shard-key defense, the resharding workflow and cross-shard correctness.

Phase 1: Requirements & Framing (2–3 min)#

State the functional scope in one sentence:

"We're storing orders for a marketplace — create, update status, read by buyer, read by merchant — and the single primary is running out of write headroom."

Then spend your time on the non-functional constraints that pick the design:

"Three numbers matter: write QPS and growth rate, dataset size and growth, and which queries must stay single-shard with strong consistency. I'll assume 40K writes/s peak growing 2× a year, 12 TB today, and that checkout — creating an order and decrementing a merchant's inventory — must be transactional."

Commit to an intent:

"I'll treat this as OLTP write scaling. Multi-tenant isolation and time-series retention are different designs; I'll point out where they'd diverge."

🎯 Staff Move: Ask for the top three queries by business value, not by volume. The shard key serves the queries that must be fast and correct; everything else can be served by an async index or the warehouse.

Phase 2: Core Entities & API (1–2 min)#

• Merchant (merchant_id) — owns inventory and orders; the transactional boundary for checkout
• Order (order_id, merchant_id, buyer_id, status, total, created_at)
• InventoryItem (merchant_id, sku, qty)
• ShardMap (logical_shard → physical_cluster, overrides: merchant_id → cluster, version)

CreateOrder(merchant_id, buyer_id, items[])     → order_id        # single-shard txn
GetOrder(order_id)                              → Order           # routable via embedded shard id
ListOrdersByMerchant(merchant_id, since, limit) → Order[]         # single-shard
ListOrdersByBuyer(buyer_id, limit)              → Order[]         # async GSI, may lag ~1–2 s

"Order IDs embed the logical shard — order_id = [41 bits time][12 bits logical shard][10 bits sequence] — so GetOrder never needs a lookup."

Phase 3: High-Level Architecture (≤5 min)#

Walk the flow in 60 seconds:

1. Every query carries merchant_id (or an order_id that encodes the logical shard).
2. The router hashes to one of 4,096 logical shards and looks up the physical cluster in a cached map.
3. Checkout — insert order + decrement inventory — is a local transaction on one shard.
4. Each shard's WAL/binlog is streamed via CDC into Kafka, which feeds the buyer-lookup index and the warehouse.
5. The directory is control plane: routers cache the map and keep serving if etcd is briefly unavailable.

🎯 Staff Move: "This is the design that works on day one. What decides whether it works in year three is how we reshard, what happens to the queries I just made expensive, and how we handle the merchant who becomes 20% of traffic." You're at ~10 minutes.

Phase 4: Transition to Depth (1 min)#

"Three areas worth going deep on: defending the shard key against the queries it hurts, how we reshard online without losing writes, and cross-shard correctness for the few operations that span merchants. I'd start with resharding — it's the one-way door that most designs get wrong. Which would you like?"

Phase 5: Deep Dives (25–30 min)#

Deep dive 1: Shard key defense (5–7 min)

Enumerate what the key costs:

"Four of six hot queries stay single-shard. The two that don't tolerate seconds of lag. That's why merchant_id wins over buyer_id: keying by buyer would make checkout cross-shard, because inventory belongs to the merchant."

Deep dive 2: Online resharding (8–10 min) — covered in 3.3. Name the phases: copy → catch-up → verify → freeze → flip → reverse-replicate → cleanup. State the write-freeze budget (1–5 s) and the rollback (flip the map back; reverse replication has kept the old shard current).

Deep dive 3: Cross-shard correctness (5–7 min) — a buyer uses store credit issued by the platform (different shard). "Outbox on the order shard, saga step debits credit idempotently keyed by order_id, compensation on failure. The invariant 'credit ledger balances' is eventually consistent for up to ~5 s; finance signs off because reconciliation runs hourly."

Deep dive 4: Hot shards (3–5 min) — directory override for whales; split a logical shard only if it contains multiple heavy keys; true single hot rows need write sharding (counter splitting) in the schema.

Phase 6: Wrap-Up (2–3 min)#

"To summarize: merchant_id into 4,096 logical shards over 16 clusters, directory-backed placement with whale overrides, async GSIs for buyer queries, online resharding as a tested workflow, and sagas for the ~2% of cross-merchant operations. What I'd build next: automated shard-balancing based on shard.cpu and size, and a quarterly resharding game day. What I would not build: global secondary indexes with synchronous writes, or a general-purpose cross-shard query engine — that's what the warehouse is for."

Common Timing Mistakes#

1. The Staff Lens#

1.1 Why This Problem Exists in Staff Interviews#

Sharding is where local design decisions become organizational constraints. Once 20 services join on merchant_id and 3 years of IDs encode logical shard numbers, changing the key is a multi-quarter migration. Interviewers want to see that you know you're choosing a constraint for teams who haven't been hired yet — and that you design the escape hatch (logical shards, directory overrides, tested online migration) before you need it.

1.2 The L5 vs L6 Contrast — Visual#

1.3 The Staff Question That Cuts Through Everything#

"Which entity must be updated atomically, and which queries are we willing to make eventually consistent?"

Every sharding decision follows from the answer. The transactional boundary becomes the shard key. The queries on other dimensions become async indexes with an explicit lag budget. If the answer is "everything must be atomic with everything," you're not designing a sharded database — you're buying a distributed SQL engine and paying its latency.

2. Problem Framing & Intent#

2.1 The Three Intents — Explained#

Write-throughput scaling (OLTP). The primary is the ceiling. The key is the entity at the center of the write path (user, merchant, account). Correctness bar: ACID within the entity. Everything else — search, reporting, cross-entity views — moves to derived stores. Typical scale: 20K–500K writes/s across 16–256 clusters.

Multi-tenant isolation (SaaS). The key is tenant_id with a directory, because tenant sizes follow a power law: in a typical B2B SaaS, the top 1% of tenants hold 30–50% of data. Hash placement for the long tail; explicit placement (dedicated clusters, specific regions for residency) for the head. The failure mode is a single tenant outgrowing a shard — which then requires intra-tenant sharding, a second key.

Time-series / append-heavy. Range on time buckets (daily/hourly) with hash within the bucket. Retention becomes DROP PARTITION, not DELETE — orders of magnitude cheaper. The failure mode is the hot "now" bucket; the fix is hashing within it ((day, hash(device_id) % 64)).

🎯 Staff Move: "These three have different keys, different placement and different failure modes. If the product has both OLTP orders and an event log, I'd shard them separately — one sharding scheme per access pattern, not one per company."

2.2 When NOT to Use Database Sharding#

"The cheapest shard is the one you don't create. I'd first move the 60% of data that's cold order history to object storage, and see if that buys two years."

2.3 What the Interviewer Leaves Underspecified#

2.4 Precise Terminology#

3. The Five Fault Lines#

Each fault line: the options, who pays, the Staff default, and when to deviate.

3.1 Fault Line 1: Shard Key — Locality vs Distribution#

The tension: A key that co-locates related data (all of a merchant's orders) makes transactions and range queries single-shard but concentrates load. A key that spreads evenly (hash of order_id) balances load but makes every entity-scoped query a fan-out.

Staff default: Key on the transactional boundary entity. Accept and name the fan-out queries; serve them from CDC-fed indexes.

When to deviate: If the dominant workload is analytical range scans over time (logs, metrics), key on time buckets. If no single entity bounds transactions, stop hand-sharding.

🧭 Principal Move: "The shard key is an org-wide contract, not a table property. If orders, payments and fulfillment all shard by merchant_id, a merchant's data co-locates across services and cell-based isolation becomes possible. If each team picks its own key, every cross-service workflow is cross-shard forever."

3.2 Fault Line 2: Placement — Algorithmic vs Directory#

The tension: Algorithmic placement (hash → shard) has no dependency and never goes stale, but can't place individual entities. A directory can put any entity anywhere, but becomes a critical dependency and a source of stale routing.

Staff default: Two-level: logical = hash(key) % 4096, physical = map[logical], plus an override table limited to ~1,000 entries. The full map is ~4K entries (~100 KB) — every router caches all of it. Updates are versioned; routers reject requests stamped with a stale version from a shard that no longer owns the data.

The stale-map problem: During a move, a router with map v416 sends a write to the old cluster after cutover to v417. Defense: each shard stores the set of logical shards it owns and the map version; it rejects writes for logical shards it no longer owns with WRONG_SHARD(v417), and the router refreshes and retries. This is fencing at the data layer, and it's what makes directory placement safe.

3.3 Fault Line 3: Resharding — Planned Online Migration vs Emergency Split#

The tension: Building copy/catch-up/verify/cutover tooling costs 1–2 engineer-quarters before you need it. Not building it means your first resharding happens when a shard is at 95% disk, under incident pressure, with hand-written scripts.

The online migration workflow (per logical shard):

Who pays: Without tooling, the on-call pays during the incident and customers pay in minutes-to-hours of write unavailability. With tooling, the DB platform team pays 1–2 quarters upfront plus ~10% ongoing.

Staff default: Build the workflow (or adopt Vitess / a managed engine that has it) before the first shard exceeds 60% capacity. Run it quarterly as a game day even when not needed, so the path is exercised.

When to deviate: If growth is predictable and slow (< 30%/year), over-provision logical shards and physical hosts up front and accept rare, planned maintenance-window moves.

🎯 Staff Move: "Resharding isn't an event, it's a capability. I want a runbook that moves one logical shard with a 5-second write pause, a verify step that blocks cutover on any checksum mismatch, and reverse replication so rollback is a config flip, not a restore."

3.4 Fault Line 4: Cross-Shard Correctness — Distributed Transactions vs Sagas#

The tension: Atomic commit across shards gives simple invariants but couples availability (all participants must be up) and holds locks across round-trips. Sagas keep shards independent but expose intermediate states.

Staff default: Design away ≥95% of cross-shard writes via the key. For the rest: transactional outbox on the initiating shard + idempotent saga steps keyed by a business ID + compensation + a reconciliation job. Name the drifting invariant and its bound.

When to deviate: Financial ledgers where regulators require atomic double-entry across accounts — co-locate the ledger by account group, or use a distributed SQL engine for that one table.

3.5 Fault Line 5: Routing Ownership — Library vs Proxy vs Engine#

Staff default: Proxy for polyglot orgs with > 3 services on the sharded DB; library only when one service owns the data. For new systems without heavy relational needs, a managed store with built-in partitioning.

🎯 Staff Move: "The routing layer is a platform product. If each team embeds its own router, the first map-format change requires coordinated deploys across every service — that's the SDK hell we already know from rate limiting."

4. Failure Modes & Operational Reality#

4.1 Hot Shard — The Whale Merchant#

Scenario: A single merchant runs a flash sale. Their logical shard lives on Cluster 07 alongside ~255 other logical shards.

t=0:       Merchant 88213 launches a flash sale; 12K orders/min on one merchant_id
t=+30s:    Cluster 07 primary CPU 45% → 92%; p99 write latency 8 ms → 340 ms
t=+1min:   255 unrelated merchants on Cluster 07 see checkout timeouts
t=+2min:   Connection pool saturation; app retries double the load
t=+3min:   Replica lag 30 s; merchant dashboards on Cluster 07 show stale orders
t=+8min:   On-call identifies merchant 88213 via per-key top-N
t=+10min:  Rate limit on merchant 88213 checkout (degraded mode) — neighbors recover
t=+50min:  Logical shard for 88213 moved to a dedicated cluster via override

Detection: shard.cpu_pct{cluster} > 80% for 2 min; shard.p99_write_ms > 50; shard.top_keys_qps showing one key > 20% of the shard. Blast radius: Every tenant co-located on the cluster — ~1/16 of merchants. Mitigation: Per-key admission control first (seconds), then move the logical shard (tens of minutes). Prevention: Directory overrides for the top 0.1% of merchants by GMV; per-key QPS budgets; pre-event capacity reviews for known campaigns. Owner: DB platform on-call (move); checkout team (per-merchant limits).

🎯 Staff Move: "Adding shards doesn't help a hot key — it spreads the neighbors, not the whale. The fix is isolation: the whale gets its own cluster, and its neighbors stop paying for it."

4.2 Stale Shard Map — Writes to the Wrong Place#

t=0:       Migration flips logical 1207 from Cluster 03 to Cluster 19 (map v417)
t=+0.2s:   38 of 40 routers pick up v417 via watch
t=+0.2s:   2 routers had a broken etcd watch after a network blip; still on v416
t=+0→45s:  Those 2 routers write ~1,100 orders to Cluster 03 for logical 1207
t=+45s:    Periodic 30 s poll... also failing (same connection issue)
t=+6h:     Customer reports missing order; reconciliation finds 1,100 orphaned rows

Root cause: Correctness depended on every router having the latest map. Prevention: shard-side fencing — Cluster 03 knows it no longer owns 1207 after the flip and rejects the write with WRONG_SHARD. With fencing, the same incident is 45 seconds of retries, not 1,100 orphaned rows. Detection: router.map_version_skew (max − min across fleet) > 0 for > 60 s; shard.wrong_shard_rejections_total. Owner: DB platform.

4.3 Resharding Gone Wrong — The Silent Diff#

Scenario: Catch-up replication skips rows written by a bulk job that bypassed the binlog (e.g., LOAD DATA with replication filters, or an unlogged table).

t=0:       Copy of logical shards 512–767 to new cluster begins
t=+2h:     Catch-up lag < 1 s; operator skips verify "to save time"
t=+2h05m:  Cutover
t=+3d:     Finance: 0.3% of refunds from a nightly batch are missing on the new cluster
t=+3d:     Reverse replication already torn down; old cluster cleaned up at t+48h

Lesson: Verification is not optional, and cleanup must wait for a business-cycle soak (at least one full nightly/weekly batch cycle). Detection: migration.verify_mismatch_chunks must be 0 to proceed — enforced by the orchestrator, not the operator. Owner: DB platform (tooling guardrail); batch job owners (must write through normal paths).

4.4 Fan-Out Tail Latency Collapse#

A "search orders by status" admin feature is implemented as scatter-gather over 64 clusters. Each cluster's p99 is 20 ms, but p99 of the fan-out is the p99.98 of individual shards.

Worse: at 200 admin QPS × 64 shards = 12,800 shard queries/s of full-index scans — a load amplifier that hurts OLTP on every shard. Detection: router.fanout_ratio (shard-queries per client query) > 1.2 fleet-wide; per-endpoint fan-out breakdown. Mitigation: Serve cross-shard queries from a CDC-fed search index or warehouse; cap fan-out QPS via a separate, rate-limited pool. Owner: The feature team that introduced the fan-out; DB platform sets the budget.

4.5 Cross-Shard Saga Stuck in the Middle#

The outbox relay crashes mid-deploy; 40K orders are PENDING with credit already debited on another shard. Detection: saga.pending_age_seconds p99 > 60 s; outbox.unpublished_rows growing. Mitigation: Relay is idempotent; restart drains the backlog. Orders older than 15 min go to a compensation job that re-checks the credit shard. Owner: Checkout team (saga logic); platform (relay infra).

4.6 Operational Reality Matrix#

5. Evaluation Rubric#

5.1 Level-Based Signals#

5.2 Strong Hire Signals#

5.3 Lean No-Hire Signals#

5.4 Common False Positives#

• Deep knowledge of consistent-hashing math ≠ sharding judgment. Virtual-node counts don't answer "which queries fan out?"
• Naming Vitess/Citus ≠ understanding. Can they describe what MoveTables actually does and where it can fail?
• Proposing Spanner immediately can be correct — but only if they price the latency and cost and explain why hand-sharding loses.
• Elaborate GSI designs — synchronous global indexes are a distributed transaction in disguise.

6. Interview Flow & Pivots#

6.1 Typical 45-Minute Shape#

6.2 How Interviewers Pivot — And What They're Testing#

6.3 What to Deliberately Skip#

6.4 Follow-Up Questions to Expect#

1. "How do you pick the number of logical shards, and what if you pick wrong?"
2. "Walk me through adding capacity with zero write loss."
3. "What happens to in-flight transactions during cutover?"
4. "How do you enforce a uniqueness constraint (e.g., unique email) across shards?"
5. "A migration corrupts data on the target. How do you find out, and how do you roll back?"
6. "How do you run a schema migration across 64 clusters?"
7. "How would you shard if the product later requires multi-merchant carts?"

7. Active Drills#

Drill 1: The Opening#

Prompt: "Our Postgres primary is at 85% CPU. Design sharding for it."

Drill 2: Choosing the Logical Shard Count#

Prompt: "Why 4,096 logical shards? Why not 16 or a million?"

Drill 3: Make Resharding Concrete#

Prompt: "You're moving from 8 clusters to 16. Walk me through it."

Drill 4: The Directory Goes Down#

Prompt: "Your etcd cluster holding the shard map is down for 20 minutes. What breaks?"

Drill 5: Hot Key — One Merchant at 30% of Writes#

Prompt: "One merchant is now 30% of all order writes. What do you do?"

Drill 6: Global Uniqueness#

Prompt: "Emails must be unique across all users, but users are sharded by user_id."

Drill 7: Build vs Buy#

Prompt: "Why not just use a distributed SQL database instead of sharding Postgres ourselves?"

Drill 8: Schema Change Across 64 Clusters#

Prompt: "Add a column to orders. You have 64 clusters."

Drill 9: Cost#

Prompt: "Finance says the database bill doubled after sharding. Defend it or fix it."

Drill 10: Multi-Region#

Prompt: "We're expanding to the EU and data must stay in-region."

8. Deep Dive Scenarios#

Deep Dive 1: Peak-Traffic Hot Shard#

Context: Singles' Day. Cluster 07 is at 98% CPU, checkout p99 is 2 s for ~6% of merchants, and the on-call escalates to you. One merchant is running a live-streamed flash sale.

Questions to Surface First:

• Is it one merchant (hot key) or many busy merchants on one cluster (hot shard)?
• Are neighbors' errors due to CPU, locks, or connection-pool exhaustion?
• Is the merchant's traffic legitimate, and what's the revenue at stake for them vs neighbors?
• Is a move possible right now without making the source cluster worse?

Typical L5 Approach: Scales the cluster vertically or adds read replicas, then proposes adding more shards. Neither helps within the hour: vertical scaling requires failover, replicas don't absorb writes, and more shards don't split one merchant.

Staff Approach: Identify the key via top-N. Apply per-merchant admission control to protect neighbors immediately (queue the whale's checkouts, don't fail them). Then decide: moving under load adds copy I/O to a saturated primary — throttle the copy to 30 MB/s or wait until the peak passes. After the event, pre-place known whales before campaigns.

Principal Approach: The incident is a symptom of missing tenant tiers. Large merchants get dedicated cells by contract; campaign calendars feed capacity planning; the platform publishes "per-tenant write budget" as part of the merchant API contract. The question for the VP is whether we charge for isolation or absorb it.

Key Takeaway: "More shards spread neighbors, not whales. Isolation is the fix; admission control buys the time to do it."

What clears the Staff bar:

• Separates hot key from hot shard within the first minute
• Protects neighbors before fixing the whale
• Converts the incident into a placement policy

Deep Dive 2: Silent Data Divergence After Resharding#

Context: Three weeks after a resharding wave, a support ticket reveals an order that exists in the warehouse but not in the OLTP database. Reconciliation finds ~2,300 missing rows across 14 logical shards.

Questions to Surface First:

• Were the rows written before, during or after cutover?
• Did the verify step run and pass for those logical shards?
• Is there any write path that bypasses the router (batch jobs, direct DB access, triggers)?
• Is the old cluster's data still available?

Typical L5 Approach: Restores from backup and re-inserts the rows. Fixes the symptom; the next wave loses rows the same way.

Staff Approach: Treat it as a correctness incident. Timestamp analysis shows all rows came from a nightly refund job connecting directly to the old cluster by hostname — bypassing the router, so fencing never saw them. Fix: backfill from the old cluster (still retained per policy) and the warehouse; revoke direct DB credentials; enforce router-only access with network policy; add per-logical-shard row-count reconciliation between OLTP and warehouse daily.

Principal Approach: Direct database access by hostname is an org-level anti-pattern that will break every future platform migration. Write the standard: all data access goes through the routing layer; exceptions need a registered, fenced client. Audit all 40 services' connection strings; publish a deadline. The fix is governance, not one job.

Key Takeaway: "Fencing only works if every writer goes through the fence. A migration's blast radius is every client you forgot about."

What clears the Staff bar:

• Looks for bypass paths, not just tooling bugs
• Moves fencing to the storage layer
• Extends soak to cover business cycles

Deep Dive 3: Large-Customer Onboarding#

Context: Sales closes an enterprise customer migrating 40M orders and expecting 3K writes/s at launch — larger than any current merchant. Launch is in 6 weeks.

Questions to Surface First:

• Does this customer fit on one cluster with 2× headroom?
• Do they need data residency or contractual isolation?
• How is the 40M-order import staged — bulk load or through the API?
• What's their 12-month growth forecast?

Typical L5 Approach: Imports through the normal API over a weekend. 40M orders at API rate limits takes days and hammers the shared cluster.

Staff Approach: Pre-provision a dedicated cluster; directory override for the merchant before import; bulk-load directly into the dedicated cluster via the migration tooling's copy path (not through OLTP APIs); build GSIs and warehouse via CDC backfill in a separate, throttled pipeline. Load-test at 2× expected peak (6K writes/s) two weeks before launch.

Principal Approach: Enterprise onboarding is a repeatable product: an "onboarding cell" template with pricing, a capacity SLA, and a lead time. Sales gets a checklist that triggers platform involvement at contract stage, not after signing.

Key Takeaway: "Onboard whales by placement, not by hope. The directory exists so the biggest customer never shares a cluster by accident."

What clears the Staff bar:

• Bulk path separate from OLTP path
• Isolates derived-store backfill from other tenants
• Connects capacity to commercial terms

Deep Dive 4: Post-Mortem — The 2PC Outage#

Context: A previous team implemented cross-shard transfers with XA/2PC. The coordinator host lost its disk after participants prepared. 11 minutes of locked rows on 6 clusters cascaded into a checkout outage. You're asked to lead the post-mortem.

Questions to Surface First:

• How many transactions were in-doubt, and which rows did they lock?
• Did the coordinator's decision log survive?
• What fraction of all writes are cross-shard?
• Why did locks on transfer rows block checkout?

Typical L5 Approach: Make the coordinator highly available (replicate its log). Reduces probability but keeps the coupling: any participant slowness still holds locks across shards.

Staff Approach: Measure: cross-shard transfers were 0.8% of writes but caused 100% of this outage. Replace 2PC with outbox + saga: debit on source shard with an outbox entry, idempotent credit on target, compensation on failure, hourly ledger reconciliation. Invariant "sum of balances is constant" is violated by in-flight transfers for ≤ 5 s — finance signs off because the ledger records both legs with a transfer ID.

Principal Approach: Publish a policy: synchronous cross-shard transactions require an exception review by the data platform council. Provide a saga/outbox library as the paved road so teams don't reinvent it. Track crossshard.sync_txn_count fleet-wide as a debt metric.

Key Takeaway: "2PC turns the availability of N shards into the product of their availabilities — and the coordinator is a single point of blocking."

What clears the Staff bar:

• Quantifies share of writes vs share of risk
• Names the drifting invariant and who signs off
• Converts one fix into a platform default

Deep Dive 5: Multi-Region Expansion#

Context: Leadership wants active-active across US-East, US-West and EU within 12 months, with EU data residency. Today: single-region, 32 clusters.

Questions to Surface First:

• Is the goal latency, availability, residency — or all three?
• Can each entity have a home region (writes go home), or must any region accept writes for any entity?
• What's the tolerance for cross-region write latency (~70–150 ms RTT)?
• Which tables are global (uniqueness claims, config)?

Typical L5 Approach: Multi-master replication for all clusters in all regions with last-writer-wins. Conflicts silently corrupt balances and inventory.

Staff Approach: Home-region placement: the directory records each merchant's home region; writes route home (~100 ms extra for remote users on writes, local reads from async replicas with a "your recent writes" session pin). EU merchants' data never replicates out. Global tables are split by region or become region-prefixed. Regional failover moves merchants' home to another region via the same migration workflow — with RPO > 0 for async replication, stated explicitly.

Principal Approach: Active-active is a 3-year program, not a feature. Stage it: Year 0 residency (EU cells), Year 1 regional read replicas + home-region writes, Year 2 automated regional failover with documented RPO/RTO. Budget: ~2.2× infra cost and one dedicated team. Say no to multi-master for money tables.

Key Takeaway: "Give every entity a home. Multi-region sharding is just placement with a region column — until someone asks for multi-master."

What clears the Staff bar:

• Separates latency, availability and residency goals
• Reuses the directory and migration workflow
• States RPO explicitly

9. Level Expectations Summary#

After studying this case study, you should be able to:

• Decide whether to shard at all, using write QPS, size and growth numbers
• Derive a shard key from the transactional boundary and list which queries fan out
• Explain logical-shard placement with a directory, overrides and data-layer fencing
• Walk through an online resharding workflow with durations, a write-freeze budget and rollback
• Distinguish hot shard, hot tenant and hot row, with a mitigation for each
• Replace distributed transactions with outbox + saga and name the drifting invariant
• Price build vs buy and describe a 3-year evolution path

The Bar for This Question#

Mid-level (L4): Knows sharding splits data across nodes; can describe hash vs range partitioning and consistent hashing. Doesn't reason about resharding or cross-shard queries unprompted.

Senior (L5): Picks a sensible key, uses consistent hashing or logical shards, knows scatter-gather is slow, proposes 2PC or sagas when asked. Handles resharding at the "add nodes" level without a data-movement plan.

Staff+ (L6): Starts from queries and transactional boundaries, commits to a key and names what it costs. Designs the online migration with verify, fencing and rollback before being asked. Treats hot keys, cross-shard invariants and fan-out as owned operational risks with metrics. Knows when not to shard and when to buy. The interviewer should learn something from the answer.

10. Staff Insiders: Controversial Opinions#

10.1 "Most Teams Shard Two Years Too Early — and the Rest Shard Six Months Too Late"#

The Staff position: Shard when your 18-month projection crosses ~60% of a single primary's ceiling — not earlier (you pay complexity for nothing), not later (you migrate in a fire).

Why this matters in interviews: Saying "we don't need to shard yet, here's the trigger" is a stronger signal than a perfect sharding design for a system that doesn't need one.

10.2 "Consistent Hashing Is the Wrong Answer to the Question You Were Asked"#

Consistent hashing minimizes how many keys move. Interviewers are asking how you move them safely. Logical shards achieve the same minimal movement with explicit, observable, controllable placement — and support pinning whales, which a ring can't.

The Staff position: Use consistent hashing for caches and Dynamo-style stores; use logical shards + a directory for relational OLTP.

Why this matters in interviews: Candidates who stop at "consistent hashing" haven't described the migration, which is the actual risk.

10.3 "Synchronous Global Secondary Indexes Are 2PC Wearing a Disguise"#

Updating a GSI on another shard in the same request means either a distributed transaction or an index that can be wrong after partial failure. DynamoDB's GSIs are asynchronous for exactly this reason.

The Staff position: GSIs are CDC-maintained, eventually consistent, with a published lag SLO (e.g., p99 < 2 s). Reads that need strong consistency go through the primary key.

10.4 "The Shard Key Is an Org Chart Decision"#

Once three services share a partition key, cells become possible and cross-service joins are local. Once they don't, you've built permanent cross-shard traffic into your architecture.

The Staff position: Choose the key with the adjacent teams in the room. It's cheaper to align now than to reconcile forever.

10.5 "If You Need Cross-Shard Transactions Everywhere, You Picked the Wrong Tool"#

Hand-sharding pays off when 95%+ of transactions are single-entity. If your domain is a graph of entities updated together, buy a distributed SQL engine and spend your engineers on the product.

11. The Principal Lens (L7)#

Why L7 Sees This Problem Differently#

A Staff engineer shards a database. A Principal engineer notices the company is about to shard its fifth database with its fifth bespoke router, its fifth migration script and its fifth incompatible shard key — and that the org will spend ~15 engineer-years maintaining them. The L7 question is not "what key for orders?" but "what is our partitioning platform, which entity is the company's partition axis, and which databases should never be hand-sharded at all?"

The Org-Level Fault Line#

One partitioning platform vs per-team sharding. Per-team sharding lets teams move fast and pick optimal keys locally; it produces divergent tooling, un-exercised migrations and cross-service data that never co-locates. A central platform (Vitess/Citus/managed distributed SQL + shared directory + migration orchestrator) standardizes operations but constrains data models and makes the platform team a bottleneck.

The L7 default: Centralize the mechanics (router, directory, migration, fencing, observability); decentralize the keys and schemas within a published convention (tenant/merchant as the company-wide partition axis). Offer a managed-KV path for teams whose model fits it.

Cost Model#

Assumptions: cloud list prices, 3-node clusters (primary + 2 replicas), ~$2.5K/month per node for 16 vCPU / 128 GB / 2 TB NVMe equivalent, fully loaded engineer $25K/month.

Opportunity cost: at medium scale, a home-grown resharding tool is 2 engineer-quarters ($150K) — cheaper than one emergency resharding incident that burns a 6-person team for 3 weeks and causes a multi-hour checkout outage.

The 3-Year Evolution Path#

One-Way Doors vs Two-Way Doors#

The Standard I'd Write#

RFC-DATA-007: Horizontal Partitioning of OLTP Stores

Scope: All OLTP datastores projected to exceed 2 TB or 10K writes/s within 18 months.

MUST:

• Partition on the company partition axis (tenant_id/merchant_id) unless an exception is approved.
• Use the platform router and directory; no direct host connections for application writes.
• Hash into ≥ 1,024 logical shards; target ≤ 1 TB per physical cluster.
• Enforce ownership fencing at the storage layer for every logical shard move.
• Maintain secondary access paths via CDC with a published lag SLO.

SHOULD:

• Keep ≥ 95% of write transactions single-shard; use the platform saga/outbox library otherwise.
• Run one resharding game day per quarter.

Exceptions: Synchronous cross-shard transactions and non-axis keys require Data Platform Council review (48-hour SLA) with a named owner and a written invariant-drift analysis.

Success metrics: resharding.p50_move_hours < 2; crossshard.sync_txn_ratio < 1%; zero data-loss incidents from moves; ≥ 80% of eligible stores on the platform within 18 months.

What I'd Tell the VP#

"Our order database will hit its hardware ceiling in about 14 months at current growth. We can split it across many smaller databases, which keeps costs roughly linear with growth and lets us isolate our largest merchants so one flash sale can't slow down everyone else. It's about two quarters of work for a four-person team, plus ongoing ownership by the data platform group. The alternative — a managed distributed database — is faster to adopt but roughly 1.5–2× the infrastructure bill and adds latency to checkout. I recommend we build on the proven open-source approach now, and make it the standard every team uses, so we pay this cost once instead of five times."

Principal Interview Signals#

Staff answers that L7 interviewers find insufficient:

• A flawless resharding workflow for one database, with no view on the other four teams about to build their own.
• "We'll use Vitess" without the headcount, the migration timeline or the exit cost if Vitess stops fitting.
• A hot-shard fix that's purely technical, ignoring that whale isolation should be a priced product tier.

Appendices

shard = hash(key) % N
N: 8 → 9   ⇒ a key stays put only if hash % 8 == hash % 9  ⇒ ~1/9 of keys stay; ~89% move

logical  = murmur3(merchant_id) % 4096
physical = override.get(merchant_id) or map[logical]   # map: 4096 entries, ~100 KB
route(query) → physical cluster, stamped with map_version

64-bit id = [41 bits ms since epoch][12 bits logical shard][11 bits per-ms sequence]
           ≈ 69 years          4096 shards        2048 ids/ms/shard

BEGIN;
  INSERT INTO orders (...) VALUES (...);
  INSERT INTO outbox (id, topic, payload) VALUES (uuid, 'order.created', ...);
COMMIT;
-- relay (or CDC on the outbox table) publishes and marks sent; consumers dedupe by id

Per shard:   shard.cpu_pct, shard.qps{op}, shard.p99_ms{op}, shard.size_gb, shard.disk_pct,
             shard.replication_lag_s, shard.top_keys_qps
Router:      router.fanout_ratio, router.map_version, router.map_version_skew, router.wrong_shard_retries
Migration:   migration.copy_mb_s, migration.catchup_lag_s, migration.verify_mismatch_chunks, migration.freeze_ms
Derived:     gsi.lag_seconds, outbox.unpublished_rows, saga.pending_age_seconds