SQL vs NoSQL: Choosing the Right Database
Stop asking 'SQL or NoSQL'. Ask what your access patterns are, then let the answer pick the store - with a defensible reason you can say in one sentence.
Covers: Relational, document, key-value, wide-column, graph and time-series stores; access patterns; polyglot persistence
“SQL or NoSQL?” is the wrong question, and interviewers know it. NoSQL is not one thing - a key-value store and a graph database have nothing in common except not being relational. The useful question is: what are the access patterns, and which engine makes them cheap?
30-second answer
Relational for structured data with relationships, transactions and ad-hoc queries. Document for self-contained aggregates with a flexible schema and a natural read-whole-object pattern. Key-value for the fastest possible lookup by a single known key - sessions, caches, feature flags. Wide-column for enormous write volumes with predictable query patterns and no joins. Graph when the relationships themselves are the data and you traverse many hops. Time series for append-only measurements queried by time range with automatic downsampling. Most real systems use two or three of these deliberately.
| Family | Data model | Wins at | Loses at | Examples |
|---|---|---|---|---|
| Relational | Tables, rows, foreign keys | Transactions, joins, ad-hoc queries, constraints | Horizontal write scaling; rigid schema migrations | Postgres, MySQL |
| Document | JSON-like nested documents | Self-contained aggregates, flexible schema, fast whole-object reads | Cross-document joins; multi-document transactions are costly | MongoDB, DynamoDB, Couchbase |
| Key-value | Opaque value by key | Sub-millisecond lookups, huge throughput, simple sharding | Any query that is not 'by key' | Redis, Memcached, DynamoDB |
| Wide-column | Row key → sparse dynamic columns | Massive write volume, linear scaling, time-ordered data | Joins, ad-hoc queries, heavy updates and deletes | Cassandra, ScyllaDB, HBase |
| Graph | Nodes and edges as first-class | Multi-hop traversal, shortest path, recommendations | Bulk analytics; scaling beyond one machine is hard | Neo4j, Neptune |
| Time series | (metric, timestamp, tags) → value | High-rate ingest, range queries, downsampling, retention | Anything not indexed by time | TimescaleDB, InfluxDB, Prometheus |
| Search | Inverted index over documents | Full-text relevance, faceting, typo tolerance | Source of truth; consistency; transactions | Elasticsearch, OpenSearch |
The honest default
For most systems below roughly 10 TB and 10,000 writes per second, Postgres does everything. It has JSONB for document-style data, full-text search, arrays, geospatial via PostGIS, LISTEN/NOTIFY for lightweight pub/sub, and partitioning for time series. Choosing it and explaining when you would outgrow it is a stronger answer than reaching for a specialised store you do not need yet.
Polyglot persistence, and its cost
One product, three stores, on purpose
Each store handles the access pattern it is good at - and every arrow between them is a consistency problem you now own.
Flow
- ApplicationPostgres(write)
- PostgresElasticsearch(CDC / outbox)
- ApplicationRedis(cache)
- ApplicationTime-series store(emit)
Say these points
- Pick by access pattern, not by SQL/NoSQL label.
- Relational = query flexibility; non-relational = shape the data around known queries.
- Postgres is a legitimate default well past the scale most candidates assume.
- Justify with a constraint (throughput, access pattern, retention), never with 'it scales'.
- One source of truth; derived stores must be rebuildable and fed by CDC or an outbox.
Avoid these mistakes
- Treating NoSQL as a single category.
- Choosing a wide-column store for a workload full of updates and deletes.
- Dual-writing to two stores from application code.
- Using a search index as the source of truth.
- Adding a specialised store before the general one has actually failed.
Expect these follow-ups
- At what point would you move a table off Postgres, and to what?
- How do you keep a search index in sync with the source of truth without dual writes?
- When is a graph database genuinely necessary rather than a nice-to-have?
30-second answer
Denormalise when a read path is hot enough that the join cost dominates and the duplicated data changes rarely relative to how often it is read. The cost is that every copy must be updated on write, so you trade cheap reads for expensive, error-prone writes and the permanent risk of copies drifting apart. Normalise by default, measure, then denormalise the specific hot path - and make the update path a single owned code path, ideally driven by an event rather than scattered across the codebase.
| Normalised | Denormalised | |
|---|---|---|
| Read cost | Joins, several index lookups | One lookup, everything present |
| Write cost | One place to update | Every copy must be updated |
| Correctness | Single source of truth by construction | Copies can drift |
| Storage | Minimal | Duplicated |
| Schema change | One place | Every copy, plus a backfill |
The three legitimate reasons to denormalise
1 · The join is genuinely the bottleneck
Measured, not assumed. A five-table join on a 30,000-QPS endpoint where each join adds latency and lock pressure. Embed the two fields you actually display - not the whole entity.2 · The data is a natural aggregate
An order and its line items are almost always read and written together and never independently. Storing them as one document is not really denormalisation - it is choosing the correct aggregate boundary.3 · The store cannot join at all
In DynamoDB or Cassandra there is no join. Denormalisation is not an optimisation there, it is the data model. You design one table per access pattern, deliberately duplicating data.
-- Normalised: every feed render joins to users
SELECT p.id, p.body, u.display_name, u.avatar_url
FROM posts p JOIN users u ON u.id = p.user_id
WHERE p.feed_id = ? ORDER BY p.created_at DESC LIMIT 20;
-- Denormalised: copy only the two fields the feed renders.
ALTER TABLE posts
ADD COLUMN author_name TEXT,
ADD COLUMN author_avatar TEXT;
SELECT id, body, author_name, author_avatar
FROM posts WHERE feed_id = ? ORDER BY created_at DESC LIMIT 20;
-- The cost you just accepted: renaming a user must now rewrite their posts.
-- Make that ONE owned path, driven by an event - never scattered UPDATE statements.Say these points
- Normalise first; denormalise a measured hot path, not speculatively.
- Copy the minimum number of fields, not whole entities.
- Denormalising immutable or slow-changing data is cheap; fast-changing data is not.
- Drive updates from a single event-based path, never scattered writes.
- Build a reconciliation job - copies will drift.
Avoid these mistakes
- Denormalising before measuring the join cost.
- Multiple code paths updating the same duplicated field.
- Duplicating fast-changing values like prices or balances.
- No plan for detecting or repairing drift.
Expect these follow-ups
- A user changes their display name. Walk me through updating 50,000 of their posts.
- How do you backfill a new denormalised column on a live 500 GB table?
- When is a materialised view a better answer than denormalising the base table?
Showing 2 of 2 questions for sql-vs-nosql-choosing-a-database.
Check your understanding
4 questions · no sign-up, nothing stored
Hands-on challenge
Build it - this is what you talk about in a deep-dive round.
Pick the stores for a marketplace
A marketplace has product listings, full-text search, user sessions, order history, real-time inventory and clickstream analytics. Choose the persistence layer.
Requirements
- For each of the six workloads, name the store family and justify it with a specific constraint.
- Identify the single source of truth and mark every other store as derived.
- Describe how each derived store stays in sync, without dual writes from application code.
- State what breaks, and how badly, if each derived store is lost entirely.
- Argue the case for doing all six in Postgres, and say at what scale that stops working.
Stretch goals
- Design the reconciliation job for one denormalised field.
- Estimate the operational headcount cost of each additional store.