PostgreSQL or MongoDB: how to choose a database

← Back to the section

When you start a new project, sooner or later the question comes up: which database should you use? People usually pick it either out of habit ("we always use PG") or by fashion ("MongoDB is NoSQL, and NoSQL is modern"). Both approaches lead to problems.

Different databases for different tasks

PostgreSQL is a relational database. Data is stored in tables with a strict schema: every column has a type, constraints (NOT NULL, FOREIGN KEY), and rows from different tables can be combined with a JOIN.

MongoDB is a document store. Data is stored in collections of documents, where each document is a JSON object. The schema is flexible: two documents in the same collection can have a different set of fields.

Neither is better than the other overall. They solve different tasks.

When your data is interconnected

Imagine an online store: an order belongs to a customer, contains products, and products have categories and stock levels. These entities constantly reference each other.

For data like this, PostgreSQL is a better fit. JOINs between tables are its native operation. Foreign keys guarantee that you won't end up with an order pointing to a customer who doesn't exist. NOT NULL and CHECK catch errors before anything is written to the database.

MongoDB is a better fit when the typical query is "give me this whole object": a user profile with all its settings, an order with all its line items, an event with arbitrary attributes. It all sits together and is read in a single lookup.

What MongoDB keeps as one document, PostgreSQL assembles at read time out of three tables.

live example

SELECT jsonb_pretty(jsonb_build_object(
         'order_id', o.id,
         'status',   o.status,
         'customer', c.email,
         'items',    (SELECT jsonb_agg(jsonb_build_object('product', p.title, 'qty', i.quantity))
                      FROM order_items i
                      JOIN products p ON p.id = i.product_id
                      WHERE i.order_id = o.id)
       )) AS document
FROM orders o
JOIN customer c ON c.id = o.customer_id
ORDER BY o.id
LIMIT 1;

The output is exactly the document MongoDB would already have on disk. The difference isn't the result but the price: a join and a subquery put it together.

When the data structure is unstable

In a relational database the schema is designed up front: columns and types are declared before the first write. A field shows up later — you write a migration, and the table changes as a whole.

Sometimes that's inconvenient. A product catalog is a good example: a jacket has a size and a material, a TV has a screen size and a resolution, a book has an author and an ISBN. One table means either dozens of mostly empty columns or a separate key–value table for the attributes.

MongoDB handles this more easily: each document stores only its own fields.

// Jacket
{ "name": "Winter jacket", "size": "L", "material": "polyester" }

// TV
{ "name": "TV 55", "diagonal": 55, "resolution": "4K" }

This difference has established names: PostgreSQL follows schema-on-write (the database checks every row against an explicit schema at write time), MongoDB — schema-on-read (the structure is implicit, and the reading code interprets it). The "schemalessness" of a document database is an illusion: the schema has not gone anywhere, it has just moved from the database into application code.

You can see that move in a small program: the same three records go through the check at the door and then through the check at parse time.

live example

import java.util.List;
import java.util.Map;

public class SchemaCheck {
    public static void main(String[] args) {
        List<Map<String, Object>> incoming = List.of(
                Map.of("id", 1, "email", "ann@shop.io", "status", "active"),
                Map.of("id", 2, "email", "bob@shop.io"),
                Map.of("id", 3, "email", "cat@shop.io", "status", 7));

        for (Map<String, Object> row : incoming) {
            Object status = row.get("status");
            String verdict = status == null ? "rejected: no status column"
                    : status instanceof String ? "accepted"
                    : "rejected: status is not text";
            System.out.println("schema on write, row " + row.get("id") + ": " + verdict);
        }

        long active = incoming.stream().filter(d -> "active".equals(d.get("status"))).count();
        long broken = incoming.stream().filter(d -> !(d.get("status") instanceof String)).count();
        System.out.println("schema on read: all " + incoming.size() + " documents accepted");
        System.out.println("at parse time: active " + active + ", without a usable status " + broken);
    }
}

Two records out of three never made it past the door. A document store takes all three, and the two incomplete ones fall to the reading code — on every read.

Another word from the same toolbox is locality: a document sits as one contiguous chunk, so reading a profile or a product card fetches everything at once — where a relational schema would need a join. The flip side: the database loads and rewrites the whole document even for a small fragment, so large, frequently appended documents eat the advantage away.

