Technologies referenced in this case study: PostgreSQL · DynamoDB · Cassandra · Redis · Elasticsearch · Time Series DBs · Kafka

Related case studies: Database Sharding · Replicated Data Store · Distributed Caching · Search Indexing · Data Modeling · Database Indexing · Consistency Models · Build vs Buy

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.

Technology-first choice:
Month 0:   New messaging feature. Team picks a wide-column store "because it scales to millions of writes."
Month 2:   Product asks for "unread count per conversation" and "search messages by sender." Neither is a
           partition-key lookup. Team adds a second table per query, written by the application.
Month 5:   The 4 denormalized tables drift: a bug writes 3 of 4. No transactions to fix it.
Month 8:   Product asks for "edit message" with history. Another table. Read-repair storms at peak.
Month 12:  Total load: 3K writes/s. A single Postgres node would have handled 10× this.
Month 14:  Two engineers full-time on data consistency tooling. The on-call rotation hates the cluster.

Access-pattern-first choice:
Month 0:   Team writes down the 8 queries, the write rate (3K/s peak, 10× in 2 years = 30K/s),
           the correctness bar (per-conversation ordering, no lost messages) and team skills.
           Picks Postgres: all 8 queries are indexed lookups; 30K/s fits a well-sized primary with batching.
           Partitions by conversation_id from day one; states the migration trigger in the design doc:
           "sustained > 50K writes/s or > 5 TB hot data → shard by conversation_id or move messages
           to a wide-column store."
Month 12:  3K writes/s, one primary + 2 replicas. Features shipped in days, not weeks.
Month 30:  Trigger hit for the messages table only. That one table migrates. The other 40 tables stay put.

Executive Summary

If you only read one section, read this. Everything else in the case study elaborates on the contrast below.

What This Interview Actually Tests#

Database selection is not a technology question. Every candidate can list SQL vs. NoSQL tradeoffs.

This is an access-pattern and commitment question that tests:

• Whether you derive the choice from queries, write rates, data size, and correctness bar instead of a store's reputation
• Whether you know the real scale envelope of the boring default, and so don't reach for a distributed store for 3K writes/s
• Whether you account for the cost of polyglot persistence: every additional store is another on-call rotation, backup regime, security review, and consistency boundary
• Whether you plan the migration path before you need it: the trigger, the mechanism, and the one-way doors

The key insight: the right database is the one whose failure modes and operational burden your team can own for five years while serving the access patterns you can write down today plus the growth you can defend. That's usually a relational database until a specific, measured access pattern proves otherwise.

The L5 → L6 → L7 Contrast — Start Here#

The Staff Positions#

The Three Intents#

🎯 Staff Move: "I'll treat this as a greenfield choice with a known growth curve. I'll default to Postgres and only deviate for a specific access pattern I can write down that it can't serve at our 3-year projection. If one appears, only that workload gets a different store, fed from the system of record."

The Five Fault Lines#

In the Wild: Real Production Systems#

Why this section belongs here: real migrations are the best evidence that database choice is about access patterns and operations, not reputation.

Discord — MongoDB → Cassandra → ScyllaDB for Messages#

Discord publicly described storing messages first in MongoDB, then moving to Cassandra (around 2017) when the data outgrew a single replica set, modeling messages by (channel_id, time bucket) partitions. Years later, with trillions of messages, they migrated again to ScyllaDB, citing garbage-collection pauses, hot partitions and operational toil with the Cassandra cluster, and they added an intermediate data-service layer to coalesce hot reads.

Staff insight: the access pattern (read recent messages in a channel, append new ones) never changed. The engine changed twice as scale and operational costs changed. Choose for the pattern, expect to revisit the engine, and keep a data-access layer that makes that revisit survivable.

Uber — PostgreSQL → MySQL (and Schemaless)#

Uber's engineering blog (2016) described moving core storage from PostgreSQL to MySQL, citing their workload's write amplification, replication behavior and upgrade difficulty at their scale. They built Schemaless, a sharded, append-only layer on top of MySQL.

Staff insight: this case is often cited as "Postgres doesn't scale." The more accurate lesson is that at very high write volume, engine internals such as MVCC, index write amplification and replication format become first-order, and big companies often build a thin custom layer over a boring engine rather than adopting an exotic one.

Figma and Notion — Scaling Postgres Instead of Replacing It#

Both companies publicly described staying on PostgreSQL through hypergrowth. Notion sharded its Postgres (2021) into hundreds of logical shards across a smaller set of physical databases, keyed by workspace. Figma first split tables across databases (vertical partitioning) and then built horizontal sharding on top of Postgres.

Staff insight: these are the counterexamples to "we'll outgrow SQL." When your data has a natural tenant key (workspace, file), sharding the relational database keeps transactions within a tenant and lets you keep the engine the team already knows. See Database Sharding.

What Interviewers Probe#

System Architecture Overview#

Reading the diagram: there's exactly one place each invariant lives: orders, payments and users in Postgres, inside transactions. The purpose-built store exists for one written-down workload and is modeled for that workload's queries. Everything else (search, cache, analytics) is a derived view fed by CDC from the log. It can be rebuilt from Kafka or from a snapshot, and its staleness is stated. The governance box matters as much as the data boxes: every engine on this diagram has a platform owner, a restore drill, and a documented migration trigger.

Quick-Reference: The 30-Second Cheat Sheet#

Key Numbers Worth Memorizing#

Interview Walkthrough

The most common mistake: the candidate names a database in minute two and spends the rest of the interview defending it. Treat the database choice as a conclusion, reached after the access patterns are on the board, and say out loud what would change your mind.

