You have a Postgres / MySQL / Oracle workload. You want to move it to MongoDB — perhaps because the access pattern is document-shaped, the schema is changing too fast for migrations, or scaling write throughput requires sharding. The migration is half schema redesign, half data movement, half cut-over choreography. This post is the playbook: schema-mapping principles, data-load tooling (mongoimport, AWS DMS, Striim, custom ETL), dual-write strategies, staged cut-over, rollback plan, and the common traps.

Series map: 20 Outbox → 21 CQRS+ES → 22 RAG → 23 SQL→Mongo migration (this post) → 24 Kafka + Debezium → … → 29 K8s + mongosync + cost.

Learning Outcome

Decide whether the workload should move at all (and which parts).
Map relational schema to document model using Post 07's patterns.
Choose a data-movement strategy (full reload, dual-write, change-data-capture).
Implement the staged cut-over with rollback gates.
Avoid the 6 most common migration disasters.

Chapter 1 — Should you migrate?

Honest answer first: most SQL workloads should stay on SQL. Migration is expensive + risky. Strong reasons to move:

Schema flexibility required (per-tenant custom fields, fast product iteration, polymorphic items).
Write throughput beyond what Postgres can vertical-scale.
Hierarchical / document-shaped data (forms, content, configs, complex products) that's painful to join.
Horizontal scaling + multi-region + sharding required.
Operational simplicity at low ops headcount (Atlas managed > self-hosted Postgres cluster).

Weak reasons:

