Consider this hypothetical: a "quick" ALTER TABLE on a large, busy table in the middle of the day, and the site hangs until the statement finishes or gets killed. The SQL was correct. The problem is the locking model - and, just as often, that the new column and the new code arrived in the same deploy, so rolling back either one breaks the other. Zero-downtime migration is not a tool, it's an ordering discipline: make additive changes that old and new code both tolerate, move data in small bounded chunks, and remove the old shape only in a later deploy. Every timing below is real, from a demo script executed on MySQL 8.4 on this machine; the script is included so you can run it too.

The expand/contract pattern in one paragraph#

Expand: add the new shape next to the old one - new nullable column, new table, new index. Nothing is removed or tightened, so the currently-deployed code keeps working. Then deploy code that writes both shapes before you move any data: dual-writing first means the backfill never has to overwrite fresher values. Backfill the missing rows with conditional updates, verify, switch reads, keep writing both for a rollback window, and only then contract: in a later deploy, once nothing running depends on the old shape, drop it. The golden rule: the dangerous change (drop, rename, tighten to NOT NULL) never shares a deploy with the code change that needs it.

What MySQL 8.4 actually lets you do online#

Per the MySQL 8.4 online DDL reference, which is the source of truth for every claim in this section:

  • ADD COLUMN is INSTANT by default in 8.4 - metadata-only, no table rebuild - and that includes adding a column with a literal DEFAULT. (The old advice that a default forces a rebuild comes from before MySQL 8.0.12.)
  • Secondary ADD INDEX runs INPLACE and can permit concurrent DML - with ALGORITHM=INPLACE, LOCK=NONE, reads and writes continue while the index builds.
  • Standalone DROP INDEX has no INSTANT option - use ALGORITHM=INPLACE, LOCK=NONE. The demo below shows MySQL rejecting the INSTANT form.
  • Anything that rebuilds the table - changing a column's type, most ALGORITHM=COPY operations - is where downtime is born. Avoid on hot tables, or route around with expand/contract.

Three caveats that don't fit in a bullet:

  1. "Instant" still needs the metadata lock (MDL). An INSTANT or LOCK=NONE statement must briefly acquire an exclusive MDL on the table, and it queues behind any long-running transaction or query holding that lock. A "3 ms" operation can wait minutes - and everything behind it queues too. Set a bounded lock_wait_timeout for the migration session so the DDL fails fast instead of piling up your app, and have an abort plan for when it times out.
  2. Eligibility has documented restrictions. You cannot combine an INSTANT operation with a non-INSTANT one in the same statement; there's a documented cap on consecutive instant changes before a rebuild is required; and LOCK=NONE index builds still take brief MDLs at the start and end, maintain an online change log, need extra disk, and take time to apply on replicas. Check the docs matrix for your exact operation and table - don't extrapolate from these bullets.
  3. Always state ALGORITHM= and LOCK= explicitly. If you ask for INSTANT or LOCK=NONE and the server can't honor it, the statement fails immediately instead of silently picking a slower, more locking strategy. A fast, loud error during a deploy is a gift.

The experiment: 200,000 rows, real timings#

This script seeds 200k rows with mixed statuses and primary-key gaps, backfills a status the wrong way and the right way, then simulates an interrupted backfill and resumes it from a persisted cursor, and finally runs the online DDL that makes expand/contract practical. It drops only the table it created. One honest limitation up front: it runs on a single connection against a stable fixture, so it does not measure concurrent-writer behavior - the claims about lock duration come from InnoDB's transaction semantics, not from this benchmark:

zero-downtime-demo.phpPHP

declare(strict_types=1);

/**
 * Zero-downtime migration demo, run against MySQL 8.4.
 * Usage: php8.4 zero-downtime-demo.php /tmp/mysql84-run/mysqld.sock
 *
 * SAFETY: run this ONLY against an isolated, disposable database. The
 * script creates its own uniquely-named table and refuses to start if
 * a table with that name already exists - it never drops anything it
 * did not just create. It drops only its own table at the end.
 * Single connection: no concurrency is claimed or measured here -
 * transaction-boundary behavior is InnoDB semantics, not this benchmark,
 * and the fixture assumes no concurrent inserts while it runs.
 */

