Database Migration Strategies: Zero-Downtime Schema Changes

Database migrations are the highest-risk operation in application deployment. A schema change that locks tables can bring production to a grinding halt. A backward-incompatible migration requires coordinated rollbacks that are nearly impossible to execute cleanly. Zero-downtime migrations solve this by ensuring every schema change is safe to deploy and safe to revert—at any point in the deployment cycle.

The Expand-Contract Pattern

The core principle of zero-downtime migrations is decoupling the schema change from the application rollout. The expand-contract (also called parallel change) pattern breaks a migration into three phases:

Phase 1 (Expand): Add the new schema elements while keeping the old ones fully functional. Both the application and database support both the old and new structures.

-- Add new column without removing old one
ALTER TABLE users ADD COLUMN display_name VARCHAR(255);
CREATE INDEX idx_users_display_name ON users(display_name);

-- Backfill existing data in batches
UPDATE users SET display_name = username
WHERE display_name IS NULL
LIMIT 1000;

Phase 2 (Migrate): Deploy the updated application code that uses the new schema. The old schema elements remain intact so rollback doesn't break the database.

# Application reads: new field with fallback to old
user_display = user.display_name or user.username

# Application writes: both fields
user.display_name = new_name
user.username = new_name

Phase 3 (Contract): After confirming stability and sufficient time for all application instances to be updated, remove the old schema elements.

ALTER TABLE users DROP COLUMN username;

The expand-contract pattern works because it makes every commit backward-compatible. A deployment can be rolled back at any point without requiring a database rollback—the old code continues to work with the new schema because the old fields still exist.

Online Schema Changes

For large tables, standard ALTER TABLE locks the table for the duration of the operation. On a table with millions of rows, adding an index with a default value can take hours—during which no writes are possible.

Use online schema change tools. PostgreSQL's pgroll provides safe, reversible, online migrations:

# Start a migration in "format" mode (add new column)
pgroll add-column users display_name \
  --default="" --type=varchar(255)

# Migration runs in background, table remains writable
# Old code sees old schema, new code sees new schema

# Complete the migration (remove old version)
pgroll complete

For MySQL, GitHub's gh-ost (triggerless online schema change) replicates DDL as change events. It creates a shadow table, streams live changes to it, and atomically swaps tables when caught up:

gh-ost --host=localhost --database=app_db \
  --table=orders \
  --alter="ADD COLUMN discount DECIMAL(5,2) DEFAULT 0.00" \
  --execute

For any database that doesn't have native online DDL or tooling support, use the expand-contract pattern with application-level backfilling. It's slower but safe.

Renaming Columns and Tables Safely

Renaming is the most dangerous schema change because old and new code access the table simultaneously during a rolling deployment. Never use ALTER TABLE ... RENAME directly. Instead:

1. Add a new column with the new name
2. Write to both columns simultaneously in the application
3. Backfill the new column from the old
4. Deploy code that reads from the new column, writes to both
5. Remove the old column

-- Step 1: Add new column
ALTER TABLE orders ADD COLUMN status_new VARCHAR(50);

-- Step 2: Backfill
UPDATE orders SET status_new = status WHERE status_new IS NULL;

-- Step 3: Create synchronizing trigger
CREATE FUNCTION sync_order_status() RETURNS TRIGGER AS $$
BEGIN
  NEW.status_new := COALESCE(NEW.status_new, NEW.status);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_order_status
  BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION sync_order_status();

-- Step 4: After deployment, drop trigger and old column
DROP TRIGGER trg_sync_order_status ON orders;
ALTER TABLE orders DROP COLUMN status;
ALTER TABLE orders RENAME COLUMN status_new TO status;

Not-Null Constraints and Defaults

Adding a NOT NULL constraint to an existing column with null values requires a multi-step approach:

-- Step 1: Add column as nullable with a default
ALTER TABLE users ADD COLUMN timezone VARCHAR(50) DEFAULT 'UTC';

-- Step 2: Backfill existing nulls
UPDATE users SET timezone = 'UTC' WHERE timezone IS NULL;

-- Step 3: Add NOT NULL constraint (fast on already-filled column)
ALTER TABLE users ALTER COLUMN timezone SET NOT NULL;

The trick is the ALTER COLUMN ... SET NOT NULL on a column that already has no nulls is a metadata-only operation—it acquires only a very brief lock to validate the constraint.

Migration Rollback Planning

Every migration needs a rollback plan. For forward-only migrations (recommended), the rollback is deploying the previous application version—not running a "down" migration:

# Migration execution plan
version: 001_add_display_name
forward:
  - ALTER TABLE users ADD COLUMN display_name VARCHAR(255)
  - UPDATE users SET display_name = username
  - CREATE INDEX idx_display_name ON users(display_name)

rollback:
  - action: deploy_previous_app_version
  - if_revert_needed_in_7_days:
      - DROP INDEX idx_display_name
      - ALTER TABLE users DROP COLUMN display_name
    else:
      - keep_migration_in_place

The key principle: never run a down migration during an incident. Deploy the old app code first. The old code uses the old columns, which still exist because you followed the expand-contract pattern. Once the incident is resolved and you have breathing room, decide whether to contract the schema.

Run Zero-Downtime Migrations with SoniNow

Database migrations don't need to mean scheduled maintenance windows. With the right patterns and tooling, schema changes happen continuously, alongside application deploys. SoniNow's engineering team designs migration strategies that keep your database available through any schema evolution.