Zero Downtime Database Migration Steps

“We need to take the site down for maintenance” is no longer acceptable. Users expect 100% uptime. Your database migration steps need to deliver it.

Zero downtime database migrations are possible—but they require different techniques than traditional migrations. For foundational steps, see our database migration steps checklist.

Why Zero Downtime Database Migration Steps Matter

The Old Way

1. Enable maintenance mode
2. Stop application servers
3. Run migration
4. Start application servers
5. Disable maintenance mode

Result: Minutes to hours of downtime.

The Zero Downtime Way

1. Deploy backwards-compatible schema change
2. Deploy new application code
3. Run data migration in background
4. Clean up old schema

Result: Zero user-facing downtime.

Database Migration Steps for Zero Downtime

Step 1: Make Schema Changes Backwards-Compatible

Never make breaking changes in a single deploy:

-- ❌ Breaking: Renaming a column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- ✅ Zero downtime: Add new column, migrate, remove old
-- Deploy 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Deploy 2: Backfill data
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Deploy 3: Application uses new column
-- Deploy 4: Remove old column
ALTER TABLE users DROP COLUMN name;

Step 2: Use Expand-Contract Pattern

Expand the schema, migrate data, contract the schema:

## Phase 1: Expand
Add new column `email_verified` (nullable)

​```sql
ALTER TABLE users ADD COLUMN email_verified BOOLEAN;
​```

## Phase 2: Migrate
Backfill existing data

​```sql
UPDATE users SET email_verified = 
  CASE WHEN email_verified_at IS NOT NULL THEN true ELSE false END;
​```

## Phase 3: Contract
Make column required, remove old column

​```sql
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
ALTER TABLE users DROP COLUMN email_verified_at;
​```

Step 3: Handle Foreign Keys Carefully

Foreign key changes require special handling:

-- Add FK without validation (instant)
ALTER TABLE orders 
ADD CONSTRAINT orders_user_id_fkey 
FOREIGN KEY (user_id) REFERENCES users(id) 
NOT VALID;

-- Validate FK in background (no locks)
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

Step 4: Create Indexes Concurrently

Standard index creation locks writes. Use concurrent creation:

# PostgreSQL
psql -c "CREATE INDEX CONCURRENTLY idx_orders_date ON orders(created_at);"

# MySQL 8.0+
mysql -e "ALTER TABLE orders ADD INDEX idx_date(created_at), ALGORITHM=INPLACE, LOCK=NONE;"

Step 5: Batch Large Data Migrations

Never update millions of rows in one transaction:

-- Batch update script
DO $$
DECLARE
  batch_size INTEGER := 10000;
  rows_updated INTEGER;
BEGIN
  LOOP
    UPDATE users 
    SET status = 'active' 
    WHERE id IN (
      SELECT id FROM users 
      WHERE status IS NULL 
      LIMIT batch_size
    );
    
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    
    COMMIT;
    PERFORM pg_sleep(0.1); -- Brief pause to reduce load
  END LOOP;
END $$;

Step 6: Use Feature Flags

Control migration rollout with feature flags:

## Zero Downtime Rollout

1. Deploy with feature flag OFF
2. Enable flag for internal users
3. Monitor for issues
4. Gradually increase to 100%
5. Remove feature flag code

Step 7: Have Instant Rollback Ready

Zero downtime means zero downtime rollback too:

-- Keep old column until new code is stable
-- Rollback is just: redeploy old code

-- For data migrations, maintain backwards compatibility
-- Old code can still read/write during migration

Executing Zero Downtime Database Migration Steps

Zero downtime migrations have more steps than traditional migrations. More steps mean more opportunities for human error. For more guidance, check our database migration best practices and production migration guide.

Stew makes your database migration steps executable. Document the expand-contract pattern once. Execute each phase with confidence. Track exactly which step you’re on across multi-day migrations.

Join the waitlist and achieve true zero downtime deployments.