Moving and Upgrading Databases Safely

The Core Problem

Schema changes are inherently dangerous because databases are stateful. Code deployments are atomic: you push new code, it runs. If it fails, you roll back. But databases persist across deployments. A column you drop is gone. A constraint you add might reject live traffic. A table rename breaks every query that references the old name.

This statefulness creates coordination problems that don't exist with stateless deployments:

Migration Fundamentals

A migration is a single, versioned unit of schema change. It has a unique identifier (typically a timestamp), an "up" operation that applies the change, and a "down" operation that reverses it. Migrations run in order, tracked in a metadata table so the system knows what's been applied.

Immutability principle

Once a migration has run in any shared environment (staging, production, a colleague's machine), treat it as immutable. Never edit the file. If you made a mistake, write a new migration to correct it. Editing applied migrations causes a checksum mismatch (in tools that verify), or silent divergence (in tools that don't). Either outcome leads to hours of debugging.

Migration granularity

Keep migrations atomic. One migration should do one thing: add a column, create a table, add an index. Resist the temptation to combine related changes. Granular migrations are easier to reason about, easier to roll back, and easier to debug when something fails.

Safe vs Dangerous Operations

Not all schema changes carry equal risk. Understanding the risk profile of each operation type determines whether you can run a migration during business hours or need a maintenance window.

The hidden dangers of "safe" operations

Even additive changes have edge cases. Adding an index on a large table acquires a lock for the duration of the index build. On PostgreSQL, this blocks writes unless you use CREATE INDEX CONCURRENTLY (see the PostgreSQL documentation on index creation). On MySQL, it depends on the storage engine and version. Adding a column with a default value in older MySQL versions rewrites the entire table. Know your database engine's behaviour.

Migration Patterns: Concrete Examples

Abstract principles become clearer with specific patterns. Here are the operations we perform regularly, with the exact approach for each.

Pattern 1: Adding a nullable column

The simplest migration. No coordination required with application code. Run the migration, then deploy code that uses the column.

The column accepts NULL, so existing rows remain valid. Application code that doesn't know about the column continues working. New code can write to it. Deploy migration first, code second.

Pattern 2: Adding a column with a default value

Slightly more complex. The default value strategy depends on your database version.

Modern databases handle this efficiently: they store the default in metadata and apply it lazily. Older versions rewrite every row in the table, which locks it for the duration. For large tables on older databases, add the column as nullable, backfill in batches, then add the NOT NULL constraint.

Pattern 3: Renaming a column (expand-contract)

Never rename directly. A direct ALTER TABLE ... RENAME COLUMN breaks all code referencing the old name instantly. Use the expand-contract pattern instead.

Example: renaming user_name to username:

1. Migration 1: ALTER TABLE users ADD COLUMN username VARCHAR(255);
2. Deploy 1: Application writes to both user_name and username. Reads from user_name.
3. Migration 2: UPDATE users SET username = user_name WHERE username IS NULL; (in batches for large tables)
4. Deploy 2: Application reads from username. Still writes to both.
5. Deploy 3: Application stops writing to user_name.
6. Migration 3: ALTER TABLE users DROP COLUMN user_name;

Three migrations. Three deployments. No downtime. No broken queries.

Pattern 4: Changing a column type

Type changes range from trivial to terrifying depending on the transformation. Widening a VARCHAR from 50 to 255 is usually safe. Changing a VARCHAR to an INTEGER requires data transformation and can fail if the data doesn't convert.

Safe type changes (usually metadata-only):

• Widening VARCHAR: VARCHAR(50) to VARCHAR(255)
• Widening numeric precision: DECIMAL(10,2) to DECIMAL(12,2)
• Changing INT to BIGINT (most databases)

Dangerous type changes (require expand-contract):

• Narrowing VARCHAR: data truncation possible
• Changing between incompatible types: VARCHAR to INTEGER, TEXT to JSON
• Changing precision in ways that lose data: DECIMAL(10,4) to DECIMAL(10,2)

For dangerous changes, use the expand-contract pattern. Add a new column with the new type, migrate data with explicit transformation logic, switch reads, drop the old column.

Pattern 5: Removing a column

Column removal must happen after all code stops referencing it. The sequence:

1. Deploy 1: Remove all reads from the column. Application no longer SELECT the column.
2. Deploy 2: Remove all writes to the column. Application no longer INSERT or UPDATE the column.
3. Wait: Ensure no running code references the column. Check for scheduled jobs, background workers, cached queries.
4. Migration: ALTER TABLE orders DROP COLUMN legacy_status;

Pattern 6: Adding a foreign key constraint

Foreign keys validate existing data. If any rows violate the constraint, the ALTER fails. For existing tables with potentially dirty data:

