Backfilling Large Tables in Batches

You add the column, deploy the code that writes it, and run the migration that fills it in. The migration is one line, so it goes out with the release. On a table with fifty thousand rows it takes a second, the deploy looks fine, and everybody moves on. On the table with forty million rows it takes twenty minutes, holds a lock for every one of them, and takes the site down.

The statement is not wrong. What is wrong is putting every row it touches into one transaction with no way out. An UPDATE is atomic by default, so the locks it takes and the undo it writes stay alive until the last row is done, and every request that wants one of those rows waits that long. The fix is the same statement run in pieces small enough that each piece finishes.

The numbers below come from MySQL 9.6.0 on a 50,000-row accounts table in an isolated instance, and from SQLite 3.51.0 on the same data. Read the ratios; the shape is what transfers.

An unbounded UPDATE is one transaction that holds every row it touches

Start with the statement everybody writes first, against a table whose new column is all NULL:

UPDATE accounts
   SET plan_name = CASE plan_code WHEN 'free' THEN 'Free'
                                  WHEN 'pro'  THEN 'Professional'
                                  WHEN 'team' THEN 'Team'
                                  ELSE 'Enterprise' END;

On 50,000 rows that took 958 ms, 1,121 ms and 1,066 ms across three runs. While it was open, information_schema.INNODB_TRX showed what the transaction was holding:

rows_locked=50406  rows_modified=50000  lock_mem_kb=88.1

Read locked rather than modified, because that is what other sessions collide with. InnoDB locked every row it examined and kept the locks until the statement finished, so a writer that wanted one row of that table waited for the whole update. On this machine a second session updating a single row by primary key waited 6,824 ms behind an unbounded update. The same probe during a batch loop, below, waited 19 ms at worst, in six tries.

The second cost is undo. InnoDB writes the previous version of every row it changes into the undo log so the transaction can roll back, and it can only purge that log after the commit. A 50,000-row transaction is 50,000 row versions plus the secondary index entries that go with them. On the same instance, thirty uncommitted passes over the table pushed innodb_undo_002 from 32 MB to 48 MB while the transaction was open and to 64 MB after it rolled back, with 1,420,630 rows modified. The space stays allocated, and INNODB_TRX.trx_rows_modified is the per-transaction number to watch.

The transaction that takes a site down is the one nobody can wait for

How long one row stays locked is the whole design question, and it is set by how many rows share a transaction. A batch of 500 rows produced these measurements, sampled one second into an open transaction:

batch     rows_locked while open   slowest batch   total time
500             503                  20.9 ms        1,260 ms
2,000         2,015                 130.3 ms        2,045 ms
10,000       10,080                 314.7 ms        2,571 ms
all 50,000   50,406               1,065.5 ms        1,066 ms

Total time is the wrong column to optimise. The naive statement is the fastest way to finish the work and the worst way to do it, because its lock hold is the whole job. The right column is the slowest batch, because that is the longest any request can be stuck behind this migration. The batch loop's median batch was 33 ms and its worst was 131 ms at a batch size of 2,000; a request that arrives at the wrong moment waits for one batch, not for the migration.

The loop is bounded by the primary key, not by OFFSET

Every serious batch loop has the same shape: remember the last key you processed, select the next slice with WHERE id > :last ORDER BY id LIMIT :n, commit, sleep, repeat until the slice comes back empty. Here it is as a shell migration using the MySQL client:

