SQL vs NoSQL Databases Compared
SQL vs NoSQL is a choice between relational databases, which store data in tables with a fixed schema, joins and ACID transactions queried in SQL, and non-relational databases (key-value, document, column-family and graph), which use flexible data models, usually scale out across many servers and often relax consistency for availability.
Every application needs somewhere to keep its data, and the first design decision is often SQL vs NoSQL. Relational databases have been the default since the 1980s, while NoSQL systems grew in the 2000s when large web services needed to spread data across clusters of commodity servers. Neither is better in every case. This article compares their data models, guarantees and scaling, then shows how to reason about consistency with a worked quorum example.
What SQL and NoSQL mean
A SQL database is a relational database management system (RDBMS). It follows the relational model proposed by Edgar Codd in 1970: data lives in tables (relations) of rows and columns, every table has a key, and tables are linked by foreign keys. SQL is the standard language for defining and querying this data, published by ISO as ISO/IEC 9075. Good relational design follows the rules of database normalization so that each fact is stored once.
NoSQL is not one technology. Sadalage and Fowler describe it as a loose group of non-relational, mostly open source, cluster friendly databases with flexible schemas. They group NoSQL stores into four families by data model:
- Key-value: a value, opaque to the database, stored and fetched by a unique key. Amazon’s Dynamo is the classic design.
- Document: each record is a self-describing document, typically JSON or a binary form of it, which can hold nested fields and arrays and can be queried by its contents.
- Column-family (wide column): rows identified by a key, with columns grouped into families, and different rows free to hold different columns. Google Bigtable and Apache Cassandra follow this model.
- Graph: nodes and edges with properties, built for queries that follow relationships, such as social networks or route finding.
SQL vs NoSQL comparison table
| Feature | SQL (relational) | NoSQL (non-relational) |
|---|---|---|
| Data model | Tables of rows and columns | Key-value, document, column-family or graph |
| Schema | Defined before data is written (schema on write) | Flexible; structure often read by the application (schema on read) |
| Query language | Standard SQL, ISO/IEC 9075 | Product specific APIs and query languages |
| Relationships | Foreign keys and joins | Embedding, references in application code, or graph edges |
| Transactions | ACID across many rows and tables | Often atomic per record; multi-record support varies |
| Consistency | Strong consistency is the norm | Often tunable, with eventual consistency common |
| Scaling | Traditionally vertical (bigger server) | Designed for horizontal scaling (more servers) |
| Typical use | Banking, orders, ERP, reporting | Sessions, catalogues, event logs, social graphs |
SQL vs NoSQL differences explained
Schema and data modeling
A relational schema is declared with CREATE TABLE before any row is stored, and the database rejects data that breaks types or constraints. Related data is split across tables and recombined with joins at query time. A document or key-value store accepts records of varying shape, so new fields can appear without a schema migration. Kleppmann calls this schema on read: the structure still exists, but the application code interprets it. The cost is that checks the database used to enforce now move into the application.
NoSQL stores also favour aggregates, the term Sadalage and Fowler borrow from domain driven design: a cluster of related data that is read and written as one unit. An order with its line items is a natural aggregate, so a document database stores it as one document, while a relational design splits it into several tables.
Transactions: ACID and BASE
Relational databases provide ACID transactions: atomicity (all or nothing), consistency (constraints hold before and after), isolation (concurrent transactions do not see each other’s partial work) and durability (committed data survives a crash). Silberschatz and colleagues treat these as the core guarantees of a transaction processing system.
Many NoSQL systems follow the BASE style instead: basically available, soft state, eventually consistent. Replicas may briefly disagree after a write, but if writes stop they converge on the same value. This makes it easier to stay available when nodes or links fail.
Scaling and the CAP theorem
Scaling vertically means moving to a server with more CPU, memory and disk. It is simple but has an upper limit. Scaling horizontally means sharding (partitioning) data across many machines and replicating each partition. Aggregate oriented NoSQL stores fit this well because each aggregate can live on one node.
Once data is replicated across a network, the CAP theorem applies. Eric Brewer conjectured in 2000, and Gilbert and Lynch proved in 2002, that a distributed system cannot guarantee consistency, availability and partition tolerance at the same time. Since network partitions cannot be ruled out, the real choice during a partition is between staying consistent (refusing some requests) and staying available (possibly serving stale data). Kleppmann notes that CAP is narrow and does not describe behaviour when the network is healthy, so it should guide thinking rather than label products.
Quorum reads and writes
Dynamo style stores keep N copies of each key and let the client set how many replicas must answer. A write succeeds after W replicas acknowledge it, and a read waits for R replicas. If R + W > N, every read set overlaps every write set in at least one replica, so a read sees the latest acknowledged write. DeCandia et al. report (N, R, W) = (3, 2, 2) as the common Dynamo configuration.
- Given
- A key-value store with replication factor N = 3, read quorum R = 2 and write quorum W = 2.
- Find
- Whether reads are guaranteed to overlap the latest write, the minimum overlap, and how many replica failures reads and writes can each tolerate.
- Assumptions
- Strict quorums (no sloppy quorum or hinted handoff); a replica either responds or has failed; clocks and conflict resolution are ignored.
- Solution
- R + W = 2 + 2 = 4, which is greater than N = 3, so the quorum condition holds
Minimum overlap = R + W minus N = 4 minus 3 = 1 replica
Write failures tolerated = N minus W = 3 minus 2 = 1
Read failures tolerated = N minus R = 3 minus 2 = 1
Check: R = 1 and W = 1 give 2, not greater than 3, so a read could miss the latest write - Answer
- Yes: at least 1 replica is shared by every read and write quorum, and both reads and writes keep working with 1 of the 3 replicas down
- Limitations
- Kleppmann shows that even with R + W greater than N, edge cases such as concurrent writes, sloppy quorums and failed partial writes can still return stale values, so quorums are not the same as full linearizable consistency.
Modeling the same order both ways
Take one order by one customer with three line items. A normalized relational design stores 1 row in Customer, 1 row in Orders and 3 rows in OrderLine, that is 5 rows in 3 tables, and showing the order needs a query that joins the three tables. A document database stores one order document with the customer summary and an array of three items embedded:
{
"orderId": 5001,
"customer": { "id": 17, "name": "Asha" },
"items": [
{ "sku": "A1", "qty": 2 },
{ "sku": "B7", "qty": 1 },
{ "sku": "C3", "qty": 4 }
]
}
The document is read in one lookup, but if Asha changes her name, every order document that copies it must be updated or accepted as a historical snapshot. The relational version changes one row. This is the classic trade-off between read locality and update anomalies.
When to use SQL vs NoSQL
Choose a relational database when:
- Data is highly structured and its relationships matter, as in accounting, inventory and booking systems.
- You need multi-row ACID transactions and strong integrity constraints.
- Users run ad hoc queries and reports that join data in ways not known in advance.
Choose a NoSQL database when:
- Access is mostly by key or by whole aggregate, such as user profiles, carts and sessions.
- Data volume or write rate needs horizontal scaling across many nodes.
- The record structure varies or changes often, or the data is naturally a graph.
Many systems use both, an approach Sadalage and Fowler call polyglot persistence. Services usually expose either kind of store through a REST API carried over HTTP, and index choice still decides lookup cost in Big O terms whichever model you pick.
SQL vs NoSQL interview and viva questions
- What is the main difference between SQL and NoSQL databases? SQL databases store data in related tables with a fixed schema and ACID transactions, while NoSQL databases use key-value, document, column-family or graph models with flexible schemas and are usually built to scale out.
- What does ACID stand for? Atomicity, consistency, isolation and durability, the guarantees a relational database gives every transaction.
- State the CAP theorem. A distributed data store cannot guarantee consistency, availability and partition tolerance at once; during a network partition it must give up either consistency or availability.
- What is the quorum condition in a Dynamo style store? R + W must be greater than N, so every read quorum overlaps every write quorum in at least one replica.
- What is an aggregate in NoSQL design? A group of related data, such as an order with its line items, that is stored and retrieved as one unit, which lets it live on a single node of a cluster.
Frequently asked questions
Is NoSQL faster than SQL?
Not in general. Speed depends on the workload, the data model and how the database is tuned. A key-value store answers a lookup by key very quickly, while a relational database is often better at ad hoc queries and joins across many tables. Measure with your own data and queries.
Can a NoSQL database support transactions?
Many can, within limits. Most key-value and document stores guarantee atomic updates to a single record or document, and several now offer multi-document transactions. Distributed transactions across many partitions cost extra coordination, so NoSQL designs usually try to keep related data in one record.
Can I use SQL and NoSQL databases together?
Yes. This is called polyglot persistence. A common design keeps orders and payments in a relational database as the system of record, and uses a key-value store for sessions or caching and a search or graph store for features that need those access patterns.
References
- A. Silberschatz, H. F. Korth and S. Sudarshan, Database System Concepts, 7th ed., McGraw-Hill
- ISO/IEC 9075-1:2023 Information technology, Database languages SQL, Part 1: Framework (SQL/Framework)
- S. Gilbert and N. Lynch, Brewer's Conjecture and the Feasibility of Consistent, Available, Partition-Tolerant Web Services, ACM SIGACT News, 2002
- P. J. Sadalage and M. Fowler, NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence, Addison-Wesley, 2012
- M. Kleppmann, Designing Data-Intensive Applications, O'Reilly Media, 2017
- G. DeCandia et al., Dynamo: Amazon's Highly Available Key-value Store, Proceedings of ACM SOSP, 2007
Get one engineering email a week
New articles, worked examples and study notes every week. Free, no spam, unsubscribe any time.