Backfilling a column without locking the table

Adding a nullable column is instant in 8.0; filling it for four million existing rows is not.

-- instant, metadata only
ALTER TABLE orders ADD COLUMN region VARCHAR(2) NULL,
  ALGORITHM=INSTANT;

-- NOT instant: a default on an existing column, in older
-- versions, rewrote the table. 8.0.29+ handles it.

-- the backfill is a separate, batched job
-- and the NOT NULL comes last, once no nulls remain
ALTER TABLE orders MODIFY region VARCHAR(2) NOT NULL,
  ALGORITHM=INPLACE, LOCK=NONE;

Three steps instead of one — add nullable, backfill, tighten — is the pattern for any column addition on a large table, and each step is individually safe to abandon. The final MODIFY needs every row populated or it fails after doing the work, so a count of remaining nulls is the gate. Writing ALGORITHM and LOCK explicitly makes the statement fail rather than silently take a lock.