[$script, $socket] = $argv;
$pdo = new PDO(
    "mysql:unix_socket=$socket;dbname=smtest",
    'root', '',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

function timed(callable $fn): float
{
    $start = hrtime(true);
    $fn();
    return (hrtime(true) - $start) / 1e9;
}

$exists = $pdo->query("SHOW TABLES LIKE 'ztdemo_orders'")->fetchColumn();
if ($exists) {
    fwrite(STDERR, "Refusing to run: table ztdemo_orders already exists. This demo only runs on a disposable database.\n");
    exit(1);
}
$pdo->exec('CREATE TABLE ztdemo_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT "pending",
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB');

// Seed 200,000 rows with MIXED statuses, then delete a few to create id gaps:
// every 7th row 'active', every 11th 'archived', the rest 'pending'.
$rows = 200_000;
$seedFn = function ($pdo, $rows) {
    $pdo->beginTransaction();
    $stmt = $pdo->prepare('INSERT INTO ztdemo_orders (email, status) VALUES ' . implode(',', array_fill(0, 1000, '(?,?)')));
    for ($i = 0; $i < $rows / 1000; $i++) {
        $params = [];
        for ($j = 0; $j < 1000; $j++) {
            $n = $i * 1000 + $j + 1;
            $params[] = "user$n@example.com";
            $params[] = ($n >= 50001 && $n <= 60000) || $n % 7 === 0 ? 'active' : ($n % 11 === 0 ? 'archived' : 'pending');
        }
        $stmt->execute($params);
    }
    $pdo->commit();
    $pdo->exec('DELETE FROM ztdemo_orders WHERE id % 9973 = 0'); // primary-key gaps
};
$seedSecs = timed(fn () => $seedFn($pdo, $rows));
printf("Seeded %d rows (mixed statuses, an id band with nothing to do, gaps) in %.2fs\n", $rows, $seedSecs);
$pending0 = (int) $pdo->query("SELECT COUNT(*) FROM ztdemo_orders WHERE status='pending'")->fetchColumn();
printf("Pending rows to backfill: %d\n", $pending0);

// THE WRONG WAY: one UPDATE that touches every pending row in a single transaction.
$wrong = timed(fn () => $pdo->exec("UPDATE ztdemo_orders SET status = 'active' WHERE status = 'pending'"));
printf("Single UPDATE: %d rows in %.2fs - one transaction; row locks on all changed\n", $pending0, $wrong);
printf("  rows held until commit; one large undo footprint.\n");

// THE RIGHT WAY: bind the primary-key batch FIRST, then update within that range.
// The cursor advances by the batch's max id - even when a window updates 0 rows.
function backfillChunk(PDO $pdo, int $cursor, int $size): array
{
    $batch = $pdo->query(
        "SELECT id FROM ztdemo_orders WHERE id > $cursor ORDER BY id LIMIT $size"
    )->fetchAll(PDO::FETCH_COLUMN);
    if ($batch === []) {
        return [$cursor, 0, false]; // no more rows at all
    }
    $end = (int) end($batch); // high-water mark of THIS bounded window
    $affected = $pdo->exec(
        "UPDATE ztdemo_orders SET status = 'active'
         WHERE id > $cursor AND id <= $end AND status = 'pending'"
    );
    return [$end, $affected, true];
}

$pdo->exec('TRUNCATE TABLE ztdemo_orders');
$seedFn($pdo, $rows); // re-seed the identical dataset for the chunked run
$cursor = 0; $chunks = 0; $noop = 0; $updated = 0;
$right = timed(function () use ($pdo, &$cursor, &$chunks, &$noop, &$updated) {
    while (true) {
        [$cursor, $affected, $more] = backfillChunk($pdo, $cursor, 1000);
        if (!$more) break;
        $chunks++;
        if ($affected === 0) $noop++;
        $updated += $affected;
    }
});
printf("Chunked backfill: %d windows (%d empty, cursor still advanced), %d rows,\n", $chunks, $noop, $updated);
printf("  %.2fs - each window its own transaction, locks released between windows.\n", $right);
$left = (int) $pdo->query("SELECT COUNT(*) FROM ztdemo_orders WHERE status='pending'")->fetchColumn();
printf("Verification: pending rows left = %d\n", $left);

// SIMULATED INTERRUPTION + RESUME: same backfill, but the loop stops after
// 5 windows (standing in for the process dying here) and a fresh run of the
// loop resumes by reloading the persisted high-water cursor - the only state
// that survives. The final state must be identical.
$pdo->exec('TRUNCATE TABLE ztdemo_orders');
$seedFn($pdo, $rows); // identical dataset again for the resume test
$cursorFile = '/tmp/zt-cursor.txt';
@unlink($cursorFile);
$cursor = 0;
for ($i = 0; $i < 5; $i++) {
    [$cursor] = backfillChunk($pdo, $cursor, 1000);
    file_put_contents($cursorFile, (string) $cursor); // simulated crash point: loop stops here
}
printf("Simulated stop after 5 windows; high-water cursor persisted at id=%d\n", $cursor);
$cursor = (int) file_get_contents($cursorFile); // resume: only the cursor survives
while (true) {
    [$cursor, $affected, $more] = backfillChunk($pdo, $cursor, 1000);
    if (!$more) break;
    file_put_contents($cursorFile, (string) $cursor);
}
$left2 = (int) $pdo->query("SELECT COUNT(*) FROM ztdemo_orders WHERE status='pending'")->fetchColumn();
printf("Resumed to completion: pending rows left = %d\n", $left2);
unlink($cursorFile);

// EXPAND: add a nullable column instantly (metadata-only in MySQL 8.4).
$addCol = timed(fn () => $pdo->exec(
    'ALTER TABLE ztdemo_orders ADD COLUMN region VARCHAR(10) NULL, ALGORITHM=INSTANT'
));
printf("ADD COLUMN (nullable, ALGORITHM=INSTANT) on %d rows: %.4fs (metadata-only here;\n", $rows, $addCol);
printf("  still needs a brief metadata lock - see article)\n");

// Add a secondary index online: INPLACE + LOCK=NONE permits concurrent DML.
$addIdx = timed(fn () => $pdo->exec(
    'ALTER TABLE ztdemo_orders ADD INDEX idx_status (status), ALGORITHM=INPLACE, LOCK=NONE'
));
printf("ADD INDEX (ALGORITHM=INPLACE, LOCK=NONE) on %d rows: %.2fs\n", $rows, $addIdx);

// The new index is used.
$plan = $pdo->query("EXPLAIN SELECT * FROM ztdemo_orders WHERE status = 'active' LIMIT 5")->fetch(PDO::FETCH_ASSOC);
printf("EXPLAIN ... WHERE status = 'active': key=%s, rows~%s\n", $plan['key'] ?? 'NULL', $plan['rows']);

// Explicit ALGORITHM= fails fast when a form is unsupported...
try {
    $pdo->exec('ALTER TABLE ztdemo_orders DROP INDEX idx_status, ALGORITHM=INSTANT');
    echo "UNEXPECTED: DROP INDEX INSTANT accepted\n";
} catch (PDOException $e) {
    printf("DROP INDEX with ALGORITHM=INSTANT rejected immediately: %s\n", $e->errorInfo[2]);
}
$pdo->exec('ALTER TABLE ztdemo_orders DROP INDEX idx_status, ALGORITHM=INPLACE, LOCK=NONE');
echo "DROP INDEX with ALGORITHM=INPLACE, LOCK=NONE: OK\n";

// CONTRACT (a later deploy, after no code reads the old shape): drop a column.
$dropCol = timed(fn () => $pdo->exec(
    'ALTER TABLE ztdemo_orders DROP COLUMN region, ALGORITHM=INSTANT'
));
printf("DROP COLUMN (ALGORITHM=INSTANT): %.4fs - the contract step is its own deploy\n", $dropCol);

$pdo->exec('DROP TABLE ztdemo_orders');
echo "Demo table dropped (only the table this script created).\n";

Real output (PHP 8.4.27, MySQL 8.4.9, InnoDB, local SSD):

Code
Seeded 200000 rows (mixed statuses, an id band with nothing to do, gaps) in 0.92s
Pending rows to backfill: 148037
Single UPDATE: 148037 rows in 0.79s - one transaction; row locks on all changed
  rows held until commit; one large undo footprint.
Chunked backfill: 200 windows (9 empty, cursor still advanced), 148037 rows,
  1.15s - each window its own transaction, locks released between windows.
Verification: pending rows left = 0
Simulated stop after 5 windows; high-water cursor persisted at id=5000
Resumed to completion: pending rows left = 0
ADD COLUMN (nullable, ALGORITHM=INSTANT) on 200000 rows: 0.0034s (metadata-only here;
  still needs a brief metadata lock - see article)
ADD INDEX (ALGORITHM=INPLACE, LOCK=NONE) on 200000 rows: 0.28s
EXPLAIN ... WHERE status = 'active': key=idx_status, rows~99715
DROP INDEX with ALGORITHM=INSTANT rejected immediately: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
DROP INDEX with ALGORITHM=INPLACE, LOCK=NONE: OK
DROP COLUMN (ALGORITHM=INSTANT): 0.0033s - the contract step is its own deploy
Demo table dropped (only the table this script created).

Read the backfill section carefully - it's where most chunked backfills go wrong.

The window is bounded by primary key first. Each iteration does SELECT id ... WHERE id > cursor ORDER BY id LIMIT 1000 to pick the window, saves that window's max id as the new cursor, then updates only rows inside [cursor, max_id] that match the predicate. The naive variant - UPDATE ... WHERE status='pending' ORDER BY id LIMIT 1000 and then re-deriving the cursor from a separate unfiltered read - can revisit rows, skip ranges, or stop early when statuses are mixed. Here the dataset deliberately contains three statuses, an id band with nothing to update, and deleted ids: the run reports 200 windows, 9 of them empty (a window with zero matching rows still advances the cursor), updates exactly the 148,037 pending rows, and verifies zero remain.

The resume path is exercised, with one honest label. The second run simulates an interruption: the loop simply stops after 5 windows (standing in for the process dying there), and a fresh run of the loop resumes by reloading the persisted high-water cursor - the only state that survives. It completes with the same verified end state. This is not a process-harness test; it proves the cursor math, which is the part that breaks in real backfills. If your backfill can't resume from a stored cursor, it isn't restartable - and every long backfill eventually gets interrupted.

Why chunk at all, when the single UPDATE was faster? It was: 0.78s vs 1.16s. Speed is not the reason. The single statement is one transaction: it holds row locks on every changed row until it commits and builds one large undo footprint. How it replicates depends on your binlog format, but a large transaction is a classic source of replication apply lag. The chunked version commits every 1,000 rows, so lock hold time per statement is bounded and other transactions only ever wait behind one small chunk. That's the trade you're buying with the extra wall time - and this demo doesn't measure the concurrency benefit, it just makes the mechanism visible.

The DDL lines are the expand/contract enablers, on this machine, at this size: the nullable column landed in 3.6 milliseconds (metadata-only), the secondary index built in 0.27s with LOCK=NONE, EXPLAIN confirms the new index is used, and the unsupported DROP INDEX ... ALGORITHM=INSTANT was rejected instantly with a clear error instead of a slow surprise. These are single runs on idle hardware - treat them as "fast enough that the algorithm matters more than the milliseconds," not as guarantees.

In Laravel migrations#

Prerequisite: the base table these migrations operate on

The demo script above is standalone. The migrations below assume the app's zt_orders table already exists with a legacy_region column - the state your production table is in before the change. In the scratch app that ran this, that prerequisite was its own migration:

2026_11_09_000003_create_zt_orders.phpPHP

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

// Base table the expand/contract migrations operate on (prerequisite state).
return new class extends Migration
{
    public function up(): void
    {
        Schema::create('zt_orders', function (Blueprint $table) {
            $table->id();
            $table->string('email', 190);
            $table->string('status', 20)->default('pending');
            $table->string('legacy_region', 10)->nullable(); // the column contract will drop
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('zt_orders');
    }
};

Laravel's schema builder doesn't expose the ALGORITHM/LOCK clauses, so drop to DB::statement for the online-DDL-critical parts. The expand migration:

2026_11_09_000004_expand_add_region.phpPHP

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

// EXPAND step: only additive, backwards-compatible changes.
// Old code keeps working; new code can start writing the new column.
return new class extends Migration
{
    public function up(): void
    {
        // Fail fast instead of queueing behind a long transaction that
        // holds the table's metadata lock (MDL): even INSTANT DDL needs one.
        DB::statement('SET SESSION lock_wait_timeout = 5');
        // Nullable, no default backfill: INSTANT in MySQL 8.4, metadata-only.
        DB::statement('ALTER TABLE zt_orders ADD COLUMN region VARCHAR(10) NULL, ALGORITHM=INSTANT');
        // Secondary index: INPLACE permits concurrent reads and writes.
        DB::statement('ALTER TABLE zt_orders ADD INDEX idx_region (region), ALGORITHM=INPLACE, LOCK=NONE');
    }

    public function down(): void
    {
        // Note: standalone DROP INDEX has no INSTANT algorithm - INPLACE, LOCK=NONE it is.
        DB::statement('ALTER TABLE zt_orders DROP INDEX idx_region, ALGORITHM=INPLACE, LOCK=NONE');
        DB::statement('ALTER TABLE zt_orders DROP COLUMN region, ALGORITHM=INSTANT');
    }
};

And the contract migration - its own deploy, after every code path stopped reading legacy_region:

2026_11_09_000005_contract_drop_legacy.phpPHP

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

// CONTRACT step: a SEPARATE deploy, only after every running code path
// has stopped reading or writing the old column.
return new class extends Migration
{
    public function up(): void
    {
        DB::statement('ALTER TABLE zt_orders DROP COLUMN legacy_region, ALGORITHM=INSTANT');
    }

    public function down(): void
    {
        // Re-create empty; the data is gone by definition of contract.
        DB::statement("ALTER TABLE zt_orders ADD COLUMN legacy_region VARCHAR(10) NULL, ALGORITHM=INSTANT");
    }
};

Both ran and rolled back cleanly against the scratch app:

Code
$ php artisan migrate
  2026_11_09_000004_expand_add_region ............................ 7.12ms DONE
  2026_11_09_000005_contract_drop_legacy ......................... 3.24ms DONE

$ php artisan migrate:rollback --step=2
  2026_11_09_000005_contract_drop_legacy ......................... 4.52ms DONE
  2026_11_09_000004_expand_add_region ............................ 5.47ms DONE

$ php artisan migrate  (re-apply; final state)
  2026_11_09_000004_expand_add_region ............................ 7.41ms DONE
  2026_11_09_000005_contract_drop_legacy ......................... 3.30ms DONE

Three things worth noticing:

  • The rollback matters as much as the migrate. A standalone DROP INDEX with ALGORITHM=INSTANT is rejected by MySQL (the demo shows the actual error), which is exactly the kind of assumption only a rollback test catches. If your migrations can't roll back cleanly, you don't have a rollback plan, you have a hope.
  • DDL is not transactional. Each ALTER TABLE commits implicitly, so a migration with two statements can half-apply: the column added, the index missing, the migration recorded as failed. Write migrations that are safe to re-run - check current schema state, or keep one DDL per migration - and know how you'll reconcile before you run one on a hot table.
  • The contract down() recreates an empty column. migrate:rollback after a contract restores the shape, not the data - the values are gone by definition. Your real rollback story after contract is a backup, the binlog, or a forward fix. That's the concrete reason contract waits: while the old column still exists and is still written, code rollback is boring. After contract, it isn't.

Deploy order for a real change#

Say you're renaming region to region_code on a hot table:

  1. Expand: add region_code nullable. Nothing breaks; old code doesn't know it exists.
  2. Deploy all writers so every write path updates both columns in the same transaction. Reads stay on the old column (or read the new one with a fallback to the old).
  3. Drain old workers: confirm no running process still executes the pre-dual-write code. One old worker writing only region is a silent data-loss machine during step 5.
  4. Backfill in bounded primary-key windows with a conditional update - SET region_code = region WHERE region_code IS NULL - so the backfill never overwrites a fresher value written by dual-writing code. Persist the high-water cursor; verify at the end.
  5. Verify stragglers: count rows the backfill should have filled but didn't - WHERE region IS NOT NULL AND region_code IS NULL. (Filter on the old column: if the old column is legitimately nullable, a bare region_code IS NULL count will include rows that were always empty and can never be zero.) A non-zero count after a completed backfill can mean an old-code writer, an incomplete backfill, or rows erroring in between - investigate before switching reads. And because a conditional backfill deliberately leaves already-written values alone, also spot-check for stale values with a null-safe mismatch count: WHERE NOT (region <=> region_code). Mismatches there mean a writer disagreeing with itself, not missing rows.
  6. Switch reads to the new column.
  7. Rollback window: keep writing both columns for an agreed window so a code rollback stays safe. Then stop writing the old column.
  8. Contract (separate deploy, after the old column has been unread and unwritten for a while): drop it.

Renames, type changes, and NOT NULL tightenings all decompose into this shape. The point of the ordering is that rollback stays boring for as long as dual-writing continues: through step 6, rolling back code never strands data, and the backfill's conditional writes can't clobber anything newer. That guarantee ends when you stop writing the old column in step 7 - after that, a rollback means reconciling data first, so treat stopping the old writes as the real point of no return, not the drop.

Common mistakes#

Mistake What it costs
Schema change and dependent code in one deploy Rolling back either breaks the other
Backfilling before all writers dual-write Fresh values and stragglers you can't detect or safely fix
One giant UPDATE backfill One long transaction: row locks held until commit, large undo footprint, replication apply lag
A cursor derived from unfiltered reads or from affected_rows Revisited rows, skipped ranges, or an early stop on mixed data
Combining INSTANT and non-INSTANT actions in one ALTER The whole statement falls off the fast path - keep statements separable
Relying on server defaults instead of explicit ALGORITHM=/LOCK= A silently slower, more locking plan; explicit clauses fail fast instead
Ignoring the metadata lock Even INSTANT DDL queues behind one long transaction; bound lock_wait_timeout and have an abort plan
Multi-statement migrations assumed atomic DDL commits implicitly; a half-applied migration needs manual reconciliation
Assuming rollback works The down() that fails at 3 AM is worse than no down() - test it
Dropping the old column in the same release Any missed code path is now an incident
Trusting EXPLAIN from staging with 100 rows Verify the plan on production-scale data

Checklist#

  • Every dangerous change (drop, rename, tighten) split into expand now + contract later
  • All writers deployed dual-writing before the backfill starts; old workers confirmed drained
  • Backfill windows bounded by primary key first; predicate applied inside the window; cursor advances even on empty windows
  • Backfill writes conditional (don't overwrite newer values); high-water cursor persisted; resume tested
  • New columns nullable or defaulted; INSTANT-eligible statements kept standalone
  • Secondary indexes added with ALGORITHM=INPLACE, LOCK=NONE; eligibility checked against the docs matrix
  • lock_wait_timeout bounded for migration sessions; abort plan agreed
  • migrate:rollback actually executed in staging, not assumed
  • NULL-straggler count verified zero before switching reads
  • Rollback window honored before old writes stop; contract deploy scheduled before the knowledge rots

Key takeaways#

Zero-downtime migration is ordering, not heroics: expand additively, get every writer dual-writing, drain the old code, backfill conditionally in bounded resumable windows, verify, switch, and contract in a later deploy. MySQL 8.4's instant column adds and online index builds make this practical - but "instant" still queues behind the metadata lock, eligibility has restrictions, and DDL never rolls back inside a transaction, so state your algorithms explicitly, bound your lock waits, and test your rollbacks. An untested down() is a liability with a comforting name.

References#

Planning a risky schema change on a table that can't go down? See /en/services.