Part 6 — Data at work › Chapter 43

NoSQL, and when relational is the wrong answer

Document, key-value, column-family, graph — the honest trade, and how to say it in an interview.

From the advert

“Competenze sui database piu richieste: SQL Server, Oracle e MongoDB” — MongoDB appears in Italian adverts constantly, usually beside SQL Server. They want somebody who is not frightened of it and does not evangelise it either.

Four families, four different problems

Family	Model	Genuinely good at	Examples
Document	Nested JSON keyed by id	Aggregates read and written whole; a schema that varies per document	MongoDB, Couchbase
Key–value	A hash table	Caching, sessions, rate limiting, queues — sub-millisecond by key	Redis, Memcached
Column-family	Wide rows partitioned by key	Enormous write volume, time-series, linear horizontal scaling	Cassandra, ScyllaDB, HBase
Graph	Nodes and edges as first-class	Traversals of unknown depth: "who influences whom, six hops out"	Neo4j, TigerGraph

🧒 Simple

A relational database is a filing system where everything is cross-referenced, so you can ask almost any question — but somebody had to design the filing system first. The others are each very good at one shape of question and much worse at the rest. The skill is knowing which shape you actually have.

CAP, stated honestly

🎓 What CAP does and does not say

In the presence of a network Partition, you must choose between Consistency and Availability. That is the whole theorem, and two things follow that are usually got wrong:

“Pick two of three” is a misreading. Partitions are not a choice — networks fail. So the real choice is only between C and A, and only during a partition.
The interesting trade is the rest of the time, which CAP says nothing about. That is PACELC: if Partition then A or C, Else Latency or Consistency. Almost every practical decision you will make is the "else" half — how much latency you are willing to pay for a consistent read — and mentioning PACELC unprompted is a strong signal.

⚠ “NoSQL scales, SQL doesn't” is out of date

A single well-indexed PostgreSQL or SQL Server instance on modern hardware handles workloads that would have needed a cluster in 2012 — tens of thousands of transactions a second, tables in the billions of rows. Meanwhile MongoDB acquired multi-document ACID transactions (4.0) and a schema validator, PostgreSQL acquired jsonb with indexing, and both moved toward the middle. Most systems that adopted NoSQL "for scale" never approached the limit of a relational database, and paid for it in lost joins and hand-written consistency.

When relational is genuinely the wrong answer

✔ Four honest cases

Caching and ephemeral state. Sessions, rate-limit counters, a leaderboard. Redis is better at this than any relational database and it is not close. Do not put a cache in your OLTP database.
Write volume beyond one machine, with a natural partition key. IoT telemetry, event streams. Cassandra scales writes linearly; you give up joins and ad-hoc queries, which for that workload you did not need.
Deep, variable-length graph traversal. "Everyone within six degrees" is a recursive CTE that gets exponentially worse; a graph database stores adjacency directly and it is what it was built for.
Genuinely per-document schema. A product catalogue where every category has different attributes, defined by the merchants. That is what documents are for — although a jsonb column inside PostgreSQL covers a surprising amount of it (chapter 18) while keeping transactions and joins.

What you give up, itemised

You lose	Which means
Joins	You denormalise, and now the same fact is in four documents. Updating it is your job, and it is not atomic
Constraints	Referential integrity moves into application code, where every bug bypasses it
Ad-hoc queries	The access pattern must be known at design time. A new question can mean a new collection and a backfill
A declarative language everyone knows	SQL is forty years old and universally understood. That is worth a great deal
Mature tooling	Backup, restore, monitoring, migration and BI integration are all more mature on the relational side

Polyglot persistence, and its real cost

"Use the right tool for each job" is correct and incomplete. Every additional data store is another thing to back up, monitor, secure, patch, staff and — the expensive one — keep consistent with the others, with no transaction spanning them. That is the dual-write problem again (chapter 24), now permanent. The honest position is that a second store must earn its operational cost, and the bar is higher than it sounds.

How to answer this in an interview

✔ A position, with reasons, and no contempt

“I default to relational, because most business data is relational and transactions, constraints and ad-hoc queries are genuinely hard to give up. I reach for Redis for caching and ephemeral state without hesitation. I would use a document store where the schema really is per-document, or a column store where write volume exceeds one machine and there is a natural partition key. What I would want to be explicit about is what each extra store costs operationally, because keeping two systems consistent has no transactional answer.”

⚠ The two answers that lose the room

“NoSQL is a fad” — you will be interviewed by people running MongoDB, and it reads as not having used one. “NoSQL scales and SQL doesn't” — a decade out of date, and the follow-up question ("scales in what dimension?") is unanswerable if you believed it.

Document, key–value, column-family and graph solve four different problems. Name the shape before naming the product.
CAP is a choice between consistency and availability during a partition; partitions are not optional.
PACELC covers the rest of the time — latency versus consistency — which is where most real decisions live.
A single well-indexed relational instance handles far more than the 2012 folklore suggests.
MongoDB has transactions and PostgreSQL has indexed JSON. The two families moved toward each other.
Redis for caching and ephemeral state is the least controversial NoSQL choice there is.
Giving up joins means denormalising, which means the same fact in several places and no atomic way to update it.
Document stores require knowing the access pattern at design time; a new question can cost a backfill.
Every extra store must earn its operational cost, and keeping two consistent has no transactional answer.

Interview questions this chapter answers

When would you choose MongoDB over SQL Server?

When the data genuinely is per-document — a catalogue where each category has different attributes defined by merchants — and the access pattern is fetch-and-store the whole aggregate by id. What I would weigh against it is losing joins and constraints, and I would check first whether a jsonb or JSON column inside the relational database covers it, because that keeps transactions and ad-hoc queries.

Explain the CAP theorem.

During a network partition you must choose between consistency and availability. The common "pick two of three" phrasing is misleading, because partitions are not something you choose. The more useful extension is PACELC, which adds the normal case: when there is no partition, the trade is latency against consistency — and that is the trade most systems are actually making.

Is NoSQL faster than SQL?

For its specific access pattern, often yes — a key–value store returning a document by id will beat a join. For anything else, usually no, and the comparison is not like for like: the relational database is enforcing constraints and offering ad-hoc queries the other is not. "Faster" is only meaningful once you name the operation.