Chapter 11 of 33

SQL vs NoSQL Databases

Almost every system design discussion reaches the same question sooner or later: "Which database will you use, and why?" In earlier chapters we added load balancers and caches in front of our servers, but the database is where the data actually lives, and choosing the wrong kind of database can make a design slow, expensive or impossible to scale.

Databases are broadly grouped into two families: SQL (relational) databases like MySQL and PostgreSQL, and NoSQL databases like MongoDB, Cassandra, Redis and DynamoDB. This chapter explains how they differ, why both exist, and how to pick one in an interview with a clear justification.

There is no "best" database. There is only a database that fits your data shape, your access patterns and your consistency needs.


What Is a SQL (Relational) Database?

A relational database stores data in tables (relations) made of rows and columns, with a fixed schema, relationships expressed through keys, and a declarative query language, SQL.

Think of a set of linked spreadsheets with strict rules:

users                         orders
+---------+-------+------+    +----------+---------+--------+
| user_id | name  | city |    | order_id | user_id | amount |
+---------+-------+------+    +----------+---------+--------+
|    1    | Aarav | Pune |<---|   101    |    1    |   499  |
|    2    | Diya  | Delhi|    |   102    |    1    |  1299  |
+---------+-------+------+    |   103    |    2    |   250  |
                              +----------+---------+--------+
          primary key  <----  foreign key