"NoSQL is modern."
"We want to avoid joins" (you'll write joins as $lookup either way).
"We need higher performance" (often a Postgres index would have fixed it).

Often the right answer is migrate a subset — the parts where the document model genuinely fits — and keep the rest in Postgres. The two coexist via outbox / CDC / app-side composition.

Chapter 2 — Schema mapping principles

Re-read Post 07 (Schema Design). Then map:

SQL pattern	MongoDB pattern
1:1 child table	embed sub-document
1:few (≤ 100) child rows	embed array
1:many (unbounded) child rows	separate collection + ref
many:many through join table	references both ways (+ counts denormalised)
polymorphic via STI (single table inheritance)	polymorphic pattern (Post 07 Ch 4) — discriminator field
polymorphic via CTI (class table inheritance)	one collection with discriminator
tree (adjacency list)	parent ref or ancestor array (Post 07 Ch 9)
audit history table	separate immutable history collection or Event Sourcing (Post 21)
timestamps columns	embedded or top-level Date fields
JSON column in Postgres	top-level fields directly
sequence / SERIAL primary key	ObjectId (Mongo default) or keep numeric per use
view	materialised view via `$merge` (Post 06)
trigger	change stream (Post 12) or Atlas Trigger (Post 16)

Worked mapping — e-commerce orders

Postgres:

1
2
3
4
5
6 -- 4 tables, joined for every order detail query
orders(id, user_id, status, total, created_at)
order_items(id, order_id, product_id, quantity, unit_price)
users(id, name, email)
products(id, name, sku, price)

MongoDB:

1
2
3
4
5
6
7
8
9
10
11
12 // one collection, denormalised
orders {
  _id, user_id,
  user_email_at_order: "...",          // extended reference
  status, total, created_at,
  items: [
    { p: product_id, name_at_order: "...", price_at_order: ..., qty: ... }
  ]
}
users { _id, name, email }
products { _id, name, sku, price }

The trade-off: the join you ran 1,000× per second goes away; product name changes don't retroactively rewrite past orders (which is usually correct anyway). A handful of cross-order reports use $lookup now and run nightly via $merge.

Four joined Postgres tables mapped to one denormalised MongoDB orders document, with users and products kept as their own collections

Chapter 3 — Data movement strategies

Pick by downtime tolerance + data size + complexity tolerance.

Strategy A — Full reload (hours of downtime)

Freeze writes to source.
Extract source → transform → load into Mongo.
Cut DNS / connection string over.
Resume writes against Mongo.

OK for: < 100 GB and you can take a maintenance window.

Strategy B — Dual-write at app layer (low downtime)

App writes to both Postgres + Mongo for new operations.
Backfill historical data via ETL.
Read-shadow: in parallel with reads from Postgres, fetch from Mongo + log differences.
Switch reads to Mongo when shadow shows parity.
Stop writes to Postgres.

OK for: medium-size apps where you control the write path.

Strategy C — CDC-driven (best for large, can't-downtime)

Tail Postgres WAL via Debezium (or AWS DMS, Striim, Estuary, Airbyte).
Transform + write to Mongo continuously.
After sync catches up + remains in sync for a while, cut reads over.
After cut-over, drain Postgres writes; flip back-flow off.

OK for: large databases, strict uptime requirements, polyglot teams.

Strategy D — Strangler fig

New features write to Mongo only.
Old features stay on Postgres.
Migrate one feature at a time over months.

OK for: legacy systems where a big-bang move is too risky.

Chapter 4 — Tooling

mongoimport

CSV / TSV / JSONL → MongoDB.

1
2
3 mongoimport --uri "..." --db shop --collection products \
  --type csv --headerline --file products.csv

For one-shot loads of dumps.

mongosh load

Run JS scripts against the cluster:

1
2 mongosh "$MONGO_URI" --file migrate_orders.js

Useful for transforming on insert.

AWS DMS (Database Migration Service)

Continuously replicates Postgres / MySQL / Oracle / SQL Server → MongoDB Atlas. Configurable transformations (rename columns, denormalise child rows into arrays via "JSON-encoded selectors").

Pros: managed; one of the easiest CDC paths to Atlas. Cons: AWS-only; transformations limited.

Debezium + Kafka + Mongo Kafka Sink Connector

Debezium reads Postgres WAL → Kafka → MongoDB Kafka Connector applies events to Atlas. Most flexible (any transformations via Kafka Streams). More moving parts.

Post 24 covers this stack.

Custom ETL

For non-trivial denormalisation (joining 5 tables into one document), write a script:

1
2
3
4
5 # pseudo
for batch in stream_postgres("SELECT * FROM orders LIMIT ... OFFSET ..."):
    docs = transform(batch)        # joins + reshape
    db.orders.bulk_write([InsertOne(d) for d in docs], ordered=False)

Use bulk_write for throughput; chunk to memory-safe batches; checkpoint progress so resumes are cheap.

Striim, Airbyte, Estuary, Hevo, Fivetran

Managed ETL platforms that handle Postgres → Mongo with UI-driven transformations + monitoring. Cost money; save weeks.

Chapter 5 — Dual-write strategy walk-through

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 def create_order(input):
    # 1. write to Postgres (system of record during migration)
    pg_id = pg.insert("INSERT INTO orders (...) VALUES (...) RETURNING id", input)
 
    # 2. write to Mongo (idempotent: include source id)
    try:
        db.orders.insert_one({
            "_id": ObjectId(),
            "pg_id": pg_id,                              # link back to source
            ...
        })
    except Exception as e:
        log.warning("mongo write failed", e)              # don't fail the request
 
    return pg_id

Properties:

Postgres is still source of truth — Mongo failures don't break the user request.
Mongo writes are idempotent (linked by pg_id) — re-runnable.
Backfill script picks up rows in Postgres not yet in Mongo.

Read-shadow phase

1
2
3
4
5
6
7
8
9
10
11
12
13
14 def get_order(order_id):
    pg_order = pg.fetch_order(order_id)
 
    # in parallel, fetch from Mongo + compare
    threading.Thread(target=shadow_compare, args=(order_id, pg_order)).start()
 
    return pg_order
 
def shadow_compare(order_id, pg_order):
    mongo_order = db.orders.find_one({"pg_id": order_id})
    diff = compute_diff(pg_order, mongo_order)
    if diff:
        log.warning("diff", order_id=order_id, diff=diff)

Run for days/weeks until diffs are rare + understood. Then flip reads to Mongo for some endpoints; eventually all.

Chapter 6 — Staged cut-over (CDC strategy)

1
2
3
4
5
6
7
8
9 T0  — CDC pipeline live; replicating writes from Postgres to Mongo
T1  — Backfill historical data complete (script ran once)
T2  — Validate parity (row counts + sampled deep-equals + queries return same answers)
T3  — Read traffic split: 1% → Mongo, 99% → Postgres
T4  — Slowly raise % to Mongo; watch error rates + latency
T5  — 100% reads on Mongo; still dual-writing (or CDC still active)
T6  — Stop writes to Postgres; CDC drained; cut over
T7  — Postgres becomes read-only / archive

Gates between steps: error budget; latency budget; data-parity assertions. Don't proceed without crossing them.

Rollback plan at each gate:

T3-T4: trivial — route reads back to Postgres.
T5: still possible — Postgres data is fresh until T6.
T6+: harder — need reverse CDC or accept the cut.

Chapter 7 — Validation

After load (or continuously during CDC):

1
2
3
4
5
6
7
8
9
10
11
12
13
14 // counts match
db.orders.countDocuments();
// vs SELECT COUNT(*) FROM orders;
 
// per-key check (random sample)
db.orders.find().limit(100).forEach(doc => {
  const pg = pg.fetch(doc.pg_id);
  assert.deepEqual(slimMongo(doc), slimPg(pg));
});
 
// aggregate sanity
db.orders.aggregate([{ $group: { _id: null, sum: { $sum: "$total" } } }]);
// vs SELECT SUM(total) FROM orders;

Automate. Run in CI. Alert on regressions.

For large collections use statistical sampling + checksums per shard / per day rather than full equality.

Chapter 8 — Application-layer changes

The migration isn't just data movement — your code changes too.

SQL idiom	MongoDB equivalent
`JOIN`	embed first; `$lookup` only when must
`WHERE x IN (subquery)`	`$lookup` + `$match` or 2-step query
`GROUP BY` + window functions	`$group` + `$setWindowFields`
`ORDER BY` + `LIMIT` + `OFFSET`	sort + skip (small) or range pagination (big)
Transactions across many tables	rethink with single-doc atomicity + embedded data first
Stored procedures	service-layer code
Triggers	change streams
Views	aggregation pipelines + `$merge` materialised views
`LIKE '%x%'`	text index, regex (left-anchored), Atlas Search
ORMs	Mongoose / Beanie / Prisma / official driver

Plan the code refactor in parallel with the data move. Don't try to make Mongo behave like Postgres — you'll get worst of both.

Chapter 9 — Common pitfalls

Pitfall	Cause	Fix
Lift-and-shift schema	one Mongo collection per SQL table	redesign for document shape
50 `$lookup`s per request	over-normalised	denormalise / embed
ObjectId vs numeric ID mismatch	app expects integer	keep numeric IDs during transition
Date type conversion fails	timezone / format inconsistencies	normalise to ISO 8601 UTC
Foreign-key constraints lost silently	Mongo doesn't enforce	app-layer validation + tests
Sequence ID gaps	new ObjectIds vs SERIAL	use new sequence or rely on ObjectId ordering
Migration script OOM on big tables	not batching	chunk + checkpoint
Forgot indexes on Mongo	full collection scans in prod	create indexes BEFORE traffic
CDC lag during big writes	undersized destination	scale Atlas tier before cut-over
No rollback plan	got stuck after T6	dual-write until you're 100% sure
Bigger bill than expected	wrong tier sizing	benchmark + size with safety margin

Chapter 10 — Rollback patterns

If Mongo turns out wrong (worse perf, missing feature, business decision change):

During dual-write window: trivial. Switch back, drop Mongo data.
After cut-over but Mongo still has CDC back to Postgres: route reads back to Postgres, stop write CDC, keep Mongo as warm standby until decision.
After full cut: you need a reverse migration. Same playbook, opposite direction. Cost as much as the original.

Plan rollback explicitly before T0. Document the decision points.

Practice Exercises

Exercise 1 — Schema mapping

Take a Postgres schema you know (e.g., from a previous project). Map to Mongo collections following Post 07. Note which tables embed and which stay as references.

Exercise 2 — mongoimport

Export one table to CSV from Postgres. mongoimport into Mongo. Verify counts + spot-check.

Exercise 3 — Dual-write

Implement Chapter 5's create_order dual-write. Insert 1000 orders; confirm both DBs have them. Kill Mongo connectivity mid-test; confirm Postgres still works + Mongo is backfillable.

Exercise 4 — Read-shadow

Implement the shadow comparator. Run 1000 reads; surface diffs. Fix any.

Exercise 5 — CDC PoC

Set up Debezium reading from a local Postgres into Kafka. Configure a Mongo sink. Insert into Postgres; see in Mongo within seconds. (Detailed walkthrough in Post 24.)

Exercise 6 — Staged cut-over plan

For a hypothetical 500 GB Postgres workload, write the full cut-over runbook with T0…T7 + rollback at each step.

Cheat-sheet

What	How
First	should you migrate at all?
Map schema	follow Post 07 patterns
Strategy	A (full) / B (dual-write) / C (CDC) / D (strangler)
Tools	mongoimport / DMS / Debezium / Airbyte / custom ETL
Dual-write	source-of-truth source; mirror to Mongo idempotent
Read-shadow	parallel read; log diff; build trust
CDC	Debezium WAL → Kafka → Mongo Sink
Cut-over	gradual % shift on reads; gated by error/latency budget
Validation	counts + sampled equality + aggregate sums
Indexes	create before traffic
Rollback	dual-write window keeps it cheap
Code changes	as much work as data move
Strangler fig	for legacy systems

What's next

Post 24 covers Kafka + Debezium MongoDB CDC — how to plug MongoDB into Kafka topics in both directions (source + sink) and the patterns that power the migrations + CQRS architectures.

References

AWS Database Migration Service to MongoDB
Debezium Postgres connector
MongoDB Kafka Connector
Airbyte Postgres → MongoDB
NoSQL Distilled by Pramod Sadalage + Martin Fowler — classic schema-modeling perspective.

Note: this post is in progress. Planned additions: full DMS task config example; reverse-migration playbook; multi-region migration considerations.