SQL vs NoSQL: How to Actually Choose
In this article we will learn what genuinely separates SQL and NoSQL databases, why the common answers about scale and schema are mostly wrong, the four NoSQL families and what each is for, and a decision procedure you can defend in a system design interview.
"SQL or NoSQL" is one of the most common system design questions and one of the most commonly answered badly. The usual reasoning — SQL does not scale, NoSQL has no schema — was never quite true and is now clearly false.
In this article we will learn what genuinely separates the two, why the familiar arguments are wrong, the four NoSQL families and what each is actually for, and a decision procedure you can defend under follow-up questions.
What is the real difference?
Short answer
The real difference is not scale or schema. It is that relational databases optimise for flexible querying of normalised data through joins, while NoSQL databases optimise for a specific access pattern by storing data in the shape it will be read in. A relational database lets you ask questions you did not anticipate; a NoSQL database makes the questions you did anticipate very fast.
Everything else follows from that. Joins are what make ad-hoc querying possible, and joins are also what make horizontal scaling hard, because a join across shards requires a network round trip.
Why the common arguments are wrong
"SQL does not scale." PostgreSQL routinely handles tens of terabytes. Read replicas scale reads nearly linearly, and modern distributed SQL systems such as CockroachDB, Vitess and Spanner scale writes horizontally while keeping SQL and transactions. Very few applications outgrow a well-tuned relational database.
"NoSQL has no schema." It has no schema enforced by the database. The schema still exists — it has moved into your application code, where nothing validates it and every version of every deployed client must agree. This is often a worse place for it.
"NoSQL is faster." Only for the access pattern it was modelled around. Query outside that pattern and you may be doing a full scan.
The four NoSQL families
| Family | Data model | Strong at | Weak at | Examples |
|---|---|---|---|---|
| Key-value | Opaque value by key | Extremely fast point lookups, caching, sessions | Any query not by primary key | Redis, DynamoDB, Memcached |
| Document | JSON-like nested documents | Data read as a unit, evolving shapes | Cross-document joins, multi-document transactions | MongoDB, Couchbase, Firestore |
| Wide-column | Rows with dynamic column families | Massive write throughput, time series | Ad-hoc queries, joins | Cassandra, HBase, ScyllaDB |
| Graph | Nodes and edges as first-class | Traversals, relationship depth | Aggregate scans across all nodes | Neo4j, Neptune, ArangoDB |
Treating "NoSQL" as one choice is the mistake. A graph database and a wide-column store have almost nothing in common beyond not being relational.
When should you choose relational?
Choose a relational database when:
- Your data is genuinely relational. Orders belong to customers, which have addresses, which appear on invoices. Modelling that as documents means either duplicating data or performing joins in application code.
- You need multi-row transactions. Anything touching money. Two accounts must debit and credit atomically, and doing this correctly without ACID transactions is difficult enough that you should not attempt it unless forced.
- Query patterns will change. A reporting requirement nobody anticipated is a new
SELECTin a relational database and a data migration in a document store. - Data integrity matters. Foreign keys, unique constraints and check constraints catch bugs the application would otherwise ship.
- You are not sure yet. This is the honest default. PostgreSQL will carry most products further than their founders expect.
When should you choose NoSQL?
Choose NoSQL when you can name the specific property you need:
- Write throughput beyond a single leader. Cassandra accepts writes on any node. If you are ingesting millions of events per second, this is the argument.
- A genuinely fixed access pattern. A session store reads by session ID and nothing else. A key-value store is the correct shape.
- Documents read as a whole. A product catalogue where each product has wildly varying attributes and is always fetched entire.
- Relationship traversal is the workload. "Friends of friends who like X" is many self-joins in SQL and one traversal in a graph database.
- Multi-region active-active writes. Accepting writes in several regions at once is something distributed NoSQL systems are built for and traditional relational systems are not.
A decision procedure
Say this out loud in an interview and you will be reasoning about constraints rather than reciting categories.
Polyglot persistence
The question is rarely either/or in a real system. A typical architecture uses several:
- PostgreSQL for users, orders and payments — the data that must be correct
- Redis for sessions and hot caching
- Elasticsearch for full-text search
- Cassandra or S3 for the event log and analytics
The cost is operational. Each additional store is another thing to back up, monitor, upgrade and staff. Adding a database because it fits one query better is a real trade against that cost, and "we kept it in Postgres" is frequently the right answer.
Common mistakes
- Choosing NoSQL for scale you do not have. Most applications never reach the point where a single Postgres instance is the bottleneck.
- Treating "schemaless" as an advantage. It relocates the schema to a place with no enforcement.
- Denormalising before you know the access pattern. Denormalisation optimises reads you have already identified. Do it too early and you have duplicated data serving queries nobody makes.
- Ignoring operational cost. Running Cassandra well requires expertise that running Postgres well does not.
- Forgetting that Postgres does JSON. JSONB covers a large share of the cases people reach for MongoDB to solve.
Conclusion
The difference between SQL and NoSQL is flexible querying versus optimised access. Relational databases normalise data and use joins so you can ask questions you had not planned; NoSQL databases store data in the shape it will be read so the planned questions are fast.
Default to relational unless you can name the specific property you need — write throughput past a single leader, multi-region active-active writes, a truly fixed access pattern, or relationship traversal as the core workload. "It will not scale" is not that property for the overwhelming majority of systems.
For how data is spread across servers once you do need to scale horizontally, see consistent hashing explained.