Database Migration Strategies: How to Evolve Your Schema Safely

Why Migrations Break Production

Database migrations are among the riskiest operations in software deployment. Unlike application code, database changes are often irreversible and affect live data. A poorly planned migration can lock tables for minutes, break running queries, or corrupt data in ways that are difficult to detect until it is too late.

Yet schema evolution is inevitable. Business requirements change, performance needs shift, and data models must adapt. The goal is not to avoid migrations but to perform them safely, predictably, and with the ability to roll back.

Version-Controlled Migration Files

Every schema change should exist as a versioned migration file committed to your repository. Tools like Flyway, Liquibase, and Drizzle Kit manage migration state through a dedicated table in the database.

-- Drizzle Kit generates files like: 0001_add_users_table.sql
-- Migration tracking table (created automatically)
CREATE TABLE __drizzle_migrations (
  id         SERIAL PRIMARY KEY,
  hash       TEXT NOT NULL UNIQUE,
  created_at BIGINT
);

-- Example migration: 0005_add_profile_columns.sql
ALTER TABLE users
  ADD COLUMN bio          TEXT,
  ADD COLUMN avatar_url   TEXT,
  ADD COLUMN display_name VARCHAR(100);

-- Create index concurrently to avoid locking the table
CREATE INDEX CONCURRENTLY idx_users_display_name
  ON users(display_name) WHERE display_name IS NOT NULL;

The key rule: never modify a migration file that has already been applied to production. Instead, write a new migration that corrects or reverses the previous change.

Expand-Contract Pattern for Zero-Downtime Migrations

The most reliable zero-downtime migration technique follows three phases: Expand, Migrate, Contract.

Phase 1 — Expand: Add the new column or table alongside the old one. Deploy application code that writes to both.

-- Migration step 1: Add new column (nullable, so no lock on insert)
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Application writes to both columns simultaneously:
-- UPDATE users SET name = $1, full_name = $1 WHERE id = $2

Phase 2 — Migrate: Backfill existing rows in small batches to avoid locking.

-- Backfill in batches of 1000 to avoid long locks
DO $$
DECLARE
  last_id UUID := '00000000-0000-0000-0000-000000000000';
BEGIN
  LOOP
    UPDATE users
    SET full_name = name
    WHERE id IN (
      SELECT id FROM users
      WHERE full_name IS NULL AND id > last_id
      ORDER BY id LIMIT 1000
    )
    RETURNING id INTO last_id;

    EXIT WHEN NOT FOUND;
    PERFORM pg_sleep(0.05);  -- Throttle to avoid I/O spikes
  END LOOP;
END $$;

Phase 3 — Contract: Once all rows are backfilled and the application no longer reads the old column, drop it.

-- Migration step 3: Remove old column after verification
ALTER TABLE users DROP COLUMN name;
-- Add NOT NULL constraint now that all rows are populated
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

Handling Dangerous DDL Operations

Some operations acquire an Access Exclusive Lock that blocks all reads and writes:

• ALTER TABLE ... ADD COLUMN ... DEFAULT ... (PostgreSQL < 11)
• ALTER TABLE ... SET NOT NULL
• CREATE INDEX (without CONCURRENTLY)

Safe alternatives:

-- UNSAFE: Rewrites the entire table (PostgreSQL < 11)
ALTER TABLE orders ADD COLUMN priority INT DEFAULT 0;

-- SAFE on PostgreSQL 11+: Instant metadata-only change
ALTER TABLE orders ADD COLUMN priority INT;
UPDATE orders SET priority = 0 WHERE priority IS NULL;
ALTER TABLE orders ALTER COLUMN priority SET DEFAULT 0;
ALTER TABLE orders ALTER COLUMN priority SET NOT NULL;

-- UNSAFE: Blocks all reads/writes while building index
CREATE INDEX idx_orders_status ON orders(status);

-- SAFE: Runs without locking (takes longer but non-blocking)
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

Rollback Planning

Every migration should have a documented rollback plan before it is applied.

-- Migration: 0010_add_subscription_tier.sql
ALTER TABLE users ADD COLUMN subscription_tier VARCHAR(20) DEFAULT 'free';
CREATE INDEX CONCURRENTLY idx_users_tier ON users(subscription_tier);

-- Rollback: 0010_rollback.sql (apply this if migration must be reversed)
DROP INDEX CONCURRENTLY IF EXISTS idx_users_tier;
ALTER TABLE users DROP COLUMN IF EXISTS subscription_tier;

Test the rollback script in a staging environment before every production migration.

Design Migrations Before You Write Code

Erdmates lets you model your target schema visually and generate the corresponding DDL. Sharing the ERD diagram with your team before executing migrations reduces surprises and makes it easier to reason about the expand-contract phases needed for each change.

Conclusion

Safe database migrations require version control, the expand-contract pattern, batch backfills, non-blocking DDL alternatives, and a tested rollback plan for every change. Treating migrations with the same care as application code — review, test, deploy incrementally — is the difference between a routine maintenance window and a production incident.