Migration rollback strategies — down() method best practices
Concept
Zero-downtime migrations are essential for production systems where running migrations cannot lock tables for extended periods. On large tables (millions of rows), naive schema changes block reads and writes, causing downtime.
The locking problem: On MySQL with InnoDB, ALTER TABLE to add a column acquires a metadata lock. While the lock is held, all queries to the table queue up. On a 50M-row table, this can take minutes.
MySQL's online DDL: Modern MySQL/InnoDB supports some ALTER TABLE operations without full locks (ALGORITHM=INPLACE or ALGORITHM=INSTANT). Adding a nullable column is instant. Changing column type or adding a foreign key may still lock.
The safe pattern for large tables:
- Add the new column as
nullable(instant DDL). - Backfill existing rows in chunks (no lock).
- Deploy code that writes both old and new formats.
- Mark column
NOT NULLafter backfill completes. - Remove old column in a later migration.
Tools: pt-online-schema-change (Percona Toolkit) and gh-ost (GitHub) perform table alterations by creating a shadow copy and swapping — zero lock time. Used in high-traffic production environments.
dontTransactionAlterTable(): Not a Laravel method, but MySQL doesn't support transactions for DDL. Schema migrations are NOT transactional on MySQL. If a migration partially fails, you may be left with an inconsistent schema — always back up before running migrations in production.
Nullable → NOT NULL upgrade: Cannot be done safely without a data migration. The pattern: add nullable → backfill → verify all rows filled → then apply NOT NULL.
Code Example
<?php
// UNSAFE on large tables — may lock for minutes
return new class extends \Illuminate\Database\Migrations\Migration
{
public function up(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->string('new_reference', 50)->after('id'); // locks table!
});
}
};
// SAFE pattern — phased approach over multiple deploys
// Migration 1: Add nullable (instant DDL in MySQL 8+)
return new class extends \Illuminate\Database\Migrations\Migration
{
public function up(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->string('new_reference', 50)->nullable()->after('id'); // no lock
});
}
public function down(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->dropColumn('new_reference');
});
}
};
// Deploy + backfill as a separate command or job (runs in background, chunked):
// Order::whereNull('new_reference')->chunkById(1000, function($orders) {
// foreach ($orders as $order) {
// $order->update(['new_reference' => generateReference($order)]);
// }
// });
// Migration 2 (after backfill is complete): Add NOT NULL constraint
return new class extends \Illuminate\Database\Migrations\Migration
{
public function up(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->string('new_reference', 50)->nullable(false)->change();
});
}
};
// Renaming a column — two-step:
// Step 1: Add new column, dual-write
// Step 2: Backfill + verify
// Step 3: Drop old column
// Adding an index without lock (MySQL 8)
Schema::table('orders', function (Blueprint $table) {
$table->index(['user_id', 'status'], 'orders_user_status_idx');
// MySQL uses ALGORITHM=INPLACE for index creation — no table lock
});