Planning a Database Migration for a Live Legacy System
Learn the engineering patterns for zero-downtime database migration from legacy systems — expand-contract, CDC, dual-write, and how to plan the final cutover.
A database migration must preserve data while applications continue reading and writing. Plan how to backfill existing records, capture concurrent changes, validate the target, and recover if cutover fails.
Expand-contract, change data capture, and coordinated dual writes can reduce disruption. Whether a migration can avoid downtime depends on the engines, schema changes, workload, and cutover requirements.
Why Legacy Database Migrations Are Hard
Legacy databases accumulate technical debt in ways that are invisible until migration time:
- Schema that reflects the decisions of systems that no longer exist
- Inconsistent data types (dates stored as strings, money stored as floats)
- Ghost records — rows that are logically deleted but never physically removed
- Implicit assumptions baked into application code that are not documented anywhere
- Missing referential integrity constraints that mean the application enforces rules the database does not
Profile data quality before selecting a migration path. Check missing values, duplicates, invalid ranges, and implicit application constraints, using queries that will not overload production.
The Expand-Contract Pattern for Schema Changes
The expand-contract pattern keeps old and new application versions compatible while a schema changes. It has three phases:
Expand: Add the new column, table, or structure alongside the old one. Do not remove anything yet. Deploy application code that writes to both old and new structures simultaneously.
Migrate: Run a background job to backfill historical data into the new structure. This can run during normal operation because writes are going to both places.
Contract: Once all data is in the new structure and the application no longer reads from the old one, remove the old structure.
Each phase is a separate deployment. The key constraint is that you cannot remove the old structure until you are certain nothing reads from it — which requires monitoring and sometimes a waiting period.
Change Data Capture for Live Replication
When you are migrating to a different database engine or a fundamentally different schema, you need a way to keep the source and target in sync while both are live. Change Data Capture (CDC) is the standard approach.
CDC works by reading the database's transaction log (the WAL in PostgreSQL, the binlog in MySQL) and streaming every insert, update, and delete to the target system in near real-time. Tools like Debezium are commonly used for this.
The migration flow with CDC looks like this:
- Establish a consistent snapshot and change-stream position so writes during the copy are not missed
- Load the snapshot and apply captured changes to the target
- Validate data and monitor replication lag
- Pause writes if the cutover requires it and drain outstanding changes
- Redirect application connections and verify reads and writes
- Retain recovery options until the acceptance and rollback window closes
The Cutover Window
Rehearse cutover with representative data and traffic. Measure how long it takes to stop new writes, drain replication, redirect connections, and validate the target. Use that result to set the maintenance or read-only window.
Plan the cutover window explicitly:
- Who executes each step
- What the rollback trigger is (and how to execute it quickly)
- What monitoring you are watching during the window
- How long you will wait before declaring success versus rolling back
Do not attempt a cutover without a tested rollback procedure.
Database Migration with Dual Writes
For some migrations — especially those involving a move between fundamentally different data models — CDC is not practical. The dual-write approach is an alternative.
In dual-write, the application layer is modified to write every operation to both the legacy database and the new target simultaneously. Reads still come from the legacy database. A reconciliation job periodically compares the two to catch discrepancies.
Once confidence is established that the target is receiving complete and correct data, reads are gradually shifted to the target (starting with read-only paths first, then write-heavy paths). When reads are fully on the new system, the dual-write can be removed.
Dual-write has a downside: it couples the application layer to the migration timeline. Every write path has to be updated. It also adds latency to writes and introduces failure modes (what happens if the write to the new system fails?). These are solvable problems but they require deliberate engineering.
What Commonly Goes Wrong
Testing at less than production scale. A migration that runs in 40 minutes on a staging database with 10% of production data may run for 14 hours on the real dataset. Always test at production scale before committing to a cutover window.
Replication lag at cutover. If the source database is under heavy write load, CDC replication may never fully close the gap. Understand your write throughput and whether the replication system can keep up.
Schema mismatches discovered late. Data type differences between source and target (a VARCHAR(255) in MySQL and TEXT in PostgreSQL behave differently) can cause silent truncation or type errors that only appear under specific data conditions.
No rollback from the target. Plan how writes accepted after cutover will be recovered if traffic returns to the source. An old snapshot alone cannot preserve those writes. Test reverse replication, replay, or another recovery method before switching.