This walkthrough uses a concrete prompt: "Which database(s) would you use for a marketplace: listings, orders, messaging between buyers and sellers, search, and an activity feed?" The same shape applies when database selection is embedded in any other design.

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

State the functional scope in one breath, then go straight to the numbers that decide engines:

"Before picking anything, I want five facts per workload: the queries, the read and write rates now and in 2–3 years, hot data size, the correctness bar, and whether the team already operates something that fits. Let me assume: 20M users, 5M listings, 200K orders/day, messages at ~2K writes/s peak, the feed at ~20K writes/s peak with fan-out, and search at ~3K queries/s."

Then set the correctness bar per workload. That's what separates the stores:

"Orders and payments need transactions and invariants: no double-sell of a unique item, and money balances. Listings need consistency for the owner and freshness within a few seconds for everyone else. Messages need per-conversation ordering and no loss. Search can be a couple of seconds stale. The feed can be minutes stale and lossy under load."

🎯 Staff Move: "I'm going to reach a database choice as a conclusion, not start with one. And I'll tell you what would change my mind: a measured trigger for each store."

Phase 2: Access Patterns & Data Model (3–4 minutes)#

This is the "entities and API" phase for this question. Write the access-pattern table:

"Now the choice falls out of the table. Patterns 1–3 are relational and transactional, and the rates are tiny, so they go in Postgres. Pattern 4 is a search engine, derived from Postgres by CDC. Pattern 5 fits Postgres today at 8K writes/s partitioned by conversation, with a trigger. Pattern 6 is the only one that grows past a single primary's comfort zone, at 150K writes/s in 3 years, so it's the one candidate for a purpose-built store. Pattern 7 goes to the warehouse via CDC."

🎯 Staff Move: Writing the rate and consistency columns next to the access patterns makes the choice defensible and makes deviations visible. Interviewers remember the candidate who built the table.

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

Walk through it in 90 seconds:

1. Postgres is the system of record for orders, listings, users and messages. The order flow is a single transaction: SELECT ... FOR UPDATE on the listing, insert the order, update inventory status.
2. Messages live in Postgres, in a table partitioned by conversation_id hash with an index on (conversation_id, created_at DESC). The trigger to move them out is "> 40K writes/s sustained or > 3 TB."
3. Feed goes to a wide-column store or DynamoDB, keyed (user_id, time_bucket), because its 3-year write rate exceeds what a single primary handles comfortably and it needs no transactions.
4. Search, cache invalidation and analytics are derived from Postgres via log-based CDC into Kafka. Each consumer is rebuildable and has a stated lag.
5. Every store has an owner, a restore drill, and a trigger in the design doc.

🎯 Staff Move: "This is two primary engines, not five. Search, cache and warehouse are views, not sources of truth. If any of them is lost, we rebuild it from Postgres and Kafka. That's the property that keeps polyglot persistence survivable."

Phase 4: Transition to Depth (1 minute)#

"The four interesting decisions are: the real limits of Postgres here and what the trigger is, the one workload that justifies a second engine, how derived stores stay consistent, and what the migration looks like the day a trigger fires. Which would you like first?"

If no preference, lead with the Postgres envelope and the trigger. It proves you know where the default stops.

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

Deep dive A: How far does the default go? (6–7 min)

"A single Postgres primary on a large instance (say 64 vCPU, NVMe, 512 GB RAM) handles roughly 10–50K simple writes/s and 100K+ indexed point reads/s, depending on row width, index count and transaction size. The first things to hurt are usually not throughput. They're operational: vacuum on high-churn tables, index build time on multi-TB tables, backup and restore time, and connection count. So my ladder, in order, is: fix queries and indexes → add a connection pooler → add read replicas for stale-tolerant reads → cache hot reads → partition large tables (native partitioning by time or hash) → split databases by domain (vertical partitioning) → shard by tenant key → only then change engines for the specific workload."

Name the triggers: "> 70% sustained CPU on the primary at peak after tuning, > 5 TB hot, restore time > our RTO (say 1 hour), or replica lag > 1s at peak. Any of those opens a design review, not an automatic migration."

Deep dive B: The one workload that earns a second engine (6–7 min)

"The feed: 150K writes/s at year 3, append-mostly, read by partition (a user's recent items), no cross-partition transactions, tolerant of minutes of staleness. That's exactly the shape a wide-column store or DynamoDB is designed for. Model query-first: partition key (user_id, month) so partitions stay under ~100 MB, clustering key created_at DESC. Writes are idempotent by (user_id, item_id). The feed is also rebuildable from the event log, so it's not a system of record. Losing it is an availability incident, not a data-loss incident."

"Managed or self-hosted? At 150K writes/s, DynamoDB on-demand would cost real money. I'd model provisioned capacity with autoscaling against a self-hosted Cassandra/ScyllaDB cluster plus the 2–3 engineers to run it. At this size I'd pick managed. Self-hosting starts to win at several times this scale, if the org already has the expertise."

Deep dive C: Keeping derived stores consistent (6–7 min)

"The anti-pattern is the application writing to Postgres and then to Elasticsearch. If the second write fails, they diverge silently, and retries reorder updates. Instead, log-based CDC reads Postgres's WAL (Debezium-style) into Kafka keyed by entity ID, which preserves per-entity order. Consumers apply updates idempotently with a version check (if incoming.version > stored.version). Rebuild is a snapshot plus replay. Staleness is measured: cdc.lag_seconds per consumer, alert at > 30s. For read-your-writes on search, the listing owner's own view reads from Postgres, not the index."