1. Query for violating rows: SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers);
2. Fix or remove violating data
3. Add the constraint: ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

On very large tables, consider adding the constraint as NOT VALID first (PostgreSQL), then validating separately:

The first statement is fast (no data scan). The second validates in the background without blocking writes.

Pattern 7: Adding a NOT NULL constraint

Adding NOT NULL to an existing column that contains NULLs fails immediately. The process:

1. Backfill NULL values with appropriate defaults
2. Verify no NULLs remain: SELECT COUNT(*) FROM users WHERE email IS NULL;
3. Add the constraint

On large tables, backfilling in batches prevents long-running transactions:

Zero-Downtime Deployment Strategies

Production systems can't go offline for every schema change. Zero-downtime migrations require understanding the relationship between schema changes and application deployments. This coordination is part of broader infrastructure management.

The deployment ordering rule

For any migration, exactly one of these orderings is safe:

Rolling deployments and schema compatibility

In rolling deployments, old and new application versions run simultaneously. Both versions must work with the current schema. This creates a compatibility window: any schema change must be backward-compatible with the previous code version and forward-compatible with the next.

The implication: most changes require multiple deployments. You can't add a required column in one deployment, because old instances (still running) don't write to it. Add it as nullable first, deploy code that writes to it, then add the NOT NULL constraint.

Blue-green deployments

Blue-green deployments run two complete environments. Traffic switches atomically from blue to green. This sounds simpler for migrations, but both environments share the same database. The same compatibility rules apply.

Blue-green does enable faster rollback: switch traffic back to blue. But if green ran a destructive migration, switching back doesn't undo it. Plan migrations to be rollback-safe independent of deployment strategy.

Feature flags for complex migrations

For complex multi-phase changes, use feature flags to control which code paths execute:

1. Deploy code that can read from old or new structure, controlled by flag
2. Run migration with flag set to "old"
3. Backfill data
4. Flip flag to "new" for a percentage of traffic
5. Monitor for errors
6. Increase percentage until 100%
7. Deploy code that only knows new structure
8. Remove old structure

This approach adds complexity but provides granular control. Reserve it for changes with significant risk.

Large Table Migrations

Tables with millions of rows require special handling. Operations that take milliseconds on small tables can run for hours on large ones, blocking production traffic the entire time.

Understanding table size thresholds

The exact threshold depends on hardware, database engine, and operation type. As rough guidance for row counts:

• Under 100,000 rows: Most operations complete quickly. Standard migration approaches work.
• 100,000 to 1 million rows: Test migration timing. Some operations may need off-peak scheduling.
• 1 million to 10 million rows: Careful planning required. Use online schema change tools or maintenance windows.
• Over 10 million rows: Every change needs explicit planning. Consider partitioning, archiving, or table rebuilding strategies.

Online schema change tools

These tools perform schema changes without extended locking by creating a shadow table, applying the change to the shadow, copying data over, and swapping tables atomically.

Batched operations

For data backfills and updates, process in batches to avoid long-running transactions and lock contention:

Batching is essential for:

• Backfilling new columns with computed values
• Data type conversions
• Deleting large numbers of rows
• Updating denormalised values

Maintenance windows

Some changes can't avoid locking. When you must lock a large table:

1. Schedule during lowest-traffic period
2. Communicate the window to stakeholders
3. Test the migration timing on production-size data
4. Have a rollback plan ready
5. Monitor replication lag if applicable
6. Set a time limit: if not done in X minutes, abort and retry later

Schema Migrations vs Data Migrations

Schema migrations change structure: tables, columns, indexes, constraints. Data migrations change values: backfilling, transforming, cleaning. They have different characteristics and should often be separate.

Why separate them

Data migration patterns

Backfilling computed values: When adding a column that should be computed from existing data (e.g., full_name from first_name and last_name), add the column in a schema migration, then backfill in a separate data migration or background job.

Format transformation: Converting phone numbers to E.164 format, splitting full addresses into components, normalising email addresses. These require row-by-row processing with validation. Run as background jobs with progress tracking.

Data cleanup: Fixing inconsistencies, removing orphaned records, correcting invalid values. Often best done as one-time scripts outside the migration system, since they're not idempotent and don't need to run in new environments.

Idempotency matters

Schema migrations are inherently idempotent: the migration system tracks what's applied. Data migrations should be explicitly idempotent:

Rollback Strategies

Every migration should have a rollback plan before it runs. "Just restore from backup" is not a rollback plan. It's a disaster recovery plan, and it means downtime.

Rollback by reversal

The "down" migration reverses the "up" migration. This works for additive changes:

• Added a column: Drop it
• Added a table: Drop it
• Added an index: Drop it
• Added a constraint: Drop it

Write the down migration at the same time as the up migration. Test it. A down migration that's never been run is a liability, not a safety net.

Rollback with data preservation

Some rollbacks would lose data. Before dropping a column or table, consider whether you might need that data back:

• Create a backup table before dropping: CREATE TABLE orders_status_backup AS SELECT id, status FROM orders;
• Export to a file: COPY (SELECT id, status FROM orders) TO '/tmp/orders_status.csv';
• Keep the column longer than you think necessary (weeks, not days)

Rollback-incompatible changes

Some changes can't be cleanly rolled back:

• Data transformations: Converting UK postcodes to uppercase destroys the original case. Store the original if you might need it.
• Data merges: Merging two columns into one loses the separation. Keep source columns until you're certain.
• Destructive updates: Overwriting values based on business logic. Take a snapshot first.

Testing rollbacks

Before running a migration in production:

1. Run the up migration in a test environment
2. Run the down migration
3. Run the up migration again
4. Verify the schema matches the original up result

Migrations that pass this cycle are safe. Migrations that fail it need fixing before they go anywhere near production.

Testing Migrations

Migration testing goes beyond "it works on my machine". Production has data volumes, edge cases, and constraints that development doesn't.

Test against production-like data

A migration that runs in 50ms on an empty table might run for 30 minutes on 10 million rows. A column that's always populated in test data might have NULLs in production. Test with realistic data volumes and variety.

Options for production-like test data:

• Anonymised production snapshots: Copy production, strip PII, use for testing
• Synthetic data at scale: Generate millions of rows with realistic distributions
• Staging environment: Maintain staging with production-like data volume

Timing estimation

Measure migration duration on production-like data. Record it. Use it to estimate production timing. Build in buffer: migrations often run slower on production due to concurrent load.

CI/CD integration

Run migrations as part of CI to catch errors early:

• Fresh database: Run all migrations from scratch. Catches syntax errors and ordering issues.
• Rollback test: Run up, then down, then up again. Catches incomplete down migrations.
• Seed data test: Run migrations, then seeders. Catches incompatibilities with expected data.

Pre-deployment checks

Before running migrations in production:

• ✓
  Review the SQL Understand exactly what statements will execute. No surprises.
• ✓
  Check for locks Identify operations that acquire table locks. Plan accordingly.
• ✓
  Estimate timing Know how long it will take. Set expectations.
• ✓
  Document the rollback Write down the exact steps to reverse if needed.
• ✓
  Coordinate with team Ensure no conflicting migrations. Check deployment schedule.

Coordinating Migrations with Deployments

Migrations don't exist in isolation. They're part of a deployment process that includes code changes, configuration updates, and often multiple services.

Single-service deployments

For a single application with its database, the typical flow:

For zero-downtime deployments, migrations run while old code is still serving traffic. The migration must be backward-compatible with old code.

Multi-service deployments

When multiple services share a database (not ideal, but common), migrations require coordination:

• Identify all services that touch the affected tables
• Ensure all services are compatible with both old and new schema during transition
• Deploy services in the correct order
• Use expand-contract to avoid simultaneous updates

Migration deployment automation

Automate migration execution as part of your deployment pipeline:

• Pre-deployment hook: Run migrations before new code starts
• Health checks: Verify database connectivity and migration success before proceeding
• Rollback triggers: Automatic rollback if deployment fails
• Notifications: Alert team when migrations run and complete

Never run migrations manually in production if you can avoid it. Manual execution introduces human error and lacks audit trails.

Common Mistakes

These mistakes appear repeatedly. Learn from others' pain.

Migration Tooling

Most frameworks provide migration support. Choose tools that track state in the database, support up and down operations, provide clear reporting, and integrate with your deployment pipeline.

For MySQL specifically, consider adding strong_migrations (Rails) or equivalent linting tools that catch dangerous operations before they reach production. These tools flag operations like adding indexes without CONCURRENTLY, changing column types, or removing columns without verification.

What You Get

Proper migration discipline yields compounding benefits over time.

• ✓
  Zero-downtime schema changes Users never see maintenance windows for routine changes
• ✓
  Confident rollbacks Every change is reversible because the down migration is tested
• ✓
  Environment parity Development, staging, and production schemas match exactly
• ✓
  Audit trail Every schema change is versioned, timestamped, and attributable
• ✓
  Team velocity Developers make schema changes without fear or coordination overhead
• ✓
  Predictable deployments Migration timing is known in advance, never a surprise

Schema evolution becomes routine. Changes that once required weekend maintenance windows deploy during business hours without anyone noticing.