You are an expert in database migration patterns for evolving relational schemas safely, reversibly, and with minimal downtime across PostgreSQL, MySQL, and SQL Server.

## Key Points

1. **Expand** — add the new structure alongside the old (new column, new table)
2. **Migrate** — copy/transform data from old to new; update application to write to both
3. **Contract** — remove the old structure once all code and consumers have switched
1. **Every migration must be reversible** — always write a corresponding rollback (down) migration. If you cannot roll back, the migration needs more planning.
2. **Never mix schema changes and data changes in one migration** — separate DDL and DML into distinct migration steps for clarity and rollback safety.
3. **Test migrations against a production-size copy** — timing and locking behavior change dramatically with table size. A 2-second migration on dev might lock production for 20 minutes.
4. **Use batched updates for data backfills** — updating millions of rows in one transaction generates huge WAL, holds locks, and can fill temp disk.
5. **Add constraints as NOT VALID, then validate** — this splits the exclusive lock (brief) from the validation scan (long but less disruptive).
6. **Version control all migrations** — migrations are code. They belong in the repository with sequential numbering or timestamps.
7. **Never edit a migration that has been applied** — if a deployed migration needs correction, create a new migration. Editing history breaks checksums and causes drift.
8. **Run migrations in CI** — apply migrations to a test database in your pipeline to catch syntax errors and constraint violations before production.
- **Adding a column with a volatile DEFAULT on a large table** — in PostgreSQL <11, `ALTER TABLE ADD COLUMN ... DEFAULT now()` rewrites the entire table. Use a two-step add-then-backfill approach.

Migration Patterns — SQL

You are an expert in database migration patterns for evolving relational schemas safely, reversibly, and with minimal downtime across PostgreSQL, MySQL, and SQL Server.

Overview

Schema migrations are the controlled process of changing database structure — adding tables, columns, indexes, constraints, or transforming data — as application requirements evolve. In production environments, migrations must be backward-compatible, reversible, and non-disruptive. A badly planned migration can lock tables for minutes, corrupt data, or cause deployment rollback failures.

Core Concepts

Migration Types

Type	Examples	Risk Level
Additive	Add table, add nullable column, add index	Low
Transformative	Rename column, change type, split table	Medium-High
Destructive	Drop column, drop table, remove constraint	High
Data	Backfill values, merge rows, fix corrupt data	Medium

Expand-Contract Pattern

The safest approach for non-trivial changes:

1. Expand — add the new structure alongside the old (new column, new table)
2. Migrate — copy/transform data from old to new; update application to write to both
3. Contract — remove the old structure once all code and consumers have switched

This is the foundation of zero-downtime migrations.

Migration Tool Landscape

Tool	Language	Features
Flyway	Java/CLI	Versioned SQL files, repeatable migrations
Liquibase	Java/CLI	XML/YAML/SQL changelogs, diffing
Alembic	Python	SQLAlchemy integration, autogenerate
golang-migrate	Go	SQL files, multiple DB support
dbmate	Go	Simple, framework-agnostic
Rails ActiveRecord	Ruby	Ruby DSL, rollback support
Knex.js	JavaScript	JS migration files, rollback
Prisma Migrate	TypeScript	Schema-driven, shadow database

Implementation Patterns

Adding a Column Safely

-- Step 1: Add nullable column (fast, no rewrite in PostgreSQL)
ALTER TABLE orders ADD COLUMN region text;

-- Step 2: Backfill in batches (avoid long locks)
UPDATE orders SET region = 'us-east' WHERE id BETWEEN 1 AND 10000 AND region IS NULL;
UPDATE orders SET region = 'us-east' WHERE id BETWEEN 10001 AND 20000 AND region IS NULL;
-- ... continue in batches

-- Step 3: Once backfilled, add NOT NULL (PostgreSQL 12+ validates without full scan if default is set)
ALTER TABLE orders ALTER COLUMN region SET DEFAULT 'unknown';
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

Renaming a Column (Zero Downtime)

Never rename a column in a single step in production. Use expand-contract:

-- Migration 1: Add new column
ALTER TABLE users ADD COLUMN display_name text;

-- Migration 2: Backfill
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- Application deploy: write to both columns, read from new

-- Migration 3: (after all code uses new column) Drop old column
ALTER TABLE users DROP COLUMN username;

Changing Column Type

-- DANGEROUS: direct ALTER can lock and rewrite the entire table
-- ALTER TABLE events ALTER COLUMN payload TYPE jsonb;

-- SAFE: expand-contract approach
-- Step 1: Add new column
ALTER TABLE events ADD COLUMN payload_v2 jsonb;

-- Step 2: Backfill using a batch script
UPDATE events SET payload_v2 = payload::jsonb
WHERE id BETWEEN 1 AND 10000 AND payload_v2 IS NULL;

-- Step 3: Application writes to both columns
-- Step 4: Switch reads to new column
-- Step 5: Drop old column
ALTER TABLE events DROP COLUMN payload;
ALTER TABLE events RENAME COLUMN payload_v2 TO payload;

Creating Indexes Without Downtime

-- PostgreSQL: CONCURRENTLY avoids exclusive table lock
CREATE INDEX CONCURRENTLY idx_orders_customer
  ON orders (customer_id);

-- If it fails partway, it leaves an INVALID index. Check and retry:
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indrelid = 'orders'::regclass;

-- Drop the invalid index and try again
DROP INDEX CONCURRENTLY idx_orders_customer;
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

Adding a Foreign Key Without Locking

-- PostgreSQL: add constraint as NOT VALID, then validate separately
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id) REFERENCES orders(id)
  NOT VALID;

-- VALIDATE acquires a less restrictive lock and scans in the background
ALTER TABLE order_items VALIDATE CONSTRAINT fk_order_items_order;

Table Partitioning Migration

-- Convert a large monolithic table to range-partitioned (PostgreSQL 10+)
-- Step 1: Create new partitioned table
CREATE TABLE events_partitioned (
  id bigint GENERATED ALWAYS AS IDENTITY,
  event_type text NOT NULL,
  created_at timestamptz NOT NULL,
  payload jsonb
) PARTITION BY RANGE (created_at);

-- Step 2: Create partitions
CREATE TABLE events_y2024_q1 PARTITION OF events_partitioned
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE events_y2024_q2 PARTITION OF events_partitioned
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Step 3: Migrate data in batches
INSERT INTO events_partitioned (event_type, created_at, payload)
SELECT event_type, created_at, payload
FROM events
WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';

-- Step 4: Swap names (brief lock)
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_partitioned RENAME TO events;

-- Step 5: Drop old table after verification period

Data-Only Migration (Backfill)

-- Batch update with progress tracking
DO $$
DECLARE
  batch_size int := 5000;
  affected int;
BEGIN
  LOOP
    UPDATE orders
    SET status_code = CASE status
      WHEN 'pending'   THEN 1
      WHEN 'active'    THEN 2
      WHEN 'completed' THEN 3
      WHEN 'cancelled' THEN 4
    END
    WHERE id IN (
      SELECT id FROM orders
      WHERE status_code IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );

    GET DIAGNOSTICS affected = ROW_COUNT;
    RAISE NOTICE 'Updated % rows', affected;
    EXIT WHEN affected = 0;

    PERFORM pg_sleep(0.1);  -- brief pause to reduce load
    COMMIT;
  END LOOP;
END $$;

Reversible Migration Template

-- UP
BEGIN;
ALTER TABLE products ADD COLUMN sku varchar(50);
CREATE UNIQUE INDEX CONCURRENTLY idx_products_sku ON products (sku);
COMMENT ON COLUMN products.sku IS 'Stock Keeping Unit - added in migration 047';
COMMIT;

-- DOWN
BEGIN;
DROP INDEX IF EXISTS idx_products_sku;
ALTER TABLE products DROP COLUMN IF EXISTS sku;
COMMIT;

Core Philosophy

