Migrating Millions of Rows With Zero Downtime: Lessons From a Mongo-to-Postgres Cutover

Moving a live application from MongoDB to Postgres sounds like a weekend of export scripts until you remember that users are still writing data while you copy it. The Zero-Downtime Migration Engine was CodeITronics' answer to that problem: move millions of rows from a document store into a normalized Postgres schema on AWS RDS without taking the application offline. The result was 0 seconds of application downtime and 95% faster query speeds after cutover. This post walks through how it worked and the decisions that mattered.

Why this migration was hard

The data had grown the way document databases often do: flexible at first, inconsistent later. Nested arrays that were really one-to-many relationships. Fields that were sometimes strings and sometimes objects. Optional keys that were effectively required. Queries that joined data in application code because the database couldn't.

Three constraints made it a real engineering problem rather than an export job:

• The application couldn't go down. No maintenance window, no read-only mode.
• Writes kept happening during the migration. Any snapshot would be stale before it finished copying.
• The shape had to change. This wasn't a lift-and-shift. Documents had to become normalized tables with foreign keys and constraints, which meant transformation logic in the middle of the pipeline.

Mapping documents to normalized tables

Before writing any pipeline code, we profiled the actual data. Not the schema people believed existed, but what was in the collections: which fields appeared, how often, with what types, and how deep the nesting went. That profile drove every mapping decision.

Document pattern	Postgres mapping	Notes
Top-level document	Row in a parent table	Mongo _id kept as a unique legacy_id column
Embedded array of objects	Child table with foreign key	Order preserved with a position column where it mattered
Embedded single object	Columns on parent, or 1:1 table	Inlined when always present and small
Mixed-type field	Normalized type plus validation	Bad values logged, not silently coerced
Free-form metadata	jsonb column	Kept flexible where structure wasn't stable
References by ID string	Real foreign keys	Resolved via the legacy_id lookup

Two choices are worth calling out.

Keep the legacy ID. Every migrated row carried its original Mongo _id. That made resolving references straightforward, made reconciliation possible, and made it trivial to trace any Postgres row back to its source document while debugging.

Don't normalize everything. Some fields were genuinely unstructured. Forcing them into columns would have created a schema that broke the next time the product changed. A jsonb column for those, with real columns for everything that was queried or joined, was the pragmatic middle.

The transformation layer was a set of pure functions: document in, set of rows out. Pure functions were easy to unit test against real sample documents, including the weird ones the profiling turned up.

The overall architecture

             +-------------------+
 App writes  |   Application     |
------------>|  (dual-write      |
             |   wrapper)        |
             +----+---------+----+
                  |         |
         primary  |         |  shadow write (async, via queue)
                  v         v
            +---------+   +-----------------+
            | MongoDB |   | Redis queue     |
            +----+----+   +--------+--------+
                 |                 |
   backfill read | (cursor,        | workers apply
   in batches    |  streamed)      | transform + upsert
                 v                 v
           +----------------------------+
           |  Transform (doc -> rows)   |
           +-------------+--------------+
                         |
                         v
                +------------------+
                | Postgres (RDS)   |
                +--------+---------+
                         |
                         v
             Reconciliation jobs (counts,
             checksums, sampled diffs)

There were two paths into Postgres. The backfill moved historical data. The dual-write path captured everything that changed after the backfill started. Both used the same transform functions and both wrote with idempotent upserts, which is what let them overlap safely.

Batched streaming backfill

Loading millions of documents into memory isn't an option. The backfill read from a MongoDB cursor as a stream, grouped documents into batches, transformed each batch, and wrote it to Postgres in a single transaction. Node streams gave us backpressure for free: if Postgres slowed down, reads from Mongo slowed down too.

A simplified version of the batching stage looks like this:

const { pipeline, Transform, Writable } = require("node:stream");

function batch(size) {
  let buf = [];
  return new Transform({
    objectMode: true,
    transform(doc, _enc, cb) {
      buf.push(doc);
      if (buf.length >= size) { this.push(buf); buf = []; }
      cb();
    },
    flush(cb) { if (buf.length) this.push(buf); cb(); },
  });
}

const writer = new Writable({
  objectMode: true,
  async write(docs, _enc, cb) {
    try {
      const rows = docs.flatMap(toRows);          // pure transform
      await upsertRows(rows);                     // ON CONFLICT (legacy_id) DO UPDATE
      await saveCheckpoint(docs.at(-1)._id);      // resume point
      cb();
    } catch (err) { cb(err); }
  },
});

const cursor = orders.find({ _id: { $gt: lastCheckpoint } }).sort({ _id: 1 }).stream();
pipeline(cursor, batch(1000), writer, (err) => err && console.error(err));

The decisions behind it:

