Von Jason Lagare
Case study

Moving 1.6 million records without downtime

A document portal moved between databases in production, with a rollback plan never more than a code revert away.

Context

A multinational insurer. A document management portal — the system of record for the documents the business and its customers depend on being able to find.

1.6 million records, moving from a relational database to a document store. In production. Two months, start to finish. I designed and implemented the migration and led a team of three.


The problem

The move was driven by performance and cost. The relational model was not the right shape for how a document portal is actually used, and it was being paid for twice — once in query performance and once on the infrastructure bill.

The architecture decision to move to a document store was made by the architects. I want to be accurate about that: I did not choose the destination. I was responsible for getting 1.6 million live records there without anyone noticing, which turned out to be the harder problem.

A document store is a better fit for documents in the obvious way — the records are self-contained, variable in shape, and read far more often than they are reshaped. But none of that helps you during the move itself, which is the part where a live system with real users has to keep working while its storage layer changes underneath it.


The constraints

  • Zero downtime. A document portal that is unavailable is indistinguishable, to the person using it, from one that has lost their documents.
  • Production data, regulated industry. Not a dataset you can afford to half-migrate and reconcile later.
  • Two different data models. Relational to document is a translation, not a copy, and translations lose things if nobody is checking.
  • A fixed, small team. Three engineers and me, over two months.

What I did

Designed and implemented a batch migration. Rather than dual-writing or running change data capture — both of which add live complexity and a second failure surface to a system that was already the thing people depended on — the migration ran as a controlled batch job moving records from the relational source into the document store.

Synced the deltas with a second batch job. A batch migration against a live system leaves an obvious hole: the records created and changed while the batch is running. A second job closed it, syncing the deltas so the document store converged on the source instead of drifting from the moment the first run started. The two jobs together are what make “zero downtime” a property of the design rather than a claim about the outcome — the system was never asked to stop changing while it was being moved.

Kept the source database intact. This is the decision the whole approach rests on. The original database was never destructively modified and never decommissioned as part of the cutover. It sat there, complete and current, for the entire migration.

Which made rollback trivial: revert the code. No restore from backup, no reverse migration, no race to rebuild state under pressure. If the new path misbehaved, the application pointed back at a database that had never stopped being correct. The expensive, high-adrenaline part of most migrations — the part where rollback means reconstructing data while users wait — was designed out rather than planned for.

Validated continuously rather than at the end. Verification ran at three levels: automated checks built into the pipeline, producing reports on every run; intensive testing by quality engineering against the migrated data; and manual spot checks against known records. The goal was to catch translation errors while they were still cheap to fix, instead of discovering them at cutover with 1.6 million records already moved.


Outcome

  • 1.6 million records migrated
  • Zero downtime throughout
  • Infrastructure cost halved — a 50% reduction in monthly service-bus spend
  • Two months, with a team of three

What I’d do differently

Spend longer looking at what already exists before building something.

The batch job worked, and it was the right shape for the constraints. But I reached for a custom implementation fairly quickly, and migration is one of the most thoroughly solved problems in software — there are mature tools and established patterns for exactly this move, and some of them would have handled the parts I wrote by hand, with more eyes on them than mine.

Building it taught me more. It did not obviously produce a better migration, and on a team of three with two months, the hours spent writing infrastructure are hours not spent on validation. Now I survey the existing options properly before deciding that a problem is mine to solve from scratch.

Working on the same thing?

Happy to go deeper on this one — get in touch.