The two familiesCore idea
SQL (relational)
Rows in tables, a fixed schema, joins, secondary indexes and ACID transactions (a group of changes either all happen or none do, and readers never see a half-done state). PostgreSQL and MySQL are the standards. SQL shines when data is highly connected and correctness matters.
NoSQL (four kinds)
Key-valueRedis, DynamoDB
O(1) lookups by key. Great for sessions and caches.
DocumentMongoDB
JSON documents with a flexible schema. Great for catalogs and profiles.
Wide-columnCassandra, Bigtable
Sparse columns at huge scale. Great for time series and event logs.
GraphNeo4j
Queries over nodes and edges. Great for social graphs and fraud detection.
Decision criteria (the interview cheat sheet)How it works
Ask these five questions in order:
Need ACID transactions?
Moving money, inventory, bookings → SQL.
Need joins or ad-hoc analytics?
Reports, relationships → SQL.
Massive write scale (millions per second)?
Events, logs, telemetry → NoSQL (Cassandra).
Schema changing fast or unknown?
Product catalog, ML features → NoSQL (MongoDB).
Access mostly by key?
Sessions, URL mappings → NoSQL (Redis / DynamoDB).
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","actorBkg":"#e7f5ff","actorBorder":"#1e1e1e","noteBkgColor":"#fff9db","noteBorderColor":"#1e1e1e"}}}%%
graph TD
Q1{"ACID or joins<br/>critical?"} -->|yes| SQL["PostgreSQL / MySQL"]
Q1 -->|no| Q2{"huge write<br/>scale?"}
Q2 -->|yes| W["Cassandra"]
Q2 -->|no| Q3{"flexible<br/>schema?"}
Q3 -->|yes| D["MongoDB / DynamoDB"]
Q3 -->|no| K["Redis key-value"]
class Q1,Q2,Q3 yellow
class SQL blue
class W,D,K green
classDef orange fill:#ffd8a8,stroke:#1e1e1e,color:#1e1e1e
classDef blue fill:#a5d8ff,stroke:#1e1e1e,color:#1e1e1e
classDef green fill:#b2f2bb,stroke:#1e1e1e,color:#1e1e1e
classDef red fill:#ffc9c9,stroke:#1e1e1e,color:#1e1e1e
classDef violet fill:#d0bfff,stroke:#1e1e1e,color:#1e1e1e
classDef yellow fill:#ffec99,stroke:#1e1e1e,color:#1e1e1e
classDef grey fill:#e9ecef,stroke:#1e1e1e,color:#1e1e1eThe sharding reality checkScale
The deep reason NoSQL wins at scale: relational features break under sharding (splitting data across many machines). Joins become queries across machines, secondary indexes need to ask every shard, and transactions across shards get painful. NoSQL gives up those features upfront so it can scale out cleanly.
Real product examplesExample
| Product | Access pattern | Store |
|---|---|---|
| Chat | Append-only history, read by chatId + timestamp | Cassandra or DynamoDB |
| News feed | Huge fan-out writes, ordered reads | Redis for the hot feed, Cassandra for the archive |
| E-commerce orders | Strict inventory and payments | PostgreSQL with transactions |
| Product catalog | Many attributes, sparse fields | MongoDB documents |
| Search | Inverted index | Elasticsearch (its own category) |
Consistency trade-offsCompare
SQL defaults to strong consistency (every read sees the latest write). Most NoSQL stores default to eventual consistency (reads may lag briefly, then catch up). This maps directly to the CAP theorem: choose CP when stale reads are unacceptable, AP when staying available matters more than freshness. Say which side your pick lands on, and why.
Polyglot persistenceDesign
Strong systems rarely use one store. A video platform might keep subscriptions in PostgreSQL, watch history in Cassandra, hot sessions in Redis and search in Elasticsearch.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","actorBkg":"#e7f5ff","actorBorder":"#1e1e1e","noteBkgColor":"#fff9db","noteBorderColor":"#1e1e1e"}}}%%
graph LR
App["Video platform"] --> PG[("PostgreSQL<br/>subscriptions, billing")]
App --> CS[("Cassandra<br/>watch history")]
App --> RD[("Redis<br/>sessions")]
App --> ES[("Elasticsearch<br/>search")]
class App orange
class PG blue
class CS green
class RD red
class ES violet
classDef orange fill:#ffd8a8,stroke:#1e1e1e,color:#1e1e1e
classDef blue fill:#a5d8ff,stroke:#1e1e1e,color:#1e1e1e
classDef green fill:#b2f2bb,stroke:#1e1e1e,color:#1e1e1e
classDef red fill:#ffc9c9,stroke:#1e1e1e,color:#1e1e1e
classDef violet fill:#d0bfff,stroke:#1e1e1e,color:#1e1e1e
classDef yellow fill:#ffec99,stroke:#1e1e1e,color:#1e1e1e
classDef grey fill:#e9ecef,stroke:#1e1e1e,color:#1e1e1eCommon interview mistakesPitfalls
Summary: key decisionsSummary
Walk the criteria, justify the pick with the workload, name the consistency trade-off, and offer a polyglot option when access patterns differ.
Frequently asked questionsFAQ
Is MongoDB better than PostgreSQL for a new app?
For a new app with a mostly-relational schema, PostgreSQL is usually the safer default because it is flexible enough (JSONB) to cover document needs while keeping SQL and transactions. Reach for MongoDB only when the document shape is genuinely variable or write scale demands it.
Why is DynamoDB so common in system design answers?
DynamoDB is the flagship of the key-value family: single-digit-millisecond reads by partition key, automatic sharding, and enormous write throughput. Candidates reach for it as the default when the access pattern is simple and the scale claim is large, frequently paired with Redis for hot data.
Can SQL and NoSQL be combined for a feed feature?
Yes, and it is a classic pattern. Keep the canonical post records in the relational store so writes stay transactional, and hold the fan-out feed index in Redis for fast timeline reads. You get integrity where it matters and read throughput where the traffic actually concentrates.
Now explain sql vs nosql to me out loud
I'll give you a design problem and push on the trade-offs, like a real interviewer.