Database migrations without downtime

The Challenge of Database Migrations

Database migrations are necessary for application evolution but can be risky. Traditional approaches require downtime, which is unacceptable for modern applications.

The Expand-Contract Pattern

This is the fundamental pattern for zero-downtime migrations:

Phase 1: Expand

Add new schema elements alongside old ones. Both old and new code can work simultaneously.

-- Add new column without removing old one
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

Phase 2: Migrate Data

Copy data from old schema to new schema while application runs.

-- Run in batches to avoid locks
UPDATE users SET email_address = email
WHERE email_address IS NULL LIMIT 1000;

Phase 3: Contract

Remove old schema elements after all code uses new schema.

-- Only after all instances use email_address
ALTER TABLE users DROP COLUMN email;

Safe Migration Practices

1. Never Break Backward Compatibility

Old code must continue working during migration. Add new columns as nullable or with defaults.

2. Use Feature Flags

Control when new code paths are activated:

if (featureFlags.useNewEmailColumn) {
  return user.email_address;
} else {
  return user.email;
}

3. Deploy in Stages

• Stage 1: Deploy schema changes (expand)
• Stage 2: Deploy code that writes to both old and new schema
• Stage 3: Migrate existing data
• Stage 4: Deploy code that reads from new schema
• Stage 5: Remove old schema (contract)

Index Management

Creating indexes can lock tables. Use concurrent index creation:

-- PostgreSQL
CREATE INDEX CONCURRENTLY idx_users_email ON users(email_address);

-- MySQL (use pt-online-schema-change for large tables)
pt-online-schema-change --alter "ADD INDEX idx_email (email_address)"

Data Type Changes

Changing data types requires careful planning:

1. Add new column with new data type
2. Write to both columns
3. Migrate existing data
4. Switch reads to new column
5. Remove old column

Testing Migrations

Always test migrations on production-like data:

• Clone production database to staging
• Run migration scripts
• Verify data integrity
• Test rollback procedures
• Measure migration duration

CupaDev Migration Tools

Use CupaDev's migration management:

cupadev db migrate --strategy zero-downtime
cupadev db rollback --if-needed

Conclusion

Database migrations without downtime are achievable with the expand-contract pattern and careful planning. CupaDev's tools and deployment strategies make this process safer and more manageable.