"SQL vs NoSQL" is the most common opening question in a data-store discussion, and the weak answer is a list of buzzwords ("SQL is structured, NoSQL scales"). Interviewers use it to see whether you can name the actual axes of the decision: the data model, the access patterns, the consistency and transaction guarantees you need, and how the data will be partitioned when it outgrows one machine. For a Backend or AI engineer the practical stakes are high: the store you pick decides which queries are cheap, which are impossible, and which invariants the database enforces for you versus which you must enforce in application code forever.
Family Unit of storage Query strength Typical examples
----------- --------------------- -------------------------- -----------------------------
Relational rows in typed tables ad-hoc joins, aggregates, PostgreSQL, MySQL, SQL Server
constraints, transactions
Document self-contained JSON/ fetch/update one aggregate MongoDB, Couchbase,
BSON document by id or indexed field (JSONB in PostgreSQL)
Key-value opaque value per key GET/PUT by exact key Redis, DynamoDB (base model),
Memcached
Wide-column row key -> sparse, range scans within one Cassandra, HBase,
partitioned column partition; very high write Bigtable
families throughput
Graph nodes + edges with multi-hop traversals Neo4j, Amazon Neptune
properties ("friends of friends")"NoSQL" only says "not the relational model"; it does not describe one design. The families differ more from each other than from SQL. What they share is a bias: most of them give up some combination of ad-hoc joins, multi-row transactions, and a rigid schema in exchange for a simpler way to partition data across many machines or a data shape that matches the application's objects. The lines are also blurring: PostgreSQL stores JSON with JSONB, MongoDB has supported multi-document transactions since version 4.0, and DynamoDB has transactional APIs. So the honest framing is "which properties does this workload need", not "which camp".
RELATIONAL (normalized) DOCUMENT (one aggregate per order)
customers(id, name) {
orders(id, customer_id, status) "_id": 100,
order_items(order_id, sku, qty, price) "customer": {"id": 1, "name": "Asha"},
"status": "PAID",
Read one order = 3 tables joined "items": [
Rename customer = 1 row updated {"sku": "BOOK", "qty": 2, "price": 300},
Bad customer_id = rejected by FK {"sku": "PEN", "qty": 5, "price": 20}
]
}
Read one order = 1 lookup, 1 disk/page region
Rename customer = update every embedded copy
Bad customer id = nothing stops itThe document model wins when the unit you read and write together is one self-contained aggregate: the whole order arrives in one read, there is no join, and the aggregate is a natural partition boundary if you shard by order or customer id. The relational model wins when the same facts are viewed from many angles ("revenue per customer", "orders containing a PEN", "which customers have no orders"), because each fact is stored once and the join engine can recombine it any way you ask. Embedding copies data, and every copy is something the application must keep consistent. That is the real cost of "schemaless": the schema did not disappear, it moved into application code, where the database can no longer enforce it.
Relational: design tables around the DATA, then write any query later.
-- new question next quarter? just write new SQL, add an index if needed.
DynamoDB / Cassandra style: design tables around the QUERIES you know today.
Query 1: "orders for customer C, newest first"
partition key = customer_id, sort key = created_at DESC -> one partition scan
Query 2: "orders by status across all customers"
not answerable efficiently unless you add a second table / index
keyed by status (and accept the extra write + eventual consistency of a GSI)
Trade: known queries become predictable-latency at any scale;
unknown future queries become a migration project.This is the axis interviewers most want to hear. A relational engine has a query planner that can choose among join orders and indexes at query time, so it tolerates questions you did not anticipate. Partitioned stores deliberately remove that flexibility: a query that does not include the partition key would have to ask every node, so they only expose access paths that stay within one partition. You get predictable latency and near-linear write scaling, but you must know your access patterns up front and duplicate data into one table per pattern. Choosing such a store for a product whose queries are still being discovered is a common and expensive mistake.
Single-node relational (PostgreSQL / MySQL):
ACID transactions over any rows, FK + CHECK + UNIQUE enforced by the DB.
Scale reads: replicas. Scale writes: bigger machine, then shard (hard, manual/extension-based).
Partitioned stores (Cassandra, DynamoDB, Mongo sharded):
Data is split by key across nodes from day one.
Consistency is often TUNABLE per request (e.g. quorum vs one replica);
atomic multi-item operations are limited or costlier, cross-partition
ones especially. Details differ per product - check the docs of the one you pick.
Distributed SQL (Spanner, CockroachDB, YugabyteDB):
SQL + transactions across partitions, paid for with higher write latency
(consensus / commit protocol across nodes).The old "SQL is ACID, NoSQL is BASE" slogan is too crude to defend. What is true is a difference in defaults and cost: a single-node relational database gives you multi-row transactions and constraints cheaply because everything is in one place, whereas any store that spreads data across machines makes cross-partition atomicity and cross-partition queries progressively more expensive, so many systems limit them. "SQL cannot scale" is likewise wrong: a single well-indexed PostgreSQL instance with read replicas carries a very large fraction of real products, and distributed SQL systems exist precisely because people wanted SQL semantics at scale. The honest statement is that scaling writes horizontally is hard for everyone, and NoSQL stores made it a first-class design constraint instead of an afterthought.
FOREIGN KEY, UNIQUE, CHECK, ACID transactions).JSONB, MongoDB has multi-document transactions (since 4.0), DynamoDB has transactions, and distributed SQL exists. Justify a choice by workload properties, not by camp.-- SQLite (portable SQL + JSON functions); run with: sqlite3 :memory:
.mode list
.headers on
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), status TEXT);
CREATE TABLE order_items (order_id INTEGER REFERENCES orders(id), sku TEXT, qty INTEGER, price INTEGER);
INSERT INTO customers VALUES (1,'Asha'),(2,'Ravi');
INSERT INTO orders VALUES (100,1,'PAID'),(101,2,'NEW');
INSERT INTO order_items VALUES (100,'BOOK',2,300),(100,'PEN',5,20),(101,'BOOK',1,300);
-- Relational: rebuild one order from three tables
SELECT o.id, c.name, o.status, i.sku, i.qty, i.price
FROM orders o JOIN customers c ON c.id=o.customer_id JOIN order_items i ON i.order_id=o.id
WHERE o.id=100;
-- id|name|status|sku|qty|price
-- 100|Asha|PAID|BOOK|2|300
-- 100|Asha|PAID|PEN|5|20
-- A question nobody planned for: total spent per customer (join + group by)
SELECT c.name, SUM(i.qty*i.price) AS total
FROM customers c JOIN orders o ON o.customer_id=c.id JOIN order_items i ON i.order_id=o.id
GROUP BY c.name ORDER BY c.name;
-- name|total
-- Asha|700
-- Ravi|300
-- Document style: the same orders as one JSON body per row
CREATE TABLE order_docs (id INTEGER PRIMARY KEY, doc TEXT);
INSERT INTO order_docs VALUES
(100,'{"customer":{"id":1,"name":"Asha"},"status":"PAID","items":[{"sku":"BOOK","qty":2,"price":300},{"sku":"PEN","qty":5,"price":20}]}'),
(101,'{"customer":{"id":2,"name":"Ravi"},"status":"NEW","items":[{"sku":"BOOK","qty":1,"price":300}]}');
SELECT id, doc FROM order_docs WHERE id=100;
-- id|doc
-- 100|{"customer":{"id":1,"name":"Asha"},"status":"PAID","items":[{"sku":"BOOK","qty":2,"price":300},{"sku":"PEN","qty":5,"price":20}]}
SELECT json_extract(doc,'$.customer.name') AS customer, json_array_length(doc,'$.items') AS n_items FROM order_docs;
-- customer|n_items
-- Asha|2
-- Ravi|1
-- Rename the customer: relational touches one row, the document copies go stale
UPDATE customers SET name='Asha K' WHERE id=1;
SELECT name FROM customers WHERE id=1;
-- name
-- Asha K
SELECT id, json_extract(doc,'$.customer.name') AS embedded_name FROM order_docs;
-- id|embedded_name
-- 100|Asha
-- 101|Ravi
-- The relational side also rejects an order for a customer that does not exist
PRAGMA foreign_keys=ON;
INSERT INTO orders VALUES (102,99,'NEW');
-- Runtime error: FOREIGN KEY constraint failed (19)Q: SQL vs NoSQL?
A: They are not two competing products but a spectrum of data models and trade-offs. Relational databases give you a rigid typed schema, joins, ad-hoc queries, constraints, and multi-row ACID transactions, and generally scale writes by moving up to a bigger machine before you have to shard. NoSQL stores (document, key-value, wide-column, graph) each optimise a narrower access pattern, and most trade away some joins, transactions, or schema enforcement in exchange for easier horizontal partitioning or a data shape that matches the application. The right answer names the workload: what are the queries, what invariants must hold, and how big will the data get.
Q: When would you choose PostgreSQL?
A: As the default source of truth when the data is relational, correctness matters (money, inventory, accounts), and the query set is not fully known in advance. It gives ACID transactions, strong constraints, a capable planner, rich indexing (B-tree, GIN, and so on), JSONB for semi-structured fields, and extensions such as pgvector for embeddings, so one system covers a lot. The cost is that scaling writes beyond one primary needs sharding or a distributed layer, and very high-volume, key-addressed, write-heavy workloads may fit a purpose-built store better.
Q: When would you choose a document database?
A: When your data is naturally a self-contained aggregate that is read and written as a unit (a product catalog entry, a content page, a user profile with variable attributes) and its shape varies between records or evolves quickly. Embedding avoids joins and makes the aggregate the natural shard boundary. The cost is duplicated data that you must keep consistent yourself, weaker cross-document integrity, and awkward many-to-many or reporting queries; if you find yourself simulating joins in application code, a relational model was probably the better fit.
Follow-up: "But NoSQL scales and SQL does not," right?
A: That is an oversimplification. A single properly indexed PostgreSQL or MySQL primary with read replicas handles a very large share of real workloads, and scaling reads is easy for both kinds. What differs is scaling writes and data size beyond one machine: partitioned NoSQL stores made sharding a built-in design constraint, at the price of restricted queries and transactions, while relational systems traditionally shard through application logic, extensions, or distributed SQL products. Say what you would scale and why.
Follow-up: How would you pick between DynamoDB and PostgreSQL for a new service?
A: I would start from the access patterns and consistency needs. If the service is a small number of well-known key-based lookups with very high, predictable traffic and little cross-entity querying, DynamoDB's partitioned model and managed scaling fit. If requirements are still fluid, involve joins, reporting, or invariants across several rows, PostgreSQL is the safer, more flexible default and is cheaper to change your mind about, because adding a query later is just SQL plus an index, whereas in DynamoDB it may require a new key design or table.
Follow-up: Can one system use both?
A: Yes, and most large systems do (polyglot persistence): one store is the source of truth, and other stores hold derived copies for their strengths, such as Redis for caching, a search engine for text queries, a column store for analytics, or a vector index for similarity search. The price is operational complexity and the need to keep the derived copies in sync, usually through change events, with clear ownership of which store is authoritative and how each copy is rebuilt if it is lost.
In a design interview, avoid announcing a database in the first minute. Derive it: enumerate the entities, the top three read paths and write paths, the invariants, and the expected volume, and only then map each requirement to a store. An e-commerce checkout might keep orders, payments, and inventory in PostgreSQL because those need transactions and constraints, serve the product catalog from a document store or a JSONB column because listings vary by category, cache hot product pages in Redis, index text in a search engine, and stream order events to an analytics warehouse. Every extra store must be justified by a requirement and must be labelled as either the source of truth or a rebuildable projection. For AI workloads the same reasoning applies: if you already run PostgreSQL, pgvector can avoid a second system for moderate embedding volumes, while a dedicated vector database becomes attractive once vector scale, latency, or index features exceed what the primary database should carry.
Choosing relational buys flexible querying, strong integrity, and simple transactional reasoning, and costs you an up-front schema, migrations for change, and a harder path to scaling writes across many machines. Choosing a NoSQL family buys either schema flexibility, a data shape that matches your objects, or easy horizontal partitioning, and costs you ad-hoc queries, cross-entity integrity, and often multi-item atomicity, so the application inherits duplication and consistency logic that the database would otherwise handle. It is the wrong choice whenever it is picked for fashion or for a scale the product may never reach: a document store chosen for "flexibility" and then used for highly relational data ends up re-implementing joins and constraints badly, and a relational database forced to hold a graph of deep traversals or a firehose of time-keyed writes will struggle. The reversible default is a relational database with a specialised store added only when a measured requirement demands it.