Key properties:

  • Schema-on-write: the table structure is declared up front, and every row must match it.

  • Normalisation: each fact is stored once (a user's city lives only in users), and tables are combined at query time with joins.

  • ACID transactions: Atomicity, Consistency, Isolation, Durability. Transferring money between two accounts either fully happens or does not happen at all.

  • Constraints: primary keys, foreign keys, NOT NULL, CHECK and UNIQUE are enforced by the database itself, not by your application code.

  • Powerful ad-hoc queries: you can ask new questions (group, filter, join, aggregate) without redesigning the storage.

Popular examples: PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server, SQLite.


What Is a NoSQL Database?

NoSQL ("Not only SQL") is an umbrella term for databases that do not use the relational table model as their primary data model. They typically offer flexible schemas and are designed to scale out across many machines.

NoSQL became popular in the late 2000s when companies like Google (Bigtable) and Amazon (Dynamo) needed to store enormous amounts of data across thousands of commodity servers with very high availability. Their papers inspired many open-source databases.

"NoSQL" is not one design. There are four main families:

TypeData modelExamplesGood for
Key-valueA giant dictionary: key → opaque valueRedis, Amazon DynamoDB, etcdSessions, caching, shopping carts, feature flags
DocumentSelf-describing JSON-like documents grouped in collectionsMongoDB, Couchbase, Amazon DocumentDB, FirestoreProduct catalogs, user profiles, content management
Wide-columnRows grouped by a partition key, each holding many sorted columnsApache Cassandra, HBase, ScyllaDB, Google BigtableTime-series, event logs, chat messages, heavy write workloads
GraphNodes and edges with propertiesNeo4j, Amazon Neptune, JanusGraphSocial networks, recommendations, fraud rings, knowledge graphs

DynamoDB is often described as a key-value and document store, because each item is addressed by a key but can hold nested attributes. The categories are a guide, not strict boxes.


A Worked Example: Same Data, Two Models

Let us store users and their orders in both styles. The script below uses Python's built-in sqlite3 module for the relational side, and a plain Python dictionary to show what a document database would store.

import sqlite3, json

# ---- Relational (SQL) version ----
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE users  (user_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
                     user_id INTEGER NOT NULL REFERENCES users(user_id),
                     amount INTEGER NOT NULL CHECK (amount > 0));
INSERT INTO users VALUES (1,'Aarav','Pune'),(2,'Diya','Delhi');
INSERT INTO orders VALUES (101,1,499),(102,1,1299),(103,2,250);
""")
db.execute("PRAGMA foreign_keys = ON")
rows = db.execute("""
SELECT u.name, COUNT(o.order_id) AS orders, SUM(o.amount) AS total
FROM users u JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id ORDER BY total DESC""").fetchall()
print("SQL result:", rows)
try:
    db.execute("INSERT INTO orders VALUES (104, 99, 100)")
except sqlite3.IntegrityError as e:
    print("SQL rejected bad row:", e)

# ---- Document (NoSQL-style) version ----
user_doc = {
    "_id": 1, "name": "Aarav", "city": "Pune",
    "orders": [{"order_id": 101, "amount": 499},
               {"order_id": 102, "amount": 1299}],
}
total = sum(o["amount"] for o in user_doc["orders"])
print("Document read (one lookup):", user_doc["name"], len(user_doc["orders"]), total)
print(json.dumps(user_doc))

Output:

SQL result: [('Aarav', 2, 1798), ('Diya', 1, 250)]
SQL rejected bad row: FOREIGN KEY constraint failed
Document read (one lookup): Aarav 2 1798
{"_id": 1, "name": "Aarav", "city": "Pune", "orders": [{"order_id": 101, "amount": 499}, {"order_id": 102, "amount": 1299}]}

What this shows:

  • The SQL version stores users and orders separately and joins them when asked. The database itself refused an order for a user (id 99) who does not exist. (SQLite enforces foreign keys only after PRAGMA foreign_keys = ON; PostgreSQL and MySQL/InnoDB enforce them by default.)

  • The document version stores the user together with their orders. Reading "a user with their orders" is a single lookup with no join, which is fast and easy to spread across machines. But nothing stops you from writing an order with a negative amount, and if you ever need "all orders above ₹1,000 across all users", the embedded design makes that query harder.

Embedding vs Referencing

Document databases give you a choice:

  • Embed related data inside the parent document when it is read together and is bounded in size (a user's 3 saved addresses).

  • Reference it by id in another collection when it grows without limit or is shared (a user's 50,000 orders, or a product shared by many orders).

A useful rule: design NoSQL data around your queries; design SQL data around the entities.


Schema: Rigid vs Flexible

Schema-on-write (SQL)Schema-on-read (most NoSQL)
When is structure checked?When data is writtenWhen application code reads it
Adding a fieldALTER TABLE (a migration)Just start writing documents with the new field
Bad dataRejected by the databaseAccepted; the application must cope
Best whenStructure is stable and correctness mattersStructure varies (e.g., products with different attributes) or evolves quickly

"Schemaless" is a slightly misleading word. The schema still exists; it has just moved from the database into your application code. Modern document databases also let you add validation rules (MongoDB supports JSON Schema validation on collections), so the line is softer than it used to be.


Recall Scalability: Vertical vs Horizontal. A traditional relational database usually runs as one primary server that handles all writes. You can scale it up (bigger machine) and add read replicas, but at some point the single write path or the dataset size becomes the bottleneck.

Typical SQL setup                 Typical NoSQL cluster

   App servers                       App servers
        |                                 |
   +---------+                 +-----+-----+-----+-----+
   | Primary | (all writes)    | N1  | N2  | N3  | N4  |  data split by key,
   +---------+                 +-----+-----+-----+-----+  each node owns a part
    /       \                   add N5 → more capacity
 Replica  Replica (reads)

Many NoSQL databases were built from day one to partition data by key across many nodes and to replicate each part, so adding machines adds capacity almost linearly. The trade-off: operations that touch many keys (joins, multi-row transactions, ad-hoc aggregations) become harder or are not offered at all.

Relational databases can be split across servers too, which we will see in Database Sharding & Partitioning, but it is usually done by an extra layer (such as Vitess for MySQL or Citus for PostgreSQL) rather than being the core design.


ACID vs BASE

Relational databases are known for ACID. Many early NoSQL systems described their guarantees as BASE:

  • Basically Available: the system keeps answering even when parts of it fail.

  • Soft state: replicas may temporarily hold different values.

  • Eventual consistency: if writes stop, all replicas eventually converge to the same value.

ACIDBASE
Correctness firstAvailability and scale first
Reads see committed data immediatelyReads may briefly see stale data
Bank transfers, orders, inventory, bookingsLikes, view counts, feeds, recommendations

Today this is a spectrum, not two camps. MongoDB has supported multi-document ACID transactions since version 4.0 (replica sets) and 4.2 (sharded clusters); DynamoDB offers transactional APIs and strongly consistent reads; Cassandra lets each query pick its consistency level. We explore why these trade-offs exist in CAP Theorem & PACELC.


The Lines Are Blurring

  • SQL databases learned NoSQL tricks: PostgreSQL's JSONB type stores and indexes JSON documents; MySQL has a JSON type too.

  • NoSQL databases learned SQL tricks: transactions, secondary indexes, SQL-like query languages (Cassandra's CQL, Couchbase's SQL++).

  • NewSQL / distributed SQL databases such as Google Spanner, CockroachDB, TiDB and YugabyteDB offer SQL and ACID transactions while partitioning data across many nodes automatically.

  • Graph querying is getting a standard: GQL (ISO/IEC 39075) was published in 2024 as the first ISO standard graph query language, alongside widely used languages like Cypher.

So in an interview, avoid saying "NoSQL does not support transactions" or "SQL cannot scale". Both statements are outdated.


Side-by-Side Comparison

AspectSQL (relational)NoSQL
Data modelTables, rows, columnsKey-value, document, wide-column, graph
SchemaFixed, enforced on writeFlexible, usually enforced by the application
RelationshipsJoins and foreign keysEmbedding, denormalisation, or application-side joins (graph DBs are the exception: relationships are first-class)
Query flexibilityVery high: ad-hoc SQLBest for queries planned at design time
TransactionsFull ACID, multi-tableVaries; often single-key atomicity, some offer multi-item transactions
ScalingVertical + read replicas; sharding needs extra workHorizontal partitioning is usually built in
ConsistencyStrong by defaultOften eventual by default, frequently tunable
Typical usePayments, orders, ERP, anything with complex relationshipsHuge write volumes, flexible data, simple key-based lookups at massive scale

How to Choose in a Design Interview

Ask these questions in order:

  1. Does the data need strong correctness across multiple records? (money, stock, seat bookings) → lean SQL.

  2. Is the data highly relational, with many ad-hoc queries? (admin reports, analytics on business entities) → SQL.

  3. Is access mostly "get/put by key" at very high scale? → key-value or wide-column.

  4. Do records have varying structure and are they read as a whole? → document.

  5. Is the main question about relationships several hops deep? ("friends of friends who like X") → graph.

  6. What scale do the numbers from Back-of-the-Envelope Estimation suggest? A few thousand writes per second and a few TB usually fit comfortably in a well-tuned PostgreSQL or MySQL setup. Do not reach for a distributed NoSQL cluster before you need it.

Example Choices

ComponentReasonable choiceWhy
Payments ledger for a UPI-style appRelational (PostgreSQL/MySQL)ACID transactions, constraints, audits
User sessions / login tokensKey-value (Redis)Simple lookups, very fast, expiry (TTL) support
E-commerce product catalogDocument (MongoDB)A phone and a T-shirt have different attributes
Chat messages / IoT sensor readingsWide-column (Cassandra)Enormous write volume, read by partition (chat id, device id) in time order
"People you may know"Graph (Neo4j)Multi-hop relationship traversal

Polyglot Persistence

Large systems rarely use one database. An e-commerce site might keep orders in PostgreSQL, the catalog in MongoDB, sessions and carts in Redis and search in Elasticsearch. Using different stores for different jobs is called polyglot persistence. The cost is operational: more systems to run, monitor, back up and keep in sync.


Common Mistakes

  • "NoSQL is faster." It is faster for the access patterns it was designed for. A query it was not designed for (an ad-hoc join, a scan across partitions) can be far slower than in SQL.

  • "SQL cannot scale." Read replicas, caching, partitioning and distributed SQL databases run very large systems on relational databases.

  • Choosing NoSQL because "the schema might change". Schema migrations are a normal, solved problem in SQL. Choose based on access patterns and scale.

  • Modelling a document database like SQL (one collection per entity, joining in application code everywhere). You get the costs of both and the benefits of neither.

  • Embedding unbounded arrays (all orders inside the user document). Documents have size limits (16 MB in MongoDB) and big documents are slow to update.

  • Treating "NoSQL" as one thing. Redis and Neo4j have almost nothing in common. Name the specific type.


Interview Questions

1. What are the main differences between SQL and NoSQL databases?

SQL databases use tables with a fixed schema, joins and ACID transactions, and traditionally scale vertically plus read replicas. NoSQL databases use other models (key-value, document, wide-column, graph), have flexible schemas, are usually designed to partition across many nodes, and often favour availability with tunable or eventual consistency.

2. Which database would you use for a banking system and why?

A relational database, because transfers must be atomic across multiple rows, balances must never be read inconsistently, and constraints and auditability matter more than raw write throughput. If global scale is required, a distributed SQL database (such as Spanner or CockroachDB) keeps ACID semantics.

3. Name the four types of NoSQL databases with one example and use case each.

Key-value: Redis, sessions/caching. Document: MongoDB, product catalogs. Wide-column: Cassandra, time-series and chat messages. Graph: Neo4j, social connections and recommendations.

4. What does BASE stand for?

Basically Available, Soft state, Eventual consistency. It describes systems that stay available and let replicas temporarily diverge, converging later, in contrast to ACID's immediate consistency.

5. When would you embed vs reference data in a document database?

Embed when the child data is read with the parent, is owned by it, and is bounded in size. Reference when it grows without limit, is shared by many parents, or is frequently queried on its own.

6. Do NoSQL databases support transactions?

Many do today. MongoDB supports multi-document ACID transactions (4.0 for replica sets, 4.2 for sharded clusters), and DynamoDB has TransactWriteItems/TransactGetItems. Most offer at least single-key atomicity. Cross-partition transactions are usually more expensive than single-partition operations.


Summary

  • SQL databases: tables, fixed schema, joins, constraints and ACID transactions. Excellent for relational data and correctness-critical workloads.

  • NoSQL databases: key-value, document, wide-column and graph models, flexible schemas, built for horizontal scale.

  • Model SQL around entities, NoSQL around queries.

  • ACID vs BASE is now a spectrum: many databases offer tunable consistency and some transactions.

  • In interviews, justify your choice using data shape, access patterns, consistency needs and estimated scale, and do not be afraid to use more than one database.

Start with the questions your system must answer. The right database is the one that answers them correctly, at your scale, with the least complexity.