Database Migration: Moving Data While Users Keep Working

A database migration is the process of moving data from one database to another, or from one schema to another, so an application can run on the new one. On a live product, the hard part is not copying rows. It is doing it while users keep reading and writing, without losing, duplicating, or corrupting anything.

The approach that holds up in practice: copy the data in the background, keep both databases in sync for a while, compare them, switch reads and then writes in small steps, and keep a way back to the old system until you trust the new one. The rest of this article walks through each step.

What is a database migration?

The term covers several different jobs, and mixing them up causes confusion:

• Schema migration: changing tables, columns, or indexes inside the same database.
• Engine migration: moving from one database product to another, such as from one SQL engine to a different one, or from a document store to a relational one.
• Hosting migration: moving the same engine to a new server or cloud provider.
• Model migration: reshaping the data to fit a new application design, often during a rewrite or replatforming.

Engine and model migrations are the risky ones. They change what the data looks like or how it is stored, and the application has to keep working through the change. Everything below applies mostly to those cases. Pure code cleanup is a different job, closer to refactoring without changing behavior.

Why is moving data the hardest part of replacing a live system?

Code can be deployed, tested, and rolled back in minutes. Data cannot. Once a user writes a record to the new database, you cannot simply redeploy the old version and pretend it never happened.

A few reasons it gets hard fast:

• Data keeps changing. Any copy you take is already stale when it finishes.
• Old data is messy. Legacy systems accumulate nulls, duplicates, inconsistent formats, and rules that live only in application code, not in the schema.
• Nobody remembers the rules. A column named status may have meant three different things over the years.
• Mistakes are quiet. A wrong timezone conversion or truncated field does not throw an error. It shows up weeks later in a customer complaint.
• The money paths depend on it. Orders, payments, subscriptions, and balances must be exact.

At avanzzada I led the full migration of a platform that applies to job openings automatically for its users, moving the whole architecture from a legacy stack to a new one while people kept using it every day. Stopping the product was not an option, so every step had to be reversible. The lesson I took from that kind of work: you plan the data movement first, and let the code plan follow from it.

What are the main strategies: big bang, trickle, and dual write?

There are three common patterns. Most real migrations combine them.

Big bang

This works for small datasets, internal tools, or products where a maintenance window is acceptable. It is the easiest to understand, but if verification fails after the switch, you are under pressure with users waiting.

Trickle

You migrate slices: one customer segment, one feature, one table family. Each slice is verified before the next. It fits well with incremental migration, where a routing layer sends some traffic to the new system and the rest to the old one. On a live migration, putting that switch in front of everything is the first thing I do, before writing any new code. Running two systems side by side has a cost, but in my experience a big-bang rewrite usually costs more and takes longer, because the old system has to keep running in parallel anyway while the new one catches up.

Dual write

The application writes every change to both databases. It sounds simple and is not. If the first write succeeds and the second fails, the databases disagree, and there is no transaction spanning both. I use dual write carefully, with the old database as the source of truth, and with reconciliation jobs that catch drift. Many teams prefer replication or change data capture instead, which I cover next.

How do you keep the old and new databases in sync?

The rule I follow: at any moment, exactly one database is the source of truth for a given piece of data. The other follows.

Common ways to keep a follower current:

1. Initial bulk copy plus incremental catch-up. Take a snapshot, load it into the new database, then replay changes that happened since the snapshot.
2. Change data capture (CDC). Read the old database’s change log and apply those changes to the new one. This avoids touching application code, and many database engines and tools support it in some form.
3. Application-level dual write. Useful when the data shapes differ a lot, but it needs reconciliation.
4. Periodic batch sync. Acceptable for data that changes rarely, such as reference tables.

Handle the transformation in one place

If the new schema differs from the old, put the mapping logic in one well-tested module or pipeline. When the same transformation is reimplemented in a backfill script, a sync job, and the application, the three versions drift apart.

Make operations idempotent

Sync jobs fail and rerun. If running the same change twice creates duplicates, you will eventually have duplicates. Use stable IDs and upserts so reruns are harmless.

Move writes after reads

A safe order is: sync data, switch reads to the new database for a small group of users, watch, widen the group, and only then move writes. Feature flags are the usual tool for this, because they let you change the routing without a deploy.

How do you verify the data before switching over?

Do not rely on a row count alone. Counts match when data is wrong in ways that matter. Layer your checks:

• Row counts per table as a first, cheap check.
• Checksums or hashes over key columns, compared between old and new.
• Aggregates on business values: total order amount per day, active subscriptions per plan, balances per account.
• Sampled record comparison: pick records at random, plus edge cases such as the oldest, the largest, and ones with nulls or unusual characters.
• Referential integrity checks: orphaned foreign keys, missing parents, duplicate unique values.

Compare behavior, not just data

A strong technique is shadow reading. The application reads from both databases for the same request, returns the old result to the user, and logs any difference. Mismatches show you the rules nobody documented before your users find them.

Verify the money paths hardest

Start with anything involving payments, orders, or balances. When I take over a system, the money paths come right after getting access and getting the build working, and a migration deserves the same priority. Reconcile them against an outside source, such as your payment provider’s records, not only against the old database, because the old database may already contain errors.

Define "good enough" up front

Agree on what mismatch rate blocks the switch. For financial data, it is zero unexplained differences. For low-stakes data, you may accept known, documented exceptions.

How do you roll back if something goes wrong?

Rollback is where many migration plans are vague. Code rollback is easy. Data rollback is only possible if you designed for it.

Practical rules:

• Keep the old database intact and running after cutover, for a defined period. Do not decommission it on switch day.
• Keep syncing backward when you can. After writes move to the new database, replicate changes back to the old one so that switching back does not lose recent data. This is extra work, and worth it for high-stakes systems.
• Switch in stages, so a problem affects a small group and rollback means flipping a flag for them.
• Take verified backups before each major step, and test that you can restore them. An untested backup is a hope, not a plan.
• Write down the trigger. Decide in advance which signals mean "roll back": error rates, mismatch counts, failed payments, support tickets.

Be honest about the limits. After enough time on the new database, rolling back may be harder than fixing forward. Define the point of no return explicitly, and reach it only after verification has been clean for long enough that you trust it.

Planning a migration of your own?

If you are about to move a live database and want a second opinion, email me@filipeeduardo.dev with a short description of your setup. I am glad to tell you what I would check first, as one engineer comparing notes with another.