last=0; batch=2000
while :; do
  n=$(mysql --socket=/run/mysqld/mysqld.sock -uroot --batch --skip-column-names -e "
    USE sitelike;
    UPDATE accounts
       SET plan_name = CASE plan_code WHEN 'free' THEN 'Free'
                                      WHEN 'pro'  THEN 'Professional'
                                      WHEN 'team' THEN 'Team'
                                      ELSE 'Enterprise' END
     WHERE id > $last AND plan_name IS NULL
     ORDER BY id
     LIMIT $batch;
    SELECT ROW_COUNT();")
  [ "$n" = "0" ] && break
  last=$(mysql --socket=/run/mysqld/mysqld.sock -uroot --batch --skip-column-names -e "
    USE sitelike; SELECT MAX(id) FROM
      (SELECT id FROM accounts WHERE id > $last ORDER BY id LIMIT $batch) t;")
  sleep 0.02
done

Three details in that loop matter more than the syntax. The cursor is the primary key, so each batch is an index range scan and the next batch starts where the last one stopped; OFFSET would re-read and re-skip everything before it, getting slower with every pass. Each iteration commits, which releases the locks and lets InnoDB purge undo, and the loop stops when a batch moves zero rows, which is the only condition that survives rows being inserted while it runs.

PostgreSQL has the same shape but no LIMIT on UPDATE, so the slice comes from a CTE. Its manual gives the pattern: select the target rows by ctid with ORDER BY, FOR UPDATE and LIMIT, then update by joining to that CTE. No PostgreSQL server was running on the machine this lesson was written on, so that is taken from the manual rather than measured here.

Batch size sets lock hold, replication lag and the timeout budget at once

Batch size is not a style choice. It settles how long a row stays locked, how much work the replica's SQL thread can start at once, and whether a writer gives up or waits.

Lock hold is the simplest of the three. Each batch is one transaction, so a batch of n rows is a transaction that touches n rows, and the sampled lock counts above scale exactly that way: 503 locked at 500, 2,015 at 2,000, 10,080 at 10,000. Nothing is amortised away. The batch size is the lock budget.

Replication adds a constraint in the other direction. Row-based replication ships changes as transactions and the replica applies each one as a unit, so a batch of 2,000 rows becomes a transaction on the replica that it cannot start until it has the whole thing and cannot interrupt halfway. With binlog_format=ROW and sync_binlog=1 on the same instance, the backfill wrote 4.65 to 4.68 MB whichever way it ran; the difference between one transaction and 130 was the transaction count, not the byte count. Those bytes have to cross a network between the source's commit and the replica's apply, so a batch that takes 300 ms to generate is a replica that is at least 300 ms behind for that window, and SHOW REPLICA STATUS lag grows in exactly the way that turns a backfill into an alert.

The third constraint is the timeout you configured. MySQL's innodb_lock_wait_timeout defaults to 50 seconds on the instance used here, and a writer that exceeds it fails with:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

That error is the application seeing your migration. A transaction that holds a row longer than the timeout converts a slow query into a failed request, and one shorter than it converts the same contention into a wait. A batch that finishes in 30 ms is under every sensible timeout; a single statement that runs for twenty minutes is over all of them. The sleep between batches is not decoration either: it lets the replica drain its relay log, lets the redo log checkpoint, and stops the migration monopolising disk bandwidth.

When the value comes from another table, the update needs a join

Backfills usually derive a value rather than compute it, and the source is a lookup table. Writing that as a correlated subquery in MySQL fails when the subquery selects from the table being updated:

UPDATE employees
   SET full_name = (SELECT m.first_name FROM employees m
                     WHERE m.id = employees.manager_id);

ERROR 1093 (HY000): You can't specify target table 'employees' for update in FROM clause

The reason is that the row being written and the row being read are the same table, and a subquery that reads the target of an UPDATE is rejected before it runs. Name the table with an alias and the error names the alias instead (You can't specify target table 'e' for update in FROM clause). The self-referential case needs a copy of the table in the FROM clause:

UPDATE employees e
  JOIN (SELECT id, first_name FROM employees) m ON m.id = e.manager_id
   SET e.full_name = CONCAT(e.first_name, ' ', e.last_name, ' (manager: ', m.first_name, ')');

Reading from a different table is the common case, and there it is an ordinary UPDATE ... JOIN in MySQL. The join goes before the SET, and it can fill several columns from one pass over the reference table:

UPDATE accounts a
  JOIN plans p ON p.code = a.plan_code
   SET a.plan_name = p.name,
       a.plan_tier = p.tier
 WHERE a.plan_name IS NULL;

On 2,000 rows that updated 2,000 rows and produced one lookup per account. SQLite and PostgreSQL spell this UPDATE ... FROM, and SQLite has accepted it since 3.33.0, where the changelog describes it as following the PostgreSQL syntax:

UPDATE accounts
   SET plan_name = p.name,
       plan_tier = p.tier
  FROM plans p
 WHERE p.code = accounts.plan_code
   AND accounts.plan_name IS NULL;

Both forms have the same trap, and PostgreSQL's manual states it plainly: make sure the join produces at most one row for each row you update, because when it does not, which matching row supplies the value is not predictable. A lookup table with a unique key on code is safe; a join to an events table is not. Derive one row per target first, then join to that.

One more difference matters when you copy a loop between engines. MySQL's single-table UPDATE accepts ORDER BY and LIMIT; the multi-table form does not, and neither does SQLite's. An UPDATE ... LIMIT on the SQLite 3.51.0 CLI in this lab failed with OperationalError: near "LIMIT": syntax error. There the bound comes from the key range instead: WHERE id > ? AND id <= ?, which walks the primary key in rowid order.

The backfill runs while both the old and the new column are live

A backfill is never finished at the moment you think, because the application is writing while it runs. Consider a rename from is_active to status. You add status as NULL and deploy code that writes both columns. The old build is still serving traffic, and it knows nothing about status, so it writes rows that leave the new column NULL.

ALTER TABLE settings ADD COLUMN status VARCHAR(16) NULL;

-- the old build, still running, does this:
UPDATE settings SET is_active = 0 WHERE name = 'd';

-- how much has it left behind?
SELECT COUNT(*) AS unfinished FROM settings WHERE status IS NULL;

In the walkthrough for this lesson, that count was all four rows. The backfill ran, the count went to zero, and the new build's write path kept it there:

-- the new build writes both
UPDATE settings SET is_active = 1, status = 'active' WHERE name = 'c';

Three deploys make this safe. Before the first, add the column nullable, so the old build keeps working. In it, change every write path to set both columns, then run the backfill, which reads the old column and touches only rows where the new one is NULL. During the window every write path must maintain both, or the backfill is chasing a moving target. A trigger is the alternative when you cannot change the write paths in time:

CREATE TRIGGER t2_sync BEFORE UPDATE ON t2
FOR EACH ROW SET NEW.status = COALESCE(NEW.status, IF(NEW.is_active = 1, 'active', 'closed'));

That trigger fires on the old build's updates too, which is the point: it translates a write the old code does not know about into the new column. It costs a row-level trigger on every write while it exists.

Completion is a predicate, and the drop waits for the deploy after next

"Is it done" is a query, not a feeling, and it has to be the same predicate the loop used:

SELECT COUNT(*) AS unfinished FROM settings WHERE status IS NULL;
-- unfinished
--          0

Zero means the backfill caught every row that existed. It does not mean the backfill is finished, because the old build may still be writing. The completion you can act on is zero on a re-check after the last old-build instance is gone, plus an assertion in the new code that no write path leaves the new column NULL. A backfill that reports zero while an old writer is live reports one row again in an hour.

Only then does the drop belong in a release. The reason is the same deploy-ordering problem the previous lesson measured: the schema is shared by every build currently running, and the old build still names the old column in its queries. Dropping it while that build is up produces:

SELECT id FROM settings WHERE is_active = 1;
ERROR 1054 (42S22): Unknown column 'is_active' in 'where clause'

INSERT INTO settings (name, is_active) VALUES ('e', 1);
ERROR 1054 (42S22): Unknown column 'is_active' in 'field list'

Both are application errors, not database errors. The column is gone and the queries that mention it are now invalid. So the sequence is: add nullable, write both, backfill, verify zero, drop the old column in the release after the last build that reads it stopped reading it. The safest version of that last step is a dated migration naming the release that removed the code, so the person who runs it six weeks later does not have to reconstruct the story.

Show the solution and the reasoning

On the machine this lesson was measured on, the unbounded update took 958 to 1,121 ms and the writer behind it waited 6,824 ms. The batch loop at 2,000 took 2,045 ms in total and its slowest batch was 130 ms; at 500 the total was 1,260 ms and the slowest batch was 21 ms. Ship the smaller batch when the table is read and written constantly and the total time does not matter, which is the usual case for an online backfill. Move up only when you have measured that the loop cannot finish inside its window.

If you ran it on SQLite, the numbers will look wrong in a useful way. One statement over 50,000 rows took 53 ms, a batch of 2,000 took 673 ms and a batch of 200 took 6,142 ms, so the unbounded statement was both the fastest and the least disruptive, because the whole job was shorter than the interval between two of your sleeps. Batching is a response to a lock hold long enough to be visible, and the way to know is the second-session probe, not the row count.

The completion query is SELECT COUNT(*) FROM accounts WHERE plan_name IS NULL returning zero, and the join that fills the columns is UPDATE accounts a JOIN plans p ON p.code = a.plan_code SET a.plan_name = p.name, a.plan_tier = p.tier WHERE a.plan_name IS NULL.

Dropping plan_code needs three things beyond a zero count. Every write path must write the new columns, every read path must read them, and every running instance must be a build that does both. The third is the one that bites, because it is a fact about your deployment rather than your database, and the only reliable way to establish it is to wait until the release that changed the code is fully rolled out and then wait one more release before dropping. The count proves the past; the deploy history proves the present.