migration-patterns

name	migration-patterns
description	Guide for database schema migrations with zero-downtime patterns, rollback strategies, data migrations, and migration testing. Use when the user writes database migrations, asks about schema changes in production, needs zero-downtime migration patterns, or plans rollback strategies. Trigger whenever database migration, schema change, or ALTER TABLE in production is discussed.

Migration Patterns

Write safe database migrations that can be applied to production with zero downtime, tested rollback paths, and separated schema and data changes.

When to Use

• User writes a database migration
• User needs to change schema without downtime
• User asks about safe column additions or removals
• User needs to migrate data between schemas
• User plans rollback strategies for migrations

Core Patterns

Zero-Downtime Column Addition

Add columns as nullable or with defaults. Never add a NOT NULL column without a default to an existing table with data.

-- Migration: Add new column
-- Step 1: Add nullable column (instant, no lock)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Step 2: Backfill data (can be done in batches)
UPDATE users SET phone = '' WHERE phone IS NULL;

-- Step 3: Add constraint (in a later migration, after backfill completes)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
ALTER TABLE users ALTER COLUMN phone SET DEFAULT '';

-- BAD: This locks the table and rewrites all rows on older PostgreSQL versions
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';

-- GOOD on PostgreSQL 11+: Default is stored in catalog, no rewrite
-- But still verify on your version and table size
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';

Zero-Downtime Column Removal

Remove columns in three phases across separate deployments to avoid breaking running code.

Phase 1 (code change): Stop reading/writing the column in application code
Phase 2 (migration):   DROP the column from the database

-- Migration file: 003_drop_legacy_status.sql

-- Phase 2: Safe to drop after Phase 1 is deployed
ALTER TABLE orders DROP COLUMN IF EXISTS legacy_status;

Never drop a column that application code still references. Deploy the code change first, then drop the column in the next release.

Zero-Downtime Column Rename

Renaming a column requires a multi-step migration to avoid breaking running code.

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Step 2: Backfill
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Step 3: Deploy code that writes to BOTH columns
-- Step 4: Deploy code that reads from new column
-- Step 5: Drop old column (next release)
ALTER TABLE users DROP COLUMN name;

Safe Index Creation

Create indexes concurrently to avoid locking the table during builds.

-- BAD: Locks table for writes during index creation
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- GOOD: Non-blocking index creation
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

-- Note: CONCURRENTLY cannot run inside a transaction block
-- Most migration tools support this with a special flag

Data Migration Pattern

Separate schema migrations from data migrations. Schema migrations change structure; data migrations transform content.

-- Schema migration: 004_add_user_roles.sql
CREATE TABLE roles (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE user_roles (
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, role_id)
);

-- Data migration: 004a_seed_roles.sql (separate file)
INSERT INTO roles (name) VALUES ('admin'), ('editor'), ('viewer');

-- Backfill from old column (run in batches for large tables)
INSERT INTO user_roles (user_id, role_id)
SELECT u.id, r.id
FROM users u
JOIN roles r ON r.name = u.role_name
ON CONFLICT DO NOTHING;

Batch processing for large data migrations:

-- Batch update to avoid long-running transactions
DO $$
DECLARE
  batch_size INT := 5000;
  rows_updated INT;
BEGIN
  LOOP
    WITH batch AS (
      SELECT id FROM users
      WHERE migrated = false
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    )
    UPDATE users SET
      full_name = first_name || ' ' || last_name,
      migrated = true
    FROM batch
    WHERE users.id = batch.id;

    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;

    COMMIT;
    RAISE NOTICE 'Updated % rows', rows_updated;
  END LOOP;
END $$;

Rollback Strategy

Every migration must have a tested reverse migration.

# Alembic example (Python)
def upgrade():
    op.add_column('users', sa.Column('phone', sa.String(20), nullable=True))
    op.create_index('idx_users_phone', 'users', ['phone'])

def downgrade():
    op.drop_index('idx_users_phone', 'users')
    op.drop_column('users', 'phone')

// Knex example (JavaScript)
exports.up = function(knex) {
  return knex.schema.alterTable('users', (table) => {
    table.string('phone', 20).nullable()
    table.index('phone', 'idx_users_phone')
  })
}

exports.down = function(knex) {
  return knex.schema.alterTable('users', (table) => {
    table.dropIndex('phone', 'idx_users_phone')
    table.dropColumn('phone')
  })
}

Migration Testing Checklist

# 1. Apply migration to a copy of production data
pg_dump production_db | psql test_db
migrate up

# 2. Verify schema matches expectations
psql test_db -c "\d+ users"

# 3. Run rollback
migrate down

# 4. Verify clean rollback
psql test_db -c "\d+ users"

# 5. Re-apply migration
migrate up

# 6. Run application test suite against migrated schema
npm test

Anti-Patterns

• No rollback migration: Every up needs a down. If you cannot reverse a migration, document the manual recovery procedure.
• Mixing schema and data migrations: Schema changes should be in one migration, data backfills in another. This makes rollbacks predictable.
• Long-running transactions: A migration that takes 30 minutes locks the table for 30 minutes. Use batched updates and concurrent index creation.
• Renaming columns directly: ALTER TABLE RENAME COLUMN breaks all running application code instantly. Use the add-copy-drop pattern instead.
• Not testing migrations on production-sized data: A migration that runs in 1 second on 1000 rows may take 30 minutes on 10 million rows. Always test with production-scale data.
• Deploying code and migration simultaneously: Deploy the migration first, verify it succeeded, then deploy the code that depends on it. Or deploy code that handles both old and new schemas.

Quick Reference

Operation	Safe Approach	Avoid
Add column	Add as nullable or with default	NOT NULL without default
Drop column	Remove from code first, then drop	Drop while code references it
Rename column	Add new, backfill, drop old	ALTER TABLE RENAME
Add index	CREATE INDEX CONCURRENTLY	CREATE INDEX (locks table)
Change type	Add new column, backfill, swap	ALTER COLUMN TYPE on large tables
Add NOT NULL	Add CHECK constraint first	ALTER SET NOT NULL on large tables
Drop table	Remove all references first	DROP TABLE while code uses it