Databases · lesson 6 of 6

Intermediate

Advanced

Also part of Databases

Databases advanced Lesson 6 of 6

Also part of: MongoDB

Normalized vs Embedded: a Tradeoff That Points in Two Different Directions

Fetching one customer with their orders costs about the same, indexed, in both a normalized PostgreSQL schema and an embedded MongoDB document. Searching across orders independent of their customer is measured 11x slower embedded — the same schema choice wins and loses depending which direction you query.

By SkillByExample · Published Sep 14, 2026

databases schema-design normalization postgresql mongodb

The same dataset, modeled two ways, measured in two different query directions — and the direction changes which model wins.

The same data, two schemas

PostgreSQL — normalized:

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), item TEXT, amount INT);
-- 5,000 customers, 50,000 orders (10 per customer)

MongoDB — embedded:

// one document per customer, orders embedded directly
{ customer_id: 1, name: "customer_1", orders: [ {item: "item_1", amount: 3}, ... 10 total ] }
// 5,000 such documents, 50,000 total order sub-documents

Same logical data, same total row/document count either way.

Query direction A: fetch one customer with their orders

CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN ANALYZE
SELECT c.name, o.item, o.amount FROM customers c
JOIN orders o ON o.customer_id = c.id WHERE c.id = 2500;

 Nested Loop  (cost=4.65..47.58 rows=10 width=24) (actual time=0.069..0.107 rows=10 loops=1)
   ->  Index Scan using customers_pkey on customers c (actual time=0.019..0.020 rows=1 loops=1)
         Index Cond: (id = 2500)
   ->  Bitmap Heap Scan on orders o (actual time=0.047..0.083 rows=10 loops=1)
         Recheck Cond: (customer_id = 2500)
         ->  Bitmap Index Scan on idx_orders_customer (actual time=0.024..0.024 rows=10 loops=1)
 Execution Time: 0.207 ms

db.customers.createIndex({customer_id: 1})
db.customers.find({customer_id: 2500}).explain('executionStats').executionStats

{
  nReturned: 1,
  executionTimeMillis: 6,
  totalKeysExamined: 1,
  totalDocsExamined: 1,
  ...
  stage: 'FETCH', inputStage: { stage: 'IXSCAN', indexBounds: { customer_id: [ '[2500, 2500]' ] } }
}

Postgres: 0.207ms, two index operations joined together. MongoDB: 6ms at whole-millisecond granularity (its explain() doesn’t report finer than that), one index scan straight into the document. Both are sub-10ms for this data size — roughly a tie. The real difference isn’t speed here, it’s operation count: Postgres did a join; MongoDB didn’t need one, because the orders were already sitting inside the document it fetched.

Query direction B: search across orders, independent of customer

EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 8;

 Seq Scan on orders  (cost=0.00..944.00 rows=15107 width=19) (actual time=1.611..2.511 rows=15000 loops=1)
   Filter: (amount > 8)
   Rows Removed by Filter: 35000
 Execution Time: 2.861 ms

15,000 matching rows, 2.861ms — a direct scan over a flat table of 50,000 rows.

db.customers.aggregate([
  { $unwind: "$orders" },
  { $match: { "orders.amount": { $gt: 8 } } },
  { $count: "matching" }
])

matching: [{"matching":15000}]
elapsed ms: 33

Same 15,000 matches, correct answer — but 33ms, a measured ~11x slower than the equivalent Postgres query. $unwind has to expand all 5,000 customer documents into 50,000 separate order-shaped records before it can even start filtering — it’s deconstructing every parent to get at its children, where Postgres’s orders table already exists as a flat structure with nothing to deconstruct.

The actual tradeoff

Neither schema is faster in general — each is faster for a different access pattern on the identical data:

Query pattern	Normalized (Postgres)	Embedded (MongoDB)
Fetch one parent + its children	Join, indexed — `0.207ms`	No join, one document — `~6ms`, roughly comparable
Search across all children by a property	Flat scan — `2.861ms`	Unwind every parent first — `33ms`, measured 11x slower

