1Understand
The idea
Choosing data stores means matching access patterns to what each technology is good at, not picking one brand and forcing every workload into it. Interviews want you to list entities and queries first, then place OLTP SQL, KV, object storage, or search where each earns its keep.
The durable source of truth is separate from caches, CDNs, and search indexes. Redis can be fast; it is not a replacement for backup, transactions, or ad hoc relational queries unless you deliberately accept the loss.
Strong answers draw a data flow: OLTP commit → outbox/CDC → search index/object derivatives. Conflicts resolve toward the durable relational core unless the workload is truly KV-native.
Intuition
Start from queries, not logos.
List: entities (user, order, video), hot reads (by id, by user timeline), hot writes (append message), need for transactions (debit/credit), need for full-text search, and blob size (thumbnails vs 4K video).
OLTP relational (Postgres, etc.), rows with constraints, joins, ACID transactions. Good for accounts, orders, inventory you must not corrupt.
KV (Redis, Dynamo-style), key → blob, high QPS, simple access. Sessions, idempotency keys, rate-limit counters, not your only copy of paid orders without durability design.
Object storage (S3, GCS), large immutable media, cheap $/GB; not for transactional updates or listing millions of tiny rows efficiently.
Search index (Elasticsearch, OpenSearch), inverted index for text/filter facets; rebuild from primary or async ingest; not source of truth for money.
Polyglot persistence is normal: SQL core + object store for media + Redis cache/session + search index fed by
Consistency per store: SQL transactional; search eventually consistent; object store read-after-write within key after upload completes; session KV best-effort.
Migration: expand-contract, backfill jobs, dual-read, do not big-bang cutover without rebuild path from SQL.
Interview flow: list three hottest queries, assign each a store with one-line justification, draw OLTP → CDC → search/object arrows. Name conflict winner (SQL for money).
What you will be able to do
- Derive store choice from queries, not from brand names
- Separate durable source of truth from caches and blobs
- Explain when SQL transactions matter vs when a KV is enough
When this comes up
- Transactions
- Query patterns
- Blob / media
- Secondary indexes
2See it
Architecture sketch
One product, several stores
Match each workload to what the store is good at. Redis is not your ledger.
Walkthrough
Route by access pattern
Checkout write vs product search vs avatar upload
1 / 4
Place order: need ACID inventory + payment row. That is Postgres (or similar OLTP).
Read the walkthrough as text
Case, video-sharing product.
Upload 500MB files; home feed is personalized; users search by title; subscribers and billing must be correct.
- Queries listed: upload video, stream by id, list user’s uploads, home feed, search titles, manage subscription.
- Object storage, raw
.mp4and transcoded renditions; SQL holdsvideo_id, metadata,s3_key, owner, visibility. - OLTP SQL, users, subscriptions, payments, video metadata; transactions for billing state changes.
- KV Redis, session tokens, maybe view counters with periodic flush; not sole store for subscription status.
- Search index, title/description/tags; indexer consumes CDC or queue from SQL; search misses rebuild from SQL.
- Feed, often precomputed/graph/cache layer; source edges may live in SQL or graph DB, say you are not storing the video bytes in Postgres.
Anti-patterns avoided. Blobs in BYTEA; one Mongo for billing + search + blobs; “Redis is our database” with no snapshot.
- Analytics: warehouse/OLAP fed by CDC, not heavy reporting on OLTP primary.
- Graph/specialized: only when traversal is the dominant query; ledgers stay relational.
Phrase: “Postgres owns subscription state; S3 owns bytes; OpenSearch is rebuildable from CDC.”
Napkin math
OLTP / money → Postgres (ACID)
hot keys / sessions → Redis (ms, TTL)
blobs / media → object store (S3)
full-text / facets → search index (derived)
analytics → warehouse (async)
One workload, one primary store. Redis is not your ledger.
3Answer it
How to answer in an interview
When the prompt asks “what database” or describes mixed workloads:
- Entities + access patterns, read/write shapes, size, consistency.
- Primary store per workload, SQL for transactional core; object for media; KV for hot ephemeral; index for search.
- Source of truth, which store wins on conflict; how search/cache rebuild.
- What you refuse, blobs in row DB, analytics in OLTP primary without need.
- Cross-store links, foreign keys in SQL point to object keys, not embedded files.
- Evolution, dual-write only if migration story is required; prefer async backfill.
If everything fits one simple relational schema with modest scale, one SQL DB is fine, say why you are not over-sharding.
Say: “List entities and queries first.” · “Source of truth per workload.” · “Index is derived, rebuildable.”
Answer checklist
Tradeoffs & numbers
- SQL everywhere: simpler ops until blobs/search/scale hurt; know the break points (row size, index size, QPS).
- KV for sessions: fast TTL eviction; session loss on flush is acceptable if users re-login.
- Search beside SQL: better relevance and tokenization; eventual consistency and dual maintenance.
- Object store: cheap durable media; higher latency for small random reads, CDN in front.
- Strong transactions across stores: avoid 2PC in interviews; use SQL transaction + async publish to index/object.
- Vendor managed vs self-hosted: trade ops burden vs control, mention once if relevant to RPO.
- When NOT polyglot: CRUD app at 50 QPS, one Postgres until blobs/search prove pain.
- Dynamo/Cassandra: justify with access pattern and tunable consistency, not default.
- Cross-store 2PC: avoid; use SQL txn + async publish.
Kinds of questions (4)
Same pattern, different interview surfaces. Recognise which variant you are in before you answer.
OLTP relational core
Example. Checkout debits inventory and charges card in one flow. List entities and queries first, then place OLTP, KV, object storage, or search; say which store is canonical and how IDs link derivatives.
How. Postgres (or similar) with ACID transaction across related rows; constraints and foreign keys. One box per access pattern; arrows for CDC/outbox; never describe Redis as the financial source of truth.
KV for sessions / hot keys
Example. Session tokens and idempotency keys with TTL. List entities and queries first, then place OLTP, KV, object storage, or search; say which store is canonical and how IDs link derivatives.
How. Redis/Dynamo for key→value with TTL; durable orders stay in SQL; Redis restart = re-login acceptable. One box per access pattern; arrows for CDC/outbox; never describe Redis as the financial source of truth.
Object storage for media
Example. User-uploaded 200MB videos and image thumbnails. List entities and queries first, then place OLTP, KV, object storage, or search; say which store is canonical and how IDs link derivatives.
How. S3 keys in SQL metadata; serve via CDN; do not stuff gigabytes into OLTP rows. One box per access pattern; arrows for CDC/outbox; never describe Redis as the financial source of truth.
Search index alongside primary
Example. Full-text search on product titles and facets. List entities and queries first, then place OLTP, KV, object storage, or search; say which store is canonical and how IDs link derivatives.
How. Elasticsearch/OpenSearch fed by CDC or queue; SQL remains source of truth; rebuild index from SQL if needed. One box per access pattern; arrows for CDC/outbox; never describe Redis as the financial source of truth.
Common mistakes
Failure modes the AI coach watches for when you practise open questions.
One box vs right tool
Avoid. Everything in one SQL primary, including 200MB videos and fuzzy search.
Prefer. OLTP for transactions; object store for blobs; search as a derived index.
Weak sketch
Bad
Stronger sketch
Good
One DB for everything
Why it happens. Forcing blobs, search, and transactions into one hammer hides real access-pattern costs.
How it fails. Store 500MB videos in Postgres BYTEA and run full-text search with LIKE.
Fix. SQL for transactional metadata; object store for bytes; search index for text, link by ids.
Cache as source of truth
Why it happens. Ephemeral KV without backup loses data on flush or failover.
How it fails. Subscription state lives only in Redis with no SQL backing or persistence snapshot.
Fix. Durable source in SQL/object store; Redis for cache/session with rebuild path.
Ignores query pattern
Why it happens. Store choice without reads/writes leads to wrong fit (graph DB for ledgers, etc.).
How it fails. “We will use Cassandra” before listing how users load the home feed.
Fix. List entities and query shapes first; assign each workload a store with one-line justification.
4Practise
Concept check
A short quiz on this lesson before open questions. Optional, and it does not gate practice.
Open questions
- about 8 min
Transactional core
Design storage for checkout: debit inventory, create order row, charge payment in one logical flow. Why OLTP SQL, what lives in which tables, and what you deliberately do not store in Redis as source of truth?
- about 7 min
Sessions in KV
You need fast session lookup with TTL and millions of concurrent users. When is Redis (or Dynamo) appropriate, what exactly do you store, and what durability/recovery story do you give compared to SQL user accounts?
- about 8 min
Media in object store
Users upload photos up to 10MB and videos up to 500MB. Product metadata must be queryable by owner and date. Sketch what goes in S3 (or equivalent) vs Postgres and how the app serves a download URL.
- about 8 min
Search beside SQL
Users full-text search product titles and filter by category. Primary catalog lives in Postgres. Would you run search in SQL only? If not, what index do you add, how does it stay updated, and which store is source of truth on conflict?
Ready to practise?
Write an answer, get AI coaching, then revise. You are not building a real system.