Zero-downtime column rename under 400 writes a second

In MySQL 8, RENAME COLUMN is theoretically an instantaneous metadata-only operation with ALGORITHM=INPLACE. In practice, running it against a table handling four hundred writes a second during business hours is the fastest way to induce a total application outage through metadata lock starvation. Here is how we executed a rename on a sixty-million-row orders table without dropping a single write.

The symptom

mysql> ALTER TABLE orders RENAME COLUMN customer_reference TO account_uuid;
-- Query starts...
-- 10 seconds later:
mysql> SHOW PROCESSLIST;
| 412 | app_user | ... | Query   | 10 | Waiting for table metadata lock | ALTER TABLE orders...
| 413 | app_user | ... | Query   | 9  | Waiting for table metadata lock | INSERT INTO orders...
| 414 | app_user | ... | Query   | 9  | Waiting for table metadata lock | SELECT * FROM orders...
-- Max connections (1500) reached in 18 seconds. All web workers blocked.

An innocent RENAME COLUMN only takes milliseconds to alter the data dictionary, but it requires an exclusive metadata lock (MDL). If a single analytical query or transaction is reading the table when the DDL arrives, the DDL queues behind it. Worse: all subsequent reads and writes queue behind the DDL request. Within twenty seconds, the application pool exhausted its connection limit.

Why it happens

MySQL’s metadata lock queue prioritizes write locks over read locks. Once an exclusive lock is requested, no new shared read locks are granted until the exclusive lock finishes. If an existing read holds a shared lock for four seconds, all incoming application queries freeze behind the pending DDL. At four hundred requests a second, a four-second freeze creates a backlog of sixteen hundred blocked processes.

The fix

The four-phase expand and contract rollout

-- Phase 1 (Deploy 1): Add new column without dropping the old one.
-- In MySQL 8, adding a nullable column at the end is instant.
ALTER TABLE orders ADD COLUMN account_uuid VARCHAR(64) NULL;

Phase 2: Deploy application code that performs dual-writes. The application writes to both customer_reference and account_uuid, but continues reading exclusively from the old column. All new records have both fields populated.

// Phase 2 application repository write:
public function save(Order $order): void
{
    $this->db->execute(
        'INSERT INTO orders (id, customer_reference, account_uuid, amount) 
         VALUES (:id, :ref, :ref, :amount)',
        [
            'id' => $order->id,
            'ref' => $order->accountReference,
            'amount' => $order->amount
        ]
    );
}

Phase 3: Backfill historical rows in deterministic batches using primary key keyset pagination rather than OFFSET. Batches of five thousand rows are updated with a five-millisecond sleep between chunks to ensure replication lag remains under one second.

# Phase 3 backfill runner
./bin/console db:backfill-column 
    --table=orders 
    --source=customer_reference 
    --target=account_uuid 
    --batch-size=5000 
    --sleep-ms=5

Phase 4: Deploy code reading from account_uuid. Once verified, deploy code stopping writes to customer_reference. Finally, drop the old column during a low-traffic maintenance window.

Verifying it worked

$ ./bin/replication-lag --replica=db-replica-01
  Lag: 0.04s (Target: < 1.0s)

$ ./bin/db-error-rate --since=4h
  Database connection timeouts: 0
  Metadata lock wait timeouts:  0
  Rows backfilled:              61,420,184 / 61,420,184 (100%)

Zero lock wait timeouts were recorded throughout the backfill. The entire sixty-million-row migration ran during active production hours without customer degradation.

What this costs

Three separate releases across two weeks instead of one instantaneous command. Expanding and contracting requires coordination across application repositories, backfill scripts, and deployment runbooks.

And temporary disk overhead. Storing the reference twice across sixty million rows added approximately 4.8GB of disk usage during the transition window. An acceptable operational price for uninterrupted 24/7 uptime.