What is the expand-contract pattern for database migrations?

The expand-contract pattern splits a breaking schema change into three phases. The expand phase adds new columns or tables alongside existing ones. The migrate phase backfills data and updates application code to write to both old and new structures. The contract phase removes the old columns or tables after all application instances use the new schema. Each phase is independently deployable and reversible.

How do you rename a database column without downtime?

Never rename a column directly in production. Instead, add the new column, deploy code that writes to both columns, backfill the new column from the old one, deploy code that reads from the new column, verify correctness, then drop the old column. This four-step process ensures both old and new application versions work correctly during the rolling deployment window.

Should database migrations run before or after application deployment?

Migrations that add structures should run before the application deployment so the new code finds the expected schema. Migrations that remove structures should run after the deployment is complete and verified, ensuring no running instance depends on the removed schema. This ordering is the foundation of zero-downtime migrations — expand before deploy, contract after deploy.

How do you handle large table migrations without locking?

Use online schema change tools like pt-online-schema-change for MySQL or pg_repack for PostgreSQL. These tools create a shadow table with the new schema, incrementally copy rows while capturing changes via triggers, then perform an atomic rename. For very large tables, batch the copy operation with throttling to limit impact on production query performance.

What is the safest way to add a NOT NULL column to a large table?

Add the column as nullable first, backfill it in batches with throttling, then add the NOT NULL constraint. In PostgreSQL 11 and later, adding a column with a constant default value is instant because the default is stored in the catalog rather than written to each row. However, adding a NOT NULL constraint still requires a full table scan to verify no nulls exist, so the backfill must complete before adding the constraint.

Database Migration Strategies for Zero-Downtime Deployments

Every production database migration carries risk. An ALTER TABLE that takes seconds in development can lock a table for minutes in production when it holds 200 million rows. A column rename that looks trivial breaks every application instance still running the previous version during a rolling deployment. The difference between a smooth migration and a production incident is not the migration tool — it is the deployment strategy.

Zero-downtime migrations require that every schema change is backward compatible with the currently running application version. This constraint eliminates most simple migration patterns and replaces them with multi-step processes that are individually safe. The complexity is not optional — it is the price of keeping the application available during deployments.

The Expand-Contract Pattern

Expand-contract is the foundational pattern for zero-downtime schema changes. Every breaking migration decomposes into three deployable steps: expand the schema to support both old and new structures, migrate the application and data, then contract the schema by removing the old structure.

Consider renaming a column from user_name to display_name. A direct rename breaks every query referencing user_name in any application instance that has not yet received the new code. The expand-contract approach avoids this entirely.

-- Step 1: EXPAND - Add new column (deploy migration)
ALTER TABLE users ADD COLUMN display_name VARCHAR(255);

-- Step 2: BACKFILL - Copy existing data
UPDATE users SET display_name = user_name WHERE display_name IS NULL;

-- Step 3: Deploy application code that writes to BOTH columns
-- and reads from display_name with fallback to user_name

-- Step 4: CONTRACT - Remove old column (after all instances updated)
ALTER TABLE users DROP COLUMN user_name;

The backfill in step 2 should run in batches for large tables. Processing all rows in a single transaction holds locks for too long and may cause replication lag in replica-based architectures. Batch sizes of 1,000 to 10,000 rows with a small delay between batches keep the impact on production queries manageable.

-- Batched backfill with throttling
DO $$
DECLARE
  batch_size INT := 5000;
  affected INT;
BEGIN
  LOOP
    UPDATE users
    SET display_name = user_name
    WHERE id IN (
      SELECT id FROM users
      WHERE display_name IS NULL
      LIMIT batch_size
    );
    GET DIAGNOSTICS affected = ROW_COUNT;
    EXIT WHEN affected = 0;
    PERFORM pg_sleep(0.1);  -- 100ms delay between batches
  END LOOP;
END $$;

Safe Schema Change Operations

Adding Columns

Adding a nullable column without a default value is always safe in PostgreSQL and MySQL. It acquires a brief metadata lock and returns immediately because no data needs to be written. Adding a column with a constant default value is also instant in PostgreSQL 11+ — the database stores the default in the system catalog and materializes it lazily when rows are read or updated.

Adding a column with a volatile default (like DEFAULT now()) rewrites the entire table because each row gets a different value. Avoid this on large tables. Instead, add the column as nullable, then backfill.

Dropping Columns

Never drop a column in the same deployment as the code change that stops using it. The old code is still running during the rolling deployment and will fail when it queries the missing column. Drop columns in a subsequent deployment after verifying no running instance references them. This is the "contract" phase of expand-contract, and it should always lag the code deployment by at least one full release cycle.

Adding Indexes

Standard CREATE INDEX locks the table against writes for the duration of the build. On a table with 100 million rows, this can take 30 minutes or more. Use CREATE INDEX CONCURRENTLY in PostgreSQL to build the index without write locks. The trade-off is that concurrent index creation takes longer and requires more disk space for the temporary sort files.

-- Safe: builds index without blocking writes
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Verify the index was created successfully
SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'users' AND indexname = 'idx_users_email';

Concurrent index creation can fail if a constraint violation or deadlock occurs during the build. A failed concurrent index leaves an invalid index that must be dropped before retrying. Always check for invalid indexes after concurrent creation.

