How do you execute Zero-Downtime database migrations on tables with 50+ million rows without locking writes in production Laravel?

Root Cause

Standard ALTER TABLE statements in MySQL can acquire exclusive metadata locks (MDL), blocking all incoming INSERT/UPDATE operations until the table rewrite finishes.

Safe Migration Patterns

1. Online DDL with Algorithm=INPLACE, Lock=NONE:

   ALTER TABLE orders ADD COLUMN fulfillment_status VARCHAR(30) DEFAULT 'pending',
   ALGORITHM=INPLACE, LOCK=NONE;
   

2. The Expand/Contract (Blue-Green Schema) Pattern:
   • Step 1 (Expand): Add the new column as NULLABLE (instant operation).
   • Step 2 (Dual-Write): Write to both old and new columns via model events or repository layer.
   • Step 3 (Backfill in Background Queues): Chunk existing 60M rows with a queue worker in batches of 5,000 using ID-based cursor pagination.
   • Step 4 (Switch Reads): Deploy code pointing all read queries to the new column.
   • Step 5 (Contract): Drop the old column after zero dependencies remain.
3. Use GitHub's gh-ost or Percona pt-online-schema-change: Creates an ghost shadow table, triggers/binlog replays changes, and performs an atomic table swap without holding locks.