System Design Database Choice Interview
System design database choice interviews separate “we’ll use Postgres and Redis” from a design that starts with access patterns, consistency, and scale shape. This guide is a practical chooser: how to pick (and defend) a primary store in a design interview.
Start with access patterns, not logos
Talk track:
“Before picking a database, I want the read/write ratio, query shapes, consistency needs, and whether we need multi-row transactions.”
If you cannot describe the hottest queries, you are guessing. Product names without query shapes read as memorized architecture astronautics.
Clarifying questions that matter
- Entities and relationships (1:1, 1:N, graph-like?)
- Transactions across multiple rows/tables?
- Query patterns: point lookup, range, full-text, analytics?
- Consistency: strong for money/inventory vs eventual for feeds?
- Scale: working set size, QPS, growth
- Geo: single region vs multi-region active-active?
Write these on the board before you say “Postgres.”
SQL vs document vs KV (interview defaults)
| Store type | Strong fit | Weak fit |
|---|---|---|
| Relational (Postgres/MySQL) | Joins, constraints, multi-row ACID, mature ops | Ultra-wide flexible docs at massive write scale (sometimes) |
| Document | Flexible nested records, per-document access | Heavy cross-document joins, complex multi-doc transactions |
| Wide-column | Time-series-ish, large sparse rows | Ad-hoc relational queries |
| KV / cache | Sessions, hot keys, simple gets | Rich querying |
| Search engine | Full-text, relevance | System of record |
| Graph DB | Deep relationship traversal | Simple CRUD with rare hops |
Default for most product APIs: relational primary unless the prompt screams otherwise. That is not conservatism—it is matching common interview expectations and real constraints.
Consistency scripts
| Need | Approach |
|---|---|
| Payments, inventory, seats | Strong consistency + transactions |
| Social feed fan-out | Often eventual; say the lag |
| User profile read-your-writes | Primary read or session stickiness |
| Analytics | Warehouse / OLAP separate from OLTP |
Never claim “NoSQL cannot do transactions” or “SQL cannot scale”—both are cartoon versions. Speak in mechanisms: sharding, replicas, isolation levels, quorum writes.
Index and query talk track
- Point lookups → primary key / unique key
- Filters + sort → composite indexes matching ORDER BY
- “Search all posts by keyword” → search index, not LIKE on OLTP
- Time-range scans → careful partitioning
- Pagination → keyset/seek vs OFFSET for large lists
Talk track:
“Orders are relational with transactional stock decrement; the product catalog search goes to a search index updated asynchronously.”
Modeling pitfalls to name early
- God table that stores every event type as JSON with no query plan
- Chatty joins across five services’ tables pretending to be a monolith DB when you already drew microservices
- Using cache as source of truth
- Dual writes to SQL and search without an outbox/event story
Polyglot is a privilege you earn
Multiple databases are fine after you justify each:
- OLTP relational for truth
- Cache for hot reads
- Queue for async
- Search / warehouse as derivatives
Drawing five stores in minute two without access patterns looks like memorization. Earn the second store with a bottleneck sentence.
Sharding and replication (only when needed)
Mention when QPS or data size forces it:
- Read replicas for read-heavy workloads (stale read caveat)
- Shard key choice (userId vs tenantId)—hot partitions
- Secondary indexes become harder when sharded—say it
- Resharding is painful—prefer vertical scale + caching first in many interviews
Talk track:
“At this QPS a single primary with replicas is enough; I’d shard by tenantId only if a single tenant can dominate or data exceeds comfortable single-node size.”
First five minutes
- List entities + critical transactions
- Name hottest queries
- Pick primary store with one sentence why
- Add cache/search only if the bottleneck needs it
- Call out consistency for the money path
Mini decision tree (say it in sixty seconds)
- Need multi-row ACID for money/inventory? → relational primary
- Mostly document fetch by id with flexible fields? → document OK
- Hot key / session / rate counters? → KV/cache beside primary—not instead
- Full-text relevance? → search derivative
- Deep multi-hop graph as the product? → consider graph; otherwise relational + careful modeling
Talk track:
“Primary is Postgres for orders and stock; Redis for session and hot product pages; OpenSearch for catalog query—each has one job.”
Freeze recovery
- Erase brand soup
- Write: entities → queries → primary DB
- Circle the one transaction that must be ACID
- Add the second store only with a named job (cache/search/analytics)
Soft practice note
Database choice is a communication skill under ambiguity. If you want a Chrome side panel to practice this system design database choice talk track under timed pressure, Luna Interview is built for calm rehearsal. Use it to structure your reasoning—not to invent fake war stories or to violate interview rules.
Day-of checklist
- [ ] Access patterns spoken before product names
- [ ] Transaction boundary named
- [ ] Primary store justified in one sentence
- [ ] Consistency level matched to the feature
- [ ] Second store has a job (not “also Redis”)
- [ ] Shard/replica only if scale requires it
Pick the store that fits the queries—then defend it cleanly.
Rehearse with an AI interview copilot, then browse commonly reported interview questions.