SQL vs NoSQL: Store One Dataset in PostgreSQL and MongoDB

by Souy Soeng /October 29, 2026

PostgreSQL tables and a MongoDB document side by side, both storing the same customer order

SQL vs NoSQL comes down to one question: should related data be split across tables and joined back together, or kept together as a single document? The clearest way to see the difference is to store the same data both ways. In this guide you build one small shop dataset in PostgreSQL as tables and in MongoDB as documents, then query both and compare the results.

Along the way you will write a JOIN and an aggregation pipeline, add indexes, read query plans with EXPLAIN, and check that both databases give the same answers. By the end you will know how each model behaves and how to choose between them for your own project.

In short

PostgreSQL stores customers, orders and order items in separate tables linked by foreign keys.
MongoDB stores each order as one document with its items embedded inside it.
SQL rebuilds an order with a JOIN, while MongoDB filters with find and reshapes data with aggregate.
Index the field you filter on, then confirm the index is used with EXPLAIN in both databases.
Choose SQL for strong relations and transactions, and documents for whole-object reads.

Set a clear goal and install your tools

The goal is a fair comparison: one dataset, two databases, the same questions asked of each. You will store a small shop's orders in PostgreSQL and in MongoDB, query both, and put the results and query plans side by side. If the two systems ever disagree, that tells you something about how the data was modelled, and you will check for exactly that later.

Run everything on your own laptop. This keeps your query plans free of network latency and shared-server noise. It also means nothing important is at risk, so you can drop and rebuild the data as often as you like while you experiment.

Basic SQL familiarity, since you will write SELECT statements and JOINs
PostgreSQL installed locally
MongoDB installed locally
The psql and mongosh shells for typing commands into each database

Design a relational schema in PostgreSQL

In a relational model, each kind of thing gets its own table. A customer is stored once, and every order points back to that customer by its id. Open psql on a database called shop, create a customers table with a serial primary key and a name, and then create an orders table whose customer_id references customers. Add a total and a timestamp to orders. Each statement replies with CREATE TABLE.

The REFERENCES clause is the foreign key, and it matters. PostgreSQL will now refuse any order whose customer_id does not exist, so the database itself guards the relationship instead of your application code. Order items need a home too: create an order_items table with an order_id foreign key, a sku and a quantity. One order then fans out into many item rows.

Each table replies CREATE TABLE when it is created
orders.customer_id references customers(id)
order_items.order_id references orders
Inserting an order for a missing customer is rejected

psql -d shop

Model each order as a MongoDB document

Now shape the same data as documents. Where SQL spreads one order across three tables, MongoDB keeps it together in a single document. Think of it as the whole receipt in one envelope rather than filed across separate drawers. In mongosh, open the shop database and call db.orders.insertOne with a customerId of 1, the customer's name, and an items array holding two line items, each with a sku and a quantity. Remember to wrap the line items in square brackets so items is an array. MongoDB acknowledges the insert and returns the new ObjectId.

Because the items are embedded, there is no separate order items collection. Reading one order returns every item with it, with no join needed. The trade-off is copied data. Storing customerName on each order speeds up reads, but if Ana changes her name, every one of her orders must be updated. The rule of thumb is to embed what you read together.

insertOne returns acknowledged: true and an insertedId
items is an array inside the order document
Copied fields such as customerName must be updated everywhere they appear

Query PostgreSQL with a JOIN

Ask a real question: which orders belong to customer 1, and what is that customer's name? Because you split the data apart, each query has to put it back together, and that is the JOIN's job. Select the name, order id and total from customers c joined to orders o on o.customer_id = c.id, where c.id = 1. You should see a small table with Ana's order and a row count of 1 underneath.

The ON clause is where the foreign key pays off. PostgreSQL matches each order's customer_id to a customer id, and it can use an index on that column to do it quickly. Results always come back as flat rows. If you also join order items, each item becomes its own row with the order details repeated, so your application has to regroup them.

The ON clause matches orders to customers by key
The query for customer 1 returns Ana's order with its total
Joining items produces one row per item, not a nested list

Query MongoDB with find and aggregate

Ask MongoDB the same question using two tools. Neither one needs a join, because the items already live inside each order. The work shifts from combining data to filtering and reshaping it. Running db.orders.find with customerId 1 returns the whole document, items included. It is the closest thing to SELECT * with a WHERE clause, and ideal when your app wants the entire order at once.

For summaries, use an aggregation pipeline. Match customerId 1, unwind the items array, then group by sku and sum the quantities. You get one result per sku with its total. Stages run in order, each feeding the next, so put $match first to cut down how many documents flow through. This is MongoDB's answer to GROUP BY, built step by step.

find returns complete documents that match a filter
$match comes first to reduce the documents processed
$unwind turns each array item into its own document
$group produces one result per sku

Add indexes and read the query plans

