Backfill a new column in the migration, in a batched job or on write

Monday's release adds tenant_id to payment_attempts. The table holds 40 million rows, the column must end up NOT NULL, and every value can be read off the parent row in payments. The same release adds normalised_hash to customer_addresses, 6 million rows, and that value only exists once an address validation service has been called for the row. Both tickets say "add a required column", and they need two different backfills.

This is the choice in front of any engineer adding a required column to a table that is written to while the migration runs. My rule: where the value comes from decides the method, the table's size only chooses between the first two methods, and the NOT NULL constraint is always its own later step, never part of the fill. I have moved a product from single-tenant to multi-tenant SaaS, and that rule is the one I apply to every backfill.

What decides it

Fill the column inside the migration with one UPDATE after the ALTER; add it nullable and run a job that fills it in batches; or add it nullable and fill each row the next time the application writes it. The criteria below are the ones that move between them.

The first row settles most cases on its own. If the value needs a call to a validation service, a gateway or a registry, no SQL statement can produce it, so the migration and the batched job are out before size is considered. If the value is a join, a copy or a constant, size decides between the first two: an UPDATE over 1,200 rows of gateway_configs finishes before anyone notices the lock, and the same statement over 40 million rows of payment_attempts holds row locks for minutes and replays on every replica as one transaction.

The backfill that finished with nulls in it

Here is the batched job going wrong in the order it goes wrong. At 09:00 the migration adds tenant_id as a nullable column, and at 09:05 the job starts: it records max(id) as 40,000,000 and begins walking upward in batches of 2,000. The application release that sets tenant_id on every new insert is queued behind the schema release in the pipeline, and it reaches production at 11:30. Between 09:05 and 11:30 checkout inserts around 18,000 attempts, all with ids above 40,000,000 and tenant_id null, because the code writing them does not know the column exists yet.

At 13:40 the job reaches its recorded end, writes done to its progress row, and the dashboard shows the backfill complete. At 14:00 the next migration runs ALTER TABLE payment_attempts MODIFY tenant_id BIGINT NOT NULL and fails: the column contains nulls. The pipeline is red, and the quick fix someone reaches for is DEFAULT 0 so the ALTER passes. Now 18,000 attempts belong to a tenant that does not exist. Nobody sees it that afternoon; the tenant sees it at month end, when their attempts report is 18,000 short of the gateway's count.

Two things contain this. The write path goes live before the first batch runs, so every row inserted during the job already carries its value and the recorded max(id) is a safe end. And the NOT NULL step is gated on a sweep, SELECT count(*) FROM payment_attempts WHERE tenant_id IS NULL, that must return zero on two consecutive runs before the constraint migration may execute.

Running the batched job

The job walks by primary key range rather than by LIMIT and OFFSET, commits one batch per transaction, persists its cursor so a restart resumes rather than repeats, and waits when the replicas fall behind.

// Simplified: batched backfill of payment_attempts.tenant_id
const BATCH = 2000;
const MAX_LAG_SECONDS = 5;

async function backfillTenantId(db: Db, replica: Db) {
  const name = 'payment_attempts.tenant_id';
  let cursor = await db.scalar<number>(
    'select cursor from backfill_progress where name = ?', [name]);
  const end = await db.scalar<number>('select max(id) from payment_attempts');

  while (cursor < end) {
    const next = cursor + BATCH;
    await db.exec(
      `update payment_attempts a
         join payments p on p.id = a.payment_id
          set a.tenant_id = p.tenant_id
        where a.id > ? and a.id <= ? and a.tenant_id is null`,
      [cursor, next]);
    cursor = next;
    await db.exec(
      'update backfill_progress set cursor = ? where name = ?', [cursor, name]);
    while ((await replica.lagSeconds()) > MAX_LAG_SECONDS) await sleep(1000);
  }
}

The tenant_id is null predicate makes every batch safe to repeat, which is what lets the cursor be persisted after the update rather than inside the same transaction. Batch size is a trade: 40 million rows at 2,000 a batch is 20,000 short transactions; raising the batch to 50,000 cuts that number but lengthens each lock, and the first customer to notice is the one whose capture waits behind it. The constraint stays a separate migration whichever method fills the column, because adding NOT NULL makes the database check every row and, on some engines, rebuild the table.

flowchart TD
  A{Value computable from rows} -->|no| W[Fill on write]
  A -->|yes| B{One statement locks too long}
  B -->|yes| J[Batched job]:::accent
  B -->|no| M[In the migration]
  M --> N[Add NOT NULL as own step]
  J --> S[Sweep until zero nulls]
  S --> N
  W --> K[Column stays nullable]

Whichever option wins, build the same three things first: the write path that populates the column for every new row, deployed before any fill begins; a backfill_progress table with a cursor per column so a job can resume and a reviewer can see where it is; and a null sweep that gates the NOT NULL migration. The method chooses how the old rows get their values. Those three pieces are what stop the new rows from losing theirs.