Migrating Stored Data While Both Formats Are Live

Changing how data is stored is the one refactoring that cannot be verified by running the old and new code side by side and comparing outputs, because the old data is the input. The store contains values written by the previous schema, the new code expects the new schema, and something must translate between them for as long as both are live.

The usual answer is to write both formats during a transition. That answer is correct and it converts a cutover problem into a consistency problem, which is easier to survive and harder to finish.

Why the code cannot wait for the data

A migration that stops the world, converts every row, and starts the new code is safe in the small and impossible in the large. The conversion of a large table takes long enough that the downtime is unacceptable, and even where downtime is acceptable, the conversion cannot run while writes continue, which means writes must stop first.

So writes continue, and the two formats coexist. The ordering constraint is asymmetric, and it is the whole design:

The new format must exist before any code writes it. A write to a column that does not exist fails.

The new code must tolerate rows in the old format, because most of the table is still old. That means every read path has two cases for a period measured in days or weeks.

The old format must be removed only after no code reads it, and after every row has been converted. Removing a column that some read path still uses produces an error; removing one that some row still depends on produces silent data loss, because the row’s only copy of that value is the column that was dropped.

That last case is the one that costs data. It happens when the backfill is believed complete and is not.

The three writes in a dual-write window

During the overlap, a single logical update produces three effects: a write to the old format, a write to the new format, and whatever consistency mechanism ties them together. The mechanism is where the design choices are.

Same transaction. If both formats live in the same database, both writes go in one transaction and are atomic by construction. This is by far the simplest case, and it is available more often than people assume: adding a column to the same table, or a new table in the same database, needs no cross-store protocol.

Ordered writes with repair. If the formats live in different stores, there is no atomic commit across them without a distributed transaction, and distributed transactions are usually unavailable or undesirable. The alternative is to write one first, then the other, and to accept a window in which one has the update and the other does not. The window is closed by a repair process that reconciles from the authoritative side.

Write one, derive the other. Where the new format is a function of the old, only the old is written and the new is computed. This removes the consistency problem entirely at the cost of a read-time computation, and it is the right choice whenever the derivation is cheap and deterministic.

The choice is determined by which store is authoritative, and that must be decided explicitly. Two stores that both accept writes for the same field, with no ordering, produce divergence that no repair process can resolve, because there is no basis for choosing which value is correct.

-- Expand: add the new column, nullable, with no default rewrite of existing rows.
ALTER TABLE orders ADD COLUMN total_cents bigint;

-- Backfill in bounded batches so no single statement holds locks for long.
UPDATE orders SET total_cents = round(total * 100)
WHERE id IN (
    SELECT id FROM orders WHERE total_cents IS NULL
    ORDER BY id LIMIT 1000
);

The WHERE total_cents IS NULL predicate is what makes the backfill resumable. A backfill that selects by id range and assumes progress is a backfill that must be restarted from the beginning after any interruption, and interruption is normal at table sizes where the backfill takes hours.

Backfill as a concurrent writer

A backfill reads rows written under the old code and writes values under the new. Meanwhile the application writes new rows and updates old ones. The two interact in ways that produce lost updates.

The sequence that breaks: the backfill reads a row, the application updates that row’s source value, the backfill writes the converted value computed from the stale read. The new column now holds a value that does not correspond to the current source value, and no error occurred.

Three mechanisms prevent this, and they compose.

Make the conversion a function of a value the application also writes, and have the application write both columns during the window. Then the backfill only fills rows that the application has not touched, which the null predicate identifies. A row updated by the application is never null in the new column, so it is never a backfill candidate.

Use a version or timestamp column and make the backfill conditional on it not having changed. The conditional update fails on a concurrently modified row, and the row is retried later.

Run the backfill in the same transaction isolation as the application and rely on the store’s conflict detection. Under snapshot isolation, a write that conflicts with a concurrent commit is aborted, which surfaces the race as a retry rather than as corruption.

The first mechanism is the one that scales best, because it eliminates the conflict instead of detecting it. It requires that the application code that writes the source value also writes the derived value, which is the dual-write, and it requires that this deploy happens before the backfill starts.

Verifying a conversion that has no oracle

The usual verification for a refactoring compares old output to new output. Here the comparison is between a stored value and its conversion, which is not the same thing: the conversion may be wrong in the same way for every row and the comparison will agree everywhere.

Four checks, in increasing strength.

Null count. The number of rows with a null in the new column must reach zero. This is necessary and it is the check most often treated as sufficient.

Recomputation agreement. Convert the source value again and compare to the stored conversion. This catches a conversion applied twice, applied with a different rounding, or applied to a value that changed after conversion. It does not catch a conversion that is consistently wrong.

Range and invariant checks. A converted amount must be within the bounds the domain allows; a converted timestamp must not be in the future; a converted enum must be a member of the new set. These catch systematic errors that recomputation cannot.

Shadow read comparison. Run the new read path against converted rows and the old read path against the same rows, and compare the results. This is the only check that compares semantics rather than storage, and it is only possible while both read paths exist, which is an argument for keeping the old read path longer than feels necessary.

def verify_batch(rows):
    bad = []
    for r in rows:
        if r.total_cents is None:
            bad.append((r.id, "missing"))
        elif r.total_cents != round(r.total * 100):
            bad.append((r.id, "mismatch"))
        elif not (0 <= r.total_cents <= MAX_ORDER_CENTS):
            bad.append((r.id, "range"))
    return bad

The contract phase and the irreversibility

Dropping the old column is the point of no return. Before it, the old format is a complete copy of the data and a rollback is a code change. After it, the old format is gone and a rollback requires restoring a backup.

That asymmetry sets the evidence bar. The contract phase should be gated on the null count being zero for longer than the longest write cycle in the system, on the shadow comparison being clean, and on no read path referencing the old column for a full deploy. Each of those is a duration, and durations are what make a migration long.

The drop itself should be a separate deploy from the code change that stops reading the column, never the same one. A combined change has no state in which the code is verified without the column, so a failure during the drop has no clean rollback. This is the contract phase of Expand, Migrate, Contract: Changing an Interface Without a Flag Day applied to a table instead of a message, and it inherits the same asymmetry: the earlier phases are reversible and the last one is not.

When dual-write is the wrong answer

Dual-write is a tool for a format change that must happen without downtime. It is the wrong tool in three cases.

When the new format is not derivable from the old, so there is no way to fill it for existing rows. That is not a migration; it is an enrichment requiring an external source, and the sequencing is a data pipeline problem.

When the two formats have different consistency requirements, such that writing both doubles the latency of every write on a hot path. Then the transition must be a background migration with a cutover at a quiescent point, and the cutover is scheduled rather than avoided.

When the store cannot support the extra write volume for the duration. The window is a capacity multiplier, and a store at its write limit has no room for a second write per update.

The common thread is that dual-write trades a scheduling problem for a duration problem. It is worth the trade when the duration is bounded and the capacity exists, and it is the source of long-lived inconsistency when the duration is unbounded. That is the same trade made in Branch by Abstraction and the Cost of Two Live Paths, applied to bytes rather than to code, and it fails in the same way when the retirement phase is left undone.