Deep dive D: The migration when a trigger fires (5–6 min)

"Say messages hit 40K writes/s and we move them to a wide-column store. Steps: (1) the data-access layer already hides the store, so only it changes. (2) CDC from Postgres feeds the new store continuously. (3) Backfill history in the background, idempotently. (4) Verify with row counts plus checksums per conversation over sampled and then full ranges. (5) Shadow reads: serve from Postgres, also read from the new store and compare, and log mismatches. (6) Cut over reads per cohort: 1% → 10% → 100%. (7) Flip writes: the new store becomes primary, with reverse CDC or dual-write back to Postgres for a rollback window of 2–4 weeks. (8) Decommission. Realistically that's 2–3 quarters for a system of record."

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

"Two primary engines: Postgres for everything with invariants and for most workloads by default, and one purpose-built store for the feed because its write rate is the only one that outgrows a single primary. Search, cache and analytics are derived from the log and rebuildable. Every store has a written trigger that would make us revisit it, and a data-access layer that keeps the migration to one module."

The org close:

"The cost I'd flag isn't the license or the instances. It's that every engine needs people who can restore it at 3am. I'd rather run two engines deeply than five badly."

🎯 Staff Move: End on the operational commitment and the trigger. It shows the choice is a managed decision with an exit, not a bet.

Common Timing Mistakes#

1. The Staff Lens#

1.1 Why This Problem Exists in Staff Interviews#

Database choices are among the longest-lived decisions in engineering. Code gets rewritten in a year. Data and the engine holding it tend to outlive the team that chose them. A wrong choice doesn't fail on day one. It fails in year two, as a feature that can't be built, a consistency bug that can't be fixed without transactions, or an on-call rotation nobody wants. Interviewers use this topic to test whether a candidate's instinct is requirements-driven and operationally honest or reputation-driven and optimistic.

It's also the most common place where Senior candidates over-engineer. A candidate who reaches for a distributed store at 3K writes/s signals that they don't know what the boring default can do, and a Staff engineer's job is often to stop exactly that decision.

1.2 The L5 vs L6 vs L7 Contrast — Visual#

1.3 The Staff Question That Cuts Through Everything#

"Which access pattern can't the store we already run serve at the scale we'll have in three years, and who will operate the thing we add?"

• If no pattern fails the test, you don't add an engine. You tune, replicate, partition or shard what you have.
• If one pattern fails, you add a store for that pattern only, modeled for it and fed from the system of record where possible.
• If you can't name the operator, whether a platform team, a managed service or an on-call rotation, you can't add the engine regardless of fit.

2. Problem Framing & Intent#

2.1 The Three Intents — Explained#

Greenfield product. You don't know next quarter's queries. Flexibility dominates: ad-hoc queries, joins, constraints, transactions and schema migrations that take minutes. The relational default wins almost always. Discipline is still required: put a narrow data-access layer in front of it, pick a tenant key early (even unsharded), and write down the trigger.

Known extreme workload. You can write the single query shape and it's big: 500K event writes/s, 10B-row time series, sub-ms counters, full-text relevance. Here a purpose-built store earns its place, and the modeling is query-first: design the partition key for the read, accept that other queries will be expensive, and keep the workload's data derived or separable.

Migrating off a store that hit its limits. The hardest intent. The data is live, the old store is failing slowly, and correctness during the transition is the whole job. The design is the migration mechanism: CDC, backfill, verification, shadow reads, staged cutover, rollback window. See Deep Dive 4 and Appendix C.

🎯 Staff Move: "Most 'which database' questions are really greenfield questions with a growth curve. So I'll start from the default and make the interviewer's scale numbers argue me out of it."

2.2 When NOT to Add a New Database#

🎯 Staff Move: "Postgres can do a surprising amount of what people add engines for: JSON, full-text, queues, geospatial with PostGIS, time partitioning. None of it is best-in-class. All of it avoids a new on-call rotation until scale proves otherwise."

2.3 What the Interviewer Leaves Underspecified#

2.4 Precise Terminology#

3. The Five Fault Lines#

3.1 Fault Line 1: General-Purpose vs Purpose-Built#

Staff default: relational SoR plus derived views. Add a purpose-built primary store only for a workload with a written access pattern whose 3-year projection exceeds the relational envelope, and keep that workload's invariants self-contained.

🧭 Principal Move: "The decision tree is the easy part. The hard part is making every team walk it the same way. I'd publish it as the paved road, with a required design review for any path that ends outside the supported-engine list."

3.2 Fault Line 2: Transactions vs Horizontal Scale#

Staff default: keep invariants in one transactional scope. If data has a natural tenant key (workspace, merchant, account), shard relational by that key so transactions stay inside a shard. Choose distributed SQL when you genuinely need cross-entity transactions at scale-out sizes and can afford the latency, as in global inventory or ledgers spanning regions.

"The question I ask is: which invariants cross entities? 'Don't sell the same seat twice' is one row, fine anywhere. 'Debit A and credit B atomically' is two rows. If A and B can be on different partitions, I need a transaction across them, a saga with reconciliation, or a distributed SQL engine. I'd pick deliberately, not discover it in production."

🎯 Staff Move: "Eventual consistency isn't a feature you choose. It's a cost you accept. I want to know exactly which invariants I'm handing to application code before I accept it."

3.3 Fault Line 3: Polyglot vs Standardized#

