PostgreSQL or MongoDB: choosing by the shape of your data

Both are excellent, both are mature, and both will run whatever you are building. Anyone who tells you one of them is simply better has not been asked the follow-up question. The choice matters at the edges, and the edges arrive later than you expect - usually the first time you need to ask a question the schema was not designed for, or the first time a half-written record costs you money.

The short version: if your data is entities that reference each other and you will need to ask unplanned questions about it, use PostgreSQL. If your data is self-contained documents that vary in shape, are read back whole, and are written whole, MongoDB will be pleasant. If you genuinely cannot tell, use PostgreSQL, because it can store documents and the reverse is much less true. The rest of this post is the reasoning, with the specifics that let you check it against your own application.

The one difference everything else follows from#

PostgreSQL stores rows in tables with a fixed set of typed columns, and the relationships between tables are expressed by keys and resolved at query time by joins. MongoDB stores BSON documents in collections, each document self-contained, with nested objects and arrays inside it, and relationships either embedded or resolved by a second query.

Everything else - transactions, schema, indexing, how much memory each wants, how you upgrade - comes out of that. A concrete example makes it obvious. An order with three line items and a customer:

-- PostgreSQL: three tables, one querySELECT o.id, o.placed_at, u.email,       i.sku, i.quantity, i.price_centsFROM orders oJOIN users u ON u.id = o.user_idJOIN order_items i ON i.order_id = o.idWHERE o.id = 4821;

// MongoDB: one document, one readdb.orders.findOne({ _id: 4821 })// {//   _id: 4821, placedAt: ISODate("2026-04-02T10:11:00Z"),//   user: { id: 77, email: "a@example.com" },//   items: [//     { sku: "AB-1", quantity: 2, priceCents: 1200 },//     { sku: "CD-9", quantity: 1, priceCents: 4500 }//   ]// }

The document version is one disk read and arrives in the shape your code already uses. The relational version costs a join but stores the customer's email once, so changing it changes it everywhere. That is the whole trade in one screen: documents optimise for reading a thing, tables optimise for knowing a fact once.

Schema: enforced by the database, or enforced by you#

PostgreSQL will not let you insert a row that does not match the table. Columns have types, NOT NULL means not null, a foreign key refuses to point at a row that does not exist, and a CHECK constraint refuses nonsense. The cost is that changing the shape is an operation you have to plan - see schema migrations without downtime for how that goes.

MongoDB will store whatever you send it. That is genuinely useful when the shape is still moving weekly, and it is a slow leak when it is not: after two years you have four generations of document in one collection and every read path has to cope with all four. The cure is schema validation, which MongoDB has had since 3.6 and which almost nobody switches on:

