SQL vs NoSQL

"The choice of data model is the most important decision you will make when designing a system." — Martin Kleppmann, Designing Data-Intensive Applications

Almost every system design starts with the same fork in the road, and interviewers know it: where does the data live, and in what shape? Get this right and joins, transactions, and scale fall into place naturally. Get it wrong and you spend the next two years bolting caches and queues onto a database that was never built for your access pattern. "SQL or NoSQL?" sounds like a religious war, but at the senior level it is a calm engineering decision — one you make by looking at your data and how you touch it, not at which logo is trending.

Core Concepts

Relational (SQL) databases — PostgreSQL, MySQL, SQL Server — store data in tables: rows and typed columns, defined by a schema you declare up front. Their superpower is the relationship: you split data into normalised tables (users, orders, products) and stitch them back together at read time with joins. They give you ACID transactions (Atomicity, Consistency, Isolation, Durability) — the guarantee that a transfer either fully happens or fully doesn't, even if the server loses power mid-write. And they speak SQL, a declarative query language that makes ad-hoc questions ("total revenue per region last quarter") trivial. Fifty years of engineering have made them extraordinarily reliable. Their historical weakness: they were designed to scale up (a bigger machine), not out (many machines).

NoSQL ("Not Only SQL") is not one thing — it is four different answers to "what if we drop some of the relational guarantees in exchange for scale, flexibility, or a better-fitting data model?" Here is the landscape:

The four NoSQL families, and when each earns its place

• Key-value (Redis, DynamoDB, Memcached) — the simplest model: a giant dictionary. You GET and SET by key in roughly O(1), and that's mostly it — no rich queries. Perfect for caching, session stores, rate-limit counters, and leaderboards. Redis holds data in memory, so reads land in sub-millisecond territory versus single-digit milliseconds for a disk-backed relational read.
• Document (MongoDB, Couchbase, DynamoDB) — stores self-contained JSON/BSON documents. There is no rigid schema, so each document can differ, and nested data (an order with its line items inside it) lives together and is read in one hit — no join. Great when your data is naturally hierarchical and your shape evolves fast, as in early-stage products or content management.
• Wide-column (Cassandra, HBase, Bigtable) — rows keyed by a partition key, with columns grouped into families; designed for massive write throughput across many nodes with no single master. This is the home of time-series data, event logs, IoT sensor streams, and activity feeds at hundreds of thousands of writes per second.
• Graph (Neo4j, Amazon Neptune) — data is nodes and edges, and traversing relationships ("friends of friends who like jazz") is a first-class, cheap operation. In a relational database the same query is a pile of self-joins that degrade fast with depth. Reach for graph when the connections are the product: social networks, fraud rings, recommendation graphs.

Why NoSQL arose

Through the 2000s, web-scale companies hit a wall relational databases weren't built for. A single Postgres box could be made enormous, but eventually you run out of "bigger machine," and scaling writes across many machines meant sharding by hand — painful, and joins across shards barely work. Meanwhile schemas that changed weekly fought against rigid ALTER TABLE migrations, and firehoses of write traffic (Amazon's cart, Facebook's feed) demanded throughput a single master couldn't sustain. NoSQL was the pragmatic answer: give up some of joins, strong consistency, and full ACID, and in return get horizontal scale, a flexible schema, and huge write throughput. That is the trade at the heart of the whole category — you are not getting scale for free; you are paying for it in guarantees.

The big comparison

This is the table to have in your head when the question comes:

The nuance senior engineers insist on

Here is the line that separates a mid answer from a senior one: the old dividing lines have blurred. "SQL doesn't scale" is a decade out of date. NewSQL systems — Google Spanner, CockroachDB, Vitess (which shards MySQL, and is what powers YouTube and Slack) — deliver horizontal scale with SQL and ACID transactions. Postgres now stores and indexes JSON (JSONB) nearly as flexibly as a document store. Meanwhile the NoSQL world has moved the other way: MongoDB added multi-document ACID transactions in 2018, and DynamoDB supports transactions too. The clean "SQL = consistent but small, NoSQL = scalable but loose" mental model is now a caricature. Choose on your data model and access patterns — not on hype, and not on a slogan from 2010.

How to choose — the decision framework

Don't start from the database. Start from the data:

1. Model your entities and their relationships. Draw them out.
2. List your access patterns — the actual reads and writes, with rough frequencies. This matters more than anything else.
3. Then match:
   • Relationships + integrity + unpredictable/ad-hoc queries → SQL.
   • Simple key lookups at massive scale, or a flexible/evolving shape, or a write firehose → the matching NoSQL family.
4. Expect to use more than one. Serious systems practise polyglot persistence — the right store for each job:

One e-commerce system, three stores, each chosen for its access pattern: Redis for hot ephemeral state, a relational database for the money and the relationships, Cassandra for the high-volume append-only feed. That is not indecision — it is maturity.

Common Mistakes

• Choosing by hype, not by data model. "We used MongoDB because it's web-scale" — for data that was deeply relational and never grew past one node. Match the model to the data, not to the conference talk.
• Believing "SQL doesn't scale." Vitess, CockroachDB, and Spanner run some of the largest systems on earth. If you say this in a senior interview unchallenged, you've dated yourself.
• Denormalising into NoSQL, then needing joins anyway. You embed everything to avoid joins, then discover you need to query across documents — and rebuild a worse join in application code.
• Ignoring access patterns until after you've picked the store. NoSQL rewards a schema designed around your queries and punishes the ones you didn't foresee. Design the queries first.
• Reaching for NoSQL to avoid schema design. "Flexible schema" is not "no schema" — it just moves the schema into your application code, where it's enforced less and breaks more quietly.
• Forgetting transactions when money is involved. If a wrong balance is a lawsuit, you want ACID. Don't trade it away for scale you don't have.

Interview Corner

Chapter Summary

• Relational (SQL) = tables, fixed schema, joins, ACID, strong consistency — the default for structured, related data that needs integrity and ad-hoc queries.
• NoSQL is four distinct models: key-value (O(1) lookups, cache/sessions), document (flexible nested JSON), wide-column (massive write throughput), graph (relationship traversals).
• NoSQL arose to win horizontal scale, flexible schemas, and write throughput — bought by giving up some joins, strong consistency, and full ACID.
• The line has blurred: NewSQL (Spanner, CockroachDB, Vitess) scales SQL horizontally, and modern NoSQL added transactions. "SQL doesn't scale" is outdated.
• Choose by data model and access patterns, not hype. Relationships + integrity + ad-hoc → SQL; simple lookups at scale / flexible shape / write-heavy → the matching NoSQL family.
• Serious systems use polyglot persistence — several stores, each for the job it's best at.
• Most apps aren't web-scale: a well-indexed relational database on one host is the right answer more often than the internet suggests.

Editorial reflection — the "SQL vs NoSQL" debate is loud because it's easy to have an opinion and hard to have the data. The engineers who navigate it well are almost boring about it: they draw the entities, list the reads and writes, and let the access patterns choose the store — often more than one. They know the flashy web-scale answer is the wrong answer for the 95% of systems that will never be web-scale, and they're unbothered by shipping "just Postgres" when Postgres is right. Fluency here isn't knowing every database. It's knowing which question to ask first — and it's always how will this data be used?