SQL vs NoSQL & scaling
DBMS · 10 interview questions
Relational databases store structured rows with a fixed schema and give you joins and ACID transactions. NoSQL is a loose family that gives some of that up in exchange for flexible schemas and easier horizontal scaling.
The families are document stores, key-value stores, wide-column stores, and graph databases. Each is shaped around an access pattern rather than being generally better.
CAP is the usual framing: under a network partition a distributed system must choose between consistency and availability. It's frequently misquoted as pick two of three — partition tolerance isn't optional in a real network, so the actual choice is what happens when a partition occurs. Modern relational databases also scale horizontally, so the old assumption that scale requires NoSQL is out of date.
SQL vs NoSQL & scaling interview questions
- What does the CAP theorem state?
- A distributed data store cannot simultaneously guarantee consistency, availability and partition tolerance. Under a partition it must sacrifice one of the first two.
- Why is 'pick two of three' a misleading reading of CAP?
- Because partitions are a fact of networks, not a design choice. You can't opt out of them, so the real decision is what the system does during one: refuse requests to stay consistent, or serve possibly-stale data to stay available.
- Why they ask: Saying this well separates people who've thought about distributed systems from people who've memorised a triangle.
- Name the four families of NoSQL database.
- Document (MongoDB), key-value (Redis, DynamoDB), wide-column (Cassandra, HBase) and graph (Neo4j). Each targets a different access pattern.
- What does BASE mean?
- Basically Available, Soft state, Eventual consistency — the counterpart to ACID. It accepts temporary inconsistency in exchange for availability, on the promise that replicas converge once writes stop.
- What is eventual consistency?
- A guarantee that if writes stop, all replicas eventually converge on the same value. It says nothing about how long that takes, so a read right after a write may return a stale value.
- What's the difference between vertical and horizontal scaling?
- Vertical means a bigger machine — simple, but bounded by the largest server you can buy and a single point of failure. Horizontal means more machines, which is unbounded but introduces partitioning, replication and consistency problems.
- What is sharding?
- Splitting rows across databases by a shard key, so each holds a subset. It scales writes, but cross-shard joins and transactions become hard or unavailable, and a badly chosen key creates hotspots.
- How does replication differ from sharding?
- Replication copies the same data to several nodes — for read scaling and failover. Sharding splits different data across nodes — for write and storage scaling. They're usually combined.
- When does NoSQL genuinely fit better?
- Schemas that vary per record, very high write throughput on simple access patterns, or data that is naturally a graph. If you need multi-row transactions and ad-hoc joins, relational remains the better tool.
- Does choosing SQL still mean giving up horizontal scale?
- No. Distributed SQL systems — Spanner, CockroachDB, Vitess — offer horizontal scaling with transactions, so the old trade-off between relational semantics and scale is much weaker than it was.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start free for 7 days