db.createCollection("orders", {  validator: { $jsonSchema: {    bsonType: "object",    required: ["userId", "placedAt", "items"],    properties: {      userId:   { bsonType: "int" },      placedAt: { bsonType: "date" },      items:    { bsonType: "array", minItems: 1 }    }  }},  validationLevel: "moderate",   // only documents you update must pass  validationAction: "error"      // reject rather than warn})

validationLevel: "moderate" applies the rule to inserts and to updates of documents that already pass, which is exactly what you want when you are tightening the rules on a live collection. Turn validation on at the start of a project, not after the mess.

The honest summary is that both databases have a schema. In one it is written in the database and checked on every write. In the other it is written in your application code, spread across every function that reads the collection, and checked by hope.

Queries: SQL, joins and the aggregation pipeline#

SQL's strength is not that it is nice to write. It is that it is declarative and old, which means a planner can rewrite your query, and forty years of people asking awkward questions have been absorbed into the language. Window functions, common table expressions, GROUP BY ROLLUP, lateral joins, recursive queries for a tree - all standard, all available, none of which you have to think about until the day you need one.

MongoDB's equivalent is the aggregation pipeline: an array of stages, each transforming the stream of documents.

db.orders.aggregate([  { $match: { placedAt: { $gte: ISODate("2026-04-01") } } },  { $unwind: "$items" },  { $group: { _id: "$items.sku",              units: { $sum: "$items.quantity" },              revenue: { $sum: { $multiply: ["$items.quantity", "$items.priceCents"] } } } },  { $sort: { revenue: -1 } },  { $limit: 20 }])

That is a perfectly good query and it is not hard to read. Two practical differences show up at scale. First, the $match stage has to come first if you want the index used, and pipelines assembled by application code have a habit of putting it somewhere else. Second, joins exist - $lookup performs a left outer join, and it can use an index on the joined field - but there is no planner deciding between a hash join and a merge join based on statistics. A three-way join across large collections is where document databases stop being fun.

The related point that catches people: countDocuments() in MongoDB actually counts, and on a big collection it is slow. estimatedDocumentCount() is instant but reads collection metadata, so it ignores your filter. PostgreSQL has the same problem from the other side - COUNT(*) with a WHERE clause is a scan unless an index covers it. Neither database has a free count, and any page that displays a total row count will eventually be the slowest page you own. Reading EXPLAIN ANALYZE is how you find out which of your queries that is.

Transactions, and what a half-finished write costs#

PostgreSQL has had proper multi-statement, multi-table ACID transactions since forever. BEGIN, do five things, COMMIT, and either all five happened or none did. The default isolation level is read committed; REPEATABLE READ and SERIALIZABLE are available when you need them.

MongoDB guarantees that a single document update is atomic, including changes to nested fields and arrays. For a well-embedded document that covers more cases than people expect: adding a line item to an order and updating the order total is one update on one document, and it either happens or it does not.

Multi-document transactions exist too, since 4.0, with one condition that matters enormously on a small server: they require a replica set. A standalone mongod cannot run them. If you are running a single instance and you want transactions, you run that single instance as a one-node replica set:

replication:  replSetName: rs0

// then, once, from mongoshrs.initiate()

That gives you an oplog, transactions, and change streams on one machine. It also means the connection string needs ?replicaSet=rs0 and the hostname in the replica set config must be one your application can actually resolve - the usual first failure. MongoDB connection strings covers the URI in detail.

Ask yourself one question: if the process died between two writes, would anyone lose money or trust? Payments, credits, stock levels, anything where two records must agree - that is the transactional case, and PostgreSQL gives it to you with no configuration at all.

Indexes, and the queries that go wrong without them#

Both databases are fast when indexed and embarrassing when not, and the failure looks identical: a query that was instant with ten thousand rows takes nine seconds with two million.

Two rules carry most of the weight. The leftmost prefix rule applies to both: an index on (status, created_at) helps a query filtering on status, or on both, but not one filtering only on created_at. And MongoDB's ESR rule - equality fields first, then sort fields, then range fields - is the single most useful piece of index advice in that ecosystem, covered properly in MongoDB indexes and schema design.

Where they genuinely differ: PostgreSQL will happily carry a dozen index types and combine several indexes for one query using a bitmap scan, and it charges you in write speed and in VACUUM work - see Postgres vacuum and bloat. MongoDB caps you at 64 indexes per collection and allows only one text index, and each index has to fit in memory alongside the working set or your read rate falls off a cliff. On a 1 GB instance, index size is the number to watch, not document count. Postgres indexes explained does the same job on the relational side.

Documents in Postgres: JSON, JSONB and when it is enough#

This is the fact that decides most arguments. PostgreSQL has a jsonb type: a binary document, indexable, queryable, with containment operators and path expressions. You can build the document model inside the relational one.

CREATE TABLE events (  id          bigserial PRIMARY KEY,  received_at timestamptz NOT NULL DEFAULT now(),  kind        text NOT NULL,  payload     jsonb NOT NULL);-- index the whole document for containment queriesCREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);-- "every event whose payload mentions this account"SELECT id, received_at FROM eventsWHERE payload @> '{"account": {"id": 77}}';-- pull one field out as textSELECT payload ->> 'source' AS source FROM events WHERE kind = 'webhook';

-> returns JSON, ->> returns text; forgetting which is which accounts for a good share of confused WHERE clauses. jsonb_path_ops makes a smaller, faster GIN index that only supports containment, which is usually all you query. And when one field inside the document turns out to matter, a generated column promotes it to a real, typed, indexable column without rewriting anything that reads the JSON.

The reverse move is much worse. A document store asked to behave relationally becomes joins written by hand in application code: fetch the orders, collect the user IDs, fetch the users, stitch them together in a loop. It works, it is slower, and every one of those stitches is a place where the data can disagree with itself.

So: use jsonb for the parts that are genuinely variable - webhook payloads, event bodies, per-tenant custom fields, API responses you cache - and columns for the parts that are not. A table that is one id column and one jsonb blob is a sign you should have used a document database. A document database being asked for a report across three collections is a sign of the opposite.

What each costs to run on a small server#

Both are comfortable on modest hardware if you configure them. Both are miserable on modest hardware at their defaults.

The Postgres number to change first is shared_buffers - around a quarter of the memory available to the server is the usual starting point - and the number to be careful with is work_mem, because it is allocated per sort or hash node per query, not once. Fifty connections running a query with three sorts at 64 MB is not 64 MB. Tuning Postgres for small servers has the full set.

MongoDB's number is the cache, and the thing to check is that it saw the container limit rather than the host's memory. Ask it directly:

db.serverStatus().wiredTiger.cache["maximum bytes configured"]

On a 1 GB instance that should be the 256 MB floor, not several gigabytes. If it reports something impossible, set storage.wiredTiger.engineConfig.cacheSizeGB explicitly and restart.

Connections are the other shared cost, and the reason is different in each. A PostgreSQL connection is a process with its own memory, so a hundred idle connections is real memory gone; MongoDB's are threads and cheaper, but a pool of 100 per application process still adds up. Either way the fix is the same and it is not a bigger plan - see connection pools and the error that arrives at the worst time.

On RE:NODE, both are sold as your own server rather than a shared cluster: PostgreSQL from $6 a month and MongoDB from $7, tiers running from 1 GB of memory and 20 GB of NVMe up to 14 GB and 100 GB, with the superuser password generated per server instead of being the published default that stock images ship with. You get the files and the console, so postgresql.conf and mongod.conf are yours to edit, which is the whole point of the table above.

The decision table, and the honest default#

If you cannot decide, use PostgreSQL. It handles documents perfectly well with jsonb when you need them, and it costs you nothing on the day you need a join, a transaction or a report. The cost of choosing PostgreSQL and never needing its strictness is a few more CREATE TABLE statements. The cost of the other mistake is a rewrite.

Two things that should not be part of the decision. Speed: at the scale of one small server, both are limited by your indexes and your queries, not by the engine, and a missing index is worth more than any engine choice. And "which one scales": MongoDB's sharding is genuinely built in, but a sharded cluster is config servers plus routers plus replica sets, which is not a thing you run beside a hobby project. Read replicas and a bigger box get almost everyone further than either.

Whichever you pick, the operational work is the same shape: a real backup with a tested restore (database backups and restores), a locked-down network position (database security checklist), and a dump you know how to take by hand, with pg_dump and pg_restore or mongodump and mongorestore.

FAQ#

Is MongoDB faster than PostgreSQL?

For fetching one self-contained document by its key, usually yes, because it is one read instead of a join. For almost everything else the difference is dominated by whether you have the right index. Benchmarks that show a large gap are normally comparing a tuned instance of one against a default install of the other.

Can PostgreSQL replace MongoDB with JSONB?

For most small and medium applications, yes. jsonb with a GIN index gives you schemaless storage, containment queries and path expressions, inside a database that can also do transactions and joins. What it does not give you is MongoDB's built-in sharding or its aggregation pipeline syntax.

Do I need a replica set to use MongoDB?

Not to store data, but yes for multi-document transactions and change streams - a standalone mongod supports neither. Running a single node as a one-member replica set with replSetName and rs.initiate() takes a minute and unlocks both.

Which one is cheaper to run on a small server?

They are close. PostgreSQL is more frugal with idle connections in absolute terms only if you pool properly, since each one is a process; MongoDB wants its WiredTiger cache plus room for indexes. On a 1 GB plan, both work fine for a real application, and in both cases the limiting factor is whether your working set and indexes fit in memory.

Can I use both in one application?

Yes, and plenty of people do - Postgres for the records that must be correct, MongoDB for event or document data. Be honest about the cost: two engines to back up, patch, monitor and connect to, and no transaction spanning the pair. Do it because a workload demands it, not to avoid choosing.

How hard is it to switch later?

Harder than moving hosts, easier than a rewrite. The data will convert; the query code will not. Every place your application touches the database has to change, which is why the choice is worth twenty minutes now rather than a fortnight in a year.