Drill 102 — The Backfill That Blocked Prod

Theme: Migrations & Releases · Format: 20-min drill · Difficulty: core

The story

A payments platform ships a small feature: a new risk_tier column on the ledger_entries table, derived from each entry's amount and merchant category. Adding the nullable column is instant — a metadata-only change, reviewed and merged without drama. The only thing left is to fill in the value for the rows that already exist. An engineer writes a one-off script: a single UPDATE ledger_entries SET risk_tier = compute(...) WHERE risk_tier IS NULL. It runs in a blink against the ten-thousand-row staging database. She opens a ticket, gets a thumbs-up, and at 2:40 on a Tuesday afternoon runs it against production.

Production has six hundred million rows. The UPDATE opens one transaction and starts rewriting them all. For the first minute nothing looks wrong — the job is "just running," the database process is healthy, CPU is busy but not pegged, and the deploy dashboard is green. Under the surface the single transaction is taking row locks it never releases, the write-ahead log is filling faster than disk can flush it, and I/O is saturating. Live INSERTs from the checkout path — every card tap, every settlement — begin queuing behind the backfill's locks and the starved disk.

By 2:44 the symptoms surface where nobody is looking for a backfill: card taps time out, the checkout service's connection pool exhausts waiting on writes, the read replica falls minutes behind, and the mobile app shows spinners that never resolve. The on-call for payments is paged for an availability incident and starts looking at the checkout service, the load balancer, the card processor — everywhere except a data-fix ticket that said "quick backfill, low risk." The product is down, and the cause is a five-minute data change nobody thought of as a deploy.

⏸ Stop here and ask the room: the database never crashed and every health check is green. So what took the product down, and why is the on-call looking in the wrong place?

Run it — facilitator script

Timing for a 20-minute session: 2 min story · 10 min staged questions · 3 min reveal · 5 min transfer.

The exact same statement ran instantly in staging. Why does it take production down?

• Listen for: scale changes the mechanics, not just the runtime. Ten thousand rows finish before any lock matters; six hundred million rows in one transaction hold locks on everything they touch for the whole run, generate enormous write-ahead log and disk I/O, and starve the live write path. It behaves like an unthrottled load test fired at peak — the number of rows, not the query plan, is the hazard.
• Weak signal: "the query was slow — add an index" or "give the box more CPU." This misreads a contention-and-I/O problem as a query-tuning problem; a faster plan that still rewrites 600M rows in one transaction still takes prod down.

The database process never died and the dashboards were green. So what should have alarmed?

• Listen for: liveness is not the same as health. The signals that were actually moving — longest-running transaction age, lock-wait time, write p99 latency, replica lag, connection-pool saturation — are the ones to alarm on. A health check that asks "is the database accepting connections?" measures the wrong thing; you need contention and saturation SLIs (service level indicators), not a ping.
• Weak signal: "the monitoring should have caught the crash." There was no crash — that is the whole point. Waiting for a red process light means every lock-storm and I/O-starvation incident is invisible until customers report it.

How do you run this backfill so it cannot take prod down?

• Listen for: treat it as a migration, not a fix. Chunk it into small batches (a few thousand rows) with a throttle between them so each lock is held for tens of milliseconds; run it in an off-peak window; compute values against a read replica so the primary only does the writes; and give it a kill switch plus a progress table so it is observable, resumable, and can pause itself when it starts hurting live traffic.
• Weak signal: "run it at night" alone — an unthrottled 3 a.m. UPDATE still locks the table and still pages whoever is awake in another region. Or "just be more careful," which is not a mechanism.

The reveal

The one-line UPDATE was the outage. Rolling it back was worse than letting it finish: killing the transaction triggered a rollback that had to undo everything it had done, extending the incident. Total customer-facing downtime was nineteen minutes. The fix was not "run the same statement more carefully" — it was to delete the one-shot script and replace it with a batched backfill job: five thousand rows per transaction, a two-hundred-millisecond pause between batches, values read from a replica, a progress table with a resume cursor, and an auto-pause wired to replica lag and write latency. Re-run in the overnight trough, it populated all six hundred million rows over a few hours with zero measurable impact on checkout.

Why this happens — the mechanics

A backfill is a write the size of the whole table, and every property that makes a single big UPDATE convenient is exactly what makes it dangerous at scale. One transaction means one atomic unit: the locks it acquires are held until commit, so a run that lasts minutes blocks conflicting live writes for minutes; the undo/redo it generates (write-ahead log, rollback segments) grows with the row count, so disk I/O and replication lag scale with the table, not with the "five minutes" the author estimated from staging. Staging lies precisely because it is small — it validates the syntax of the statement, never its mechanics under production volume and concurrent load. The migration checklist calls this out directly: staging with ten thousand rows validates syntax, not mechanics.

The reason it stays invisible until customers feel it is a monitoring seam. Availability dashboards and health checks are built to catch liveness failures — the process died, the port stopped answering, the box fell over. A backfill causes none of those. The database is up, serving reads, answering health checks; it is simply refusing to make progress on the write path because a giant transaction is sitting on the locks and the disk. The signals that would reveal it — lock-wait time, longest-transaction age, write p99, replica lag — live one layer below the green lights, and nobody wired them to a page because "the database is fine" was true in the only sense the alarm understood.

