Cruxly › System design › Foundations of Data Systems

Choosing a Datastore

access pattern first, product second

The question is never "SQL or NoSQL". It is: what are my queries, what consistency does each class of data need, and what shape is the write path. Answer those and the product is nearly determined. This is the short version: the full treatment, with worked case studies, is the lesson "How to choose a database" in the topic Databases: Choosing One.

Start from the queries

Write down every query the system performs and how often. Usually there are three or four that matter:

• Fetch the last 50 messages in a conversation, newest first — 60,000/s
• Insert a message — 20,000/s
• List a user's conversations ordered by last activity — 5,000/s
• Search messages by text — 100/s

Now the decisions make themselves. The first two are point lookups and appends on a single partition key — any partitioned key-value or wide-column store does that at this rate, and a relational primary would need sharding to keep up. The third is a small sorted list per user, ideally maintained on write rather than computed. The fourth is a different problem entirely — it needs an inverted index, so it is a separate system fed asynchronously, and a hundred per second means it can be seconds stale.

The conclusion writes itself: one system does not serve all four, and that is fine. Saying so is the mark of someone who has actually shipped something.

What relational actually gives you

Candidates dismiss relational databases too early. Say what they buy before you move on:

• Transactions across rows and tables. Genuinely hard to rebuild. If your invariant spans records — "the seat is assigned and the payment is recorded, or neither" — this is worth a great deal.
• Constraints the database enforces. Foreign keys, uniqueness, checks. Application-enforced uniqueness is racy by construction.
• Ad-hoc queries. You do not have to know the query at design time, which matters because you will not.
• Secondary indexes for free, kept consistent with the data.
• Operational maturity and a workforce that knows it.

The limits are real: a single primary caps write throughput, joins across shards do not work, and schema changes on very large tables need care. But a modern single primary handles roughly 10-20k durable writes per second and tens of terabytes, which covers the great majority of systems. "This is 3,000 writes/s — one Postgres primary with read replicas, and I'd revisit at 10×" is frequently the correct and highest-scoring answer.

The non-relational families

:::warn The document-store trap, stated precisely "Flexible schema" doesn't remove the schema — it relocates it into your application code, where nothing enforces it. Old documents keep their old shape forever, so every reader must handle every historical shape you have ever written. That cost arrives later, which is exactly why it gets underweighted at design time. :::

Wide-column is the usual right answer for chat and telemetry — time-ordered data under an entity key is precisely the shape it's built for.

Naming a real product per family is worth doing unprompted — it signals you've actually operated one of these, not just read the category name:

• Key-value — Redis, DynamoDB, etcd. Reach for it for sessions, caches, feature flags: anything where the whole query is "give me the value for this key," nothing more.
• Document — CouchDB, MongoDB. Reach for it when records genuinely vary in shape and are always fetched whole — a product catalog with wildly different attributes per category is the classic fit.
• Wide-column — Cassandra, HBase. Reach for it for time-ordered data under an entity: messages in a conversation, events per device, metrics per host.
• Graph — Neo4j, Amazon Neptune. Reach for it only when the query is genuinely a recursive traversal — "friends of friends within three hops" — that a relational join would have to reimplement badly.

:::key The historical tradeoff — and why it's no longer a clean split The classic framing: relational databases give rich queries and ACID transactions on one node; NoSQL stores scale out by giving some of that up.

The lines have blurred, so state the specifics instead of the stereotype:

• Many "NoSQL" stores are strongly consistent. HBase is consistent per row; DynamoDB and MongoDB offer strongly consistent reads and transactions.
• Distributed SQL exists: Spanner, CockroachDB, YugabyteDB and TiDB give ACID transactions that scale horizontally, at the cost of consensus latency on every write.
• Don't call this the CAP theorem. ACID's "C" (constraints hold after a transaction) isn't CAP's "C" (linearizability), and CAP is only about behaviour during a partition. The real trade here is coordination cost: cross-node transactions need consensus or two-phase commit, which costs latency.

:::

Polyglot persistence, and its cost

Using several stores is normal and correct. The thing to get right is which one is authoritative.

Pick one source of truth per piece of data. Everything else is a derived view — fed asynchronously, and rebuildable. For a chat system, messages live in the wide-column store; the search index and the analytics warehouse are both derived from the same change stream. If the search index is corrupted you rebuild it from the log. You never reconcile it by hand.

The cost is the sync, and this is where interviewers push:

:::warn Dual writes are a bug, not a shortcut Writing to the database and then the search index in application code means a crash between them leaves the two permanently inconsistent — and no amount of retry logic fixes the general case, because the crash can land after the retry logic itself is gone. :::

:::key Naming the outbox pattern unprompted is a strong senior signal Write once to the source of truth and derive everything else from its change stream — change data capture, or the outbox pattern: write the row and an outbox record in one transaction, and a separate relay publishes from the outbox.

Then state the lag: "the search index is 1–2 seconds behind, so a message is searchable almost immediately but not atomically — which is fine, because nobody searches for a message they just sent." :::

Schema and access shape

Normalise for writes, denormalise for reads. Normalised means one place to update and joins on read; denormalised means fast reads and every update touching many rows. Choose by ratio — at 1000:1 reads, denormalise without hesitation; at 1:1, normalise.

Precompute on write when the read is hot. A user's conversation list ordered by recency should be a maintained row updated on each message, not an aggregate computed per read. You are moving work from the frequent path to the rare one.

:::key The primary key is the performance decision

PRIMARY KEY (message_id)
→ "last 50 messages in a conversation" becomes a full scan

PRIMARY KEY (conversation_id, timestamp DESC)
→ the same query is one sequential partition read

Same data. Same store. Two orders of magnitude, decided entirely by which query you designed the key around. :::

@@ 1 | Never run a blocking ALTER on a large hot table Adding a column to a billion-row table is a four-step online process, not a statement:

add nullable → dual-write → backfill in batches → make it required.

Saying you'd sequence it this way is a small detail that reads as real experience, because it's the kind of thing you only learn by having taken a table down once.

How to say it in the interview

"Let me list the three queries this system actually runs, and their consistency needs — I'd rather pick the store from the access pattern than start from a product and justify it backwards."

Checklist

• Listed the actual queries with their rates before naming any product
• Said what relational buys, and why it is or is not enough here
• Matched each data class to a store family by access shape
• Named a real product per NoSQL family, not just the category
• Stated the scale-out-vs-transactions trade precisely, including distributed SQL
• Named one source of truth per piece of data
• Avoided dual writes; used change capture or the outbox pattern
• Stated the lag between the source of truth and each derived store
• Chose the primary key from the dominant query
• Kept large blobs in object storage with a reference in the database

Related lessons

• Reliable, Scalable, Maintainable: Measuring a System
• Clocks, Ordering and Partial Failure
• How Indexes Actually Work
• Replication and Consistency
• Partitioning and Sharding
• Transaction Isolation Levels