From MongoDB to PostgreSQL

Teams leave MongoDB for PostgreSQL when Atlas usage cost climbs, when the SSPL license becomes a procurement or compliance problem, or when per-node pricing on a replica set stops making sense for a workload that was never really horizontal to begin with. PostgreSQL is free, mature, and runs anywhere, including managed services like RDS, Cloud SQL, and Aurora. The trap is assuming this is a connection-string swap. It is not. You are changing data models and query languages at the same time.

The real decision: tables or JSONB

Before you move a single document, decide how each collection lands in Postgres. You have two honest options and most schemas use both.

• Normalize into relational tables when the document shape is stable. A users collection with predictable fields becomes a real table with typed columns, foreign keys, and check constraints. This is where Postgres earns its keep: referential integrity, joins, and a planner that has seen everything.
• Keep documents as JSONB when the schema is genuinely fluid or deeply nested. Postgres JSONB is binary, indexable, and queryable. You are not forced to flatten an event payload that has 80 optional keys.

A pragmatic hybrid: promote the fields you filter and join on into real columns, and park the long tail in a single JSONB column.

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  status      text NOT NULL,
  total_cents int  NOT NULL,
  payload     jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX ON orders USING gin (payload jsonb_path_ops);

Moving the data

pgloader can read a live MongoDB instance and load it into Postgres, casting BSON types as it goes. It is the fastest path for simple, well-behaved collections:

pgloader mongodb://user:pass@host/appdb postgresql://user:pass@pg/appdb

For anything with nested arrays, references, or shape drift, write a custom ETL. Stream collections with the Mongo driver, transform each document in code, and COPY into Postgres in batches. COPY is dramatically faster than row-by-row INSERT, so stage to a CSV or pipe directly to psql \copy.

Rewriting queries

This is the part that ambushes people. Every MongoDB query API call and aggregation pipeline stage has to become SQL.

• find({status: "open"}) becomes WHERE status = 'open'.
• $lookup becomes a JOIN, and it will usually be faster.
• A $group and $match pipeline becomes GROUP BY with HAVING.
• $unwind over an array maps to jsonb_array_elements or a normalized child table.

Plan to rebuild every index by hand. Mongo’s compound indexes do not transfer, and JSONB access wants GIN indexes while typed columns want B-tree. Audit your slow queries first and index for those.

The gotchas that surprise Mongo teams

A relational target enforces things Mongo let slide, and each surfaces during migration:

• Type consistency. A field that is sometimes a string and sometimes a number across documents is legal in Mongo and illegal in a typed column. Decide a canonical type and clean the outliers on load, or keep that field in JSONB.
• _id and object IDs. Mongo’s ObjectId is not a Postgres bigint. Map it to text or uuid, and pick a new primary-key strategy (a bigserial or uuid) rather than forcing the ObjectId shape.
• Missing versus null. Mongo distinguishes an absent field from a null one; a relational column collapses both to NULL unless you preserve the document in JSONB. Confirm the application does not rely on that distinction.
• Dates and numbers. BSON dates and Mongo’s number types (including the loosely typed defaults) need explicit casting to timestamptz, numeric, or bigint so precision and time zones survive the move.

Transactions and dual-run

Postgres gives you real multi-statement ACID transactions without the caveats Mongo attaches to multi-document writes across shards. Lean on BEGIN/COMMIT and SERIALIZABLE where correctness matters.

De-risk the cutover with a dual-run period. Keep MongoDB authoritative for writes, replicate into Postgres, and route a slice of read traffic to Postgres. Compare result sets and latency. When parity holds for days, flip writes.

Flipping writes without losing data

Because you are changing the data model and the query layer at once, the cutover is an application release, not just a database switch. Sequence it so the rewritten queries and the new schema ship together: dual-run reads until result parity holds over a representative period, then pick a low-traffic window to flip writes to Postgres. Keep MongoDB intact and, ideally, still receiving a copy of writes for a short hold-back period so rollback is repointing the application rather than reconstructing lost data. Watch for write patterns that assumed Mongo’s per-document atomicity but now span multiple relational tables; wrap those in a transaction before cutover, not after you find a partial write in production.

Where MongoDB still wins

Be honest about staying put. Do not migrate when:

• Your data is truly schemaless and shapes change weekly. Forcing that into columns is a tax you pay forever.
• You need real horizontal sharding at scale. Mongo’s sharding is built in; Postgres needs Citus or manual partitioning to match it.
• The application is document-native, with code that reads and writes whole nested objects and never joins. Rewriting all of that query logic may cost more than the license you are trying to escape.
• You depend on Mongo Change Streams or Atlas Search and have no appetite to rebuild them with Postgres logical replication and full-text search.

Is the re-platform worth it?

MongoDB to PostgreSQL is a genuine re-platforming, not a connector swap. The work splits into three buckets: modeling each collection as tables, JSONB, or a hybrid; moving data with pgloader or a COPY-based ETL; and rewriting every query and index from the Mongo API to SQL. Done well, you trade per-node and license cost for a planner and integrity model that will serve you for years. Done carelessly, you inherit a worse version of both. Run both databases in parallel until read parity is proven, then cut writes over. Model the license savings, managed-service cost, and rewrite effort in the calculator above before you commit.