Data & Storage

Choosing Data Stores

Match access patterns to SQL, document, KV, or blob stores, and say what each is bad at.

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

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.

100%
ServiceStoreCacheOther

Walkthrough

Route by access pattern

Checkout write vs product search vs avatar upload

1 / 4

100%

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.

  1. Queries listed: upload video, stream by id, list user’s uploads, home feed, search titles, manage subscription.
  2. Object storage, raw .mp4 and transcoded renditions; SQL holds video_id, metadata, s3_key, owner, visibility.
  3. OLTP SQL, users, subscriptions, payments, video metadata; transactions for billing state changes.
  4. KV Redis, session tokens, maybe view counters with periodic flush; not sole store for subscription status.
  5. Search index, title/description/tags; indexer consumes CDC or queue from SQL; search misses rebuild from SQL.
  6. 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.

  1. Analytics: warehouse/OLAP fed by CDC, not heavy reporting on OLTP primary.
  2. 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

Store choice napkin

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:

  1. Entities + access patterns, read/write shapes, size, consistency.
  2. Primary store per workload, SQL for transactional core; object for media; KV for hot ephemeral; index for search.
  3. Source of truth, which store wins on conflict; how search/cache rebuild.
  4. What you refuse, blobs in row DB, analytics in OLTP primary without need.
  5. Cross-store links, foreign keys in SQL point to object keys, not embedded files.
  6. 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

1. List entities and the queries you must support. 2. Pick a primary store per workload (not one DB for everything by default). 3. Say consistency / transaction needs. 4. Call out what you will *not* do in that store (huge blobs in row DB, etc.). 5. Migration / dual-write only if the prompt forces evolution.

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

100%

Stronger sketch

Good

100%
  • 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

Ready to practise?

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

Practise with an open question

All systems lessons