SQL vs NoSQL
Access patterns decide the store — not fashion, and not scale you don't have.
35 minDifficulty 3/5database · designAI-writtenWritten by a model on 16 August 2026 and not yet read by a person. Checked automatically: schema, the pedagogical rules the hand-written material is held to, and every diagram parsed for real.
Before this
Why this exists
A team picks MongoDB because "it scales better than SQL" for an application with 10,000 users and a handful of well-defined, relational entities — a decision made for a scaling problem they don't have, at the cost of losing joins and transactions they genuinely needed. The right store follows from the actual ACCESS PATTERN, not from which database is trendier or which one claims to scale further.
The mental model
A relational (SQL) database is built around FIXED SCHEMAS and relationships expressed via joins — strong consistency guarantees, at the cost of requiring the schema to be defined upfront and relationships to be explicit. A document/NoSQL database is built around FLEXIBLE, self-contained documents — each record can carry its own shape, and related data is often embedded directly rather than joined, trading some consistency and structure flexibility for a different scaling and access pattern.
How it works
The real question is how the data will actually be queried, not how it's structured on paper
Data with many-to-many relationships that need to be queried from multiple different angles (an e-commerce system: products, orders, customers, inventory, all cross-referenced) fits SQL's join model naturally. Data that's naturally self-contained and always accessed as a whole unit (a user's profile document with nested preferences, rarely queried by anything other than user id) fits a document model naturally, since there's little relational structure to actually benefit from joins.
Schema flexibility is a genuine tradeoff, not a pure win
A document database lets you insert a record with any shape at any time, with no upfront schema migration — genuinely convenient for rapidly evolving data. But this ALSO means the database itself won't catch a typo'd field name, a missing required field, or inconsistent types across documents; that validation, if wanted, has to be enforced entirely in application code instead of the database structure itself.
SQL's ACID transactions across multiple tables are a genuine, hard-won guarantee
A relational database's transaction can atomically update rows across MULTIPLE tables (debit one account row, credit another) with full ACID guarantees, out of the box. Many document databases historically offered transactional guarantees only WITHIN a single document, requiring careful denormalization (embedding related data into one document) specifically to fit within that boundary — a real design constraint that shapes how data must be modeled.
Horizontal scaling was NoSQL's original selling point, but it's not exclusive to it anymore
Early NoSQL databases were specifically designed to shard easily across many machines, when relational databases of that era were harder to scale horizontally. Modern relational databases have since gained significant horizontal scaling capabilities of their own, and modern document databases have gained stronger consistency features — the historical dividing line between 'SQL doesn't scale, NoSQL doesn't have transactions' has blurred considerably, making access patterns and consistency needs the more durable deciding factors today.
The mechanism
Choosing between them starts with mapping out the actual, concrete queries the application will run: how many distinct entity types are there, how do they relate, and how often is data queried across those relationships versus as a self-contained whole? Relationships queried from many angles, needing strong cross-entity consistency, point toward a relational model. Self-contained, independently-accessed records with a flexible or evolving shape point toward a document model.
What people get wrong
- NoSQL databases always scale better than SQL databases.
- This was more true historically, when relational databases were genuinely harder to shard horizontally — modern relational databases have significantly closed that gap, and 'scaling better' now depends heavily on the specific database, workload, and access pattern, not a blanket SQL-vs-NoSQL rule. Choosing a database purely on an outdated scaling reputation, rather than the actual access pattern and consistency needs, is a common cause of picking a tool poorly matched to the actual problem.
- A flexible schema means you don't need to think carefully about data structure upfront.
- Schema flexibility moves the responsibility for consistency and validation from the database to the APPLICATION — without deliberate discipline, this can lead to inconsistent, hard-to-query data over time, which a rigid schema would have caught immediately. Treating schema flexibility as 'no design needed' rather than 'design responsibility shifted elsewhere' is why document databases sometimes accumulate messy, inconsistent data that a relational schema's constraints would have prevented from the start.
- You must choose exactly one database type for an entire application.
- Many real systems use BOTH — a relational database for strongly relational, transactional core data, and a document or key-value store for a specific subsystem (like session storage or a search index) where its particular tradeoffs fit better. Treating this as an all-or-nothing choice ignores that different parts of a real application often have genuinely different access patterns, each best served by a different tool.
When not to use it
- The data has many well-defined relationships that need to be queried and joined from multiple different angles, with strong consistency requirements.
- A relational (SQL) database — this is precisely the access pattern joins and ACID transactions are designed for.
- The data is naturally self-contained, has a flexible or rapidly evolving shape, and is almost always accessed as a whole unit by a known key.
- A document or key-value store — the schema flexibility and simple access pattern fit naturally, without needing joins the data doesn't actually require.
Terms
- Relational database
- — A database organizing data into tables with fixed schemas, related via foreign keys and queried with joins, typically offering strong ACID transactional guarantees.
- Document database
- — A database storing flexible, often self-contained documents (like JSON), typically prioritizing schema flexibility and simple key-based access over cross-entity joins.
- Sharding
- — Splitting a dataset across multiple database instances (shards) to distribute load, a key mechanism for horizontal scaling in both SQL and NoSQL systems.
- Access pattern
- — The actual, concrete way an application queries and relates its data, which should drive database choice more than abstract scaling claims.
In an interview
A team is choosing between a relational database and a document database for a new e-commerce platform with products, orders, customers, and inventory that need to be cross-referenced in reports. Which fits better, and why?
- a relational database fits better here — the data has genuine many-to-many relationships (products in orders, customers with multiple orders) queried from multiple angles for reporting
- SQL's joins and ACID transactions directly support this kind of cross-entity querying and consistency need
- a document database would likely require significant denormalization/embedding to avoid joins the data structurally needs, adding complexity rather than removing it
Can you recall it?
Why should the choice between SQL and NoSQL be driven by access patterns rather than by which one 'scales better'?