Handling Large Table Migrations

Tables with hundreds of millions of rows require specialized tooling. PostgreSQL's ALTER TABLE acquires an ACCESS EXCLUSIVE lock for many operations, blocking all reads and writes until the operation completes. For tables that cannot tolerate any lock time, online schema change tools perform the migration without blocking.

For MySQL, pt-online-schema-change from Percona creates a shadow table, copies rows in chunks, captures changes via triggers, and atomically swaps the tables. For PostgreSQL, pg_repack achieves similar results. Both tools keep the original table available throughout the migration.

# pt-online-schema-change for MySQL
pt-online-schema-change \
  --alter "ADD COLUMN phone VARCHAR(20)" \
  --execute \
  --chunk-size=1000 \
  --max-lag=1s \
  --critical-load="Threads_running=50" \
  D=myapp,t=users

The --max-lag parameter pauses the copy when replication lag exceeds the threshold, preventing the migration from overloading replicas. The --critical-load parameter aborts the migration entirely if the server's thread count exceeds a safety limit. These guardrails make the tool safe for production use on busy databases, which is especially important in TypeScript applications where ORMs may generate unexpected query patterns during migrations.

Migration Ordering in CI/CD

The deployment pipeline must enforce a strict ordering: run expand migrations before deploying new application code, and run contract migrations after the deployment is verified. This ordering ensures the database schema always satisfies both the old and new application versions during the rolling deployment window.

# Deployment pipeline pseudocode
deploy:
  steps:
    # 1. Run expand migrations (additive changes only)
    - run: migrate --target expand-v42

    # 2. Deploy new application instances (rolling)
    - run: kubectl rollout restart deployment/api
    - run: kubectl rollout status deployment/api --timeout=300s

    # 3. Run smoke tests against new version
    - run: ./smoke-test.sh

    # 4. Run contract migrations (removal changes)
    #    Only after all old instances are terminated
    - run: migrate --target contract-v41

Separating migrations into expand and contract steps requires discipline. Every migration file should be tagged with its type, and the pipeline should fail if a contract migration runs before the corresponding code deployment. Migration frameworks like modern build tools can enforce this with linting rules that check migration content against a whitelist of safe operations for each phase.

Rollback Strategies

Not every migration can be rolled back, and planning for rollback is as important as planning the migration itself. Additive changes (adding columns, tables, or indexes) are safe to leave in place during a code rollback — the old code simply ignores the new structures. Destructive changes (dropping columns, changing types, removing constraints) cannot be undone by re-running a migration because the data is gone.

For destructive changes, create a backup before the migration and document the restoration procedure. For data-modifying migrations like backfills, store the original values in a temporary table or column until the migration is verified. This safety net adds storage overhead but provides a clear rollback path if the migration introduces data corruption.

-- Rollback-safe type change
-- Step 1: Add new column
ALTER TABLE orders ADD COLUMN total_cents BIGINT;

-- Step 2: Backfill (keep original for rollback)
UPDATE orders SET total_cents = ROUND(total_amount * 100)
WHERE total_cents IS NULL;

-- Step 3: Deploy code reading total_cents
-- Step 4: Verify for N days
-- Step 5: Only THEN drop total_amount
ALTER TABLE orders DROP COLUMN total_amount;

Testing Migrations Against Production Data

Migrations that pass in development often fail in production because development databases lack the data volume, variety, and edge cases of production. Testing migrations against a production-like dataset catches issues before they cause incidents.

The most reliable approach is to restore a recent production backup to a staging database and run the migration against it. Measure the execution time, lock duration, and replication impact. If the migration takes longer than your deployment window allows, restructure it into smaller steps or use an online schema change tool.

For privacy-sensitive data, use anonymized production snapshots. Tools like pgdump-anonymize or custom masking scripts strip personally identifiable information while preserving the data distribution, cardinality, and edge cases that affect migration behavior. An empty or synthetic dataset does not test the same code paths as real data and should not be trusted for migration validation.

Common Mistakes

Running migrations inside a transaction on large tables. In PostgreSQL, DDL statements are transactional by default, which means an ALTER TABLE inside a transaction holds its locks until the transaction commits. If the transaction includes slow operations like backfills, the lock duration extends from milliseconds to minutes.

Forgetting that NOT NULL constraints require a table scan. Adding NOT NULL to a column with 500 million rows scans every row to verify no nulls exist. This scan holds a lock and blocks writes. Add the constraint with NOT VALID to skip the scan, then validate it separately with VALIDATE CONSTRAINT, which only acquires a weaker SHARE UPDATE EXCLUSIVE lock.

-- Fast: skip validation during constraint creation
ALTER TABLE users ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Separate step: validate without blocking writes
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

Assuming all replicas are in sync. Migrations execute on the primary and propagate to replicas via replication. If a replica lags, application instances reading from that replica see the old schema while instances reading from the primary see the new schema. Monitor replication lag during migrations and pause the deployment if lag exceeds your threshold.

Zero-downtime migrations are more process than technology. The tools exist in every major database. The discipline to use them correctly — decomposing changes, ordering deployments, testing against production data, and planning rollbacks — is what separates teams that deploy confidently from teams that deploy anxiously. Build the process into your development workflow early, and it becomes second nature rather than a burden.