Module 04 · Lesson 01

Choosing a datastore without naming brands first

Ask about access patterns, then about consistency, then pick a family — the product name is the last decision.

Intermediate 9 min read Updated 2026-11-01

What you will be able to do

Derive the store family from access patterns rather than habit
Compare relational, document, wide-column, and key-value stores on their trade-offs
Explain when a single relational database is the correct answer
Describe polyglot persistence without hand-waving

Four questions before any product name

What are the reads? Point lookup by id, range scan by time, full-text search, or graph traversal. Each points at a different family.
What are the writes? Rate, size, and shape. Append-only events behave differently from in-place updates to large documents.
What must never be wrong? Money, inventory, and uniqueness need transactions; a view counter does not.
How will it grow? Can the data be partitioned by a key you already have (user id, tenant, time)?

Make the list visible

Write the access patterns on the board before choosing. It converts a taste argument into an engineering one, and it gives you a place to point when the interviewer asks why not Postgres.

The families

Family	Strength	Cost	Fits
Relational (SQL)	Joins, constraints, multi-row transactions, flexible secondary access	Scaling writes needs partitioning; very large tables need care	Billing, orders, users, anything with invariants
Document	Whole-object reads and writes, flexible nesting, easy sharding by document id	Weak cross-document transactions, joins in application code	Catalogs, profiles, CMS content, per-user settings
Wide-column	Enormous write volume, partition by key, predictable latency	You must design the table around the query; no ad-hoc joins	Events, messages, time-series at scale, feeds
Key-value	Microsecond lookups, trivial horizontal scale	No queries beyond the key	Sessions, caches, leaderboards, feature flags
Search engine	Relevance ranking, tokenised text, faceting, aggregations	Not the source of truth; reindexing is a pipeline	Product search, log search, autocomplete

Why one relational database is often the right answer

A single well-tuned Postgres instance handles tens of thousands of simple queries per second and terabytes of data. It gives you transactions, foreign keys, and flexible indexing, so changing your mind about an access pattern later is a migration rather than a rewrite. In a 45 minute interview, reaching for five systems when one would do reads as pattern-matching, not engineering.

Simple indexed read

0.1-1 ms

In memory or SSD

Comfortable single-node write rate

1-10k/sec

Batched commits help enormously

Practical table size before partitioning

Hundreds of GB

Depends on working set fit in RAM

Read replicas

3-5 typical

Each adds a copy to keep consistent

The signal to move on is specific, not stylistic: the working set no longer fits in memory, writes exceed what one primary can absorb, or the query you need (full-text, ranking, time-series aggregation) is not what the engine is built for.

Denormalisation is a transaction you now owe

Once you store a copy of a user's name inside a post row to avoid a join, that copy can drift. Every denormalised field needs an owner and an update path: written by the same transaction, updated by a change stream, or rebuilt by a job. Say which one you chose, because the interviewer's next question is always what happens when the name changes.

Instead

MongoDB because the data looks like JSON

Do this

Document store because the app reads and writes a whole object at once and rarely queries across objects

Instead

Cassandra because it needs to scale

Do this

Wide-column because writes are append-heavy and every query is keyed by user id and time

Instead

A separate service for every entity

Do this

One database until a measured limit forces a split, then split along the busiest boundary

Polyglot persistence, honestly

Real systems do use several stores, but each one earns its place: Postgres for orders, Redis for sessions and hot counters, Elasticsearch for product search, object storage for media, and a log store for events. The cost is real — more backups, more failure modes, more places to get consistency wrong. When you propose a second store, name the pattern it serves and the pipeline that keeps it fed.

orders        -> Postgres   (transactions, money)
session       -> Redis      (TTL, microsecond reads)
product_search-> Elasticsearch (ranking, facets)
images        -> S3 + CDN   (cheap bytes, range reads)
click_events  -> wide-column log (append heavy, keyed by day)

Worth remembering

Start with the query, not the product. Write down the five access patterns before choosing anything.
Relational databases are not legacy: joins, transactions, and constraints buy correctness you would otherwise reimplement.
Denormalising is the price of scale on write-heavy systems, and keeping the copies consistent becomes a design problem.
Multiple stores mean multiple failure modes — every extra store needs a reason in one sentence.

Checkpoint

Answer all 3 questions to finish the lesson.

1. Which question should come first when choosing a datastore?

The access pattern determines the family. Product choice follows from it, and saying so shows the decision is derived rather than habitual.

2. You denormalise a user's display name into every post row. What must you now specify?

Every duplicated field needs an owner and a propagation mechanism — same transaction, change stream, or rebuild job. Otherwise it silently drifts.

3. A wide-column store is the right choice primarily when:

Wide-column engines scale by partitioning on a key you supply and expect you to design tables per query. Ad-hoc joins and constraints are where relational stores win.

← PreviousProtecting the API from its own users Next →Schemas and the index that saves the query