SQL vs NoSQL: choose the data shape first

For SQL vs NoSQL, start with your access patterns: they decide the data shape, the shape decides most of the cost, and many databases can hold either shape. In our lab one SQLite engine held the same 100,000 users as normalized tables and as one document per user, and the winner flipped with the workload.

How should access patterns guide SQL vs NoSQL?

"SQL or NoSQL" mixes two decisions. One is the data shape: do you keep shared facts in their own records and connect them by ID, or do you put related data inside one self-contained record? The other is the database product. The two do not always travel together. SQLite and PostgreSQL store JSON documents in a column (SQLite JSON functions, PostgreSQL JSON types), and MongoDB's own modeling guide explains when to use references instead of embedding (embedding vs references).

So ask what your app does most: read one whole object, or change data that many objects share? Answer that, pick the shape, then pick a database that serves that shape well. The same reasoning shows up when you split reads and writes across databases: the workload comes first.

What our lab measured

The lab (labs/sd-21/run.mjs) builds one in-memory SQLite database with Node 22's built-in node:sqlite and loads identical synthetic shop data twice. Engine, process and memory are shared, so vendor, network and driver differences are out and only the shape changes.

-- shapes
normalized: users(id, name, email)
            products(id, name, price_cents)
            orders(id, user_id, product_id, qty, price_cents, ordered_at)  + index on orders(user_id)
document:   user_docs(user_id, doc)   doc = {"id":8, ..., "orders":[{"productName":"Product 0001", ...}, ...]}

-- data (seed 21)
100,000 users · 1 to 9 order lines each (500,397 total) · 1,000 products
popularity 1/rank: product 1 is in 13.5% of order lines and in 47,999 user documents

Each measured pair runs both shapes, alternating which goes first, and every result is checked: both shapes must return identical profiles and report rows, and after a rename no stale copy may remain. Results at 100,000 users on one Apple M2 Pro:

Workload	Tables (median)	Documents (median)	Samples	Winner
Load one profile with its orders	0.0130 ms	0.0036 ms	300 each	documents, 3.6x
Rename the most popular product	0.017 ms, 1 row	628 ms, 47,999 docs rewritten	5 each	tables, by thousands of times
Revenue per product, all users	249.5 ms	982.4 ms	2 each	inconclusive (too few samples)

The rename ratio is large but imprecise: 0.017 ms is only about twice the lab's timer floor, so "thousands of times" is as exact as the lab can claim.

Why one rename rewrote 47,999 documents

With tables, the rename is UPDATE products SET name = ? WHERE id = 1: one row, 28 logical bytes. With documents, every copy of the name has to change, and the lab has no index on a value nested inside a JSON array, so it looks inside every document:

-- sd-21 rename, document shape (one transaction)
SELECT user_id, doc FROM user_docs d
WHERE EXISTS (SELECT 1 FROM json_each(d.doc, '$.orders') j
              WHERE json_extract(j.value, '$.productId') = ?);
-- then, for each match: parse, change the name, UPDATE user_docs SET doc = ? WHERE user_id = ?

That scanned 100,000 documents. The application reserialized 47,999 documents, totaling 34.4 MB of logical JSON content. The lab measures no disk-page, WAL or fsync traffic, so it cannot predict the on-disk timing gap. PostgreSQL documents one storage cost: an UPDATE writes a new row version, and large field values such as a big jsonb document are kept in TOAST storage, compressed or split into chunks, so a changed value is stored again (PostgreSQL TOAST).

MongoDB supports multikey indexes on array fields. Such an index can locate matching documents without scanning every document when the query planner selects it; maintaining current names still requires updating every matching document. Partial-update operators shrink the bytes sent per document, though the count of documents to update stays the same. The cost scales with how many records hold a copy, which is why the lab renamed the most popular product on purpose.

The lab runs each rename serially and then checks that no stale copy remains; it does not test concurrent traffic. Under concurrent writes the copy path has a second risk. Replacing a document from an earlier read can overwrite concurrent changes unless isolation or a version check prevents it. Use atomic field updates or compare-and-swap with retries. If copies must change atomically across documents, verify the database's transaction guarantees. For concurrent updates, also check how transactions protect a shared seat, and for copies that may lag, what eventual consistency allows.

Why the document won the profile read

Loading a profile with its orders is one primary-key lookup and one JSON.parse for the document shape. The normalized shape runs one query with two joins (users to orders through the orders(user_id) index, then each order to its product by primary key) and rebuilds the nested object in JavaScript. The lab estimates about 11 logical records for the normalized profile versus one document; it does not measure physical page reads. Both paths execute one query per profile. The measured advantage includes SQLite execution, result conversion and JavaScript object construction; production latency needs a separate benchmark.

The revenue report shows the other side: the document shape has to parse every document with json_each to answer a question it was not built for. At 100,000 users the lab only fit 2 samples per shape, below its 3-sample rule, so it reports no winner; at 10,000 users, with 27 samples, tables were 5.3 times faster (19.7 ms vs 104.8 ms).

How to choose the shape

1. List the top reads and writes with rough rates: "load profile, 2,000/s", "rename product, a few per day", "report, hourly".
2. Embed what is read together, owned by the parent and bounded, such as a user's settings or a post's last few comments.
3. Reference what is shared and changes: product names and prices, user display names. Copy them only if a stale copy is acceptable, or if you store a snapshot on purpose (an order should keep the price paid).
4. Watch growth. Unbounded arrays inside one document make every read and rewrite slower.
5. Then pick the database whose indexes, transactions and scaling model fit that shape and those rates. This is the same "requirements first" habit as deciding when to scale up or out.

Trade-offs

Shape	Good for	Costs you	Use when
Normalized tables	changing shared facts, ad-hoc questions across all data, strong constraints	joins on every whole-object read	shared data changes, or you cannot predict the questions
Embedded documents	reading one whole object in one lookup	copies to keep in sync, write amplification, scans for cross-cutting queries	data is read together, owned by one parent, rarely shared
Hybrid (IDs in documents, or JSON columns in tables)	most real apps	two models to keep consistent	hot reads need one object, shared facts stay in one place

Our take

Documents loaded profiles faster; normalized tables renamed shared products faster. Start from the two or three operations your app does most, choose the shape that makes those cheap, and treat the SQL or NoSQL product as the second decision. Keep shared, changing data in one place, and embed only what is owned and read together. If you cannot name those operations yet, we would start with tables, because they keep shared changes and new questions cheap.

These timings apply to this synthetic, in-memory workload on one machine. The lab compares no database products. Product behaviour is described from public documentation.