Concept Drills: 18 Database Probes

Database questions appear in nearly every system design interview. These are the ones that actually get asked.

Types and guarantees

1. SQL or NoSQL? · L5 · Testing: whether they decide or default

Depends on three things: is the data structured, do I need multi-record ACID transactions, and does it fit on a node. If I need transactions across records, relational and stay there. If not, the access pattern picks the family — key-value for lookup by identity, document for varied self-contained records, graph for traversal, columnar for analytical scans. I'd also check whether the scale supposedly forcing me off relational is real and measured; usually one node handles far more than people assume.

2. What does ACID actually give you? · L4 · Testing: precision

Atomicity — all statements in a transaction commit or none do. Consistency — the database's own invariants hold before and after. Isolation — concurrent transactions don't interfere; the result matches sequential execution. Durability — a committed transaction survives failure. Together they handle dirty reads, lost updates, and phantom reads so application code doesn't have to.

3. Is ACID's C the same as CAP's C? · L5 · Testing: the classic confusion

No. ACID's C means the database's own invariants hold — constraints, foreign keys — within one logical database. CAP's C means replicas agree with each other. A system can be fully ACID on every node and completely divergent across them.

4. What's impedance mismatch? · L5 · Testing: why document stores exist

The relational model is flat tables; application code uses nested objects. Bridging them needs a translation layer that decomposes an object into rows on write and reassembles it on read. It's the honest reason to reach for a document database — one order lives in one document instead of being assembled from four tables — much more than "NoSQL is faster."

5. Columnar or wide-column — same thing? · Staff · Testing: a detail most people get wrong

Different. Columnar databases like Redshift and BigQuery store by column and optimize for analytics — scanning few columns across many rows. Wide-column stores like Cassandra and HBase group data into column families and optimize for high-throughput writes and semi-structured data. They share a name fragment and little else.

6. What do you give up moving off relational? · Staff · Testing: knowing the cost

Two things concretely. No standard query language, so each system has its own API and porting is hard. And weaker integrity — losing foreign-key constraints means referential integrity becomes application code that has to run everywhere and be remembered. Plus most relax strong consistency for availability. Those are all survivable; they just have to be chosen rather than inherited.

Replication

7. Synchronous or asynchronous replication? · L5 · Testing: the durability trade

Synchronous waits for replica acks, so replicas stay current and no acked write is lost — at the cost of write latency bounded by the slowest replica, and blocking if one is down. Asynchronous acks immediately and loses unreplicated writes if the primary dies. Most leader-based systems default to async, which quietly means durability isn't guaranteed. Semi-synchronous — wait for one replica — is the usual middle ground.

8. Compare the three replication models. · L5 · Testing: breadth

Single-leader: one node takes all writes, followers serve reads. Scales reads, not writes; needs failover. Multi-leader: several nodes accept writes, better write scalability and offline operation, but conflicts are possible. Leaderless: every node is equal, no failover to get wrong, consistency tuned with quorums.

9. Statement-based, WAL, or logical replication? · Staff · Testing: depth on a real mechanism

Statement-based ships SQL and re-executes it — simple, but nondeterministic functions like NOW() produce different values on each replica and diverge. WAL shipping ships low-level byte changes — deterministic and good for crash recovery, but tightly coupled to engine internals, so you can't easily run mixed versions. Logical shipping row-level changes is the flexible one: version-independent, and it can feed entirely different systems, which is what makes CDC into a warehouse possible.

10. The primary dies. What happens? · Staff · Testing: split brain

A secondary is promoted, manually or by automatic leader election. The danger with automatic is that secondaries infer death from silence, and silence is indistinguishable from a slow or partitioned primary — so you can end up with two primaries accepting writes. The mitigation is fencing: the new leader carries a higher epoch, and storage rejects writes carrying a stale one. Note also that above three nines you have no time for manual failover, so the availability target forces automation and therefore forces fencing.

11. Two leaders get conflicting writes. Resolve it. · Staff · Testing: conflict strategies

Three options. Conflict avoidance — route all writes for a record to one leader, which works until failover redirects traffic. Last-write-wins by timestamp — simple, and it silently loses data under clock skew, so only for disposable state. Custom logic — merge, or ask the user; a shopping cart merges cleanly because the union loses nothing. I'd pick per data type, not once for the system.

12. Why is all-to-all the preferred topology? · L5 · Testing: failure paths

In circular and star topologies, updates pass through intermediate nodes, so a single node failure interrupts the replication flow for others. All-to-all has no such dependency — more connections, and messages can arrive out of order along different paths, but no single node severs the mesh.

13. Derive r + w > n. · Staff · Testing: the quorum argument

It's a counting argument. With n=3, if a write must land on w=2 nodes and a read queries r=2, then 2+2=4 exceeds 3, so the sets can't be disjoint — they share at least one node, and that node has the newest write. Drop to w=1, r=2 and 3 is not greater than 3, so a read can land entirely on stale replicas. The numbers are a statement of intent: low w means never reject a write, low r means always answer fast, and you can't have both.

Partitioning

14. Key-range or hash-based sharding? · L5 · Testing: the locality trade

Exact opposites. Key-range keeps data sorted and makes range queries easy because you know which node holds a range — but uneven keys create hotspots. Hashing distributes uniformly and destroys locality, so range queries have to hit every partition. Choose from whether your dominant query is a range scan or a point lookup.

15. Why is hash mod n a trap? · L5 · Testing: rebalancing

Because changing n changes the modulus for nearly every key. Add one node to five and 1235 mod 5 = 0 becomes 1235 mod 6 = 5 — that key moves, and so does almost all the data. Consistent hashing or a fixed partition count gives a stable indirection layer so adding a node moves roughly 1/n of the data instead of all of it.

16. Compare the three rebalancing strategies. · Staff · Testing: depth

Fixed partition count — create many partitions upfront, assign several per node, and move whole partitions when nodes join. Simple, but the count is a permanent guess that caps your maximum cluster size. Dynamic — split partitions past a size threshold and merge when they shrink; adapts automatically but rebalancing under live traffic is complex. Proportional to nodes — each node owns a fixed number of partitions and splits existing ones on join; used by Cassandra. I'd also throttle whichever I pick, since unthrottled rebalancing under load can cascade into an outage.

17. Local or global secondary index? · Staff · Testing: the read/write trade

Local — each shard indexes its own data. Writes are cheap, reads are a scatter-gather across every shard, and the query's latency becomes the slowest shard's. Global — one index partitioned by term, so a lookup targets specific nodes. Reads are cheap, writes may touch several index nodes. Decide from the read/write ratio; read-heavy means global, and accept that it's usually updated async so the index can briefly lag the data.

18. How does a client find the right node? · L5 · Testing: request routing

Three options: connect to any node and let it forward, go through a routing tier, or make the client partition-aware and connect directly. The hard part in all three is keeping the map fresh as the topology changes, which is why systems use a consensus-backed coordination service like ZooKeeper. The routing map is small, rarely changes, and must be strongly consistent — a completely different workload from the data, which is why it gets its own control plane.

Self-check

The cheat sheet next compresses the chapter onto one page.