Start here: 'not only SQL'#
TL;DRthe 30-second version
- A relational database stores data as tables with a fixed schema. You normalize (split data into tables with no duplication), join those tables at read time, and wrap writes in ACID transactions. The payoff: you can ask any question later, even ones you didn't design for.
- The cost of that flexibility: joins and cross-row transactions are hard to run across many machines, so scaling writes horizontally (splitting one dataset across many servers) is painful, and changing the schema on a huge table is slow.
- A NoSQL store drops those guarantees on purpose. It's shaped around one access pattern — a document you fetch by id, a row you look up by key, a graph you traverse — so it scales out cheaply and takes flexible data without a fixed schema.
- The decision is never 'which is faster.' It's which axis your product forces: known queries vs ad-hoc, relationships vs standalone records, strong transactions vs eventual consistency, one machine's worth of data vs a firehose, a stable schema vs one that changes weekly.
- Most real systems use both — a relational database for the core transactional data and one or more NoSQL stores for the parts that don't fit. That's called polyglot persistence, and 'we'd use both, here's the split' is usually the strongest answer.
Picture the data behind an online store. There are customers, orders, products, and payments, and they relate to each other: one customer has many orders, one order has many line items, each line item points at a product. A relational database is built for exactly this. You keep each kind of thing in its own table, store every fact once, and stitch them back together at read time with a join (a query that matches rows across tables on a shared key). Because the data is stored in a clean, normalized shape, you can answer a question nobody anticipated — 'total revenue per product category last March' — without having pre-planned it.
Now picture a different job. You're storing a billion sensor readings a day, or the session for every logged-in user, or a social graph of who-follows-whom with hundreds of millions of edges. There are no ad-hoc reporting questions here — you know exactly how the data gets read, and you need it to be fast and to spread across many machines. Forcing this into a single relational database with joins and transactions fights the grain of the workload. This is the space NoSQL was built for: give up the general-purpose querying, and in exchange get a store that's shaped to your one access pattern and scales out.
What each side actually gives you#
Start with what a relational database hands you, because that's the baseline everything else is measured against. First, a schema: every row in a table has the same columns, each with a type, enforced by the database. Second, normalization: you store each fact in exactly one place and reference it by key, so there's no duplicated data to keep in sync. Third, joins: because the data is split across tables, the database recombines it at query time on demand. Fourth, ACID transactions — a group of writes that is Atomic (all or nothing), Consistent (leaves the data valid), Isolated (concurrent transactions don't corrupt each other), and Durable (survives a crash). Together these let you write a query for a question you didn't foresee and trust the answer.
A NoSQL store trades some or all of that away for scale and flexibility. Instead of splitting data across tables and joining it, you usually denormalize: you store the data already shaped the way you'll read it, duplication and all, so a read is one lookup instead of a join. Instead of a fixed schema, most NoSQL stores are schema-flexible — two records in the same collection can have different fields, so you can add a field without migrating a giant table. And instead of ACID across the whole dataset, many NoSQL stores offer weaker guarantees, often summarized as BASE (Basically Available, Soft state, Eventual consistency): a write may take a moment to become visible everywhere, but the store stays available and scales. You give up the general query engine; you get horizontal scale and a schema that bends.
Now the four NoSQL families, each defined by the access pattern it's shaped for. Read the family by asking 'how do I get my data back out?' — that question, not storage internals, is what makes one fit.
| Family | A record is… | Read pattern it's built for | Typical stores |
|---|---|---|---|
| Document | A nested JSON-like document, fetched whole by id | Get / update one self-contained object (a user profile, an order with its items) | MongoDB, Couchbase |
| Key-value | An opaque blob under a single key | Get / set by exact key, extremely fast, no querying inside the value | Redis, DynamoDB, Riak |
| Wide-column | A row of columns, keyed and partitioned for scale | Huge write volume and lookups by a known key, spread over many machines | Cassandra, HBase, Bigtable |
| Graph | Nodes joined by edges (relationships are first-class) | Traverse relationships — friends-of-friends, shortest path, recommendations | Neo4j, Neptune |
Notice what unites them: each is fast at exactly one thing and mediocre or unable at the others. A key-value store is unbeatable at get-by-key and can't query inside the value at all. A graph database makes a friends-of-friends traversal trivial, the same query that would be a nightmare of self-joins in SQL, but it's the wrong tool for reporting. Choosing NoSQL isn't one decision — it's committing to an access pattern and picking the family that serves it.
The five axes that actually decide it#
There isn't one question that picks SQL or NoSQL — there are five, and different products land on different sides of each. The skill is naming which axis dominates for your workload, because that's the one that forces the choice. Here they are, each phrased as the question to ask.
- Access patterns — do you know your queries in advance, or not? If the queries are ad-hoc and will keep changing ('let product analysts slice the data any way they want'), you need SQL's general query engine. If there's a small, fixed set of reads you can name up front, NoSQL lets you shape the data to serve exactly those.
- Relationships and joins — is the data highly connected, or mostly standalone? Many-to-many relationships you constantly recombine (orders, products, customers) are what joins and normalization are for. Records that are self-contained (a document you fetch whole) or a graph you traverse point away from a relational join model.
- Transactions and consistency — do you need ACID, or is eventual consistency fine? Money, inventory, and bookings need a transaction that's atomic and immediately consistent. A like count, a view counter, or a cached feed can be eventually consistent, and paying for ACID there just buys you a scaling ceiling you didn't need.
- Scale — how much data, and how fast are the writes? One machine's worth of data with a moderate write rate fits comfortably in a single relational database. A write firehose or a dataset far bigger than one server pushes you toward a store built to partition (split data across machines) from day one.
- Schema flexibility — is the shape stable, or does it change constantly? A stable, well-understood schema is a strength in SQL, where the database enforces it. If records are genuinely heterogeneous or the shape changes every sprint, a schema-flexible document store avoids painful migrations on a huge table.
PredictYou're building the core ledger for a payments product: every transfer moves money between two accounts, and the two balance updates must both happen or neither. Reads are simple lookups by account. Which family, and which axis forced it?
Hint: Two writes that must both happen or neither — what's that called, and which store guarantees it?
Relational (SQL). The forcing axis is transactions and consistency: moving money between two accounts is the textbook case for an ACID transaction — both balance updates commit together or neither does (atomic), and no one can ever read a state where the money left one account but hasn't arrived in the other (isolated, immediately consistent). Eventual consistency here isn't a minor staleness, it's money appearing or vanishing. The other axes don't override it: the query pattern is simple and the data volume for a ledger is usually well within one machine's reach, so there's no scale pressure strong enough to justify giving up ACID. Note the shape of the answer — you don't say 'SQL is safer,' you name the axis (cross-account atomicity) and show it dominates the others.
Matching the store to the workload#
Once you've named the dominant axis, the fit usually falls out. A few rules of thumb that hold up under questioning:
- Core transactional data with relationships and ad-hoc reporting (orders, users, payments, inventory): relational. Joins, ACID, and a query engine you can point at any question are the whole reason this database family exists.
- A self-contained object you fetch and update whole (a user profile, a product catalog entry, a CMS page): document store. One read returns the whole thing with no joins, and the schema can vary record to record.
- Get / set by a known key at very high speed (a session store, a cache, a rate-limit counter, a feature flag): key-value store. There's no querying inside the value, and you don't need any.
- A write firehose or a dataset far bigger than one machine, read by a known key (time-series metrics, event logs, activity feeds, messaging): wide-column store. It's built to partition across machines and absorb writes a single relational database can't.
- Relationships are the query — you traverse them constantly (social graphs, recommendations, fraud rings, dependency graphs): graph database. What would be layers of self-joins in SQL is a native traversal here.
The trap this table sets is thinking you must pick one. You almost never do. The dominant pattern in real systems is polyglot persistence: keep the core transactional data in a relational database, and route the parts that don't fit — the session store, the metrics firehose, the search index, the social graph — to the NoSQL store shaped for each. An e-commerce site might run Postgres for orders and payments, Redis for sessions and carts, Elasticsearch for product search, and Cassandra for the event log. None of those is 'the database'; each is the right store for one job.
There's also a cost to denormalizing that the scaling story tends to gloss over. When you store data already shaped for reads — an order document with the customer's name copied inside it — you've duplicated that fact. If the customer renames themselves, every copy is now stale, and it's your application's job to find and fix them, because there's no join and no single source of truth. Relational normalization exists precisely to avoid that. So the NoSQL trade isn't free scale; it's moving the work of keeping data consistent out of the database and into your code.
The decision, in two tables#
First, the two families side by side across the axes. Read it as 'what each is good at,' not 'a winner per row' — every strength here is a trade paid for somewhere else.
| Axis | Relational (SQL) | NoSQL |
|---|---|---|
| Schema | Fixed and enforced by the database | Flexible — records can differ, changes are cheap |
| Data shape | Normalized across tables, joined at read time | Denormalized — stored the way it's read |
| Queries | Ad-hoc, any question via the query engine | Fast on the planned access pattern, weak off it |
| Transactions | ACID across rows and tables | Often limited to one key; many are eventually consistent |
| Scaling writes | Hard to spread across machines (joins, ACID) | Built to partition across machines |
| Best when | Relationships + ad-hoc queries + strong consistency | One known access pattern at large scale or flexible shape |
And the decision phrased the way you'd reason through it out loud — name the dominant thing about your workload, and let it point you.
| If your workload is… | Lean toward | Because |
|---|---|---|
| Related data + ad-hoc queries + ACID | Relational (SQL) | Joins, a general query engine, and atomic transactions are exactly this |
| Self-contained objects fetched whole | Document store | One read returns the object, no joins, schema can vary |
| Get / set by key at high speed | Key-value store | A plain map is the fastest possible lookup |
| Write firehose, huge dataset, read by key | Wide-column store | Partitions across machines and absorbs writes SQL can't |
| Relationships are the query | Graph database | Traversal is native; the same query is self-joins in SQL |
| Both transactions AND global scale | NewSQL / distributed SQL | Keeps SQL and ACID while partitioning across machines |
PredictA team says: 'We're picking NoSQL because we expect to be huge and NoSQL scales better.' What's wrong with that reasoning, and what would you ask them?
Hint: Is 'scales better' free, or is it bought with joins, transactions, and ad-hoc queries?
The reasoning skips the actual decision. 'Scales better' isn't a property of NoSQL in the abstract — it's a property you get by giving up joins, cross-key transactions, and ad-hoc queries, and only if your access pattern actually fits one of the NoSQL families. If their data is relational and their queries will keep changing, NoSQL doesn't scale better for them; it makes every read they didn't plan for painful and pushes consistency work into their app. The questions to ask: what are your actual access patterns, do you need transactions across records, and how much data and write volume are you really at? Most teams that 'expect to be huge' are nowhere near a single relational database's ceiling yet, and a modern relational database (with read replicas, and later partitioning, or a NewSQL option) carries them a very long way. Premature NoSQL trades away flexibility they'll miss for scale they don't yet need.
In the wild
- Relational (SQL): PostgreSQL and MySQL run the transactional core of most products — orders, users, payments, inventory — because the data is related and the queries are open-ended. This is the default you should have to argue your way out of, not into.
- Document (MongoDB): a good fit when the record is a self-contained object with a shape that varies — a product catalog where different categories have different attributes, or a CMS where each page is different. You fetch the whole document by id and don't join.
- Key-value (Redis, DynamoDB): Redis backs sessions, caches, rate limiters, and leaderboards — get/set by key at microsecond speed. DynamoDB powers Amazon-scale workloads where the access pattern is known and fixed and predictable single-digit-millisecond latency at any scale matters more than query flexibility.
- Wide-column (Cassandra, Bigtable): built for a write firehose spread over many machines — time-series metrics, event logs, and messaging history. Discord stores its trillions of messages in a wide-column store (ScyllaDB) precisely because the write rate and data size dwarf what one relational database could hold.
- Graph (Neo4j): shines where the relationships are the query — fraud rings, recommendation graphs, and network/dependency analysis, where a friends-of-friends traversal is native instead of a stack of self-joins.
- Polyglot in practice: a single company routinely runs several of these at once. The lesson isn't 'MongoDB won' or 'Postgres won' — it's that each store earns its place by matching one workload, and mature systems stop treating 'the database' as a single choice.
Pitfalls & gotchas
Isn't NoSQL just strictly more scalable than SQL?
No — it's more scalable for the access pattern it's shaped around, by giving up joins, cross-key transactions, and ad-hoc queries. Off that pattern it's often worse: a query you didn't plan for can mean scanning everything or maintaining a second copy of the data by hand. And modern relational databases scale much further than the old reputation suggests, with read replicas, partitioning, and NewSQL options. 'Scales better' is meaningless until you say at what.
Does NoSQL mean I don't have to design a schema?
It means the database won't enforce one — not that you get to skip designing it. In fact NoSQL demands more up-front design of your access patterns, because you shape the data to match your reads and can't fall back on an ad-hoc join later. 'Schema-flexible' moves schema decisions from the database into your application and your data model; it doesn't remove them. Design the queries first, then the data.
Can NoSQL stores do transactions at all?
Increasingly yes, but usually with limits. Many document and wide-column stores guarantee atomicity for writes to a single document or single key, and some (MongoDB, DynamoDB) now offer multi-record transactions. But cross-record, cross-partition transactions are either unavailable or costly, because that guarantee is exactly what's hard to provide across many machines. If your workload leans on multi-record atomicity, that's a strong pull back toward relational or NewSQL.
What does denormalizing actually cost me?
Consistency work. When you store a fact in several places so each read is one lookup, an update has to touch every copy, and there's no join or single source of truth to lean on — so keeping the copies in sync becomes your application's job. Relational normalization exists to avoid exactly this. NoSQL doesn't delete that work; it moves it out of the database and into your code.
QuizA social app needs friends-of-friends suggestions: for a user, find everyone within two hops in the follow graph, over a graph with hundreds of millions of edges. Which store fits, and why is the relational default awkward here?
- Relational — a couple of joins handle it fine at any scale.
- Key-value — store each user's suggestions under their key.
- Graph database — traversal is native, where the relational version is expensive multi-level self-joins.
- Wide-column — it scales writes, which is what this needs.
Show answer
Graph database — traversal is native, where the relational version is expensive multi-level self-joins. — A graph database fits, because relationships are the query. Finding everyone within two hops is a native traversal: start at the node, follow edges out, follow again. The relational version is a self-join of the follows table onto itself, then again for the second hop, and each level multiplies the rows scanned — it grows expensive fast and gets worse as the graph grows. Key-value could cache a precomputed answer but can't compute the traversal, and wide-column solves write scale, which isn't the bottleneck here — the bottleneck is the shape of the query, not the write rate. The forcing axis is relationships-and-joins: when traversal is the core operation, the store built for traversal wins.
In an interview
This question rewards judgment over trivia. The failure mode the interviewer is listening for is 'NoSQL scales better, so let's use it.' The strong answer never declares a winner — it names the axis that forces the choice, and it reaches for both stores when the workload has two shapes.
- Lead with the axes, not a verdict: access patterns (known vs ad-hoc), relationships, transactions/consistency, scale, and schema flexibility. Say which one dominates this workload and why — that's the whole answer.
- Default to relational and argue your way out. The relational model with ACID and a query engine is the safe base case; reach for NoSQL when a specific axis (a write firehose, a self-contained document, a graph traversal) clearly pulls you off it.
- Pick the NoSQL family, never just 'NoSQL.' Match document / key-value / wide-column / graph to the read pattern. Saying 'NoSQL' without naming the family signals you haven't made the real decision.
- Propose polyglot persistence when the data has two shapes: core transactional data in a relational store, the firehose or session or search or graph in the store built for it. 'We'd use both, here's the split' is usually the strongest answer.
- Know the traps: 'schema-flexible' means more up-front access-pattern design, not less; denormalizing pushes consistency work into your app; and NewSQL (Spanner, CockroachDB) breaks the old 'ACID or scale' either/or.
PredictAn interviewer says: 'Design the storage for a ride-sharing app. You've got user accounts and trip history, live driver locations updating every few seconds, and you want to suggest drivers near a rider.' What's the strong answer?
Hint: One app, but the account data, the location firehose, and the nearby-search have different shapes — one store, or three?
Recognize three workloads with three different shapes and route each to the store that fits, instead of forcing one database. User accounts and trip history are related, transactional, and queried ad-hoc (billing, support, reports) — that's a relational database with ACID. Live driver locations are a write firehose: every driver pushing an update every few seconds is huge, mostly-overwrite write volume with a simple key (driver id), which points at a key-value or wide-column store built to absorb writes, not a relational table you're hammering with updates. 'Drivers near a rider' is a geospatial query, best served by a store with geo-indexing (Redis geo commands, or a geo-index in the document store) rather than joins. The strong move is to name the three shapes, note that consistency needs differ (a stale driver location for one second is fine; a wrong trip charge is not), and split the storage accordingly — polyglot persistence, justified per workload. What makes it strong is refusing the single-store framing and tying each choice to the axis that forced it.
References
- Designing Data-Intensive Applications, Ch. 2 — Kleppmann on the relational, document, and graph data models and when each fits.
- Amazon DynamoDB paper (2007) — the Dynamo design — The influential key-value / eventual-consistency design that seeded much of NoSQL.
- Google Bigtable paper (2006) — The wide-column model built for huge, sparse, write-heavy datasets.
- Google Spanner paper (2012) — Globally-distributed SQL with ACID transactions — the NewSQL line-blurrer.
- Martin Fowler — PolyglotPersistence — Why real systems use several stores, each matched to one workload.
- MongoDB — Data modeling and schema design — Modeling for access patterns in a document store, including when to embed vs reference.
Ready to try it?
The simulator is a real, deterministic implementation — pick a scenario and step through it, scrubbing the timeline forward and backward through every change.