• Sort by _id and checkpoint the last one. If the job died, it resumed from the checkpoint instead of starting over. On a long backfill, this matters a lot.
• Upsert, not insert. ON CONFLICT (legacy_id) DO UPDATE meant re-running a batch was harmless, and the backfill could overlap with dual-writes without creating duplicates.
• Tune batch size by measurement. Bigger batches mean fewer round trips but longer transactions and more memory. We picked a size by watching database load and lag, not by guessing.
• Throttle against production. The backfill read from the same Mongo cluster serving users. It ran with a ceiling on concurrency so it never competed meaningfully with live traffic.
• Defer what you can. Some secondary indexes were created after the bulk load rather than during it, so inserts didn't pay index maintenance costs on every row.

Keeping both databases in sync

A backfill alone is a snapshot, and snapshots go stale. While historical data was being copied, users were still creating and updating records. We needed every change after the backfill began to reach Postgres too.

There are two common approaches:

Approach	How it works	Trade-off
Dual-writes	Application writes to Mongo, then enqueues the same change for Postgres	Simple and explicit, but every write path must be covered
Change capture	Read the database's change stream and replay changes	Catches every write, but adds infrastructure and ordering concerns

We used application-level dual-writes routed through a Redis queue. The application wrote to Mongo as the source of truth, then pushed a job to the queue. Workers transformed the document and upserted into Postgres.

Why the queue instead of writing to Postgres inline? Because a slow or failing Postgres write should never break a user request during the migration. Mongo stayed authoritative; Postgres was a shadow that caught up asynchronously, with retries.

The hardest part was ordering. If two updates to the same record arrive out of order, the older one can overwrite the newer one. We handled this by having workers re-read the current document from Mongo when processing a job, instead of trusting the payload in the queue. The job meant "this record changed," not "here is the new value." That made out-of-order jobs harmless: whichever ran last wrote the latest state.

The backfill and dual-writes overlapped on purpose. Because both paths upserted by legacy_id and dual-write workers always read fresh state, it didn't matter which one touched a row last.

Verification and reconciliation

"The script finished without errors" isn't proof the data is right. We ran three layers of checks continuously, not just at the end:

1. Counts. Row counts per table against document counts per collection, with expected ratios for child tables.
2. Checksums. Hashes of normalized field values computed on both sides for ranges of records, so mismatches could be narrowed to a range and then a record.
3. Sampled deep diffs. Random documents transformed fresh and compared field by field against what was in Postgres.

Mismatches went into a reconciliation table with the legacy ID and reason. A repair job re-ran the transform for those records. Most mismatches traced back to edge cases the profiling had flagged as rare, which is exactly why we logged instead of coercing.

We also ran shadow reads before cutover: for selected read paths, the application queried both databases and compared results, logging differences without affecting what users saw. Shadow reads also surface the performance difference before cutover: reads that needed application-side joins against Mongo become indexed joins in Postgres.

Cutover and rollback

Cutover wasn't a single switch. It was a sequence of flags:

1. Reads move to Postgres behind a feature flag, gradually. Writes still go to Mongo first.
2. Primary write flips. Postgres becomes the source of truth, and the queue now shadow-writes back to Mongo.
3. Mongo kept warm for a defined period, still receiving shadow writes.
4. Decommission only after a quiet period with clean reconciliation.

Because each step was a flag and both databases stayed in sync in both directions, rollback at any stage was a flag flip, not a data recovery exercise. That's the real secret behind 0 seconds of application downtime: there was never a moment where the system depended on a one-way door.

We also wrote down, before starting, what would trigger a rollback: error rates above baseline, reconciliation mismatches above a threshold, or latency regressions on key endpoints. Deciding those criteria in advance meant no one had to make a judgment call under pressure.

Key Takeaways

• Profile the real data before designing the schema. The edge cases you find early are the bugs you won't ship.
• Stream and batch the backfill with checkpoints and idempotent upserts so it can be paused, resumed, and re-run safely.
• Route shadow writes through a queue and have workers read fresh state, so ordering problems disappear.
• Verify continuously with counts, checksums, sampled diffs, and shadow reads, not once at the end.
• Make cutover a series of reversible flags. Zero downtime comes from never having a one-way door.

FAQs

Why move from MongoDB to Postgres at all?

In this case the data was relational in practice, and queries were doing joins in application code. A normalized schema with proper indexes made those reads significantly faster.

Dual-writes or change data capture?

Dual-writes are simpler when you control all write paths. Change capture is safer when writes come from many places you can't easily modify. Either works if writes are idempotent.

How do you handle writes that happen during the backfill?

Capture them through dual-writes or change capture, and make both the backfill and the sync path upsert by a stable key so they can overlap without conflicts.

How do you know the migration is correct?

Layered reconciliation: counts, range checksums, sampled field-level diffs, and shadow reads that compare real query results before users depend on them.

What makes rollback possible?

Keeping the old database in sync after cutover and controlling each step with a flag. If something goes wrong, you flip back instead of restoring data.

Working on Something Similar

If you're planning a database move and can't afford a maintenance window, the approach above is the one we'd start from and adapt to your data. You can read more about the Migration Engine and other builds on our work page, or reach out to talk through your migration.