When do you choose SQL vs NoSQL?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
Choose SQL (PostgreSQL, MySQL) when data is relational, schemas are stable, and you need JOINs and ACID transactions. Choose NoSQL — document (MongoDB), key-value (Redis, DynamoDB), wide-column (Cassandra), or graph (Neo4j) — for flexible schemas, massive horizontal scale, or access patterns a relational model fights. Many systems use both.
Detailed explanation
SQL databases enforce a schema, model relationships with foreign keys, and answer ad-hoc queries with JOINs plus ACID transactions — the right default for orders, payments, inventory, anything where a half-written update is corruption. NoSQL is four families solving different pains: document stores (MongoDB) hold semi-structured JSON per record, ideal for catalogs where each item has different attributes; key-value stores (Redis, DynamoDB) are giant hash maps with single-digit-millisecond primary-key lookups, ideal for sessions, carts, and feature flags; wide-column stores (Cassandra) partition huge write volumes across nodes, ideal for time-series and event streams; graph databases (Neo4j) traverse relationships natively, ideal for social and recommendation data. NoSQL buys horizontal scale and schema flexibility at a cost: usually no JOINs, often eventual consistency rather than ACID, and queries limited to designed access patterns. Decision rule: need transactions, complex queries, or strong integrity? SQL. Single-aggregate reads, key-only access at huge scale, extreme write throughput, or relationship traversal? The matching NoSQL family. In practice teams use polyglot persistence — PostgreSQL for the transactional core, Redis for sessions and rate limits, Elasticsearch for search — each store doing what it is best at.
Example
-- SQL: transactional order + payment update atomically
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
INSERT INTO orders (customer_id, product_id) VALUES (7, 42);
COMMIT;
-- NoSQL document: varying attributes, no joins, read by key
-- db.products.findOne({ _id: 'laptop-9' })
-- { _id: 'laptop-9', ram_gb: 32, cpu: 'M3' } vs { _id: 'shirt-3', size: 'M' }Interview tip
Name all four NoSQL families with one example each, then state the decision rule (transactions and joins → SQL; scale or shape → NoSQL). Mentioning polyglot persistence signals production experience.
Common mistakes
- Claiming NoSQL is always faster or more scalable without naming the trade-offs.
- Not knowing the four NoSQL families.
- Forgetting NoSQL usually sacrifices JOINs and strong consistency.