Database Migration Strategies: A Complete Guide

You rename a column in a migration, it runs fine locally, and you deploy. For the next couple of minutes, the servers still running the old code throw Unknown column 'user_name' on every request, because the schema changed before the code did. Or the migration hits a big table and every query against it sits waiting until the ALTER finishes.

Most migration pain comes from those two problems: the app and the schema being out of step, and locks on large tables. This post covers the patterns I use to avoid both (expand-contract, blue-green, rolling deploys, batched backfills) plus rollback planning and a short look at the tools.

Understanding Migration Challenges

If a code deploy goes wrong, you redeploy the previous build and you're back. A schema change doesn't work that way, because the data has already moved. These are the things that make it harder:

The Expand-Contract Pattern

This is the pattern I reach for most. Expand-Contract (also called Parallel Change) avoids the breaking change entirely. You expand the schema so it supports both the old and new shape, move the data, switch the app over, and only then contract by removing the old shape. It's slower than a single migration, but at no point is there code running against a schema it doesn't understand.

Example: Renaming a Column

Say you need to rename user_name to username on the users table. Here's the same change in three flavors:

// database/migrations/2026_03_21_001_add_username_column.php

public function up(): void
{
    Schema::table('users', function (Blueprint $table) {
        // Add new column (nullable initially)
        $table->string('username')->nullable()->after('user_name');
    });
}

public function down(): void
{
    Schema::table('users', function (Blueprint $table) {
        $table->dropColumn('username');
    });
}

// database/migrations/2026_03_21_002_copy_username_data.php

public function up(): void
{
    // For small tables, direct update works
    DB::statement('UPDATE users SET username = user_name WHERE username IS NULL');
    
    // For large tables, use chunking:
    // User::whereNull('username')
    //     ->chunkById(1000, function ($users) {
    //         foreach ($users as $user) {
    //             $user->update(['username' => $user->user_name]);
    //         }
    //     });
}

// app/Models/User.php - During transition period

class User extends Model
{
    // Accessor: read from new column, fallback to old
    public function getUsernameAttribute(): string
    {
        return $this->attributes['username'] 
            ?? $this->attributes['user_name'];
    }

    // Mutator: write to both columns
    public function setUsernameAttribute($value): void
    {
        $this->attributes['username'] = $value;
        $this->attributes['user_name'] = $value; // Keep in sync
    }
}

// database/migrations/2026_03_28_001_drop_user_name_column.php
// Run this AFTER all app servers are updated!

public function up(): void
{
    Schema::table('users', function (Blueprint $table) {
        $table->dropColumn('user_name');
    });
}

-- V1__add_username_column.sql
ALTER TABLE users 
ADD COLUMN username VARCHAR(255) NULL AFTER user_name;

-- Create index for the new column
CREATE INDEX idx_users_username ON users(username);

-- V2__copy_username_data.sql
-- For small tables (< 100k rows):
UPDATE users SET username = user_name WHERE username IS NULL;

-- For large tables, use batched updates:
-- Run this script repeatedly until 0 rows affected
UPDATE users 
SET username = user_name 
WHERE username IS NULL 
LIMIT 10000;

-- Check progress
SELECT 
    COUNT(*) as total,
    SUM(CASE WHEN username IS NOT NULL THEN 1 ELSE 0 END) as migrated
FROM users;

-- V3__drop_user_name_column.sql
-- ⚠️ Run only after ALL app instances use 'username'

-- First, make the new column NOT NULL
ALTER TABLE users 
MODIFY COLUMN username VARCHAR(255) NOT NULL;

-- Then drop the old column
ALTER TABLE users 
DROP COLUMN user_name;

-- Flyway migration: Expand phase
ALTER TABLE users 
ADD COLUMN username VARCHAR(255);

-- Trigger to keep columns in sync during transition
DELIMITER //
CREATE TRIGGER sync_username_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.username IS NULL THEN
        SET NEW.username = NEW.user_name;
    ELSEIF NEW.user_name IS NULL THEN
        SET NEW.user_name = NEW.username;
    END IF;
END//
DELIMITER ;

-- Backfill existing data
UPDATE users SET username = user_name WHERE username IS NULL;

-- Remove sync trigger first
DROP TRIGGER IF EXISTS sync_username_insert;
DROP TRIGGER IF EXISTS sync_username_update;

-- Make new column required
ALTER TABLE users 
MODIFY COLUMN username VARCHAR(255) NOT NULL;

-- Drop old column
ALTER TABLE users 
DROP COLUMN user_name;