If an application’s dominant query is “show me this customer’s orders,” embedding removes a join and keeps the read to one document. If it’s “find all orders matching this filter, regardless of customer,” a flat table is the shape the query actually wants — the embedded model has to undo its own structure just to answer it.

Practice

1. Add a multikey index on the embedded field and re-run the cross-order search.

db.customers.createIndex({"orders.amount": 1})

Re-running the same $unwind/$match/$count aggregation after this index exists can help MongoDB locate matching sub-documents faster, but $unwind still has to reconstruct each match back out of its parent document — the structural cost of “children live inside a parent” doesn’t fully disappear the way it did for query direction A, where the parent lookup already was the entire answer.

2. Confirm the two schemas really do hold the same total order count.

SELECT COUNT(*) FROM orders;

db.customers.aggregate([{$unwind: "$orders"}, {$count: "total"}])

Both report 50000 — confirming the two measurements above are a fair comparison of the same underlying data, not an artifact of one side having more or less to search through.

3. Fetch a customer that doesn't exist and compare how each engine reports "nothing found."

SELECT * FROM customers WHERE id = 999999;

 id | name 
----+------
(0 rows)

db.customers.find({customer_id: 999999}).toArray()

[]

Both report an empty result rather than an error for a query that legitimately matches nothing — worth confirming directly rather than assuming, since a missing-parent-document case behaves differently in some embedded designs where a query expects a specific nested shape to exist.

That closes this pass. The thread through this whole track: comparing engines side by side turns “SQL vs NoSQL” from a page of asserted prose bullet points into six real measured mismatches — a query the ACID engine loses an update on too, a distribution summary that reports the wrong total in two unrelated engines, and a schema choice that wins or loses depending entirely on which direction the next query goes.

Frequently Asked Questions

So which is actually better, normalized or embedded?

Neither, in general — this lesson measures two opposite query patterns on the identical dataset (5,000 customers, 50,000 orders, 10 per customer) and gets two opposite winners. "Fetch a customer with their orders" is roughly a tie once both sides are indexed. "Search across all orders by a property, regardless of which customer they belong to" measured 11x slower on the embedded side. The right schema depends on which query pattern actually matters for the application, not a general rule.

Why was fetching one customer's orders roughly the same speed in both engines, once indexed?

Once PostgreSQL has an index on orders.customer_id and MongoDB has an index on customer_id, both queries resolve to "look up one thing by an indexed key" — Postgres does an index scan on customers plus a bitmap index scan on orders, joined via a nested loop; MongoDB does one index scan directly into the document holding both the customer and its embedded orders. Different mechanisms, same order of magnitude of work for this data size.

Why was searching across all orders by amount so much slower embedded?

PostgreSQL's orders table is flat — a scan reads 50,000 order rows directly. MongoDB's $unwind has to expand every one of the 5,000 customer documents into 10 separate order-shaped records first, then filter those — it's doing strictly more work: deconstructing every parent document to get at its children, rather than scanning a table of children that already exists as its own flat structure.

Could an index have closed that 11x gap?

A multikey index on orders.amount (indexing the amount field inside the embedded array) can help MongoDB find matches faster, but $unwind still has to materialize a matched order back out of its parent document — the structural cost of "children are nested inside parents" doesn't fully disappear the way indexing removed the cost difference in the other query pattern, because there the parent lookup itself was the whole answer.

Is this the same tradeoff as the embedding lesson in the mongodb track?

No — mongodb/schema-design-and-modeling.md is about what happens when an embedded array grows unbounded inside a single document (a real 5.9MB document, a real 50x fetch-time cost). This lesson keeps the array bounded (10 orders per customer, never growing) and instead compares two different query patterns against the same bounded shape, across two different engines — a different question entirely: not "is this array too big," but "which direction am I querying in."