Staff default: a supported catalog, for example relational (Postgres), managed KV (DynamoDB), cache (Redis), search (Elasticsearch/OpenSearch), streaming log (Kafka), warehouse. Anything else needs a design review, a named owning team with on-call, backup/restore evidence, and a sunset plan.

🧭 Principal Move: "Each engine we support costs roughly 2–4 engineers a year in platform, upgrades, security patching and on-call, before any product work. Six engines is a 15–25 person investment. That's the budget conversation, and it's why 'best tool for the job' has to include 'the org can run it.'"

3.4 Fault Line 4: Managed vs Self-Hosted (and Lock-In)#

Staff default: managed until the infrastructure savings of self-hosting exceed the fully loaded cost of the team to run it with margin. That usually means spend well into the millions per year on that engine and existing in-house expertise. Accept proprietary lock-in consciously for workloads where the managed service is materially better, and keep a data-access layer so the lock-in is in one module, not 400 call sites.

"DynamoDB lock-in is real, but the cost of the lock-in is the cost of the migration, and that is bounded by how many places touch the API. A repository layer makes lock-in a quarter's work instead of a year's."

3.5 Fault Line 5: Evolve in Place vs Migrate#

Staff default: climb the ladder in order and stop at the first rung that clears the 3-year projection. Change engines only when the workload's shape (not just its size) is wrong for the current store, or when the operational cost of stretching exceeds the migration cost.

🎯 Staff Move: "A migration is a product with a roadmap, a team and an exit criterion. If I can't staff it that way, the right answer is another rung of the ladder on the current engine."

4. Failure Modes & Operational Reality#

4.1 The Wrong-Shape Store — Features You Can't Build#

Month 0:   Orders stored in a wide-column store, partitioned by customer_id ("it scales").
Month 4:   Finance needs "all orders for merchant X in March." Not a partition-key query.
           Team adds orders_by_merchant table, written by the app alongside the first.
Month 6:   Support needs "orders by status = stuck." A third table. A nightly full scan as backup.
Month 9:   A partial failure writes orders but not orders_by_merchant. Merchant payouts are short.
Month 10:  Reconciliation job built. Two engineers on data integrity for 6 months.
Month 16:  Decision to migrate orders to Postgres. 3 quarters of work.

Detection: data.reconciliation_mismatch_total between denormalized tables; feature lead-time for "new query" requests; count of app-maintained denormalized copies per entity.

Blast radius: every downstream consumer of the entity. Here, merchant payouts.

Mitigation: introduce CDC from the primary table to derive secondary views instead of app dual-writes; run reconciliation jobs with alerting.

Prevention: access-pattern table in the design review; for entities with money or unknown future queries, a relational SoR by default.

Owner: the service team, with the design-review board owning the missed check.

4.2 Dual-Write Drift Between SoR and Derived Store#

The application writes Postgres, then Elasticsearch. A deploy restarts pods between the two writes, and 0.3% of listing updates never reach search. Retries reorder two updates to the same listing, so search shows the older price.

Detection: search.drift_ratio from periodic sampled comparison (1,000 random IDs every 10 min, comparing version numbers); user reports of "price in search ≠ price on page."

Blast radius: every derived view fed by dual-writes. Silent until users notice.

Mitigation: full reindex from the SoR snapshot (hours), then switch the feed to CDC.

Prevention: CDC from the WAL, ordered by key; versioned idempotent upserts; a continuous drift checker.

Owner: the search/data platform team owns the CDC pipeline; the listing team owns the version field.

4.3 Hot Partition in a Partitioned Store#

A celebrity's feed partition receives 40K writes/s. The DynamoDB partition caps at ~1,000 WCU, so throttling hits that key and adaptive capacity only partially helps. In Cassandra, the replicas owning that partition hit 100% CPU and coordinator timeouts spread to neighbors.

Detection: db.throttled_requests{partition}; top-N partition key sampling; p99 latency by key prefix.

