SQL vs NoSQL
Relational databases, key-value, document, wide-column and graph stores: what each is good at and when to pick it.
Start with SQL
Relational databases (PostgreSQL, MySQL) store rows in tables with a schema, support joins, and give you ACID transactions. They handle far more than people expect, often tens of thousands of queries per second on one well-sized primary with replicas. Unless you have a specific reason, a relational database is the safe default.
The NoSQL families
| Type | Good at | Watch out for | Examples | Typical use |
|---|---|---|---|---|
| Relational | Joins, transactions, ad-hoc queries, strong consistency | Scaling writes past one primary needs sharding | PostgreSQL, MySQL | Users, orders, payments |
| Key-value | Very fast get/set by key, huge scale | No queries beyond the key | Redis, DynamoDB | Sessions, caches, counters |
| Document | Flexible JSON-like records, nested data | Joins and cross-document transactions are limited | MongoDB, Firestore | Product catalogues, profiles |
| Wide-column | Massive write throughput, time-ordered data | Design tables per query; limited ad-hoc queries | Cassandra, HBase, Bigtable | Chat messages, events, metrics |
| Graph | Traversing relationships many hops deep | Niche; harder to scale horizontally | Neo4j | Social graphs, recommendations |
| Search index | Full-text search, ranking, filters | Not a source of truth; kept in sync from the main store | Elasticsearch, OpenSearch | Search boxes, log search |
How to decide
- Need transactions or relationships between entities? Relational.
- Simple lookups by ID at huge scale? Key-value.
- Enormous write volume, accessed by key and time? Wide-column.
- Schema changes constantly and records are self-contained? Document.
- Most real systems use several: Postgres as the source of truth, Redis as a cache, Elasticsearch for search, object storage (S3) for files.
"NoSQL scales, SQL doesn't" is outdated. The real trade-off is flexibility of queries versus how easily the data splits across machines.
In the interview
- Pick the database per access pattern and say why ("messages are written constantly and read by conversation and time, so wide-column").
- Keep files in object storage and only their metadata in the database.
- Mention consistency needs: money stays in a database with transactions.