Database migrations are risky. A poorly planned schema change can take down your application or corrupt data. Zero-downtime migrations let you evolve your database while keeping the application running.
The Problem
Dangerous Migration
Consider what happens when you rename a column in a live system. The moment the migration runs, any application code expecting the old column name breaks.
This migration looks simple, but it will cause immediate downtime. The moment it runs, all application code expecting the old column name will fail.
// This migration will lock the table and cause downtime
Schema::table('users', function (Blueprint $table) {
$table->renameColumn('name', 'full_name');
});
// Application code expecting 'name' column will fail immediately
Safe Migration (Expand-Contract)
The expand-contract pattern spreads the change across multiple deployments, ensuring the application continues working at every step.
The expand-contract pattern breaks risky changes into safe, incremental steps. Each phase is deployed and verified before moving to the next.
Phase 1: Expand - Add new column
Phase 2: Migrate - Copy data, update application
Phase 3: Contract - Remove old column
This approach takes longer but eliminates downtime entirely.
The Expand-Contract Pattern
Phase 1: Expand
Start by adding the new column without removing the old one. This phase is always safe because you're only adding, not changing or removing.
In the expand phase, you add the new structure alongside the old. This migration is completely safe because it only adds a column.
// Migration 1: Add new column (safe)
Schema::table('users', function (Blueprint $table) {
$table->string('full_name')->nullable()->after('name');
});
// Application still uses 'name' column - no changes needed yet
The nullable constraint is essential here. Without it, the migration would fail because existing rows don't have a value for the new column.
Phase 2: Migrate Data
Copy data from the old column to the new one, and update your application to write to both columns during the transition period.
Now you'll copy existing data and update your application to dual-write. The model writes to both columns and can read from either.
// Migration 2: Copy existing data
DB::statement('UPDATE users SET full_name = name WHERE full_name IS NULL');
// Update application to write to both columns
class User extends Model
{
public function setNameAttribute($value)
{
$this->attributes['name'] = $value;
$this->attributes['full_name'] = $value;
}
public function getFullNameAttribute()
{
return $this->attributes['full_name'] ?? $this->attributes['name'];
}
}
The dual-write ensures that any updates during the transition populate both columns. The getter falls back to the old column for rows that haven't been migrated yet.
Phase 3: Switch Reads
Once all data is migrated and the dual-write is deployed, switch reads to the new column. Continue writing to both columns during this phase.
With all data migrated, you can switch reads to the new column. Keep dual-writing until you're confident the new column is working correctly.
// Deploy application reading from new column
class User extends Model
{
public function setNameAttribute($value)
{
$this->attributes['name'] = $value; // Still write to old
$this->attributes['full_name'] = $value; // And new
}
public function getFullNameAttribute()
{
return $this->attributes['full_name']; // Read from new only
}
}
Monitor for any issues after this deployment. If problems arise, you can quickly revert to reading from the old column.
Phase 4: Contract
After confirming the new column is working correctly and all old application instances have been replaced, remove the old column.
In the final contraction phase, you remove the old column and simplify your code. Only run this after verifying the new column works correctly.
// After confirming all reads use new column
// Remove writes to old column from application
class User extends Model
{
protected $fillable = ['full_name']; // Only new column
public function getFullNameAttribute()
{
return $this->attributes['full_name'];
}
}
// Migration 3: Drop old column (safe - no longer used)
Schema::table('users', function (Blueprint $table) {
$table->dropColumn('name');
});
Only run this migration after all application servers are using the new column exclusively.
Common Scenarios
Adding a Non-Nullable Column
Adding a required column to a table with existing data requires a three-phase approach to avoid failures.
You can't add a NOT NULL column to a table with existing data because those rows have no value for the new column. Use this three-phase approach instead.
// WRONG: Will fail if table has data
Schema::table('orders', function (Blueprint $table) {
$table->string('tracking_number'); // NOT NULL by default
});
// RIGHT: Three-phase approach
// Phase 1: Add nullable
Schema::table('orders', function (Blueprint $table) {
$table->string('tracking_number')->nullable();
});
// Phase 2: Backfill data
DB::table('orders')
->whereNull('tracking_number')
->update(['tracking_number' => 'LEGACY-' . DB::raw('id')]);
// Phase 3: Add constraint
Schema::table('orders', function (Blueprint $table) {
$table->string('tracking_number')->nullable(false)->change();
});
The backfill step is critical. You must provide values for existing rows before adding the NOT NULL constraint.
Adding an Index
Index creation can lock tables for extended periods on large datasets. Most databases provide concurrent index options, but they require special syntax.
Standard index creation locks the table for writes. On large tables, this can cause significant downtime. Use concurrent index creation when available.
// WRONG: Locks table during index creation
Schema::table('orders', function (Blueprint $table) {
$table->index('customer_id');
});
// RIGHT: Create index concurrently (PostgreSQL)
DB::statement('CREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer_id)');
// For MySQL, use pt-online-schema-change or gh-ost
// Or schedule during low-traffic period
Concurrent index creation takes longer but doesn't block writes. For MySQL, external tools like pt-online-schema-change provide similar functionality.
Changing Column Type
Type changes often require table rewrites. Use the expand-contract pattern with a new column to avoid locking.
Direct type changes can lock tables for extended periods. This four-phase approach uses a new column and a trigger to maintain consistency during the transition.
// WRONG: Direct type change locks table
Schema::table('products', function (Blueprint $table) {
$table->decimal('price', 10, 4)->change(); // Was decimal(10, 2)
});
// RIGHT: Expand-contract
// Phase 1: Add new column
Schema::table('products', function (Blueprint $table) {
$table->decimal('price_v2', 10, 4)->nullable();
});
// Phase 2: Sync data
DB::statement('UPDATE products SET price_v2 = price');
// Add trigger to keep in sync
DB::statement('
CREATE TRIGGER sync_price_v2
BEFORE UPDATE ON products
FOR EACH ROW
SET NEW.price_v2 = NEW.price
');
// Phase 3: Update application to use price_v2
// Phase 4: Drop old column and trigger
DB::statement('DROP TRIGGER sync_price_v2');
Schema::table('products', function (Blueprint $table) {
$table->dropColumn('price');
$table->renameColumn('price_v2', 'price');
});
The trigger ensures that any updates to the old column during migration are reflected in the new column automatically.
Removing a Column
Never remove a column while application code still references it. The removal process requires coordination between code and database deployments.
Column removal must follow your code deployment. If you drop the column before the code change is fully deployed, running instances will fail.
// WRONG: Immediate removal breaks running application
Schema::table('users', function (Blueprint $table) {
$table->dropColumn('legacy_field');
});
// RIGHT: Phased removal
// Phase 1: Stop writing to column in application
// (Deploy this change first)
// Phase 2: Wait for all old application instances to drain
// (Usually one deployment cycle)
// Phase 3: Remove column
Schema::table('users', function (Blueprint $table) {
$table->dropColumn('legacy_field');
});
The waiting period in Phase 2 ensures no running application instance still expects the column to exist.
Large Table Migrations
Batch Updates
Large tables require batch processing to avoid locking and memory issues. Process rows in chunks with pauses between batches.
This migration processes rows in batches with pauses between them. It avoids overwhelming the database and allows other queries to complete between batches.
class BackfillUserFullName extends Migration
{
public function up()
{
// Process in batches to avoid locking
$batchSize = 1000;
$lastId = 0;
while (true) {
$affected = DB::table('users')
->where('id', '>', $lastId)
->whereNull('full_name')
->orderBy('id')
->limit($batchSize)
->update(['full_name' => DB::raw('name')]);
if ($affected === 0) {
break;
}
$lastId = DB::table('users')
->where('id', '>', $lastId)
->orderBy('id')
->limit($batchSize)
->max('id');
// Avoid overwhelming the database
usleep(100000); // 100ms pause between batches
}
}
}
The pause between batches gives the database time to handle other queries and prevents replication lag in distributed setups.
Using pt-online-schema-change
Percona's pt-online-schema-change creates a shadow table, copies data in chunks, and swaps tables atomically. This is the gold standard for MySQL schema changes.
The pt-online-schema-change tool handles large table migrations safely. It creates a shadow table, copies data incrementally, and swaps tables atomically.
# MySQL tool that performs online schema changes
pt-online-schema-change \
--alter "ADD COLUMN full_name VARCHAR(255)" \
--execute \
D=mydb,t=users \
--chunk-size=1000 \
--max-lag=1s
Using gh-ost
GitHub's gh-ost uses binary log streaming instead of triggers, making it safer for high-traffic tables.
GitHub's gh-ost is an alternative to pt-online-schema-change that avoids triggers by using binary log streaming.
# GitHub's online schema migration tool
gh-ost \
--database="mydb" \
--table="users" \
--alter="ADD COLUMN full_name VARCHAR(255)" \
--execute \
--chunk-size=1000 \
--max-lag-millis=1500
Both tools require careful testing in staging environments before production use.
Backward Compatibility
Application-Database Version Matrix
During migrations, different application versions may run against different database schemas. Map out which combinations are valid.
This compatibility matrix shows which application and database version combinations are safe. Use it to plan your deployment order.
App V1 reads: name
App V2 reads: name, full_name (prefers full_name)
App V3 reads: full_name only
DB V1 has: name
DB V2 has: name, full_name (both populated)
DB V3 has: full_name only
Safe combinations:
- App V1 + DB V1 ✓
- App V1 + DB V2 ✓ (name still exists)
- App V2 + DB V1 ✓ (falls back to name)
- App V2 + DB V2 ✓
- App V2 + DB V3 ✓ (uses full_name)
- App V3 + DB V3 ✓
Unsafe combinations:
- App V1 + DB V3 ✗ (name doesn't exist)
- App V3 + DB V1 ✗ (full_name doesn't exist)
This matrix helps you plan deployment order and identify when it's safe to run each migration phase.
Feature Flags for Migration Phases
Feature flags let you control migration behavior without deploying new code. This is especially useful when you need to quickly roll back.
Use feature flags to control which column your application uses. This lets you switch between old and new columns without deploying code changes.
class User extends Model
{
public function getDisplayNameAttribute()
{
if (Feature::enabled('use_full_name_column')) {
return $this->full_name ?? $this->name;
}
return $this->name;
}
public function setDisplayNameAttribute($value)
{
if (Feature::enabled('write_full_name_column')) {
$this->attributes['full_name'] = $value;
}
// Always write to name until migration complete
$this->attributes['name'] = $value;
}
}
You can enable the feature flag gradually across servers, monitoring for issues before full rollout.
Rollback Strategies
Forward-Only Migrations
Some migrations can't be truly reversed. When that's the case, document why and ensure the down method at least cleans up safely.
Migrations involving encryption or data transformation may not be reversible. Document the limitation and provide safe cleanup in the down method.
// Sometimes rollback isn't possible
class AddEncryptedEmailColumn extends Migration
{
public function up()
{
Schema::table('users', function (Blueprint $table) {
$table->text('email_encrypted')->nullable();
});
// Encrypt existing emails
User::chunk(100, function ($users) {
foreach ($users as $user) {
$user->update([
'email_encrypted' => encrypt($user->email),
]);
}
});
}
public function down()
{
// Can't decrypt without keys that may have rotated
// Just remove the column
Schema::table('users', function (Blueprint $table) {
$table->dropColumn('email_encrypted');
});
}
}
Keeping Old Data Temporarily
During risky migrations, keep the old column or data structure around longer than strictly necessary. This provides a rollback path.
Keep the old column around as insurance during the transition period. Schedule its removal only after the new column is proven in production.
class RenameOrderStatusColumn extends Migration
{
public function up()
{
// Don't delete old column immediately
Schema::table('orders', function (Blueprint $table) {
$table->string('status_new')->nullable();
});
DB::statement('UPDATE orders SET status_new = status');
// Keep old column for rollback capability
// Schedule removal for 2 weeks later
}
public function down()
{
// Rollback by reverting to old column
DB::statement('UPDATE orders SET status = status_new');
Schema::table('orders', function (Blueprint $table) {
$table->dropColumn('status_new');
});
}
}
The old column acts as insurance. Once you're confident the migration succeeded, schedule a cleanup migration.
Testing Migrations
Test Against Production-Like Data
Migrations that work on empty tables often fail with production data. Test with realistic data volumes and edge cases.
Test your migrations with production-like data, including edge cases like NULL values and empty strings. These often cause unexpected failures.
class MigrationTest extends TestCase
{
public function test_migration_handles_null_values()
{
// Create data that might exist in production
DB::table('users')->insert([
['name' => 'John', 'email' => 'john@example.com'],
['name' => null, 'email' => 'null@example.com'], // Edge case
['name' => '', 'email' => 'empty@example.com'], // Edge case
]);
Artisan::call('migrate');
$this->assertDatabaseHas('users', [
'email' => 'john@example.com',
'full_name' => 'John',
]);
// Verify null/empty handling
$nullUser = DB::table('users')->where('email', 'null@example.com')->first();
$this->assertNull($nullUser->full_name);
}
public function test_migration_performance()
{
// Create realistic data volume
User::factory()->count(10000)->create();
$startTime = microtime(true);
Artisan::call('migrate');
$duration = microtime(true) - $startTime;
// Migration should complete in reasonable time
$this->assertLessThan(30, $duration, 'Migration took too long');
}
}
Performance tests catch migrations that work but would take hours on production data.
Monitoring Migrations
Track migration execution to detect problems and measure performance over time.
This monitoring class tracks migration execution time and detects database locks. Use it to alert on slow migrations and identify blocking issues.
class MigrationMonitor
{
public function beforeMigration(string $migration): void
{
Log::info("Starting migration: {$migration}");
$this->metrics->gauge('migration.running', 1, [
'migration' => $migration,
]);
}
public function afterMigration(string $migration, float $duration): void
{
Log::info("Completed migration: {$migration}", [
'duration_seconds' => $duration,
]);
$this->metrics->histogram('migration.duration', $duration, [
'migration' => $migration,
]);
$this->metrics->gauge('migration.running', 0);
}
public function monitorLocks(): void
{
// PostgreSQL
$locks = DB::select("
SELECT pid, query, state, wait_event_type
FROM pg_stat_activity
WHERE state = 'active' AND wait_event_type = 'Lock'
");
foreach ($locks as $lock) {
Log::warning('Database lock detected during migration', [
'pid' => $lock->pid,
'query' => $lock->query,
]);
}
}
}
Lock monitoring is especially important for migrations that touch frequently-accessed tables.
Conclusion
Zero-downtime migrations require planning and discipline. Use the expand-contract pattern for schema changes, process large tables in batches, and maintain backward compatibility between application and database versions. Test migrations with production-like data before deploying. The extra effort upfront prevents the pain of emergency rollbacks and user-facing outages during deployments.