Blue-Green Database Deployments

With blue-green you run two copies of production and flip traffic between them. On the app side that's easy. On the database side it means two instances (or schemas) kept in sync, which is where it gets hard: cutover and rollback are quick, but only if the data on both sides actually matches.

When to Use Blue-Green for Databases

Rolling Migrations for Distributed Systems

Once you have more than one app server, a deploy isn't instant. Nodes get updated one at a time while the rest keep serving traffic, so for a while old and new code hit the same database together. The schema has to work for both.

Migrating Large Tables

On a table with millions of rows, a plain ALTER TABLE or one big UPDATE can turn into a long transaction that holds locks, spikes CPU, and leaves replicas lagging behind.

# Add a column to a large table without locking
gh-ost \
  --host="db.example.com" \
  --database="myapp" \
  --table="users" \
  --alter="ADD COLUMN email_verified TINYINT DEFAULT 0" \
  --execute

# How it works:
# 1. Creates a ghost table with new schema
# 2. Copies data in chunks (configurable size)
# 3. Uses binary log to capture changes during copy
# 4. Atomic table swap at the end

use Illuminate\Support\Facades\DB;

class MigrateUserEmailsJob implements ShouldQueue
{
    public $timeout = 3600; // 1 hour max
    
    public function handle()
    {
        $lastId = cache('migration:users:last_id', 0);
        $batchSize = 1000;
        $processed = 0;
        
        do {
            $users = DB::table('users')
                ->where('id', '>', $lastId)
                ->whereNull('email_normalized')
                ->orderBy('id')
                ->limit($batchSize)
                ->get();
            
            foreach ($users as $user) {
                DB::table('users')
                    ->where('id', $user->id)
                    ->update([
                        'email_normalized' => strtolower($user->email)
                    ]);
                $lastId = $user->id;
                $processed++;
            }
            
            // Save progress
            cache(['migration:users:last_id' => $lastId], now()->addDays(7));
            
            // Throttle to reduce DB load
            usleep(100000); // 100ms between batches
            
        } while ($users->count() === $batchSize);
        
        logger()->info("Migration complete: {$processed} users processed");
    }
}

-- Step 1: Create shadow table
CREATE TABLE users_new LIKE users;
ALTER TABLE users_new ADD COLUMN status ENUM('active', 'inactive') DEFAULT 'active';

-- Step 2: Copy data in batches (run repeatedly)
INSERT INTO users_new (id, name, email, created_at, status)
SELECT id, name, email, created_at, 'active'
FROM users
WHERE id > (SELECT COALESCE(MAX(id), 0) FROM users_new)
LIMIT 10000;

-- Step 3: Set up triggers for ongoing changes
CREATE TRIGGER users_insert_shadow
AFTER INSERT ON users
FOR EACH ROW
INSERT INTO users_new VALUES (NEW.id, NEW.name, NEW.email, NEW.created_at, 'active');

-- Step 4: Atomic swap when ready
RENAME TABLE users TO users_old, users_new TO users;

Rollback Strategies

Work out how you'd undo a migration before you run it. At 2am with errors piling up is a bad time to start thinking about it.

class UserRepository
{
    public function getUsername(User $user): string
    {
        // Feature flag allows instant rollback
        if (feature('use-new-username-column')) {
            return $user->username;
        }
        
        return $user->user_name;
    }
    
    public function updateUsername(User $user, string $username): void
    {
        // Always dual-write during transition
        $user->update([
            'username' => $username,
            'user_name' => $username,
        ]);
    }
}

Migration Tools Comparison

Most of the time the tool is picked for you by your framework. These are the common ones:

Migration Checklist

What I go through before running a migration against production:

When I'd use expand-contract (and when I wouldn't)

Expand-contract costs you something. One rename becomes three or four migrations spread over several releases, you carry dual-write code for a while, and someone has to remember to ship the contract step. If nobody does, you end up with both columns forever.

So I don't use it for everything. If the app runs on a single server, you can afford a short maintenance window, or the table is small and nobody depends on the column yet, a normal migration in the same deploy is fine. Purely additive changes (a new nullable column, a new table) don't need it either, since old code just ignores them.

I do use it when old and new code will overlap in production (multiple servers, queue workers on the old build, rolling deploys), when the table is big enough that a rewrite would lock it, or when the change is a rename or type change that old code can't survive. For blue-green, I'd only consider it on smaller, read-heavy databases. For busy write-heavy ones, keeping two databases in sync is usually more trouble than it saves, and expand-contract against a single database is simpler.