One caveat: a schema that changes chaotically is not a reason to use MongoDB, it is a sign of a poorly designed domain.

Transactions: when an operation must either complete fully or not at all

Transferring money between accounts is the classic example: debiting one account and crediting another must happen as a single action, and if something goes wrong in the middle, both operations are rolled back.

PostgreSQL has supported full ACID transactions from the very beginning. A transaction across several tables is a routine operation.

MongoDB added multi-document transactions in version 4.0 (for a replica set), and for sharded clusters in 4.2. They carry more overhead than in PG. If most of your operations require transactions across several documents, that's a signal the schema would have been a better fit for a relational database from the start.

Data volume and scaling

As long as there isn't much data (up to a few hundred gigabytes on a single server), both databases work equally well. The difference shows up as you grow.

PostgreSQL does great on a single powerful server. Table partitioning (by range, by list of values, or by hash) solves most performance problems. Horizontal scaling across several servers is possible, but it requires a separate tool (Citus).

MongoDB was designed for horizontal scaling from the start. A sharded cluster is the standard operating mode for large clusters. If it's clear up front that you'll have tens of terabytes and more than one server, MongoDB simplifies the infrastructure.

PostgreSQL + jsonb: the third option people often forget

Between "pure relational" and "pure document" there's a middle option: PostgreSQL has a jsonb type that lets you store arbitrary JSON right in a column, build indexes on it, and search inside the JSON.

CREATE TABLE product (
    id          BIGSERIAL PRIMARY KEY,
    category_id BIGINT REFERENCES category(id),
    name        TEXT NOT NULL,
    attributes  JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- GIN index for fast search by JSON content
CREATE INDEX product_attributes_gin ON product USING GIN (attributes);

-- Find red products
SELECT * FROM product WHERE attributes @> '{"color": "red"}';

This gives you flexible attributes while keeping relationships, foreign keys, and transactions. If the task sounds like "we need flexible attributes, but the data is interconnected" — try jsonb before moving to MongoDB.

What each database is good at

The operational side

PostgreSQL is a mature tool with decades of practice behind it: migrations via Flyway or Liquibase, monitoring through pg_stat_*, backups and replication are all well trodden. It is not maintenance-free either: autovacuum and table bloat, the connection limit, a major version upgrade — chores of its own.

MongoDB requires a different set of skills: indexes are built by different rules, sharding is a separate engineering discipline, and backing up a sharded cluster is non-trivial. No one on the team with real MongoDB operating experience is a risk when the first serious problem hits.

Common mistakes when choosing

"MongoDB — because the schema might change." A flexible schema doesn't remove the need to design your data: a year later, if nobody wrote migrations, it's unclear which documents in the collection are valid.

"Let's use both for flexibility." Two sets of migrations, two monitoring setups, two backups, and the risk of data getting out of sync. You take on two databases when the nature of the tasks is genuinely different: transactional data in PG and a growing event log in MongoDB.

"PG + jsonb will always replace MongoDB." With deep nesting (several levels of arrays inside the JSON), jsonb queries read worse and run slower than the equivalent in MongoDB.

"MongoDB doesn't need migrations." It does. A new required field, a rename, a type change — those are the same migrations, only you write them by hand, and skipping them shows up later.

In short

• PostgreSQL is better when data is interconnected and you need JOINs, transactions, and foreign keys.
• MongoDB is better when data is read as whole documents, the schema is heterogeneous, and the scale implies several servers.
• The difference isn't only speed: with schema-on-write the database checks the structure, with schema-on-read the code that parses the document does.
• PostgreSQL + jsonb is the middle option: the flexibility of JSON while keeping relational guarantees.
• MongoDB supports transactions, but they're more expensive — if you need transactions constantly, you probably need PG.
• Two databases in one service are justified only when the nature of the tasks is genuinely different and the team can operate both.

Further reading

• JSONB in PostgreSQL — what it is, how it works, and when to use it — the middle option in detail: operators, GIN indexes, and where it hurts.
• Document modeling in MongoDB: embed vs reference, indexes — how to design the document if MongoDB is the choice.
• ACID, read and write concerns, transactions in MongoDB — what multi-document transactions really cost.
• Cassandra, PostgreSQL or MongoDB: when to reach for a wide-column NoSQL — the third option in the same decision.