Mitigation: write sharding (suffix the key with #0..#15, fan-in on read), or cache/aggregate before write; move pull-model fan-out to fan-out-on-read for celebrities.

Prevention: key design review asking "what is the maximum rate on a single key?"; load test with realistic key skew.

Owner: the service team (key design).

4.4 Relational Primary at the Ceiling — Slow Degradation#

CPU climbs 3% a month. Vacuum can't keep up on the 2B-row events table, bloat grows, and queries slow. A restore test takes 9 hours against a 1-hour RTO. Nobody pages, because nothing is down.

Detection: db.cpu_p95_at_peak trend; db.table_bloat_ratio; db.restore_drill_duration vs. RTO; db.replica_lag_p99.

Mitigation: archive or partition the events table by month (drop old partitions instead of deleting rows); move the events workload out; scale up as a bridge.

Prevention: capacity triggers with a 6-month runway alert; quarterly restore drills with the time recorded; per-table growth dashboards.

Owner: DB platform team (monitoring and drills) and service team (schema, archival).

4.5 Migration Divergence#

During a live migration, a backfill job and the CDC stream both write the same row. The backfill writes an older version after the CDC applied a newer one. 0.01% of rows are silently stale in the new store, and the cutover would have made them authoritative.

Detection: full verification pass with per-row version and checksum comparison after backfill completes and CDC catches up; shadow-read mismatch rate.

Mitigation: re-run verification; repair mismatches from the SoR; don't cut over until the mismatch rate is 0 over N days of shadow reads.

Prevention: version-guarded writes (only if incoming.version > current.version) for both backfill and CDC.

Owner: the migration team, with a single accountable lead.

4.6 Operational Reality Matrix#

🎯 Staff Move: "The CDC replication slot is the classic hidden coupling. A dead consumer makes Postgres retain WAL until the primary's disk fills. The derived store's outage becomes the system of record's outage unless someone owns that alert."

5. Evaluation Rubric#

5.1 Level-Based Signals#

5.2 Strong Hire Signals#

5.3 Lean No-Hire Signals#

5.4 Common False Positives#

• Reciting CAP ≠ selecting a database. CAP describes partition behavior. It doesn't pick your engine.
• Knowing Cassandra's compaction strategies ≠ knowing when to use Cassandra.
• Benchmarks ≠ scale envelope. A vendor benchmark of 1M ops/s says little about your schema, your queries and your on-call team.
• "We'll use Spanner" ≠ solving consistency. It solves it at a latency and cost price that must be justified.

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#

• Storage engine internals (LSM vs. B-tree) beyond one sentence tied to a read/write ratio.
• Vendor feature checklists.
• ORM discussions.
• Detailed index tuning. Link to Database Indexing if asked.

6.4 Follow-Up Questions to Expect#

1. "Why not DynamoDB for everything?" New queries need new GSIs or tables, transactions are limited in scope, and hot keys and lock-in are costs. Good for known key-based patterns.
2. "When would you pick MongoDB?" Document-shaped aggregates read and written as a unit, a team with Mongo expertise, and moderate cross-document invariants.
3. "How do you handle read-your-writes with replicas?" Route the writer's reads to the primary for N seconds, or use LSN/GTID-based session consistency.
4. "How do you do schema migrations on a 2 TB table?" Expand/contract: add nullable column, backfill in batches, dual-read, switch, drop. Use online schema-change tools.
5. "How do you delete a user's data across all stores (GDPR)?" A deletion event via CDC/Kafka that every derived store consumes, with verification. Derived stores that can't delete aren't allowed.
6. "What about the cache — is it a database?" A derived store with TTL and invalidation. Never the SoR. See Distributed Caching.
7. "Who approves adding a new engine?" The platform architecture review, with evidence: access pattern, projection, owner, restore drill, exit plan.

7. Active Drills#

Drill 1: The Opening#

Prompt: "Which database would you use for a ride-sharing app?"

Drill 2: The Envelope#

Prompt: "We're at 8K writes/s on Postgres and the team wants to move to Cassandra 'before it's too late.' What do you say?"

Drill 3: Make It Concrete — Query-First Modeling#

Prompt: "You chose DynamoDB for chat messages. Show me the table design."

Drill 4: Derived Store Consistency#

Prompt: "Search shows stale prices after sellers update listings. Fix it properly."

Drill 5: The Dependency Goes Down#

Prompt: "The primary database fails. Walk me through what happens."

Drill 6: The Hot Key#

Prompt: "One seller has 2M listings and 30% of all traffic. The store is sharded by seller_id. What now?"

Drill 7: Multi-Tenant — Shared or Isolated?#

Prompt: "B2B SaaS with 5,000 customers. One database per customer or shared tables?"

Drill 8: Build vs Buy — Managed or Self-Hosted?#

Prompt: "Our DynamoDB bill is $400K/month. Should we move to self-hosted Cassandra/ScyllaDB?"

Drill 9: Changing the Engine Without an Outage#

Prompt: "Move the 12 TB orders table from MySQL to Postgres with zero downtime."

Drill 10: Multi-Region#

Prompt: "We need the app in US and EU with low latency writes in both. What changes in the database layer?"

8. Deep Dive Scenarios#

Deep Dive 1: Peak-Traffic Incident — The Primary Hits the Wall#

Context: During a seasonal peak, the Postgres primary for the core app is at 95% CPU, p99 query latency is 800ms (normally 15ms), and checkout is timing out. The on-call escalates to you.

Questions to Surface First:

• Which queries dominate load right now (pg_stat_statements)? New or old?
• Is it CPU from queries, lock contention, or connection overload?
• Are stale-tolerant reads hitting the primary instead of replicas?
• What changed recently: deploys, index drops, data growth, plan changes?

Typical L5 Approach: Scale the instance up (a 20–30 min failover risk mid-incident), add replicas, and propose moving to a "more scalable" database.

Staff Approach: Finds the top queries by total time. Typically one query with a regressed plan or a missing index dominates, or a new feature is doing N+1 queries on the primary. Kills or rate-limits the offending path with a feature flag, routes browse traffic to replicas, and sheds non-critical writes to a queue. After the incident: fix the index or plan, add query-level budgets, and model capacity against the next peak.

Principal Approach: Asks why one feature could consume the shared primary's capacity. Introduces per-service query budgets and statement timeouts per role, moves the SoR onto a domain-split topology so checkout's database is isolated from browse and recommendations, and adds DB capacity to the pre-peak readiness review.

Key Takeaway: "When the relational primary hits the wall, it's usually one query, not the engine. Find it before you reach for a new database."

What clears the Staff bar:

• Diagnoses via top queries before scaling
• Uses shedding and routing, not a risky failover
• Converts the incident into query governance

Deep Dive 2: Silent Failure — The Derived Store That Stopped#

Context: Customers report that newly created listings don't appear in search. It started ~6 hours ago. Postgres disk usage on the primary has grown 400 GB in those 6 hours and is at 88%.

Questions to Surface First:

• Is the CDC connector running? Is the replication slot advancing?
• How much WAL is the slot retaining?
• How long until the primary's disk fills?

Typical L5 Approach: Restart the search indexer; investigate search. Doesn't connect the disk growth.

Staff Approach: Recognizes the coupling immediately: the CDC connector died, its logical replication slot stopped advancing, and Postgres is retaining WAL for it. The search outage is about to become a system-of-record outage when the disk fills. Priority one is the primary: restart the connector if it can catch up quickly; otherwise drop the slot, which frees the disk and means the index must be rebuilt from a snapshot. Then rebuild search from snapshot plus fresh CDC.

Principal Approach: Treats derived-store pipelines as able to harm the SoR and sets a standard: every replication slot has an owner, a max_slot_wal_keep_size (or equivalent) safety limit, and an alert on retained bytes. The org decides explicitly that the SoR's availability beats the derived store's completeness: slots are dropped automatically at a threshold, and derived stores must support rebuild.

Key Takeaway: "Derived stores are supposed to be disposable. A replication slot without a retention cap makes them dangerous."

What clears the Staff bar:

• Connects search staleness to disk growth
• Prioritizes the SoR over the derived view
• Establishes guardrails as a standard

Deep Dive 3: Large Customer Onboarding — The Whale Tenant#

Context: A new enterprise customer will bring 3 TB of data and 25% of current write volume in a multi-tenant Postgres setup (pooled tables, tenant_id everywhere). Sales signed it; onboarding is in 6 weeks.

Questions to Surface First:

• What's their write pattern: steady, or bulk imports?
• Do they need isolation (contractual, compliance, residency)?
• Which tables will they dominate, and how do their indexes fit in memory?
• Can our tenant router place a tenant on a dedicated database today?

Typical L5 Approach: Scale up the shared database and import their data.

Staff Approach: Puts them in the silo model: a dedicated Postgres cluster behind the same tenant router, same schema, same code path. Imports via a throttled bulk path with verification. Their load and failures are isolated from 5,000 other tenants, and the shared database's working set stays in memory. If the router can't do per-tenant placement yet, that's the 6-week project.

Principal Approach: Makes tenant placement a product capability with pricing. Tenants above a size threshold (e.g., > 500 GB or > 5% of load) are automatically siloed, priced accordingly, and reviewed at deal time. Capacity is part of the sales process, not a surprise after signature.

Key Takeaway: "Design the tenant router so a whale is a placement decision, not a re-architecture."

What clears the Staff bar:

• Isolates rather than scales up
• Keeps one code path for pooled and siloed tenants
• Pushes capacity into the commercial process

Deep Dive 4: Post-Mortem — The NoSQL Choice That Couldn't Answer Questions#

Context: Two years ago, a team built the billing system on a wide-column store "for scale." Today billing has 400 writes/s, finance can't run the reports it needs, and a reconciliation bug overcharged 2,000 customers because two denormalized tables disagreed. You're asked to lead the post-mortem and the path forward.

Questions to Surface First:

• What were the access patterns and rates at design time? Were they written down?
• Who approved the choice, and was there a design review?
• How many denormalized copies of each entity exist, and who maintains them?
• What does finance need that can't be queried today?

Typical L5 Approach: Fix the reconciliation bug; add another table for finance's queries.

Staff Approach: Names the root cause as a selection error: billing is a correctness-critical, query-heavy, low-rate workload, the opposite of what the store is good at. Proposes migrating billing to Postgres with the full mechanism (CDC, backfill, verification, shadow reads, staged cutover), with a transactional model where invoice, line items and payments commit together. Keeps the wide-column store for the workload it suits, if any. Short term: reconciliation job with alerting and a manual review queue.

Principal Approach: Fixes the process that allowed it. Introduces a data-class policy (money, identity, inventory must live in a transactional SoR) and a design review gate for any new SoR engine with the access-pattern table as a required artifact. Also reviews other systems against the policy and funds the migrations in priority order.

Key Takeaway: "Scale was never the constraint for billing. Correctness and queryability were. Choose for the constraint you actually have."

What clears the Staff bar:

• Identifies the selection error without blaming individuals
• Plans a full, verified migration
• Fixes the process with a data-class policy

Deep Dive 5: Multi-Region Expansion#

Context: The product runs on a single-region Postgres SoR. The company is launching in the EU with residency requirements and wants low latency for EU users within 9 months.

Questions to Surface First:

• Which data must stay in the EU: all user data, or specific classes?
• Which operations need global invariants (unique usernames, global catalog, cross-region payments)?
• Do users ever collaborate across regions?

Typical L5 Approach: Set up a cross-region replica of Postgres in the EU and route EU reads there; writes still go to the US.

Staff Approach: Home-region partitioning. Each user/tenant has a home region stored in a small global directory. EU users' data lives in an EU Postgres cluster with the same schema and code. The global directory and a few global invariants (username uniqueness) live in a small global store, which is either a single-region service with caching (cross-region write latency only at signup) or a distributed SQL engine. Derived stores (search, analytics) are per-region, with an aggregated, residency-compliant analytics layer.

Principal Approach: Writes the residency standard: data classes, allowed cross-region flows, audit logging, and a migration workflow for users changing region. Sequences the rollout: new EU users first, existing EU users migrated by cohort. Also chooses whether to invest in distributed SQL as a platform for future regions or keep the home-region pattern.

Key Takeaway: "Multi-region databases are mostly a data-classification exercise. Home-region most data, and pay for global consistency only where an invariant demands it."

What clears the Staff bar:

• Separates home-regioned data from global invariants
• Includes derived stores in residency
• Reuses the tenant router and migration mechanism

9. Level Expectations Summary#

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

• Build an access-pattern table (query, rate now and at 3 years, size, consistency, shape) and derive the engine from it
• State the realistic envelope of a relational primary and climb the scaling ladder before changing engines
• Keep invariants in one transactional scope, and name exactly which invariants you hand to application code when you don't
• Design derived stores via CDC with versioned idempotent updates, drift detection and a rebuild path
• Model a key-value/wide-column table query-first, with bounded partitions and hot-key mitigation
• Execute a zero-downtime migration: repository layer, CDC, backfill, verification, shadow reads, staged cutover, reverse replication
• Price polyglot persistence in headcount and argue managed vs. self-hosted with a TCO

The Bar for This Question#

Mid-level (L4): Knows SQL vs. NoSQL differences, picks a reasonable store for a single workload, designs a schema.

Senior (L5): Chooses competently per workload and knows each engine's strengths. But the choice is often made from reputation, polyglot costs are ignored, derived stores are kept in sync with app dual-writes, and migration is "later."

Staff+ (L6): Reaches the choice as a conclusion from written access patterns and rates, defaults to a relational SoR, adds a specialist only for a workload that crosses the envelope, derives the rest via CDC, and documents triggers and a migration mechanism. Names owners, restore drills and operational cost. The interviewer should learn something from the answer, whether that's the replication-slot coupling, the backfill-vs-CDC version race, or how little a feature needs to justify staying on Postgres.

10. Staff Insiders: Controversial Opinions#

10.1 "Postgres Is the Right Answer More Often Than Anyone Admits"#

The Staff position: Postgres by default. Make the workload prove otherwise with numbers.

Why this matters in interviews: defending the boring choice with an envelope and a trigger is more senior than picking the exotic one.

10.2 "Schemaless Is a Lie — You Just Moved the Schema Into Every Reader"#

The Staff position: use schema-on-read where data is genuinely heterogeneous. Otherwise enforce schema at write, including in document stores (schema validation), or use JSONB inside a relational table for the flexible part only.

Why this matters in interviews: "flexible schema" is the weakest common justification for a document store.

10.3 "Most Polyglot Architectures Are Org Charts, Not Designs"#

The Staff position: one SoR per entity, derived views via CDC, a supported-engine catalog, and a sunset plan for strays.

Why this matters in interviews: it shows you see the org cost of technical choices.

10.4 "The Database Migration Is Never the Hard Part — The Access Layer Is"#

The Staff position: a narrow data-access layer per domain from day one. It's the cheapest insurance in the system.

Why this matters in interviews: answering "how would you migrate?" with "first, there's one module to change" signals you've done one.

10.5 "Discord's Two Migrations Are a Success Story, Not a Cautionary Tale"#

The Staff position: expect to revisit engine choices every few years at hypergrowth. Design so revisiting is affordable.

Why this matters in interviews: it reframes "choose right the first time" into "choose defensibly and keep the exit cheap."

11. The Principal Lens (L7)#

Why L7 Sees This Problem Differently#

A Staff engineer picks the right database for a system. A Principal engineer sees that the org's data engines are a portfolio of long-lived commitments. Each carries a platform team, a skill pool, a security surface, a backup and restore regime, a vendor relationship, and a migration liability. The unit of decision is the catalog: which engines the company supports, how deeply, for which data classes, and which are being retired. Individual selection decisions get easy once the catalog, the data-class policy and the review gate exist. Without them, 40 teams make 40 locally reasonable choices that add up to an unoperable estate.

The Org-Level Fault Line#

Team autonomy in data-store choice vs. a governed platform catalog. Autonomy lets teams move fast and fit tools to problems. It also produces engine sprawl, orphaned clusters after reorgs, inconsistent backups and a security team that can't audit what it can't see. A strict single-engine mandate avoids sprawl but forces bad fits and invites shadow IT.

The Principal position: a supported catalog of 4–6 engines, each with a funded platform team, paved-road tooling (provisioning, backups, CDC, observability, access control), and published guidance on when to use it. Off-catalog engines are allowed through a review that requires a named owning team with on-call, restore evidence, a cost estimate, and an exit plan. Every off-catalog engine is re-reviewed yearly.

🧭 Principal Move: "I'd rather say yes to a new engine with an owner and an exit plan than say no and find it running unowned in production 18 months later. The review isn't a gate to stop teams. It's a way to make the cost visible before it's committed."

Cost Model#

Assumptions: managed-service list prices; fully loaded engineer ~$300K/year; "ops" includes upgrades, patching, backups/restore drills, capacity planning and on-call.

Reading it: at growth scale, each unnecessary engine costs ~$0.6–1M/year in people before infra. Consolidating two stray engines often funds a whole platform team.

The 3-Year Evolution Path#

One-Way Doors vs Two-Way Doors#

The Standard I'd Write#

RFC: Data Store Selection & Operation Standard v1

Scope: Any new data store, or any new system of record, in production.

MUST

1. Every design doc includes an access-pattern table: query, rate now and at 3 years, hot data size, consistency requirement, latency SLO.
2. Money, identity, inventory and entitlement data (Class A) live in a transactional system of record from the supported catalog.
3. Each entity has exactly one system of record. Other copies are derived via the CDC platform, must be rebuildable, and must document staleness.
4. No application dual-writes to keep stores consistent.
5. Every store has a named owning team, on-call, a restore drill at least quarterly with recorded duration vs. RTO, and a documented migration trigger.
6. Every replication slot / CDC consumer has an owner and a retention cap.
7. Access to each store goes through a per-domain data-access layer.

SHOULD

• Prefer managed offerings of catalog engines unless a TCO review shows > 30% savings net of team cost.
• Tenant/owner key present on every row from day one.

Supported catalog v1: PostgreSQL (SoR), DynamoDB (high-scale key-value), Redis (cache/ephemeral), OpenSearch (search, derived), Kafka (log/CDC), warehouse (analytics, derived).

Exceptions: Architecture review with the access-pattern table, owner, cost estimate and exit plan. Re-reviewed annually.

Success metrics: off-catalog engines ≤ 3 org-wide; 100% of SoRs with a passing restore drill in the last quarter; zero dual-write drift incidents; migration triggers documented for 100% of SoRs.

What I'd Tell the VP#

"Our biggest data risk isn't that we picked a database that can't scale. It's that we run eleven different kinds of databases, several with no clear owner and no tested restore. Every extra kind costs us roughly two to four engineers a year just to keep safe. I want to standardize on five supported types, give each one a platform team, and move the strays onto them over the next 18 months. That should free up around 8–10 engineers and cut the chance that a data-loss incident hits a system nobody knows how to restore. For new projects, teams will write down their data needs before choosing a store, which is cheap and prevents the expensive rewrites we did on billing last year."

Principal Interview Signals#

Staff answers that L7 interviewers find insufficient:

• "I'd pick Postgres here and add Elasticsearch for search." Right for the system, but silent about the org's catalog, platform ownership and the cost of the search cluster.
• "We'll migrate when we hit the trigger." That doesn't budget or staff the migration, or say what else it displaces on the roadmap.
• "Managed is easier." That's true, but without a TCO it isn't a decision, and it ignores lock-in on proprietary data models.

Appendices

Appendix A: Engine Families in Depth#

A.1 Relational (Postgres/MySQL)#

Right when: multi-entity invariants, evolving queries, moderate to high scale within one primary or a tenant-sharded fleet. Wrong when: the workload is a single extremely high-rate append pattern with no joins, or relevance search.

-- Order placement: the invariant lives in one transaction
BEGIN;
SELECT status FROM listings WHERE id = :listing FOR UPDATE;   -- must be 'available'
INSERT INTO orders (id, listing_id, buyer_id, amount, status) VALUES (...,'pending');
UPDATE listings SET status = 'sold' WHERE id = :listing;
INSERT INTO outbox (event_type, payload) VALUES ('order_created', ...);  -- for CDC consumers
COMMIT;

The transactional outbox gives downstream systems an ordered event without dual-writes.

A.2 Key-Value / Document (DynamoDB, MongoDB)#

Right when: access is by known keys, latency must stay flat at any scale, items are self-contained aggregates. Wrong when: ad-hoc queries, multi-entity transactions at scale, or unknown future access patterns.

Table: user_inbox
PK = user_id            SK = last_activity#conversation_id
attrs: unread_count, last_message_preview
Query: PK = :user ORDER BY SK DESC LIMIT 20

A.3 Wide-Column (Cassandra/ScyllaDB/Bigtable)#

Right when: very high write throughput, time-ordered data per partition, multi-DC replication. Wrong when: you need joins, secondary queries or strong multi-row transactions. Also wrong for delete-heavy workloads (tombstones).

CREATE TABLE feed (
  user_id bigint, bucket text,          -- bucket = 'YYYY-MM' bounds partition size
  created_at timestamp, item_id uuid, payload blob,
  PRIMARY KEY ((user_id, bucket), created_at, item_id)
) WITH CLUSTERING ORDER BY (created_at DESC);

A.4 Distributed SQL (Spanner/CockroachDB/YugabyteDB)#

Right when: you need cross-entity transactions and horizontal scale, possibly across regions. Global ledgers and inventory are examples. Wrong when: a single primary or tenant sharding suffices. You'd pay consensus latency and cost for nothing.

A.5 Where Each Family Sits#

Appendix B: The Access-Pattern Worksheet#

Appendix C: Migration Mechanics#

Rules:

• Both backfill and CDC use version-guarded upserts, which removes the older-overwrites-newer race.
• Verification runs after CDC has caught up past the backfill's end.
• Write cutover always has a rollback path (reverse replication) for a defined window.
• The old store has a decommission date set at the start.

Appendix D: Consistency Contracts for Derived Stores#

Appendix E: Observability#

E.1 Core Metrics#

# System of record
db.cpu_p95_at_peak, db.connections_active, db.replica_lag_seconds
pg.stat_statements.top_total_time, pg.table_bloat_ratio
db.restore_drill_duration_minutes (vs RTO)
db.growth_bytes_per_day → runway_days to trigger
# CDC and derived stores
cdc.lag_seconds{consumer}, pg.replication_slot_retained_bytes{slot}
derived.drift_ratio{store}               # sampled version comparison
# Partitioned stores
db.throttled_requests{partition}, db.hot_key_topN
# Migrations
migration.shadow_read_mismatch_rate, migration.backfill_progress_pct

E.2 Critical Alerts#

Appendix F: Scale Evolution#

What you don't build on day one: a polyglot architecture. Do build on day one: the repository layer, the tenant key, tested backups and a written trigger.

Appendix G: Multi-Tenancy, Fairness & Cost#

• Pooled vs. silo: pooled tables with tenant_id + row-level security for the long tail. Dedicated databases for whales and compliance tenants, behind the same router.
• Noisy neighbors: per-tenant statement timeouts and connection limits; per-tenant query budgets on shared primaries.
• Cost attribution: storage bytes, IOPS and query time per tenant, which feed pricing tiers.
• Deletion: per-tenant and per-user deletion events flow through CDC to every derived store; deletion completeness is verified, not assumed.