Module 6: Migrating Data Without Downtime
Backfill: loading the historical data
Overview
Lesson 3 turned on the dual-write and left a very visible pending task: usb-c-hub and webcam —and all the history— are still only in the old, because the dual-write doesn't look back. This lesson closes that gap with the second piece of the migration: the backfill. The idea, in one sentence: copy to the new store the records that already existed before turning on the dual-write, translating them with the ACL, until the new has both the front (what the dual-write put) and the rear (what the backfill puts).
It sounds like a simple loop that copies everything from the old to the new, and in essence it is —but it has two hard rules that, if ignored, corrupt data silently—:
- Don't overwrite the fresh writes. The dual-write already put in the new the current values of the records written since it was turned on. If the backfill copies over them an old version (the one the record had when the backfill took its snapshot of the old), it overwrites fresh data with stale data. The backfill must copy only what's missing, never overwrite what the dual-write already left.
- Idempotence. The backfill of a real system can take hours and can fall midway; you have to be able to run it again without fear. An idempotent backfill, run twice (or ten times), leaves the new exactly the same as run once: it doesn't duplicate, doesn't corrupt, only completes what's missing.
The two rules are born from the same design decision: the backfill does insert-if-absent —it inserts a row only if it's not already in the new—. That single rule achieves both things: it doesn't overwrite the fresh (because the fresh is already there, so it skips it) and it's idempotent (running it again finds nothing new to insert). The whole lesson revolves around why that rule is the right one.
And there's a third piece, which is about order: the backfill goes after turning on the dual-write, never before. The reason is subtle and crucial, and you're going to see it executed: if you backfill first and turn on the dual-write after, the writes that occur in the gap between the two are lost for the new. Turning on the dual-write first guarantees that gap doesn't exist.
Connection with the module. The dual-write (lesson 3) covered the front; the backfill covers the history; together they leave the new complete. But "complete" is a claim that has to be verified, and that's lesson 5 (parallel-run): comparing the two stores to check that the dual-write plus the backfill really left the new equal to the old. Notice the boundary: here the backfill is an in-memory loop over a dictionary; at scale, backfilling billions of rows without saturating the database or blocking the live writes (in batches, with pauses, resumable with checkpoints) is a production data-engineering problem —from the Data Engineering ecosystem—. The pattern (insert-if-absent, idempotent, after the dual-write) is identical; the machinery to execute it at that scale belongs to the other ecosystem.
An analogy: copying the old filing cabinet to the new without overwriting the fresh
You're still with the move of lesson 3. The mail forwarding (the dual-write) is already active: every new letter reaches both houses. Now it's your turn to do what the forwarding doesn't do —carry the historical archive—: you have an old filing cabinet full of files accumulated over years, and you want to have all those files in the new filing cabinet of the new house too.
The obvious plan: take each file out of the old cabinet, photocopy it, and store it in the new. But there's a trap, and it's the rule that gives the lesson its name. While you copy the archive (it takes days), the forwarding keeps bringing fresh updates. Imagine the "SSD" file: yesterday, when you started copying, it said "price 89.99". Today an update arrived through forwarding —"price 84.99"— which you already stored in the new cabinet. If you now take out of your box the old photocopy of the "SSD" file (the 89.99 one, which you took yesterday) and put it in the new cabinet, you overwrite the fresh 84.99 version with the old 89.99 one. You just staled the data with your own move.
The rule that avoids the disaster is simple: before storing a photocopy in the new cabinet, check if there's already a file for that record there. If there is one —the forwarding put it, it's fresher—, don't touch it. Only store the photocopies of the files that are missing in the new. That's insert-if-absent: you copy the absent, you respect the present.
And that same rule gives you a gift: you can go through all the files as many times as you want without doing harm. If you're interrupted mid-move and the next day you start again from the first file, it doesn't matter: the ones you already copied are already in the new, so you skip them; you only copy the ones still missing. You never duplicate a file or overwrite a fresh one. That peace of mind of "I can re-run it without fear" is idempotence, and it's what makes a backfill that takes hours and maybe falls midway manageable.
The order detail is missing. Why did you activate the forwarding before starting to copy the archive, and not the other way around? Because if you copied the whole archive first and then activated the forwarding, any letter that arrives in the gap between "I finished copying" and "I activated the forwarding" isn't caught by either: the forwarding wasn't there yet, and your copy of the archive already passed. By activating the forwarding first, that gap doesn't exist: everything new is caught by the forwarding, everything old is caught by the copy, and no no-man's-land is left between the two.
Worked example: the backfill that doesn't overwrite the fresh, and is idempotent
We're going to execute the two rules head-on. The setup reproduces the filing cabinet problem: there are three historical records (dual-write off, only in the old). The backfill takes its snapshot of the old at that moment —with ssd-1tb at 89.99—. After, with the dual-write on, a fresh write arrives: ssd-1tb drops to 84.99, and the dual-write puts it in both stores. Now the new has the fresh ssd-1tb (84.99), but the backfill's snapshot still says 89.99. We compare a naive backfill (which overwrites everything) against a safe one (insert-if-absent).
import copy
old_db = {}
new_db = {}
dual_write_on = False
def to_new_model(old_row):
return {
"id": old_row["sku"],
"title": old_row["name"],
"price_usd": old_row["price_cents"] / 100,
"in_stock": old_row["stock"] > 0,
}
def write_product(row):
old_db[row["sku"]] = row
if dual_write_on:
new_db[row["sku"]] = to_new_model(row)
# --- HISTORICAL phase: 3 rows written with dual_write OFF (only in old). ---
for row in [
{"sku": "ssd-1tb", "name": "SSD 1TB", "price_cents": 8999, "stock": 12},
{"sku": "usb-c-hub", "name": "USB-C Hub", "price_cents": 3499, "stock": 40},
{"sku": "webcam", "name": "Webcam HD", "price_cents": 5999, "stock": 0},
]:
write_product(row)
# --- The backfill takes its SNAPSHOT of the old NOW: ssd-1tb at 89.99. ---
snapshot = copy.deepcopy(old_db)
# --- dual_write ON. A FRESH write arrives: ssd-1tb drops to 84.99 (to both). ---
dual_write_on = True
write_product({"sku": "ssd-1tb", "name": "SSD 1TB", "price_cents": 8499, "stock": 12})
print("State before the backfill:")
print(f" old['ssd-1tb'].price_cents = {old_db['ssd-1tb']['price_cents']} (fresh: 84.99)")
print(f" new['ssd-1tb'].price_usd = {new_db['ssd-1tb']['price_usd']} (fresh, via dual_write)")
print(f" snapshot['ssd-1tb'].price_cents = {snapshot['ssd-1tb']['price_cents']} (OLD: 89.99)")
print(f" new has: {sorted(new_db)} (missing the historical: usb-c-hub, webcam)\n")
# --- NAIVE backfill: writes each snapshot row WITHOUT checking if it already exists. ---
def naive_backfill(target_new, snap):
for sku, old_row in snap.items():
target_new[sku] = to_new_model(old_row) # always overwrites
# --- SAFE backfill: insert-if-absent. Only writes what's MISSING in the new. ---
def safe_backfill(target_new, snap):
copied = 0
for sku, old_row in snap.items():
if sku not in target_new: # doesn't overwrite the fresh
target_new[sku] = to_new_model(old_row)
copied += 1
return copied
# Run each backfill on a COPY of the new to compare without crossing them.
new_naive = copy.deepcopy(new_db)
new_safe = copy.deepcopy(new_db)
naive_backfill(new_naive, snapshot)
copied1 = safe_backfill(new_safe, snapshot)
copied2 = safe_backfill(new_safe, snapshot) # <- IDEMPOTENCE: 2nd run
print(f"{'backfill':<22}{'ssd-1tb in new':>16}{'rows in new':>14}")
print("-" * 52)
print(f"{'naive':<22}{new_naive['ssd-1tb']['price_usd']:>16}{len(new_naive):>14}")
print(f"{'safe (1st run)':<22}{new_safe['ssd-1tb']['price_usd']:>16}{len(new_safe):>14}")
print("-" * 52)
print(f"\n naive -> overwrote the fresh 84.99 with the snapshot's 89.99: CORRUPTION.")
print(f" safe -> respected the 84.99 (already there) and copied the historical. {copied1} rows.")
print(f" idempotence -> the 2nd run of the safe backfill copied {copied2} more rows")
print(f" (nothing to copy: the new is already complete, no duplicates).")What to expect. When you run the file, the output is exactly this:
State before the backfill:
old['ssd-1tb'].price_cents = 8499 (fresh: 84.99)
new['ssd-1tb'].price_usd = 84.99 (fresh, via dual_write)
snapshot['ssd-1tb'].price_cents = 8999 (OLD: 89.99)
new has: ['ssd-1tb'] (missing the historical: usb-c-hub, webcam)
backfill ssd-1tb in new rows in new
----------------------------------------------------
naive 89.99 3
safe (1st run) 84.99 3
----------------------------------------------------
naive -> overwrote the fresh 84.99 with the snapshot's 89.99: CORRUPTION.
safe -> respected the 84.99 (already there) and copied the historical. 2 rows.
idempotence -> the 2nd run of the safe backfill copied 0 more rows
(nothing to copy: the new is already complete, no duplicates).
Read the state before the backfill first, because there the conflict is set up. The old has ssd-1tb at 84.99 (the fresh value, already updated). The new also has ssd-1tb at 84.99 (the dual-write put it). But the snapshot the backfill took says 89.99 —the old value, from before the update—. The new has only ssd-1tb; it's missing the historical usb-c-hub and webcam. This is the classic scenario where a backfill can do harm.
Now the table:
- The naive backfill left
ssd-1tbin the new at 89.99. It's a corruption: it overwrote the fresh value (84.99, which the dual-write had put correctly) with the old value of its snapshot (89.99). The user had changed the price to 84.99, and the backfill reverted it to 89.99 without anyone asking. This is the worst a migration can do: not lose a piece of data, but dirty one that was fine. And it's silent: the naive threw no error; it simply overwrote. - The safe backfill left
ssd-1tbat 84.99 —the fresh value, intact—. Becausessd-1tbwas already in the new, the insert-if-absent skipped it: it had nothing to insert there. And it did copy what was missing: the 2 historical rows (usb-c-hub,webcam), which weren't in the new. The result is 3 rows in the new (1 fresh + 2 historical), all with the correct value. It copied exactly 2 rows, the ones that were missing.
And the idempotence line closes the lesson: the second run of the safe backfill copied 0 rows. Nothing to do: the new was already complete, so the insert-if-absent found no absent row. Running it again —or ten times— changes nothing and corrupts nothing. That's the property that lets you re-run a backfill of hours without fear: if it fell midway, you launch it again and it picks up what was missing, without overwriting or duplicating what was already done.
Deep dive: why the backfill goes after the dual-write
The order rule —dual-write first, backfill after— seems arbitrary until you draw the timeline and see the gap that appears if you invert it.
The correct order: dual-write, then backfill.
dual_write ON backfill (copies the historical)
│ │
────────┼─────────────────────────────────────┼──────────────▶ time
│ │
every write from here copies everything before
lands in BOTH (front covered) (rear covered)
└── no gap: dual_write catches
the new, the backfill the old ──┘
With this order, every write of the system lands in at least one of the two mechanisms: if it happens after turning on the dual-write, the dual-write catches it; if it happened before, the backfill catches it. There's no instant that escapes both. (The overlap —a row the dual-write already put and the backfill sees again— is safe precisely because of the insert-if-absent: the backfill skips it.)
The inverted order: backfill, then dual-write. The gap.
backfill (copies the historical) dual_write ON
│ │
────────┼─────────────────────────────────────┼──────────────▶ time
│ ╲ GAP ╱ │
copies everything ▼▼▼▼ from here lands in both
before here the writes in the gap land ONLY in old:
the backfill already passed, dual_write isn't on yet
If you backfill first and turn on the dual-write after, the writes that occur in the gap between the two moments land only in the old: the backfill already took its snapshot and already passed (it won't see them), and the dual-write isn't on yet (it won't copy them). Those writes are lost for the new —silently—. In a system with traffic, that gap can be seconds or minutes, but in those seconds sales come in, price changes, stock adjustments: real data the new will never have, until a parallel-run exposes it much later. Turning on the dual-write first eliminates the gap at the root.
Why insert-if-absent, and not "write if newer". You could think of a cleverer rule: "copy the backfill row only if it's more recent than the new's". But that requires a reliable version field or timestamp on each row, and comparing dates between two different models —more complexity and more ways to go wrong—. Insert-if-absent is simpler and sufficient: during the dual-write, what's in the new is always at least as fresh as the backfill's snapshot (because the dual-write writes in real time and the snapshot was taken at a fixed point in the past). So "it's already in the new" implies "it's fresh or fresher", and skipping it is always correct. Simplicity isn't laziness here: it's what makes the backfill easy to reason about and hard to break.
Idempotence as an operational requirement, not a luxury. A backfill over a real database goes through millions of rows, in batches, over hours. It's going to fail midway at some point —a deploy, a restart, a timeout—. If the backfill weren't idempotent, each fall would force you to reason "where was I?, what did I already copy?, what happens if I copy again what was already there?" —an operational hell—. With idempotence, the answer is always the same: launch it again, whole. It'll pick up what's missing and skip what's already there. That's why insert-if-absent isn't an implementation detail: it's what makes the backfill operable in the real world.
Common mistakes
The backfill that overwrites the fresh writes. What happens: the backfill copies the whole old to the new without checking if the row is already there, overwriting values the dual-write had put fresh with the old values of its snapshot —like the naive that reverted ssd-1tb from 84.99 to 89.99—. Why it happens: "copy everything" is the natural impulse, and the conflict with the fresh writes is invisible in an example without concurrency. How to spot it: after the backfill, the parallel-run reports discrepancies in rows that had been updated during the migration —the old has the new value, the new has the reverted value—. Worse: if the backfill runs after the new already received traffic, it can revert changes the users already saw. How to fix it: insert-if-absent, always. The backfill copies what's missing, never overwrites what's present. What's present in the new, during the transition, is equal to or fresher than the backfill's snapshot; overwriting it can only stale data.
Backfilling before turning on the dual-write. What happens: the team, with the logic of "first I fill the new with what's there, then I turn on the synchronization", runs the backfill and then activates the dual-write —opening the gap of lost writes—. Why it happens: intuitively it seems more orderly to "load first, synchronize after", like filling a tank before opening the tap. How to spot it: the parallel-run reports as missing or out of sync exactly the rows that were written in the window between the end of the backfill and the turn-on of the dual-write. How to fix it: invert the order. Dual-write first (so no new write escapes), backfill after (for the earlier). The overlap is safe thanks to the insert-if-absent; the gap of the inverse order has no fix except re-backfilling. The rule is counterintuitive but firm: turn on the synchronization of the future before copying the past.
A backfill that isn't idempotent. What happens: the backfill duplicates rows, or corrupts, if run twice —for example, one that inserts unconditionally instead of insert-if-absent, generating duplicates on the second pass—. Why it happens: on the first "happy" run it's never noticed; the problem appears when the backfill falls midway and has to be relaunched. How to spot it: after a second run (or after a restart midway), duplicated rows or altered values that weren't there before appear. How to fix it: design the backfill idempotent from the start —insert-if-absent, or an upsert by key that doesn't depend on the order or the number of runs—. The test is concrete: running it twice in a row must leave the new identical to running it once (like in the example, where the second run copied 0 rows). If that test doesn't pass, the backfill isn't ready for production, where it will run more than once.
Exercises
Exercise 1 — Reconstruct the naive's corruption. In the example, the naive backfill left ssd-1tb at 89.99 in the new, when the correct value was 84.99. (a) Where did the 89.99 come from? (b) Why was the correct 84.99 already in the new before the backfill ran? (c) What single line of the safe backfill avoids this corruption, and how?
See solution
(a) The 89.99 came from the snapshot (snapshot) the backfill took of the old before the price update. At that moment ssd-1tb was worth 8999 cents (89.99). The naive backfill copied that old snapshot to the new, translating it to 89.99, and since it writes unconditionally, it put it over the fresh value.
(b) Because the dual-write was already on when the update to 84.99 arrived, so that write landed in both stores —including the new—. When the backfill ran, the new already had ssd-1tb at 84.99, put by the dual-write in real time. The new was correct; the naive backfill dirtied it.
(c) The line if sku not in target_new: of the safe backfill. Before writing, it checks if the row is already in the new. Since ssd-1tb was already there (fresh, put by the dual-write), the condition is false and the backfill skips it, leaving the 84.99 intact. It only inserts the absent rows (the historical ones that were missing). That single check —insert-if-absent— turns a dangerous backfill into a safe one.
Exercise 2 — Draw the gap. A team decides to backfill first and turn on the dual-write after. During the 30 seconds between "the backfill finished" and "the dual-write was turned on", 3 writes arrive: a new product mouse-pro, a price change for webcam, and a stock adjustment for ssd-1tb. (a) In which store(s) does each land? (b) Which of those 3 changes will the new have after turning on the dual-write? (c) How does the correct order (dual-write first) avoid this problem?
See solution
(a) The 3 land only in the old. During those 30 seconds, the backfill already finished (it took its snapshot before, it won't see anything new) and the dual-write isn't on yet (it doesn't copy to the new). So mouse-pro, the price change for webcam, and the stock adjustment for ssd-1tb are written only in the old.
(b) The new will have none of the 3, at least not through these mechanisms. The backfill already passed and didn't copy them (they happened after its snapshot). The dual-write, on turning on, only catches the future writes, not those of those 30 seconds that already passed. The 3 changes stay in the old and absent from the new —until a parallel-run detects them and a reconciliation (or a re-backfill) recovers them—. mouse-pro will be missing entirely; webcam and ssd-1tb will be out of sync (old with the change, new without it).
(c) The correct order —dual-write first, backfill after— eliminates the gap. By turning on the dual-write before backfilling, those 3 writes (which would now happen after the turn-on) are caught by the dual-write and land in both stores. The subsequent backfill covers only what's earlier than the turn-on, and the overlap with what the dual-write already put is safe thanks to the insert-if-absent. No window is left where a write escapes both mechanisms.
Exercise 3 — The idempotence test. You're given a backfill written by another team and you want to know if it's safe for production. (a) What concrete test do you run to verify it's idempotent? (b) In the example, the second run of the safe backfill copied 0 rows: why is that the sign of idempotence? (c) Why does idempotence matter so much in a real backfill when in the toy example it's barely noticeable?
See solution
(a) You run it twice in a row on the same state and verify the result is identical to running it once: same rows, same values, no duplicates, no changes on the second pass. If the second run alters something —duplicates rows, changes values—, it's not idempotent and it's not ready for production. (A more demanding version: run it, interrupt it midway, and launch it again whole; it must converge to the same result.)
(b) Because copying 0 rows on the second run means the backfill found nothing to do the second time: everything that should be there was already there, and its insert-if-absent inserted nothing. That "does nothing the second time" is idempotence: running it again doesn't change the state. If the second run had copied rows (or duplicated), it would have changed the state, and it wouldn't be idempotent.
(c) Because in the toy example the backfill copies 3 rows in an instant and never falls; running it once or twice makes no difference and the risk is theoretical. In a real backfill you go through millions of rows, in batches, over hours, and it will fall at some point (a deploy, a restart, a database timeout). Without idempotence, each fall forces you to figure out exactly where you were and to fear that relaunching it will duplicate or corrupt. With idempotence, the recovery is trivial: you relaunch the whole backfill and it picks up what's missing, without overwriting or duplicating what's done. At scale, idempotence is the difference between an operable backfill and one that's terrifying to touch.
Summary and next step
In this lesson you closed the historical gap the dual-write left open: the backfill. You saw, with the old filing cabinet copied to the new without overwriting the fresh files that already arrived through forwarding, that the backfill does insert-if-absent —copies what's missing, respects what's present—, and that that single rule achieves the two things that matter: not overwriting the dual-write's fresh writes and being idempotent (re-running it doesn't duplicate or corrupt). You executed it: the naive backfill reverted ssd-1tb from 84.99 to 89.99 (silent corruption), while the safe one respected the 84.99 and copied only the 2 missing historical rows; and its second run copied 0 rows, the proof of idempotence. And you saw, with the timeline, why the backfill goes after turning on the dual-write: inverting the order opens a gap where writes are lost for the new.
Before moving on you should be able to: explain the insert-if-absent rule and how it achieves the two hard rules at once; draw the gap that appears if you backfill before the dual-write; distinguish an idempotent backfill from one that isn't and the test to verify it; and argue why overwriting a fresh write is worse than losing a row.
Lesson 5 answers the question the dual-write and the backfill leave pending: did it really turn out right? The dual-write covered the front, the backfill covered the history, and now you claim the new is complete —but claiming isn't verifying—. The parallel-run reads from both stores, compares them row by row, and reports every discrepancy: a row the backfill skipped, a field the ACL translated wrong, a write the dual-write lost through a partial failure. It's the safety net of the whole migration, and the exact equivalent —over data— of module 2's characterization test. You're going to see a parallel_run report discrepancies = 2 and understand why, as long as that number isn't zero, the read-switch isn't touched.
Resources
- Sam Newman, Monolith to Microservices (O'Reilly, 2019), ch. 4, "Decomposing the Database" — the section on loading the existing data into the new store during the migration and coordinating that backfill with the live synchronization. The direct reference of this lesson. In English.
- Pramod Sadalage and Martin Fowler, Refactoring Databases: Evolutionary Database Design (Addison-Wesley, 2006) — the refactorings that involve moving and transforming existing data (like "Migrate Data") treated as idempotent and reversible steps. In English.
- Stripe Engineering, "Online migrations at scale" — stripe.com/blog/online-migrations. A production account of the complete pattern (dual-write, backfill, comparison, cut) over a huge, in-use table, with the emphasis on backfilling without blocking the live traffic. The bridge between this lesson's technique and its execution at scale. In English.
- GitHub Engineering, "gh-ost: GitHub's online schema migration tool for MySQL" — github.blog/2016-08-01-gh-ost-github-s-online-migration-tool-for-mysql. How GitHub migrates schemas of large tables without downtime, copying data in the background while the table receives writes —a production backfill with all its rules—. In English.