Both queries work, but are they efficient? They both filter on the customer, so that is the field to index. In psql, create an index called idx_orders_customer on orders (customer_id), then run EXPLAIN on the SELECT where customer_id = 1. You want to see Index Scan, not Seq Scan, which reads every row. In mongosh, createIndex on customerId returns the name customerId_1. Run the same find with explain and open the winning plan. You want an IXSCAN stage, not COLLSCAN, which scans the whole collection.

Tiny tables can mislead you. With only a few rows, PostgreSQL may decide a sequential scan is cheaper and skip your index entirely. That is a sensible choice by the planner, not a broken index. Load more sample data before you judge the plan.

PostgreSQL plan shows Index Scan using idx_orders_customer
MongoDB winning plan contains IXSCAN
Seq Scan or COLLSCAN on a tiny dataset may not be a problem

db.orders.createIndex({ customerId: 1 })
# returns 'customerId_1'
db.orders.find({ customerId: 1 })
  .explain().queryPlanner.winningPlan
# look for IXSCAN, not COLLSCAN

Confirm both databases give the same answers

Two models of the same data should always agree, so check them. Counting is the simplest test. For customer_id = 1, count(*) in PostgreSQL returns 1 order and countDocuments in MongoDB also returns 1. For items, a join in SQL gives 2 and the array length in MongoDB gives 2. When the counts match, your two models really do describe the same data.

Numbers alone are only half a pass. Rerun both EXPLAINs and confirm you see Index Scan in PostgreSQL and IXSCAN in MongoDB. Then spot-check one record by eye: pull Ana's order from both systems and compare skus and quantities line by line. When counts, plans and a real record all agree, you are done.

Order count for customer 1 is 1 in both databases
Item count is 2 in both databases
Both plans show an index being used
Ana's skus and quantities match line by line

Fix the most common errors

If the numbers do not line up or a query crawls, the cause is usually one of four familiar problems. A slow lookup shows Seq Scan or COLLSCAN in the plan, so index the filtered field and rerun EXPLAIN. Zero matches often mean a string id is being compared with a number, so store one type consistently. A count that is too high usually means the join repeats orders, so count distinct ids.

Huge documents are the fourth problem. If an embedded array grows without limit, move the items into their own collection. MongoDB rejects any single document over 16 MB, so an unbounded array will eventually fail on write. Bound what you embed from the start and this never turns into an emergency.

Slow lookup: index the filtered field, then rerun EXPLAIN
Zero matches: use the same type for ids everywhere
Count too high: count distinct ids after a join
Huge documents: keep arrays bounded, well under the 16 MB limit

Choose the model that fits how you read data

Neither database is simply better. Each is shaped around a different kind of question. Choose SQL when your data has strong relations across many tables, when several changes must all succeed together in a transaction, and when you expect to ask new, ad hoc questions later. Choose a document database when shapes change often, the data nests naturally, and your app reads one whole object at once.

Model the reads first. In MongoDB, list the queries your screens actually make and shape documents so each can be answered in one fetch. In SQL, normalise first, then index for those same queries. Mixing is also fine: plenty of teams keep orders and payments in PostgreSQL and a product catalogue in MongoDB, running both side by side.

Strong relations and transactions point to SQL
Whole-object reads and changing shapes point to documents
Design around your most common queries
Using both in one system is a valid choice

Key takeaways

The same order can be stored as related table rows or as one document with embedded items.
SQL reassembles data at query time with JOINs, while MongoDB stores it already assembled.
Index the field you filter on and confirm it is used with EXPLAIN in both databases.
Verify your models agree by comparing counts, query plans and one real record.
Watch for mismatched id types, repeated rows from joins, and unbounded arrays.
Let your data's relations and your app's read patterns decide the database.

Frequently asked questions

What is the main difference between SQL and NoSQL databases?

A SQL database like PostgreSQL splits related data into separate tables linked by keys and rebuilds it with JOINs. A document database like MongoDB keeps related data together in one document, so a single read returns the whole object.

Does MongoDB support joins?

In this example no join is needed, because each order's items are embedded inside the order document. MongoDB uses find for simple lookups and an aggregation pipeline with stages such as $match, $unwind and $group to filter and summarise data.

Why does PostgreSQL ignore my index?

On very small tables, the planner may decide that a sequential scan is cheaper than using the index. Load more sample data and run EXPLAIN again before deciding the index is not working.

What is the MongoDB document size limit?

MongoDB rejects any single document larger than 16 MB. If an embedded array can grow without limit, move those items into their own collection to avoid write failures.

Why is my SQL count higher than my MongoDB count?

When you join orders to their items, each order is repeated once per item, which inflates a plain count. Count distinct order ids instead so each order is counted once.

Should I use SQL or NoSQL for my project?

Choose SQL when data has strong relations, needs transactions, or will face new ad hoc queries. Choose a document database when data nests naturally, shapes change often, and your app reads whole objects at once. Many systems use both side by side.

Watch the full video on YouTube →

Tag

Ad