MongoDB → PostgreSQL

When to migrate from MongoDB to PostgreSQL?

• ✓Data has stabilised — schema flexibility no longer outweighs query power
• ✓Need complex JOINs, window functions, or transactional integrity
• ✓MongoDB Atlas costs are exceeding expectations at scale
• ✓Reporting stack expects SQL — BI tools, dbt, Metabase
• ✓Compliance requires row-level security or full audit trail
• ✓Want to consolidate — one database for both OLTP and analytics

How Schema Inference Works

MongoDB has no schema. DBMigrateAIPro samples up to 10,000 documents per collection (configurable) and infers the dominant schema:

1. 1.For each field, the type distribution is computed across the sampled documents.
2. 2.Fields present in ≥ 80% of documents become explicit columns.
3. 3.Fields below the threshold are merged into a jsonb_extra JSONB column.
4. 4.Nested subdocuments below a configurable depth are stored as JSONB.
5. 5.Arrays of scalars with a consistent element type become native arrays (TEXT[], INTEGER[]).
6. 6.Arrays of objects become JSONB[] or optionally a child table with a foreign key.

Review the inferred schema in the Schema tab before running migration. You can override any field type or flattening depth.

MongoDB 4.0+ (source). For CDC: replica set mode (rs.initiate()) — Change Streams require a replica set. A read-only user with readAnyDatabase or per-collection read access is sufficient for bulk migration. PostgreSQL 14+ target recommended for JSONB performance.

The assessment for MongoDB reports: collection count, estimated document count, average document size, polymorphic field detection, deeply nested field analysis (depth > 3 flagged), DBRef references (cross-collection foreign keys), and GridFS collections.

After assessment, open the Schema tab. Each collection maps to a proposed PostgreSQL table. Review the inferred columns, types, and which fields are merged into jsonb_extra. Adjust thresholds or override individual field types before proceeding.

Click Create Schema on Target to execute the generated CREATE TABLE statements on PostgreSQL. Check the DDL preview before executing. ObjectId fields default to VARCHAR(24) — change to UUID in settings if you prefer native UUID storage.

Documents are streamed from MongoDB via the driver cursor and inserted into PostgreSQL using COPY. Arrays and nested objects are serialised to JSONB. Parallel workers default to 8. For large collections, increase workers and set batch_size to 5000–10000 documents.

After bulk migration, enable CDC in the CDC tab. DBMigrateAIPro subscribes to the MongoDB Change Stream and applies INSERT/UPDATE/DELETE/REPLACE events to PostgreSQL. Resume token is saved — migration survives restarts.

Run the Comparison Report to validate document-to-row counts per collection. Review the orphan-reference report for unresolved DBRef fields. Update your application connection string to PostgreSQL when satisfied.