SQL vs NoSQL
7 pages · ~5 min✓ Reviewed
Builds on Concurrency Control.
Database Families and Mental Model
1. Five families
| Family | Data shape | Strength | Examples |
|---|---|---|---|
| Relational | Tables, fixed schema | SQL joins, strong transactions | PostgreSQL, MySQL |
| Document | JSON-like, flexible schema | Nested data in one fetch | MongoDB |
| Key-value | Key to value | get/put by key, fastest, simplest | Redis, DynamoDB |
| Wide-column | Partition key, sparse column families | Huge write volume | Cassandra, HBase |
| Graph | Nodes and edges | Fast multi-hop traversals | Neo4j |
2. Mental model: the query shape
- Joins + transactionsRelational
- One nested docDocument
- Lookup by keyKey-value
- Write firehoseWide-column
- Many hopsGraph
- Key-value: no queries on values, only the key
- Document: whole entity in one fetch, no join
- Graph: edges are stored data, so hops need no joins
bash
SET session:42 "alice" GET session:42
Key-value: the key is the only way in
3. Mistakes
- Searching a key-value store by value
- Expecting joins from a document or wide-column store
- Treating every family as a drop-in for SQL
Pick by access pattern
The family is chosen by how you read the data, not by brand.
CAP Theorem
1. The Three Guarantees
- Consistency: every read sees the latest write
- Availability: every request gets a non-error response
- Partition tolerance: keeps working when the network splits
2. CP vs AP
- Partitions are unavoidable, so the real choice is C or A during a partition
- With no partition you can have both
| CP | AP | |
|---|---|---|
| Under partition | Refuses or errors some requests | Answers, maybe stale |
| Afterwards | Stays consistent | Reconciles later |
| Examples | HBase, MongoDB (majority writes) | Cassandra, DynamoDB default |
3. Not the ACID C
Two different Cs
CAP 'C' is linearizability. ACID 'C' means constraints hold.
4. Interview Point
- Why not pick CA? Partitions happen, so you choose C or A
- Is CAP's C the ACID C? No: latest-write reads versus constraints
ACID vs BASE
1. ACID
| Letter | Guarantee |
|---|---|
| Atomicity | all or nothing |
| Consistency | constraints hold |
| Isolation | concurrent transactions do not interfere |
| Durability | committed data survives crashes |
2. BASE and quorum
- BASE: Basically Available, Soft state, Eventual consistency
- Replicas converge if writes stop
- A read right after a write may return old data
- N = replicas holding the data; W = acks needed for a write; R = replicas that answer a read
- R + W > N: every read set shares a node with the latest write set
- N=3, R=2, W=2: 2 + 2 = 4 > 3, so reads see the latest write
Node 1
in W set
has the write
Node 2
in W and R
overlap: latest
Node 3
in R set
may be stale
3. Which to use
| ACID | BASE | |
|---|---|---|
| Use for | money, inventory, orders | feeds, counters, carts, analytics |
| Puts first | correctness | availability, scale |
Tunable
Lower R or W for speed; keep R + W > N for fresh reads.
Replication and Sharding
1. Replicate vs Shard
| Replication | Sharding | |
|---|---|---|
| Data | Same data on many nodes | Different data per node |
| Scales | Availability, reads | Writes, storage |
2. Replication Modes
- Writeto leader
- Replicateto followers
- Follower readmay be stale
- Leader-follower: writes go to the leader, followers serve reads
- Replication lag causes stale reads
- Multi-leader and leaderless: several nodes accept writes
- Conflicts need resolution: last-write-wins,
vector clocks
3. Sharding
| Range | Hash | |
|---|---|---|
| Range scans | Supported | Broken |
| Load | Hotspots | Even |
- Consistent hashing: adding or removing a node moves only about 1/N of keys
- Bad shard key = hot shard: low cardinality, or monotonic id
Shard key
The shard key decides load spread and query cost.
Choosing a Database and Common Mistakes
1. Start from the workload
- Ask first: queries, read/write ratio, consistency need, data size and growth
- Default to PostgreSQL unless a clear need rules it out
- It also handles JSON columns
2. Typical fits
| Store | Fits |
|---|---|
| Redis | Cache, sessions |
| MongoDB | Catalogs, content |
| Cassandra | Time-series, event logs |
| Neo4j | Recommendations, fraud rings |
3. Mistakes
- NoSQL for scale you do not have, then needing joins and transactions later
- One database for everything
- Primary storePostgreSQL
- Synccopy changes
- Cache or search indexRedis, search
Polyglot persistence
One primary store plus a cache or search index synced from it.
Database Choice Cheat Sheet
1. Family to use case
| Family | Use for | Example |
|---|---|---|
| Relational | transactions, joins | PostgreSQL |
| Document | flexible entities | MongoDB |
| Key-value | cache, lookup | Redis |
| Wide-column | massive writes | Cassandra |
| Graph | relationships | Neo4j |
2. CAP and ACID vs BASE
| Means | Fits | |
|---|---|---|
| CP | consistent, may reject | money |
| AP | available, may be stale | feeds |
| ACID | correctness first | orders |
| BASE | scale and availability first | counters |
3. Replication vs sharding
- Replication = availability and reads
- Sharding = capacity and writes
- Shard key decides everything
4. Decision order
- Access pattern
- Consistency need
- Scale
- Pickdefault PostgreSQL
Default
Start with PostgreSQL; leave it only for a clear need.
Check yourself
N=3, W=2, R=1. Can a read return stale data?
- Yes: R + W = 3, not above N=3.
- The read may hit the one replica missing the write.
- Use R=2 so read and write sets overlap.
A CP store splits; a client reaches the minority side and writes. What happens?
- The write is refused or errors.
- Consistency is kept, availability is lost.
- Only the majority side keeps serving.
Orders are range-sharded by auto-increment id. Which shard is overloaded?
- The last one: every new id lands there.
- Hash the key to spread writes.
- Cost: range scans now hit every shard.
A 5k-user app picks Cassandra for scale, then needs joins and transactions. Mistake?
- It chose for scale it did not have.
- Start from access patterns; default to PostgreSQL.