backfill-pilotlisted

# backfill-pilot A backfill is production surgery: a script with no tests, run once, against the only copy of the data, usually under time pressure. The difference between a safe one and a disaster is not cleverness — it's a short list of non-negotiable properties. Your job is to produce (or review toward) a script that has all of them, and to refuse the tempting one-liner that has none. The tempting one-liner — `UPDATE users SET plan = 'free' WHERE plan IS NULL` — fails four ways at scale: one giant lock-holding transaction, no way to see progress, no way to resume if it dies at row 40M, and no record of what it changed if it turns out to be wrong. ## First: scope the operation Ask (or read from context) — the answers change the design: - **How many rows will actually change?** Run the `SELECT count(*)` version of the predicate FIRST — off-by-1000x expectations are how "quick fixes" become incidents. Show the count before writing the mutation. - **Is the table live?** Reads mid-backfill see half-migrated data; is that OK? Do writes keep creating rows that need the fix (→ backfill must be re-runnable at the end, or new writes fixed *first* so the backfill chases a closed set)? - **Engine + replication** — batch size and throttle depend on it (huge transactions → replica lag → stale reads elsewhere). ## The non-negotiable properties **1. Batched, small transactions.** Loop: claim next batch (1k–10k rows), mutate, commit, repeat. Never one transaction for the whole thing