MongoDB vs PostgreSQL

MongoDB and PostgreSQL Side by Side

PostgreSQL 18 1,289 , released in September 2025, is the current major version, and each major is supported for five years, so one you deploy today is patched into 2030. Start with what the two query languages look like for one job — active users in a country, newest first, with their paid order count.

The query in PostgreSQLSQL

SELECT u.id, u.name, count(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'
WHERE u.active AND u.country = 'MY'
GROUP BY u.id, u.name
ORDER BY u.created_at DESC LIMIT 20;

The same question as an aggregation pipeline

db.users.aggregate([
  { $match: { active: true, country: 'MY' } },
  { $lookup: { from: 'orders', localField: '_id', foreignField: 'userId',
      pipeline: [{ $match: { status: 'paid' } }], as: 'paidOrders' } },
  { $project: { name: 1, createdAt: 1, orders: { $size: '$paidOrders' } } },
  { $sort: { createdAt: -1 } }, { $limit: 20 }
]);

Both express the same intent and run acceptably given the right index. The difference appears as the question grows: add three joined tables and a window function and the SQL stays one statement the planner optimizes as a whole, while each added $lookup is a separate access path you reason about stage by stage. Conversely, if orders are read only per user you would embed them, and the pipeline collapses to a single find.

Where the two engines differ in ways that change your design
Concern	PostgreSQL 18	MongoDB 8.3 1,815
Schema enforcement	Declarative, in the engine	Optional JSON Schema validators
Multi-record atomicity	Default, any statement	Needs a replica set or sharded cluster
Joins	Planner chooses the algorithm	$lookup, evaluated per stage
Horizontal write scaling	Manual or via an extension	Built-in sharding
Variable shapes per record	JSONB column	Native

MongoDB has had multi-document transactions since 4.0, so "MongoDB cannot do transactions" is out of date — but they need a replica set or sharded cluster, default to a one-minute runtime limit, and MongoDB's own documentation says a distributed transaction "incurs a greater performance cost over single document writes" and should not substitute for effective schema design.

MENU