SQL vs NoSQL: Choosing a Data Store
Choose by access pattern, not brand. On 20,000 orders, one order fetch tied (~0.006 ms) as a join or a document; a cross-order report ran 4-5x slower over documents.
Choose a relational SQL database for joins, constraints, multi-row transactions or unplanned queries; choose a NoSQL store when nearly every access uses one known key and data must spread across machines. Interviewers expect the choice to follow access patterns: one order read cost the same both ways, but a cross-order report ran 4 to 5 times slower over documents.
Pick a relational database when you need joins, constraints, multi-row transactions or questions you cannot predict; pick a NoSQL store when nearly every access goes through one key and the data must spread across machines. Modelled both ways in SQLite, reading one order cost the same, while a report across all orders ran four to five times slower over embedded documents.
- On 20,000 orders modelled in SQLite, fetching one whole order took about 0.006 ms both as an indexed three-table join and as a single JSON document.
- A top-selling-SKU report over the same 20,000 orders took 21 ms with a relational GROUP BY and 115 ms when every embedded document had to be unpacked.
- Embedding user data in orders duplicates it: renaming a user with 10 orders changed 1 row in the relational model and rewrote 10 documents.
- SQLite ships with foreign key enforcement off; PRAGMA foreign_keys starts at 0 on every new connection and must be set ON before the first write.
- Key-value, document, wide-column and graph NoSQL stores each lay data out so one main access pattern is cheap, accepting that other questions get harder.
- MongoDB caps a single document at 16 MiB, and a write to one document is atomic even when it changes several nested parts.
01Start with the queries, not the product
SQL versus NoSQL sounds like a choice between products. It is really a choice about when the work of assembling data happens. A relational database stores each fact once, across tables, and puts the pieces back together at read time with joins. Most NoSQL stores move that effort to write time: you decide which pieces are read together and store them together, under one key.
So begin by writing down the access patterns. Is data read by one ID, by a time range, by following relationships, or by questions nobody has asked yet? Must a change show up everywhere at once? Will the data outgrow one machine?
Here "SQL" means a relational database: declared tables, a query language that can combine any of them, and transactions across rows. "NoSQL" is a loose label for stores that give up some of those features to make one access pattern cheap or to spread data over many machines. Each family gives up different things.
02What a relational database promises
Four things come with the relational model. The schema is declared once and checked on every write. Constraints (NOT NULL, UNIQUE, foreign keys, CHECK) are rules the engine will not break. Joins combine any tables at query time, including in ways the designer never planned. Transactions let several rows change together or not at all, as the ACID and transactions topic explains.
Constraints only help when they are on. SQLite ships with foreign key enforcement off for backwards compatibility, and the script at the end of this section shows what that means. The first insert names a user that does not exist and is accepted, because PRAGMA foreign_keys starts at 0 on every new connection. After setting it to ON, the same kind of insert fails. The SQLite documentation warns that the pragma silently does nothing inside an open transaction, so set it before the first write. The last insert shows the schema at work: an undeclared column is rejected.
PostgreSQL, and MySQL with InnoDB, enforce foreign keys without a switch.
import sqlite3
db = sqlite3.connect(":memory:")
print(db.execute("PRAGMA foreign_keys").fetchone()) # 0 means off
db.executescript("""
create table users(id integer primary key, name text not null);
create table orders(id integer primary key,
user_id int not null references users(id));
""")
db.execute("insert into orders values (1, 999)") # no user 999, accepted
db.execute("delete from orders"); db.commit()
db.execute("PRAGMA foreign_keys = ON") # outside any transaction
try:
db.execute("insert into orders values (2, 999)")
except sqlite3.IntegrityError as e:
print("IntegrityError:", e)
try:
db.execute("insert into orders(id, user_id, coupon) values (3, 1, 'X')")
except sqlite3.OperationalError as e:
print("OperationalError:", e)(0,)
IntegrityError: FOREIGN KEY constraint failed
OperationalError: table orders has no column named coupon03Four NoSQL families, four access patterns
Key-value. The interface is roughly get(key) and put(key, value), and the store does not look inside the value. Amazon's Dynamo paper (2007) describes values as opaque blobs, usually under 1 MB, operations on a single key, no relational schema and no isolation guarantees. Sessions, carts and caches fit here.
Document. The value is a structured document, usually JSON or a binary form of it, and the database understands its fields, so you can index and query inside it. A write to one document is atomic even when it changes several nested parts. MongoDB caps a single document at 16 MiB.
Wide-column. Stores in the Bigtable and Cassandra family group rows under a partition key that decides where data lives, and typically keep rows sorted within a partition. "Latest 100 readings for sensor 17" becomes one contiguous read; a query that does not start from the partition key is slow or refused.
Graph. Data is nodes and edges, and queries follow edges. "Friends of friends who bought this book" is a short traversal there and a chain of self-joins in SQL.
The shared trade: lay data out so each main question touches one place, and accept that other questions get harder.
04Modelling: normalise the data or shape it for the read
Take an order with a customer and some line items. Normalised, that is a row in users, a row in orders and one row per item in items, tied together by foreign keys. As a document it is one JSON object with the user and the items embedded, as the diagram shows.
The embedded form makes the common read trivial: one lookup by ID returns everything the order page needs. The price is duplication. The user's name now sits in every order they placed, so a rename must find and rewrite all of them. In the measured run, user 42 had 10 orders: 1 row changed on the relational side, 10 documents on the document side.
MongoDB's modelling guide gives a workable rule. Embed data that belongs to the parent and is read or updated with it. Store a reference (an ID pointing elsewhere) when the child list can grow without bound, is very large, or changes on its own schedule.
Some duplication is correct. Each item line here stores price_cents, the price actually paid. That must not follow the catalog when the price changes next week, because the invoice has to show what was charged. The modelling skill is telling snapshot fields, which must never change, from live facts, which must stay in step.
The code at the end of this section builds both shapes from one synthetic dataset: 2,000 users, 20,000 orders (10 per user) and 60,137 item lines over 50 SKUs. Both run inside SQLite (its JSON functions serve the documents), so the comparison is about data shape, not products.
import sqlite3, json, random, time, statistics
random.seed(7)
users = [(u, f"user{u}", f"u{u}@example.com") for u in range(2000)]
orders = [(o, o % 2000, f"2026-10-{1 + o % 28:02d}") for o in range(20000)]
items = [(o, f"sku{random.randrange(50)}", random.randint(1, 4), random.randint(100, 5000))
for o in range(20000) for _ in range(random.randint(1, 5))]
rel = sqlite3.connect(":memory:") # normalised: 3 tables
rel.executescript("""
create table users(id integer primary key, name text, email text);
create table orders(id integer primary key, user_id int references users(id), created text);
create table items(order_id int references orders(id), sku text, qty int, price_cents int);
create index items_order on items(order_id);
create index orders_user on orders(user_id);""")
rel.executemany("insert into users values (?,?,?)", users)
rel.executemany("insert into orders values (?,?,?)", orders)
rel.executemany("insert into items values (?,?,?,?)", items)
lines = {}
for o, sku, q, p in items:
lines.setdefault(o, []).append({"sku": sku, "qty": q, "price_cents": p})
doc = sqlite3.connect(":memory:") # one JSON document per order
doc.execute("create table orders(id integer primary key, body text)")
doc.executemany("insert into orders values (?,?)", [
(o, json.dumps({"_id": o, "created": c, "items": lines[o],
"user": {"id": u, "name": users[u][1], "email": users[u][2]}}))
for o, u, c in orders])
05Measured: the same 20,000 orders both ways
The script below continues the previous one and times four questions a shop asks (medians in milliseconds; one full run shown).
One whole order came out level at about 0.006 ms. With an index on items.order_id SQLite runs three index searches, which costs about the same as one primary-key lookup plus parsing a 266-byte JSON string. Joins are slow when they scan: in a variant that dropped that index, the plan became a full scan of the items table and the join took about 2 ms, around 300 times slower.
The top-selling SKU was sku21 with 3,154 units in both models. GROUP BY took 21 ms; the document version took 115 ms, because it parsed every document and unpacked its item array first.
One user's orders took 22 ms over documents with no index, since every document was opened. An index on the expression body->>'$.user.id' brought it to 0.005 ms, level with the relational index.
Schema drift is the last pair of lines. One document spells the field quantity instead of qty. Nothing complains, the sku17 total stays at 3,021, and 500 units vanish from the report without an error. The relational table refuses such an insert.
Across four runs the absolute timings moved a lot (GROUP BY ranged from 21 to 39 ms), but the document report was 3.9 to 5.4 times slower every time. This models two data shapes on one sandbox machine; it is not a benchmark of MongoDB, PostgreSQL or any product. A single node also cannot show the main reason teams choose NoSQL, covered next.
def ms(fn, reps):
ts = []
for _ in range(reps):
t = time.perf_counter(); fn(); ts.append(time.perf_counter() - t)
return round(statistics.median(ts) * 1000, 4)
pick = lambda n: random.randrange(n)
JOIN = """select o.created, u.name, i.sku, i.qty from orders o
join users u on u.id = o.user_id join items i on i.order_id = o.id
where o.id = ?"""
print("one order, join ", ms(lambda: rel.execute(JOIN, (pick(20000),)).fetchall(), 2000))
print("one order, document ", ms(lambda: json.loads(doc.execute(
"select body from orders where id = ?", (pick(20000),)).fetchone()[0]), 2000))
AGG_R = "select sku, sum(qty) s from items group by sku order by s desc limit 1"
AGG_D = """select j.value->>'sku' sku, sum(j.value->>'qty') s
from orders, json_each(orders.body, '$.items') j
group by sku order by s desc limit 1"""
print("top SKU, GROUP BY ", ms(lambda: rel.execute(AGG_R).fetchall(), 5), rel.execute(AGG_R).fetchall())
print("top SKU, json_each ", ms(lambda: doc.execute(AGG_D).fetchall(), 5), doc.execute(AGG_D).fetchall())
BY_USER = "select id from orders where body->>'$.user.id' = ?"
print("user's orders, rel ", ms(lambda: rel.execute(
"select id from orders where user_id = ?", (pick(2000),)).fetchall(), 200))
print("user's orders, doc ", ms(lambda: doc.execute(BY_USER, (pick(2000),)).fetchall(), 200))
doc.execute("create index doc_user on orders(body->>'$.user.id')")
print(" + expression index", ms(lambda: doc.execute(BY_USER, (pick(2000),)).fetchall(), 200))
print("rename, rows changed", rel.execute("update users set name='Priya' where id=42").rowcount)
print("rename, docs changed", doc.execute("""update orders set body = json_set(body,
'$.user.name', 'Priya') where body->>'$.user.id' = 42""").rowcount)
doc.execute("insert into orders values (?, ?)", (20000, json.dumps(
{"_id": 20000, "user": {"id": 1}, "items": [{"sku": "sku17", "quantity": 500}]})))
SKU17 = """select sum(j.value->>'qty') from orders, json_each(orders.body, '$.items') j
where j.value->>'sku' = 'sku17'"""
print("sku17 units, docs ", doc.execute(SKU17).fetchone()[0])
print("sku17 units, rel ", rel.execute("select sum(qty) from items where sku = 'sku17'").fetchone()[0])one order, join 0.0059
one order, document 0.0066
top SKU, GROUP BY 21.1926 [('sku21', 3154)]
top SKU, json_each 115.1941 [('sku21', 3154)]
user's orders, rel 0.0048
user's orders, doc 22.2487
+ expression index 0.0049
rename, rows changed 1
rename, docs changed 10
sku17 units, docs 3021
sku17 units, rel 302106Scale, consistency and transactions
The one-key model pays off when data no longer fits on one server. If every request names a key, the key decides which machine to ask, and the request touches one node. Dynamo placed keys with consistent hashing and virtual nodes and copied each key to N replicas. A write waited for W acknowledgements and a read asked R replicas; (N, R, W) = (3, 2, 2) was a common setting. With R + W > N the read and write sets overlap, which the paper calls "quorum-like", because its sloppy quorum and hinted handoff can still let a read miss the latest write during failures. Dynamo chose to stay writable and let replicas disagree for a while, resolving conflicts on read with vector clocks; the CAP theorem and quorums topic covers that trade.
A join across machines is harder. If users live on one shard and orders on another, every join is a network round trip, and a transaction spanning both needs a coordination protocol.
Relational databases scale out as well: read replicas are routine and sharding by customer ID is well established, as the database sharding and replication topics show. Once sharded, cross-shard joins become just as awkward. The difference is the starting point: relational designs start with full joins and give some up, NoSQL designs start without them.
Transactions have moved too. In MongoDB a single-document write is atomic, and its manual documents multi-document transactions on replica sets and sharded clusters, while warning that they usually cost more than single-document writes and should not replace good schema design. Other products differ, so check the exact product and version.
07Choosing for an online shop
Take the classic prompt: users, orders, a product catalog and carts.
Orders and payments belong in a relational database. They need foreign keys (an order must reference a real user), multi-row transactions (reduce stock and create the order together) and the ad hoc questions finance will ask later. Users sit beside them, joined to orders constantly.
The catalog is where a document shape helps. A laptop has RAM and screen size; a T-shirt has size and colour. That does not require a second database. PostgreSQL's jsonb column stores binary JSON, and a GIN index on it speeds up containment (@>) and key-exists (?) queries. Know its documented quirks: jsonb drops whitespace and key order, and keeps only the last of duplicate keys.
Carts and sessions are read and written by one key, very often, and losing a few minutes of a cart is tolerable. A key-value store fits, once traffic shows the main database straining.
Every extra store adds backups, monitoring and a sync path that will one day disagree; name the source of truth for each field.
- Joins, constraints or unplanned reports: relational.
- Nearly every read by one key at very high volume: key-value.
- Entities read whole with varying shape: document, or
jsonbin your relational database. - Time-ordered data per device or user at scale: wide-column.
- Questions about paths and connections: graph.
08Common interview questions
Q: SQL or NoSQL, in one answer?
A: Relational for joins, constraints, multi-row transactions and unpredictable queries; a NoSQL family when nearly every access uses a known key and data must spread across machines.
Q: Is NoSQL faster than SQL?
A: Only for the pattern it was shaped for. On 20,000 orders, one order took about 0.006 ms both as an indexed join and as a document, while a report across all orders took 21 ms with GROUP BY and 115 ms over documents.
Q: When do you embed and when do you reference?
A: Embed children read and updated with the parent. Reference lists that grow without bound, are large, or change on their own schedule. Embedding makes shared facts fan out: one rename rewrote 10 documents instead of 1 row.
Q: Is NoSQL schemaless?
A: The schema moves into application code, and every reader must handle every shape ever written. A misspelled field went in silently and dropped 500 units from a total.
Q: Can NoSQL databases do ACID transactions?
A: Depends on the product. Single-document writes are atomic in document stores, and MongoDB supports multi-document transactions, including on sharded clusters, at higher cost. Dynamo gave up isolation for availability.
Q: Document database or PostgreSQL with jsonb?
A: A document database for whole-document access by key that must scale out by that key, with no joins or cross-entity constraints. Otherwise jsonb with a GIN index often suffices.