Database migrations are the controlled evolution of your most critical stateful system. Unlike application code, which can be redeployed in seconds, a botched migration can lock tables, corrupt data, or require hours of recovery from backups. This asymmetry demands a discipline that application code does not: every migration must be planned for reversibility, tested against production-scale data, and designed to coexist with the currently running application code.

The expand-contract pattern is the foundation of zero-downtime schema changes. Instead of renaming a column in place (which breaks running code instantly), you add the new column alongside the old one, update the application to write to both, switch reads to the new column, and finally remove the old one. This multi-step process is slower than a single ALTER TABLE, but it guarantees that at every intermediate step, both the old and new versions of the application can run without errors. In a deployment pipeline with rolling updates, this guarantee is not optional.

Migrations are code and deserve the same rigor as application code: version control, code review, automated testing, and CI validation. A migration that passes syntax checks on a 100-row development database may lock a 100-million-row production table for twenty minutes because the timing behavior changes with scale. Testing against a production-sized copy — and measuring lock duration, WAL generation, and replication lag — is the only way to catch these problems before they become incidents.

Anti-Patterns

• Mixing DDL and DML in a single migration — combining schema changes (ALTER TABLE) with data changes (UPDATE) in one migration makes rollback nearly impossible and debugging a failure ambiguous. Separate structural changes from data transformations into distinct migration steps.

• Editing a migration that has already been applied — migration tools track applied migrations by filename and checksum. Editing a deployed migration breaks the checksums, causes the tool to refuse subsequent migrations, and creates schema drift between environments.

• Dropping a column before removing application references — the application is still reading the column when the migration runs. Always deploy the code change that removes column access first, wait for all instances to roll over, and then run the migration that drops the column.

• Running ALTER TABLE without CONCURRENTLY or NOT VALID — operations like CREATE INDEX and ADD CONSTRAINT take exclusive locks by default. On large tables, this blocks all reads and writes for the duration of the operation. Use CONCURRENTLY for indexes and NOT VALID with deferred VALIDATE for constraints.

• Backfilling millions of rows in a single transaction — one massive UPDATE generates enormous WAL, holds row locks, and can fill temp disk. Backfill in batches of a few thousand rows, committing between batches and pausing briefly to reduce load on the replication pipeline.

Best Practices

1. Every migration must be reversible — always write a corresponding rollback (down) migration. If you cannot roll back, the migration needs more planning.
2. Never mix schema changes and data changes in one migration — separate DDL and DML into distinct migration steps for clarity and rollback safety.
3. Test migrations against a production-size copy — timing and locking behavior change dramatically with table size. A 2-second migration on dev might lock production for 20 minutes.
4. Use batched updates for data backfills — updating millions of rows in one transaction generates huge WAL, holds locks, and can fill temp disk.
5. Add constraints as NOT VALID, then validate — this splits the exclusive lock (brief) from the validation scan (long but less disruptive).
6. Version control all migrations — migrations are code. They belong in the repository with sequential numbering or timestamps.
7. Never edit a migration that has been applied — if a deployed migration needs correction, create a new migration. Editing history breaks checksums and causes drift.
8. Run migrations in CI — apply migrations to a test database in your pipeline to catch syntax errors and constraint violations before production.

Common Pitfalls

• Adding a column with a volatile DEFAULT on a large table — in PostgreSQL <11, ALTER TABLE ADD COLUMN ... DEFAULT now() rewrites the entire table. Use a two-step add-then-backfill approach.
• Forgetting CONCURRENTLY for index creation — CREATE INDEX without CONCURRENTLY takes an exclusive lock. On a 100M-row table, this can block writes for minutes.
• Dropping a column that the application still reads — always deploy the code change that removes column access before the migration that drops the column.
• Long-running transactions during migration — an open transaction from before the migration can block the DDL statement. Monitor pg_stat_activity for long-running transactions before migrating.
• Implicit locks from foreign keys — adding a FK to a heavily written parent table can cause contention. Use NOT VALID + VALIDATE.
• Not accounting for replication lag — in replicated setups, schema changes propagate asynchronously. Ensure replicas have applied the migration before deploying code that depends on it.