Back to System design
SQL vs NoSQL: When to Pick Which
easySQL databases store rows in tables with a fixed set of columns, and let you combine tables in a query. NoSQL is a label for several different kinds of database that give up some of that in exchange for other things.
ScalePostgres: 5-50K QPS/node; Cassandra/Dynamo: 1M+ scales linearly
StoragePostgres: TB single node; NoSQL clusters: PB-scale
Key Concepts
1
The honest version of this question is not which is better. It is what your data looks like, how you will
read it, and how much correctness you need.
2
Start by noticing how far one SQL database goes. A single well-tuned PostgreSQL server handles
thousands of writes and tens of thousands of reads per second, and holds several terabytes. Most products
never outgrow that.
Start by noticing how far one SQL database goes.
3
Reaching for a distributed database before you have measured means paying its costs — no joins, weaker
guarantees, an unfamiliar system to operate — for a scaling problem you do not have yet.
4
What SQL gives you. Joins, so you can answer questions you did not plan for. Transactions, so "take
money from one account and add it to another" either happens completely or not at all. Constraints, so bad
data is rejected at the door rather than hoped against. And a query language that outlives the team that
wrote the application.
What SQL gives you.
5
What the NoSQL families are for. They are not one alternative; they are four different tools.
What the NoSQL families are for.
6
Key-value (Redis, DynamoDB) one value per key, very fast, very simple
Document (MongoDB) whole records that vary in shape
Wide-column (Cassandra) huge write volumes, queries known upfront
Graph (Neo4j) relationships several hops deep
7
The question that decides it: what queries cannot you answer? In a wide-column database you design the
table around the query. A new question usually means a new table and copying the data into it. That is
fine when the product is settled and painful when it is still changing.
The question that decides it: what queries cannot you answer?
8
SQL is the opposite trade: harder to spread across machines, much cheaper to ask a new question of.
9
Most real systems use several. Orders in PostgreSQL because they need transactions. Sessions in Redis
because they are short-lived and hot. Search in Elasticsearch. The cost is that each one is another system
to run, and keeping them in step becomes your problem.
Most real systems use several.
10
When you do use two, do not write to both directly. Writes to two systems fail independently, and you
find out when they disagree. Write to one, and let the other follow by reading a change feed from it.
When you do use two, do not write to both directly.
11
What the interviewer is probing.
What the interviewer is probing.
12
1. "SQL or NoSQL for this?" Probing: whether you ask about the data first. Stalls: picking one
immediately. Moves up: what the queries are, whether they are known in advance, what must be
transactional, how much is written.
1. "SQL or NoSQL for this?"
13
2. "Why not use NoSQL for everything?" Probing: whether you know what you give up. Stalls: "It
scales better." Moves up: no ad-hoc queries, limited transactions, data copied into several places, and
a schema change that means rewriting data.
2. "Why not use NoSQL for everything?"
14
3. "Does PostgreSQL scale?" Probing: calibration. Stalls: "No, that is why we use NoSQL." *Moves
up:* one server handles thousands of writes a second and terabytes; replicas and partitioning stretch that
a long way.
3. "Does PostgreSQL scale?"
15
4. "You need a new query on a Cassandra table." Probing: query-first design. Stalls: "Add an
index." Moves up: the partition key decides what you can ask, so a new question usually means a new
table and a backfill.
4. "You need a new query on a Cassandra table."
16
5. "How do you keep two databases in step?" Probing: the cost of using several. Stalls: "Write to
both." Moves up: they fail independently; make one the source of truth and let the others follow it.
5. "How do you keep two databases in step?"
Components
- Schema design — driven by query patterns; in NoSQL design per-query, in SQL normalize first then denormalize for performance.
- Indexing strategy — primary key, secondary indexes, covering indexes, partial indexes (Postgres).
- Replication — sync, async, multi-master, leaderless quorum.
- Sharding — range, hash, directory, geo.
- Connection pooling — PgBouncer, ProxySQL, RDS Proxy.
- Migrations — Flyway, Liquibase, Atlas for schema evolution.
- Backups + PITR — base backups + WAL/binlog for point-in-time recovery.
- CDC pipeline — Debezium, Postgres logical decoding, Mongo change streams to derive read models.
What one relational instance handles
A single tuned PostgreSQL node (modern 16-32 core server, NVMe):
Simple point reads (indexed) tens of thousands / s
Write transactions 2,000 - 20,000 / s
(workload dependent)
Dataset size on one node single-digit TB, routine
Connection limit before pooling a few hundred
(use pgbouncer beyond that) Extending without sharding, in order of cost:
1. Indexes and query fixes often 10x, costs nothing
2. Connection pooling removes a common cliff
3. Read replicas scales reads linearly-ish
4. Partitioning (declarative) keeps big tables manageable
5. Move cold data out shrinks the working set
6. THEN shard, or move to a
distributed SQL engine Why this list matters in an interview
Candidates who jump to step 6 are designing for a scale they
have not measured and giving up joins and transactions to get
there. Saying "measure first, and here is what I would try in
order" is a stronger answer than naming a fashionable database.Family by access pattern
Key-value (Redis, DynamoDB, Memcached)
Shape: get/put by one key, very high rate, small values.
Gives: predictable single-digit ms, trivial horizontal scale.
Costs: no queries beyond the key; secondary indexes are bolted on. Document (MongoDB, DocumentDB)
Shape: records read and written whole, varying fields.
Gives: flexible schema, one round trip per record.
Costs: joins are manual; denormalised copies drift. Wide-column (Cassandra, HBase, ScyllaDB)
Shape: massive write volume, queries known in advance.
Gives: linear write scaling, tunable consistency, multi-region.
Costs: the partition key IS the query; new patterns mean new
tables; no joins; deletes are tombstones with their own
operational quirks. Graph (Neo4j, Neptune)
Shape: multi-hop relationship traversal.
Gives: queries that are trivial to express and fast to run.
Costs: another system to operate; poor fit for bulk analytics. Relational (PostgreSQL, MySQL)
Shape: anything where the questions are not fully known yet.
Gives: joins, transactions, constraints, ad-hoc queries.
Costs: horizontal write scaling is real work.Keeping two stores consistent
The naive approach and why it breaks
write to PostgreSQL
write to Elasticsearch <- fails: now they disagree, and
the failure is silent Outbox + change data capture
1. In ONE transaction: write the row and an outbox record.
2. A relay (Debezium, or a poller) reads the outbox / WAL.
3. It publishes to the other stores, retrying until success.
Result: one authoritative store, others eventually consistent,
no partial writes. Duplicate delivery is possible, so consumers
must be idempotent. What to make authoritative
The store with the transactional requirement. Orders live in
PostgreSQL; the search index is derived and rebuildable. Rebuildability as a design rule
Any derived store should be reconstructible from the
authoritative one. If you cannot rebuild your search index from
the database, it is not a derived store -- it is a second
authoritative store you did not plan for, and it will drift.