Underneath both is a category error that this theme keeps punishing: treating a data change as a quick fix instead of as a migration. A migration gets a rehearsal on production-sized data, a rollback plan, a run window, and a kill switch. A "quick backfill" gets a thumbs-up on a ticket. The instant a data change touches every row of a hot table, it is a migration whether or not anyone called it one — and the moment of maximum risk is a mid-afternoon UPDATE that "should only take a minute."

Diagrams

Problem — how the failure unfolds:

sequenceDiagram
    autonumber
    participant E as Engineer
    participant DB as Ledger DB
    participant API as Payment API
    participant U as Cardholders
    E->>DB: Run one-shot backfill<br/>UPDATE, unthrottled
    Note over DB: One txn locks 600M rows<br/>WAL and disk IO saturate
    API--xDB: Live writes queue<br/>behind the locks
    U--xAPI: Card taps time out<br/>checkout fails
    Note over DB: Process still up<br/>health checks stay green
    Note over E,U: Found by failing payments<br/>not by the job

Solution — where the guardrails go:

flowchart TD
    A["New column added<br/>nullable, no default"] --> B["Backfill in small batches<br/>(chunk + throttle)"]
    B --> C["Run off-peak<br/>(traffic-trough window)"]
    C --> D["Compute values from<br/>a read replica"]
    D --> E["Progress table +<br/>kill switch (resumable)"]
    E -.-> AL["Replica lag over 5s or<br/>write p99 over 200ms<br/>auto-pauses the job"]
    AL --> F["Batches resume when<br/>the signals recover"]

What good looks like

• Chunked, throttled batches with a lock-hold cap — update 1,000–5,000 rows per transaction over a keyset cursor, commit, then sleep 100–250 ms between batches. Target lock-hold under 50 ms per batch and write p99 unchanged from baseline; never a single transaction over the whole table.
• Off-peak scheduling as policy, not vibe — run in a defined trough window (e.g. 01:00–04:00) where live QPS is under 10% of the daytime peak, and refuse to start a large backfill inside business hours. Off-peak plus throttling, not either alone.
• Compute against a read replica — derive the new values by reading from a replica so the primary only takes the writes; keep the read load and any expensive compute() off the box that serves checkout.
• A kill switch wired to live-traffic SLIs — the job polls replica lag and write p99 and auto-pauses when replica lag exceeds 5 s or write p99 exceeds 200 ms, resuming only after both recover. A human can also stop it with one command; because it is batched and cursor-based, stopping mid-run never triggers a giant rollback.
• A progress table you can watch — persist rows_done / rows_total, the last-processed key, and an ETA, updated every batch, so "how far along and is it hurting anything?" is a query, not a guess. The job is idempotent and resumes from the cursor after any pause or restart.
• A rehearsal on production-sized data — dry-run the batched job against a restored production-sized copy, measure real lock-hold and lag per batch, and tune batch size and sleep from those numbers before touching prod. Ten thousand staging rows prove syntax, not mechanics.
• Contention alarms independent of the job — page on longest-transaction age (> 60 s on a hot table) and lock-wait time regardless of who caused it, so the next unthrottled statement — from anyone — is caught in minutes, not by customers.

The lesson

Any write that touches every row of a hot table is a migration, not a quick fix — so it gets the migration treatment: chunk it into throttled batches with a bounded lock-hold, run it off-peak, read from a replica, and give it a kill switch tied to live-traffic signals. A green health check proves the database is alive, not that it is making progress; alarm on contention and saturation, not just liveness.

Named concepts: online / chunked backfill, throttling & off-peak scheduling, replica offload, kill switch + progress monitoring.

Real-world echoes

• GitHub's gh-ost (2016). GitHub built and open-sourced gh-ost specifically because online schema changes and backfills on large MySQL tables were causing production pain — lock contention and replication lag from naive ALTER/UPDATE operations. gh-ost's whole design is this drill's fix productized: it works in throttled chunks, reads from a replica, watches replication lag, and offers a live kill switch and pause/resume — batched, observable, and interruptible instead of one giant blocking write.

Transfer

Which one-off script or data fix in your stack would take a table lock or saturate I/O if someone ran it against production at 2 p.m. today? "It's just an update" is the tell, not the reassurance — name the batch size, the run window, and the signal that would pause it.

Make it a ticket: inventory every backfill/data-fix path that can touch a large hot table; for each, convert it to a chunked+throttled job with an off-peak window, a replica read, and an auto-pause on replica lag > 5 s or write p99 > 200 ms — starting with anything on a table the payment path writes to.

Related

• Drill 56 — The Lock at 10 A.M. (this theme): the sibling failure — a schema ALTER that ran in seconds on staging holds a long table lock on production at peak. Same root: staging validates syntax, not lock mechanics at scale.
• Drill 57 — The Rollback That Couldn't (this theme): the other half of "a data change is a migration" — when the way back is itself destructive, killing the operation makes the incident worse instead of ending it.
• Drill 8 — The Backfill That Rewrote History (Theme 2 — Data Disasters): the same word, the correctness failure mode — a backfill that silently overwrites good data rather than one that locks the table. Backfills are dangerous on both the availability and the integrity axis. </content> </invoke>