Database Migration Strategies: A Complete Guide

Database migrations are one of the most critical—and risky—operations in software development. A poorly executed migration can lead to data loss, application downtime, and frustrated users. Yet as applications evolve, schema changes become inevitable. The key is knowing how to migrate safely, efficiently, and with minimal impact on your production systems.

In this comprehensive guide, we'll explore battle-tested migration strategies used by companies handling millions of records. You'll learn when to use each approach, how to implement zero-downtime migrations, and the critical mistakes to avoid. Whether you're renaming a column or restructuring an entire database, these strategies will help you sleep better at night.

Understanding Migration Challenges

Before diving into strategies, let's understand why database migrations are challenging. Unlike application code that can be instantly rolled back, database changes often involve:

The Expand-Contract Pattern

The Expand-Contract pattern (also called Parallel Change) is the gold standard for zero-downtime migrations. Instead of making breaking changes directly, you expand the schema to support both old and new formats, migrate data, update the application, then contract by removing the old format.

Example: Renaming a Column

Let's say we need to rename user_name to username in a users table. Here's how to do it safely:

// 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

Blue-Green deployment is a technique where you maintain two identical production environments. For databases, this typically involves maintaining two database instances or schemas, allowing instant cutover and rollback.

When to Use Blue-Green for Databases

Rolling Migrations for Distributed Systems

In distributed systems with multiple application servers, rolling migrations allow you to update one node at a time while the others continue serving traffic. The database migration must support both old and new application code running simultaneously.

Migrating Large Tables

When dealing with tables containing millions or billions of rows, standard migration approaches can cause serious issues: long-running transactions, table locks, replication lag, and high CPU usage. Here are strategies to handle large tables safely.

# 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

Every migration should have a rollback plan. The best time to plan for failure is before you need 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

Choosing the right migration tool depends on your stack, team size, and requirements. Here's a comparison of popular options:

Migration Checklist

Use this checklist before running any production migration:

Conclusion

Database migrations don't have to be scary. By following the strategies outlined in this guide—especially the Expand-Contract pattern—you can make schema changes safely, without downtime, and with confidence that you can roll back if needed.

Remember the key principles:

• Break big changes into small, reversible steps
• Always maintain backward compatibility during transitions
• Test with production-like data volumes
• Have a rollback plan before you start
• Monitor everything during and after migration

With these strategies in your toolkit, you can evolve your database schema as your application grows, keeping your users happy and your data safe.