Database Migration: Schema Changes and Zero-Downtime Deploy

What is Database Migration?

Database migration is a controlled process used to change the database schema (table structures, indexes, constraints).

Schema changes: Adding new tables, adding/modifying/deleting columns, adding indexes, adding foreign keys, changing data types.

Risks: Data loss, downtime, performance degradation, backward incompatibility.

Goal: Apply schema changes safely, repeatably, and rollback-ably.

Migration Tools

Flyway: SQL-based migration tool. Versioned migration files (V1__create_users.sql). Java, Node.js, CLI support.

Liquibase: Changelog in XML/YAML/JSON/SQL format. Rollback support, diff generation, database comparison.

Prisma Migrate: Integrated with Prisma ORM. Automatically generates migration from schema.prisma.

Knex.js: Node.js migration tool. Migration files in JavaScript. Up and down functions.

Django/ActiveRecord: Framework built-in migration tools. Automatically creates migration from model changes.

Zero-Downtime Migration Strategies

Expand and Contract (Parallel Change): 1) Add new column/table (expand), 2) Update code (support both new and old column), 3) Remove old column (contract).

Backward compatible changes: Adding new column (nullable), adding new table, adding new index. These changes don't require downtime.

Breaking changes: Column deletion, column type change, rename. These changes should be done with expand-contract pattern.

Feature flags: Put new schema behind feature flag. Reduce risk with gradual rollout.

Blue-green deployment: Test new schema in parallel environment. Switch traffic. Revert to old environment if problem.

Data Migration

Backfill: Make existing data compatible with new schema. Process large datasets with batch processing.

Dual write: Write to both old and new schema. Maintain data consistency during transition period.

Data validation: Check data integrity after migration. Row count, checksum, sample comparison.

Performance: Migration on large tables creates lock and I/O load. Run during off-peak hours. Use chunked processing.

Rollback Plan

Down migration: Write corresponding down migration (rollback) for each up migration. Flyway, Liquibase support rollback.

Backup: Take full backup before migration. Have point-in-time recovery (PITR) capability.

Testing: Test migration in staging environment. Use same data volume and schema as production.

Monitoring: Monitor query performance and error rate after migration. Rollback if abnormal deviation.

Communication: Share migration plan with team. Define maintenance window.

Best Practices

Version control: Track migration files with Git. Each migration should be atomic (single change).

Idempotency: Running migration multiple times should produce same result. Use IF NOT EXISTS, IF EXISTS.

Small migrations: Split large changes into small migrations. Each migration should be independently deployable.

Code review: Run migration files through code review. Schema changes are critical and hard to revert.

Documentation: Document the purpose and impact of each migration. Clearly indicate breaking changes.