Migrating Data Without Trusting the Data

How a PostgreSQL-to-MongoDB migration led me to build a separate data-repair pipeline

In 2022, while I was working at Playboy, a data migration that should have been mostly mechanical took three days to run.

The destination was MongoDB. The source was a PostgreSQL system that had been operating since 2006. It held information about more than 40,000 films and the people connected with them: performers, directors, cinematographers and other members of the production team.

The figure we used for the scale of the migration was roughly three million records. That did not mean three million films. It meant the relational rows needed to describe those 40,000 films, their contributors and the relationships between them.

That distinction matters, because we were not copying rows from one database to another. We were assembling documents.

A film in PostgreSQL was spread across several related structures. In MongoDB, those structures needed to become a single aggregate containing the film and its associated information. Every aggregate therefore depended on several reads, several relationships and a set of assumptions about what the source data contained.

The shape of the operation was closer to this:

Find a film, collect its related records, resolve the people and roles, construct a nested document, validate it and write it to MongoDB.

That was already more expensive than a simple export. Then the data began disagreeing with the code.

Some PostgreSQL values were NULL. In the Node.js application, absent properties surfaced as undefined, and failed numeric conversions could produce NaN. Some related records were incomplete. In other cases, a value had the correct type but appeared to be factually wrong.

The last category was the dangerous one.

Suppose a date of birth is stored as 1985-04-17. It is a valid date. It satisfies the schema. Nothing about its shape tells us that a trusted historical record contains 1985-04-07 instead.

Schema validation can tell you whether a value looks like a date. It cannot tell you whether it is the right date.

Our migration had to deal with both kinds of failure: values that could not be processed and values that could be processed but could not necessarily be trusted.

Faster, but still fragile

The first migration took approximately three days. I worked with a senior engineer to profile it and understand where the time was going.

Performance was the visible problem, but profiling exposed a more consequential one. The process could do a substantial amount of work and then stop when an inconsistent legacy record reached transformation code that expected a complete, correctly typed value.

I no longer have the original exception, so I cannot responsibly reproduce an exact stack trace. What I do remember is the failure mode: a missing or malformed source value reached the Node.js transformation layer, appeared there as something such as undefined or NaN, and violated an assumption while the film aggregate was being constructed.

We had made the migration much faster. We had not made its inputs more reliable.

That changed the question.

Instead of asking, “How do we make the migration faster again?”, we began asking, “Why are we waiting until migration to discover that a record cannot be trusted?”

The idea that followed did not arrive fully formed. A colleague proposed an initial direction during a brainstorming session. I brought it to our daily meeting, where the team and the senior engineer challenged some of its assumptions and helped refine it.

The conclusion was simple: migration and repair were different jobs.

The migration should transform valid PostgreSQL data into MongoDB documents. It should not also be responsible for diagnosing years of inconsistent data, determining what an absent value ought to have been and deciding whether a plausible-looking value was historically correct.

So I built a separate server-side Node.js process to inspect and repair the PostgreSQL dataset before migration.

Giving each failure somewhere to go

The repair logic needed to handle several distinct conditions without becoming a long sequence of intertwined conditionals. I implemented it as a Chain of Responsibility.

Each handler represented a repair policy. It examined the relevant value and its context, decided whether it could handle that condition and either produced a deterministic result or passed the case to the next handler.

A valid value required no write. A missing required value followed a different path from an optional one. An invalid numeric conversion was not treated in the same way as a populated value that conflicted with historical data. If none of the automated rules could establish a safe result, the record was separated for investigation.

This is an important qualification: the chain did not replace every NULL with an empty string or every undefined with a default.

Doing that would have made the data look complete. It would not have made it correct.

When the active value could not be trusted, the process matched the entity with its corresponding record in a known-good PostgreSQL backup. The backup was not treated as automatically superior to the current dataset. It supplied a candidate recovery value.

A correction was automated only when the entity match was unambiguous and the applicable repair rule established that the historical value was safe to restore. Otherwise, the process refused to guess.

Deterministic corrections were written back to the PostgreSQL dataset used by the migration. I no longer recall whether that was the active production database or a dedicated migration copy, so I would not describe it as a production write without being able to confirm that detail.

The design now had a useful boundary:

The repair process established whether known problem data was safe to migrate. The migration process changed its representation.

The Chain of Responsibility made that boundary easier to maintain, but it did not make the repair process fast.

Changing the unit of work

The first version of the repair process performed too much work at individual-record level.

For a questionable record, it could read the source, inspect the value, retrieve the historical reference and write a correction before moving to the next record. Each operation was reasonable in isolation. Repeated across a large dataset, the database round trips dominated the run.

That implementation took approximately two hours and forty minutes.

The optimisation was not a more elaborate design pattern. It was a change in the unit of work.

Instead of moving through the data one record at a time, the revised process loaded a bounded batch. It classified the records in memory, collected the reference data needed for that batch and compared the values together. It then wrote only the corrections that were actually required.

The difference looked roughly like this:

Batching reduced the number of database interactions and kept the working set bounded. Avoiding writes for already-correct values removed another source of unnecessary work. Expensive reconciliation was reserved for records that had actually been flagged by a repair rule.

The equivalent repair operation subsequently completed in approximately 20 minutes. Compared with the two-hour-and-forty-minute repair baseline, that was an 87.5 per cent reduction in measured runtime.

That comparison applies to the repair process. I no longer have the instrumentation needed to combine repair and migration into a trustworthy end-to-end measurement. The original three-day migration, the first 160-minute repair run and the final 20-minute repair run are related parts of the story, but they are not three measurements of precisely the same operation.

Knowing when a repair was finished

Every automated correction had a postcondition.

The repaired value had to satisfy the expected type or structure. When historical reconciliation was involved, it had to agree with the trusted record selected by the repair rule. It also had to pass through the transformation path that had previously failed.

If the process could not establish those conditions, it did not manufacture a plausible value. It separated the record for investigation.

This gave us deterministic verification at repair level. I no longer have evidence of a separate dataset-wide checksum or final reconciliation report, so I would not claim one. The guarantee I can describe is narrower: automated repairs were accepted only when their rules could establish and revalidate a specific result.

Once that repair and validation pass had completed, the PostgreSQL-to-MongoDB migration could operate against a dataset whose known inconsistencies had already been addressed.

The original workflow had effectively been:

That sequence is the part of the project I still find most useful.

The design pattern helped organise the exceptional cases. Batching produced most of the runtime reduction. The backup made certain repairs recoverable. But the larger improvement came from deciding that uncertainty should be handled before the expensive transformation began.

The lesson I kept

Migration performance and migration correctness are separate engineering problems.

At first, the visible problem was that the migration took three days. Profiling and reducing processing overhead were worthwhile, but speed was not the only constraint. We were still discovering untrustworthy data at the point where failure was most expensive.

The repair process gave that uncertainty somewhere else to go. Known cases could be handled by explicit rules. Historical values could be used when the match and repair were deterministic. Ambiguous records could be isolated instead of disguised with defaults. The migration itself could return to the narrower job of changing the data’s representation.

I would design some operational details differently today. A long-running repair would normally be a background job with explicit progress, checkpoints and reporting rather than an HTTP request initiated through Postman. I would also retain end-to-end timing, reconciliation totals and an audit trail as part of the migration artefacts.

But the central decision still holds. Do not make the migration responsible for discovering whether its inputs can be trusted. Establish that first. Performance determines how quickly you can move the data. Reconciliation determines whether you should move it at all.