Database Migrations Safely

Overview

Database migrations evolve your schema as your application changes. Unsafe migrations — like adding a non-nullable column to a large table or dropping a column still referenced by old code — can cause downtime, data loss, or deployment failures. The solution below covers safe migration patterns using Alembic (Python), Knex.js (JavaScript), and Flyway (Java), plus zero-downtime deployment strategies.

When to Use

Use this resource when:

• You’re deploying schema changes to a production database with live traffic. See Database Migrations for tooling overview.
• You need to add, rename, or remove columns without breaking running applications. See Input Validation for schema safety.
• You’re migrating data between tables or formats. See Data Validation for integrity checks.
• You want to establish a rollback plan before running any migration. See Retry Logic for recovery patterns.

Solution

Python (Alembic)

# migration: add new nullable column, backfill, then make non-nullable
from alembic import op
import sqlalchemy as sa
from sqlalchemy.sql import table, column

revision = "abc123"
down_revision = "xyz789"

def upgrade():
    # Step 1: Add as nullable so existing rows don't fail
    op.add_column("users", sa.Column("display_name", sa.String(255), nullable=True))

    # Step 2: Backfill with default value
    users = table("users", column("display_name"))
    op.execute(users.update().values(display_name="Unnamed User"))

    # Step 3: Now safe to make non-nullable
    op.alter_column("users", "display_name", nullable=False)

def downgrade():
    op.drop_column("users", "display_name")

JavaScript (Knex.js)

// migration: safe column rename using views or dual writes
exports.up = async function(knex) {
  // Phase 1: Add new column, keep old column
  await knex.schema.table("users", (table) => {
    table.string("full_name", 255).nullable();
  });

  // Phase 2: Backfill from old column
  await knex("users").whereNull("full_name").update({
    full_name: knex.ref("name"),
  });

  // Phase 3: Make non-nullable in a later deploy after all code writes new column
  // await knex.schema.table("users", (table) => { table.string("full_name").notNullable().alter(); });
};

exports.down = async function(knex) {
  await knex.schema.table("users", (table) => {
    table.dropColumn("full_name");
  });
};

Java (Flyway)

// V2__add_user_status.sql
-- Add enum column as text first, migrate data, then add CHECK constraint in V3
ALTER TABLE users ADD COLUMN status VARCHAR(20) NULL;

UPDATE users SET status = 'active' WHERE status IS NULL;

-- V3__enforce_user_status.sql (deployed in next release)
-- ALTER TABLE users ALTER COLUMN status SET NOT NULL;
-- ALTER TABLE users ADD CONSTRAINT chk_status CHECK (status IN ('active', 'inactive', 'banned'));

Explanation

Safe migrations follow the expand-contract pattern for any breaking change:

1. Expand: Add the new schema element (column, table, index) alongside the old one. Keep it optional.
2. Migrate: Deploy application code that writes to both old and new structures (dual-write).
3. Contract: Once all old code paths are gone, make the new structure required and remove the old one.

This pattern guarantees that any running instance of your app (including during rolling deploys) can read and write without errors.

Variants

Strategy	When to Use	Example
Expand-Contract	Renaming columns, changing types	Add full_name, dual-write, drop name
Online DDL (pt-online-schema-change)	MySQL large tables	Alter 100M+ row tables without locks
Concurrent index creation	PostgreSQL indexes	CREATE INDEX CONCURRENTLY to avoid table locks
Backfill in batches	Large table migrations	Update 10k rows per transaction to avoid long locks
Blue/Green deploy	Critical systems	Run new schema on green, switch traffic, then drop old

What Works

• Always make new columns nullable first: Existing rows must not fail during the migration.
• Backfill before making non-nullable: Update existing rows with sensible defaults before adding NOT NULL.
• Add indexes concurrently: On PostgreSQL, use CREATE INDEX CONCURRENTLY; on MySQL, use pt-online-schema-change or ALGORITHM=INPLACE.
• Keep migrations idempotent: Running the same migration twice should be safe.
• Version your migrations and test on a copy: Restore a production backup to a staging environment and run the full migration suite before production.

Common Mistakes

• Adding a non-nullable column without a default: Locks the table while populating every row, potentially for hours.
• Dropping a column still read by old code: Rolling deployments run old and new code simultaneously; the old code will crash.
• Running heavy migrations during peak traffic: Schedule schema changes during maintenance windows or use online DDL tools.
• No rollback plan: Every migration should have a tested downgrade or revert script.
• Ignoring lock timeouts: Long-running migrations can exceed statement timeouts and leave the database in a half-migrated state.

Additional Best Practices

6. Set lock_timeout before DDL. Prevents migrations from waiting indefinitely for locks:

SET lock_timeout = '10s';
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

7. Use statement_timeout for batch operations. Aborts backfills that run too long:

SET statement_timeout = '60s';

8. Run ANALYZE after large backfills. Updates planner statistics so queries choose optimal plans:

ANALYZE users;

9. Create indexes concurrently. CREATE INDEX CONCURRENTLY in PostgreSQL avoids blocking writes but cannot run inside a transaction.

10. Use feature flags for schema-dependent code. Decouple code deploys from schema changes:

if feature_flags.is_enabled("use_full_name"):
    display = user.full_name
else:
    display = user.name

Performance Tips

1. Batch backfills with LIMIT and sleep. Process 1,000-10,000 rows per batch with short pauses to minimize replication lag and lock contention.

2. Use CREATE INDEX CONCURRENTLY for all production indexes. Takes longer but doesn’t block writes. Monitor progress via pg_stat_progress_create_index.

3. Configure work_mem for migration sessions. Increase it for large batch operations:

SET work_mem = '256MB';

4. Monitor pg_stat_activity during migrations. Watch for long-running queries and lock waits:

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

5. Use pg_repack for table bloat. After large backfills, tables and indexes can become bloated. pg_repack rebuilds tables without exclusive locks.