Fundamentals · 08
Databases: SQL vs NoSQL
The main database families, ACID versus BASE, and how to pick one from the access patterns rather than from fashion.
Picking a database is one of the most visible decisions in a design. The honest answer is usually “a relational database, unless an access pattern or scale requirement says otherwise”. What the interviewer wants is the reason.
The families
| Type | Data looks like | Strong at | Examples |
|---|---|---|---|
| Relational (SQL) | Tables with rows; related by keys | Joins, transactions, ad-hoc queries | PostgreSQL, MySQL |
| Key-value | key → value blobs |
Very fast lookups by key, simple scaling | Redis, DynamoDB |
| Document | JSON-like documents | Self-contained records with flexible fields | MongoDB, Firestore |
| Wide-column | Rows with many sparse columns, partitioned | Massive write throughput, time series | Cassandra, HBase, Bigtable |
| Graph | Nodes and edges | Relationship traversal (friends of friends) | Neo4j, Neptune |
| Search | Inverted index | Full-text search, filtering, ranking | Elasticsearch, OpenSearch |
| Time series | Timestamped points | Metrics, rollups, retention | InfluxDB, TimescaleDB |
ACID vs BASE
ACID is the transaction guarantee of relational databases:
- Atomicity: all of a transaction happens, or none of it does.
- Consistency: constraints (like “balance ≥ 0”) always hold.
- Isolation: concurrent transactions don’t see each other’s half-done work.
- Durability: once committed, data survives crashes.
BASE (basically available, soft state, eventually consistent) describes many distributed NoSQL stores. They stay available and fast across machines and regions, and replicas converge over time. See CAP and consistency.
SQL vs NoSQL
Relational (SQL)
- Strong consistency and multi-row transactions
- Joins and flexible queries you didn’t plan for
- Mature tooling, well understood
- Scales vertically well; read replicas for reads
- Sharding is possible but manual and painful
NoSQL
- Designed to scale horizontally from day one
- Flexible schema, good for evolving or varied data
- Very fast for the access patterns it was modelled for
- Limited joins and transactions (varies by product)
- Query patterns must be known up front; data is often duplicated
How to choose
- Is the data relational, and does correctness matter? Payments, inventory, bookings → SQL.
- Is it simple lookups by key at huge scale? Sessions, carts, feature flags → key-value.
- Is it write-heavy and append-mostly? Logs, messages, metrics → wide-column or time series.
- Is the core query a relationship walk? Social graphs, recommendations → graph.
- Do users search text? Add a search index next to your main database.
Normalisation vs denormalisation
- Normalised: store each fact once and join at read time. Writes are simple and consistent; reads need joins.
- Denormalised: copy data where it’s read, like keeping the author’s name on each post. Reads are fast; writes must update every copy. NoSQL designs denormalise heavily, around the queries.
Test yourself
Answer in your head, then click a card to check. All cards are in the Anki deck.