Dual write and backfill: where migrations really tear

At some point in every modernization, data has to move. A column gets a new type, a table gets split, a service gets its own store. And almost always the condition applies that makes the difference between an evening and a quarter: the business keeps running.

The procedure for that is well known and called expand and contract: both side by side first, then switch, then remove the old one. The description takes three sentences, and migrations still fail regularly. They do not fail on the procedure, they fail at four places the short version skips over.

This article walks through the five phases, shows the code for the trickiest one, and names the four places where it tears in practice.

The five phases

For orientation, the whole sequence first. Every phase is its own release, and between two phases there is always a state that could run indefinitely.

1. Expand. The new target appears, empty. Nothing reads it, nothing writes it. Way back: remove the new structure.
2. Write to both. Every change goes to both targets, reading still happens from the old one. From here on everything new is in both places.
3. Backfill. The existing data gets transferred, in batches, outside the release.
4. Switch. Reading moves to the new target, writing continues to both.
5. Contract. Writing to the old target stops, then it is removed.

The most important sentence about that list is not in it: between phase 4 and 5 there is a waiting period, and it is longer than anybody wants. It has to outlast the longest reporting period in the building. Anybody running an analysis once a quarter notices a fault in the quarter after.

Phase 2: writing to both without doubling the failures

The obvious implementation is also the wrong one:

// Bad: what happens when the second line fails?
$this->oldStore->save($order);
$this->newStore->save($order);

If the second line fails, the order is in the old target and not in the new one. The reconciliation in phase 3 finds that later, but until then the number of differences is unknown, and worse: nobody knows whether it is growing.

The question that decides is: may a failure in the new target fail the operation? And in phase 2 the answer is always no. The new target is not yet the truth; a failure there must not prevent an order.

public function save(Order $order): void
{
    // The old target is the truth. A failure here aborts the operation.
    $this->oldStore->save($order);

    try {
        $this->newStore->save($order);
    } catch (Throwable $e) {
        // Deliberately logged, not thrown. In phase 2 the new target is a
        // copy, not a contract. The counter below is the abort criterion:
        // if it rises, the migration stops.
        $this->metrics->increment('dualwrite.new.failed');
        $this->log->warning('secondary write failed', [
            'order' => $order->id(),
            'error' => $e->getMessage(),
        ]);
    }
}

Two things about that are not decoration.

The counter is the abort criterion. Without it, "we write to both" is an assertion. With it, it is a measurement, and a rising failure count stops the migration before phase 4 comes up.

The identifier of the record is in the log. That makes every difference individually traceable afterwards. A log entry without an identifier only creates the feeling of being informed.

And the transaction boundary: where both targets live in the same database, the secondary write does not belong in the same transaction. Otherwise a failure in the new target rolls the whole operation back, which is exactly what the exception handling is there to prevent.

Why the old structure looks the way it does in the first place is covered in The real legacy is your database schema.

Phase 3: the backfill

The existing data gets transferred in batches. That run needs three properties, and all three are missing from the first version anybody writes.

It can be interrupted and resumed. A run over eight hours will be interrupted, by a deployment, a restart or somebody cutting the connection. So progress does not live in memory, it lives in the database.

It is repeatable. Transferring the same record twice must break nothing. In practice: insert or update, never blind insert.

It throttles itself. A run that saturates the database makes the application slow, and then it gets switched off and never switched on again.

public function transferBatch(int $size = 500): int
{
    $from = (int) $this->progress->get('backfill.order', 0);

    // By ID, not with OFFSET: with OFFSET the window shifts as soon as
    // writes happen in parallel, and individual records get skipped.
    $rows = $this->db->fetchAll(
        'SELECT * FROM orders WHERE id > ? ORDER BY id LIMIT ?',
        [$from, $size]
    );
    if ($rows === []) {
        return 0;
    }

    foreach ($rows as $row) {
        // Insert or update: in phase 2 the application is already writing
        // to the new target in parallel. A blind insert would fail on
        // exactly those records.
        $this->newStore->saveRaw($row);
        $from = max($from, (int) $row['id']);
    }

    $this->progress->set('backfill.order', $from);

    // Throttle. The backfill is in no hurry, the application is.
    usleep(100_000);

    return count($rows);
}

On batch size: 500 is a starting value, not a law. The right size is the one at which the application's response time stays unchanged, and it gets measured, not guessed.

Reconciliation: not "done" but "equal"

A completed backfill does not mean both targets contain the same thing. It means a program has run over all records once. That difference is the reason for the phase most often left out.

Reconciliation runs in three stages, cheap to expensive.

Counts. Two queries, immediate. Finds missing records, not wrong values.

Checksum per range. Build a sum over ranges of identifiers and compare. Finds wrong values without comparing every record, and shows which range holds the fault.

-- One checksum per 10,000 IDs. Differences point at the range that
-- needs comparing.
SELECT FLOOR(id / 10000) AS range_no,
       COUNT(*)          AS rows_in_range,
       SUM(CRC32(CONCAT_WS('|', id, status, amount_cents, customer_id))) AS checksum
FROM orders
GROUP BY range_no ORDER BY range_no;

Field-by-field comparison in the suspicious range. Only here does it get expensive, and thanks to stage two it covers a fraction of the data.

The result of those three stages is the condition for phase 4. Not "the backfill finished", but "both targets are equal across all ranges, and the failure count from phase 2 is zero".

Four places where it actually tears

The procedure is sound. What goes wrong in practice is almost always the same four things, and none of them is in the short version.

One: write paths nobody knows about. The application writes to both. The nightly import does not. The maintenance script does not. The partner interface does not. Every one of those paths creates records that do not exist in the new target, and reconciliation finds them without saying where they came from. The search for them belongs before phase 2, not after: one week of query log, filtered to writes against the affected tables, produces the complete list.

Two: deletions. Everybody remembers to write to both. Almost nobody remembers to delete in both. The result is a new target holding records that disappeared from the old one long ago, and at phase 4 they reappear. The same goes for status changes that amount to a deletion in domain terms.

Three: the way back after phase 4. After the switch the application still writes to both targets, so technically the way back exists. It does not exist any more once the new target produces data the old model cannot represent, for instance a finer breakdown. Then phase 4 is one-way, and that belongs said in advance rather than noticed afterwards.

Four: the gap between read and write. A record is read by the backfill, the application changes it in the same second, then the backfill writes the old version into the new target. It is rare and it happens. What helps is having the backfill write only when the record in the target is older than the source, and running reconciliation again at the end.

The same idea on the operations side: a way back exists only when both versions can work at the same time. Blue/green and canary without Kubernetes.

The way back, and when it ends

Until phase 5 the way back is a switch: the read path goes back to the old target. That is why phases 4 and 5 are separate and why the waiting period between them matters so much.

What gets watched during that time is not only failures. It is three things, and the second one is the one most likely to be dropped.

• Failures and differences, meaning the counters from phase 2 and a daily reconciliation run.
• Response times. A new target is not automatically faster. If the 95th percentile of the read path rises after the switch, that is a finding, even when all the data is correct.
• The quiet consumers. Reports, exports, the finance department. They do not speak up on day one, they speak up on the first of the month, and that is exactly why the waiting period is longer than two weeks.

Phase 5 is then the clean-up, and it actually gets done rather than tracked as a ticket. While both targets are written, the system carries double the complexity, and the state of "we migrated but we still write both to be safe" is worse than either end state.

The pattern behind it is the same as in every step-by-step modernization: small steps, each live on its own, each reversible on its own. How I cut that overall is on its own page.

This article belongs to a series about systems that already exist. The retrospective orders every article in it by situation.