Replace the engine while the website is running.

The difficult part of a database migration is not moving yesterday’s data. It is preserving the changes people make while you are moving it.

Imagine a WordPress directory moving from a collection of metadata queries to a typed search model. Profiles are still being edited, subscriptions are still changing and background imports are still running. A one-off export followed by a switch can produce an impressive new table that is already out of date.

A live migration is a sequence of compatible application states. Each state must make sense while different requests, workers and deployment versions overlap. “Zero downtime” is a property to demonstrate for a particular system, not a setting to enable on an ALTER statement.

Expand before you ask the application to change

Begin by adding the new representation without removing the old one. Deploy code that understands the expanded schema while continuing to serve through the established read path. Old application workers must still function during the rollout.

Write a compatibility matrix: which release reads which representation, which release writes it and which workers can remain active at each stage. Include command-line jobs and queued tasks deployed separately from the web application. A web release is not complete if an older worker can still overwrite the new structure with obsolete assumptions.

The migration discussed here concerns application-owned tables and adapters. Do not silently reshape a vendor plugin’s internal schema. Use its supported extension model or maintain a separate projection that your application owns.

Establish change capture before the backfill

The destination needs both the historical data and the edits that happen after the copy begins. Establish a durable way to capture those edits before choosing a backfill boundary. Capturing a save hook in memory is insufficient if the process can die before the pending work is recorded.

When you own the authoritative write transaction, you can persist a change record alongside it. When writes span WordPress metadata, plugin hooks and external imports, first map what can be made atomic and what requires reconciliation. If you cannot guarantee complete capture, include a final write fence or maintenance step instead of promising uninterrupted correctness.

Give changes a source version or another meaningful ordering token. A timestamp with coarse resolution may not distinguish two quick edits. A delayed backfill job must not overwrite a newer change that has already reached the destination.

Backfill in resumable, bounded batches

Use a stable cursor such as an increasing record ID, with a recorded upper boundary for the initial pass. An upper ID bounds the work; it does not freeze the contents of those records. The concurrent-change mechanism still has to account for edits, deletions and records created after that boundary.

The following PHP example expresses an application-specific migration contract. The repository, projector and checkpoint store are interfaces you must implement. The source query returns unique records ordered by ID, and the projector atomically refuses to replace a newer destination version with an older one.

function enjin_backfill_batch(
    $source,
    $projector,
    $checkpoints,
    string $run_id,
    int $upper_id,
    int $batch_size = 200
): int {
    $cursor = $checkpoints->load($run_id);
    $records = $source->after_id($cursor, $upper_id, $batch_size);

    foreach ($records as $record) {
        $projector->apply_if_newer(
            $record['id'],
            $record['source_version'],
            $record['search_data']
        );
        // Persist only after the destination write has committed.
        $checkpoints->advance($run_id, $record['id']);
    }

    return count($records);
}

If the process fails after a destination write but before the checkpoint, that record is replayed. The operation must tolerate this. Reversing the two steps can skip a record permanently. The simple loop assumes one worker owns the run; parallel workers need explicit partitions or leases rather than racing to advance a shared cursor.

Throttle using observed database load, replica lag where relevant, and application latency. A small batch size alone does not impose a rate limit if batches run continuously. Record progress and failures so the migration can pause and resume without restarting from the beginning.

Deletion is data, too

A deleted source row no longer appears in the next source query. Without a deletion record or a deliberate reconciliation pass, its destination record can remain searchable indefinitely.

Use versioned tombstones or another durable deletion mechanism. Keep them long enough that an older queued update cannot recreate the deleted record. A tombstone’s retention period depends on the maximum replay and recovery window, not on an arbitrary daily cleanup schedule.

Define the same behaviour for unpublishing, suspended access and changes to relationships. A migration that copies field values perfectly but loses visibility rules has not preserved the application.

Verify meaning before switching readers

Row counts are useful smoke tests, but equal counts do not establish equal contents. Compare sampled entities, null handling, date interpretation, relationship cardinality and search results. Include difficult records and permission boundaries deliberately.

Normalise representations before comparing them: a JSON object and a set of relational rows can describe the same information. Document intended changes separately from unexpected differences. Our shadow-execution essay describes how to compare application behaviour while the old path still serves visitors.

A cutover needs an explicit freshness condition. “The queue looks nearly empty” is not enough when a new write can arrive between the check and the switch. Use a durable change-stream checkpoint, a coordinated write fence, or a version-aware routing protocol that establishes the required boundary. The right mechanism depends on the source of truth and the application’s consistency requirements.

Online DDL can still wait on locks

Schema changes have their own concurrency behaviour. MySQL’s online DDL limitations describe metadata locks and the way existing transactions can block completion. “Online” does not mean that the operation is invisible to every query at every stage.

Rehearse the exact operation on the actual database engine and version with realistic table size and indexes. Decide in advance which algorithm and locking behaviour are acceptable, and make unsupported behaviour fail visibly rather than falling back to a more disruptive operation unnoticed. Allow for temporary disk usage and the cost of maintaining indexes during the copy.

Inspect long-running transactions before starting. Set a bounded lock-acquisition policy and an operational abort condition. A queued schema operation can become an availability problem even before it begins the work you expected it to perform.

Rollback must account for new writes

Switching a feature flag back is a rollback only if the old path still understands every write accepted by the new one. If the new representation becomes authoritative and the old one stops receiving compatible updates, the rollback window has closed.

During a deliberate verification period, maintain a documented compatibility path or reverse projection where it is feasible. Avoid ambiguous dual authority: choose which system resolves conflicts, and make partial writes recoverable. If a transformation is lossy, say so before cutover and define recovery from a preserved source or event history.

A full database restore can erase valid activity that happened after the backup. It is not a harmless substitute for a migration rollback plan on a busy application.

Contract only after the old world has stopped running

Remove obsolete reads, writes, fields and tables after the observation period, after delayed jobs are handled and after rollback expectations have been explicitly retired. Check deployed versions and background workers rather than assuming an elapsed number of days proves compatibility is no longer needed.

Keep an auditable migration record: schema versions, checkpoints, reconciliation results and the conditions that authorised each transition. The goal is a system that can explain what happened if it stops halfway through. That is what makes a live migration an engineering process instead of a hopeful import script.

Code examples illustrate the architecture and identify application-specific interfaces. Adapt them to your environment and test the failure paths before deployment. Sources are linked alongside the relevant discussion.