Database Migrations in CD Pipelines

Application code can roll back in seconds. Schema changes can take hours to reverse — if they’re reversible at all. That asymmetry makes database migrations the hardest part of continuous deployment.

The core tension: CD assumes stateless, independent deployments. Databases are stateful and shared. Your application runs across multiple pods that can be replaced atomically, but every instance talks to the same database. A migration that takes 200ms on your development dataset can lock tables for four minutes against 50 million production rows. During that window, writes queue up, connection pools exhaust, timeouts cascade, and users see errors.

I’ve watched teams debate mid-incident: do we wait out the lock, or roll back and make things worse? Neither option is good once you’re in that situation. The answer is to never get there — to treat migrations as a distinct deployment concern with their own safety patterns, rollback strategies, and monitoring.

This article covers why migrations fail, how to decompose risky changes into safe ones, and how to build pipelines that make zero-downtime the default.

Why Migrations Break Deployments

The Lock Problem

Most DDL operations acquire exclusive locks. While you hold that lock, every other query on that table waits — reads, writes, everything. On a small table, nobody notices. On a table with tens of millions of rows, the lock can persist for minutes while the database rewrites data or rebuilds indexes.

Here’s what happens when you run ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active' on older PostgreSQL (pre-11):

During steps 1-3, every application thread trying to touch that table blocks. Connection pools fill up with waiting queries. Those queries eventually timeout. The timeouts cascade through your application as dependent operations fail. Users see errors.

Modern PostgreSQL (v11+) made some operations instant — adding a nullable column or a column with a default no longer rewrites the table. But plenty of operations still require rewrites: changing column types, adding NOT NULL constraints to existing columns, certain index operations. MySQL has similar behavior without online schema change tools.

The Compatibility Window Problem

Rolling deployments mean old and new code run simultaneously. During a typical Kubernetes deployment, you might have 15 minutes where half your pods run v1 and half run v2. Your schema needs to work with both versions throughout that window.

The timing creates a catch-22:

The solution is that every schema change must be backward-compatible. The schema after your migration must work with both the old application version and the new one. This constraint drives most of the patterns in this article.

Common Migration Failures

These are the failure modes I see repeatedly:

The theme across all of these: migrations fail when they assume atomic, instantaneous changes in a system that’s actually gradual and concurrent. The fix is always some form of phased rollout — expand first, then contract.

The Expand-Contract Pattern

Pattern Overview

The expand-contract pattern (sometimes called parallel change) is the fundamental technique for zero-downtime schema changes. Instead of making one risky change, you decompose it into multiple safe changes that maintain backward compatibility at every step.

The pattern has three phases:

Let’s trace through a concrete example: renaming a column from email to email_address. A direct ALTER TABLE RENAME COLUMN would break every query referencing the old name instantly. Here’s the expand-contract approach:

Total time: 2-3 weeks for a “simple” rename. This is why you think carefully before renaming columns in production.

Implementation Example

Here’s the concrete code for each phase. First, the expand migration:

-- Migration 001: Expand - Add new column
-- Safe: Adding nullable column is instant in PostgreSQL 11+

ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- Create index concurrently (doesn't lock table)
CREATE INDEX CONCURRENTLY idx_users_email_address ON users(email_address);

// Application code during expand phase
// Writes to BOTH columns, reads from OLD column

class UserRepository {
  async createUser(data: CreateUserInput): Promise<User> {
    const result = await db.query(`
      INSERT INTO users (name, email, email_address)
      VALUES ($1, $2, $2)
      RETURNING *
    `, [data.name, data.email]);

    return this.mapToUser(result.rows[0]);
  }

  async updateEmail(userId: string, newEmail: string): Promise<void> {
    // Write to both columns
    await db.query(`
      UPDATE users
      SET email = $1, email_address = $1
      WHERE id = $2
    `, [newEmail, userId]);
  }

  async getUser(userId: string): Promise<User> {
    // Still read from old column during expand
    const result = await db.query(`
      SELECT id, name, email FROM users WHERE id = $1
    `, [userId]);

    return this.mapToUser(result.rows[0]);
  }
}

-- Migration 002: Backfill existing data
-- Run as background job, not blocking migration

-- For small tables:
UPDATE users SET email_address = email WHERE email_address IS NULL;

-- For large tables (millions of rows), batch it:
DO $$
DECLARE
  batch_size INT := 10000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET email_address = email
    WHERE id IN (
      SELECT id FROM users
      WHERE email_address IS NULL
      LIMIT batch_size
    );

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

    -- Small sleep to reduce load
    PERFORM pg_sleep(0.1);

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

-- Migration 003: Contract - Remove old column
-- Only run AFTER all application code uses email_address

-- First, verify no code uses old column (monitoring/logs)
-- Then drop:
ALTER TABLE users DROP COLUMN email;

Some teams use database views as an additional compatibility layer during migrations. A view can expose the old column name while the underlying table uses the new name, letting legacy code work without modification. Feature flags at the application layer can also control which schema path code takes, though this adds complexity and should be cleaned up once the migration completes.

Safe Migration Patterns

Now that we’ve covered the fundamental pattern, here’s a quick reference for common schema changes. Each pattern follows expand - contract principles, but some operations have database-specific shortcuts that are safe to use directly.

Adding Columns

Adding columns is the most common migration, and modern databases have made the safe path easy:

-- ✅ SAFE: Adding nullable column (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Instant, no table rewrite

-- ✅ SAFE: Adding column with default (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
-- Instant, default stored in catalog not data

-- ❌️ DANGEROUS: Adding NOT NULL without default
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL;
-- Fails if table has data, or requires table rewrite

-- ✅ SAFE: Add nullable first, then add constraint
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
UPDATE users SET phone = 'unknown' WHERE phone IS NULL;
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

The key insight: PostgreSQL 11+ and recent MySQL versions store defaults in the catalog rather than rewriting every row. Adding a nullable column or a column with a default is now instant regardless of table size. But adding NOT NULL without a default still requires a table scan.

Removing Columns

Removal is where teams most often skip the safety steps. The column seems unused, so why not just drop it?

-- ❌️ DANGEROUS: Direct drop while code might use it
ALTER TABLE users DROP COLUMN legacy_field;

-- ✅ SAFE: Expand-contract removal

-- Step 1: Stop code from using the column (code deploy)
-- Step 2: Wait for all old pods to terminate
-- Step 3: Verify column is unused (query logs)
-- Step 4: Drop column
ALTER TABLE users DROP COLUMN legacy_field;

The “verify column is unused” step is critical. I’ve seen teams drop columns that were still referenced by scheduled jobs, reporting queries, or that one microservice nobody remembered existed. Query your database logs or enable query sampling before the drop.

Renaming Columns

There’s no safe way to rename a column in one step. The moment the name changes, every query using the old name fails.

-- ❌️ DANGEROUS: Direct rename
ALTER TABLE users RENAME COLUMN email TO email_address;
-- Breaks all code instantly

-- ✅ SAFE: Expand-contract rename (3 deployments)

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

-- Deploy 2: Application writes to both, reads from old
-- Backfill: UPDATE users SET email_address = email WHERE email_address IS NULL;

-- Deploy 3: Application reads from new
-- After verification: ALTER TABLE users DROP COLUMN email;

Yes, this means a “simple” rename takes three separate deployments spread over days or weeks. That’s the cost of zero-downtime schema changes. Factor this into your planning when naming columns — getting it right the first time saves significant effort later.

Changing Column Types

Type changes combine the risks of renames (code compatibility) with the risks of rewrites (locking). They’re the highest-risk common migration.

-- ❌️ DANGEROUS: Direct type change
ALTER TABLE orders ALTER COLUMN amount TYPE DECIMAL(10,2);
-- May require table rewrite, blocks writes

-- ✅ SAFE: Expand-contract type change

-- Step 1: Add new column with new type
ALTER TABLE orders ADD COLUMN amount_decimal DECIMAL(10,2);

-- Step 2: Dual-write in application
-- Step 3: Backfill with conversion
UPDATE orders SET amount_decimal = amount::DECIMAL(10,2) WHERE amount_decimal IS NULL;

-- Step 4: Switch reads to new column
-- Step 5: Drop old column
ALTER TABLE orders DROP COLUMN amount;
ALTER TABLE orders RENAME COLUMN amount_decimal TO amount;

Adding Indexes

Index creation on large tables can lock writes for minutes or hours. Every database has a way to avoid this:

-- ❌️ DANGEROUS: Standard index creation
CREATE INDEX idx_users_email ON users(email);
-- Locks table for writes during creation

-- ✅ SAFE: Concurrent index creation (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Doesn't lock table, but takes longer
-- Note: Can't run inside a transaction

-- MySQL: Use pt-online-schema-change or gh-ost
-- pt-online-schema-change --alter "ADD INDEX idx_email (email)" D=mydb,t=users

-- Check index creation progress (PostgreSQL)
SELECT
  phase,
  round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "% done",
  round(tuples_done / 1000000.0, 1) AS "millions of tuples done"
FROM pg_stat_progress_create_index;

The tradeoff with concurrent index creation: it takes longer and uses more resources because it has to handle concurrent writes during the build. On a busy table, expect it to take 2-3x longer than a locking index creation.

Adding Constraints

Foreign key constraints are particularly tricky because they lock both tables and require scanning all existing data. The NOT VALID approach separates these concerns:

-- ❌️ DANGEROUS: Adding constraint that might fail
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id);
-- Fails if orphan data exists, locks both tables

-- ✅ SAFE: Validate data first, add constraint with NOT VALID

-- Step 1: Find violations
SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

-- Step 2: Fix or delete violations
DELETE FROM orders WHERE user_id NOT IN (SELECT id FROM users);

-- Step 3: Add constraint without validation (instant)
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 4: Validate constraint separately (can be slow but doesn't block)
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

The NOT VALID constraint enforces referential integrity for new rows immediately while allowing you to validate existing data as a separate, non-blocking operation.

Live Schema Change Tools

When native database features aren’t enough — particularly for MySQL or complex PostgreSQL alterations — dedicated schema migration tools let you modify large tables without locking. These tools work by creating a shadow copy of your table, applying the schema change to the copy, then atomically swapping the tables once the copy catches up with production writes.

PostgreSQL Tools

PostgreSQL has made significant progress with native non-blocking DDL support. Most common operations no longer require external tools:

For operations that still require ACCESS EXCLUSIVE locks, pgroll from Xata automates the expand-contract pattern. It creates triggers to dual-write during migrations and provides CLI commands for progressive rollout. The tradeoff: it’s a newer tool with a smaller community than the MySQL alternatives.

MySQL Tools

MySQL’s native DDL support is more limited than PostgreSQL’s, making external tools essential for large tables. Two tools dominate:

Both tools follow the same general strategy:

The difference is in step 3: pt-online-schema-change uses database triggers to capture writes, while gh-ost streams from the MySQL binlog. The binlog approach has less overhead but requires more infrastructure access.

Using gh-ost

GitHub developed gh-ost (GitHub Online Schema Transmogrifier) after running into limitations with pt-online-schema-change at scale. The tool has become the de facto standard for MySQL schema changes at companies running large databases — it’s battle-tested against tables with billions of rows.

What makes gh-ost particularly useful in production: you can pause and resume migrations, throttle based on replica lag or server load, and even test the migration without actually cutting over. The --dry-run and --test-on-replica modes let you validate that a migration will complete successfully before touching your primary.

Here’s a typical gh-ost invocation for adding a column:

#!/bin/bash
# Add column to large MySQL table without locking

gh-ost \
  --host=mysql-primary.example.com \
  --database=myapp \
  --table=users \
  --alter="ADD COLUMN phone VARCHAR(20)" \
  --allow-on-master \
  --chunk-size=1000 \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=100 \
  --execute

# Options explained:
# --chunk-size: Rows to copy per iteration
# --max-load: Pause if this load threshold exceeded
# --critical-load: Abort if this load threshold exceeded
# --execute: Actually run (vs --dry-run)

# Monitor progress:
# gh-ost creates a socket file for control
echo "status" | nc -U /tmp/gh-ost.myapp.users.sock

The --max-load and --critical-load flags are where gh-ost shines. If your database starts struggling (threads piling up, replica lag increasing), gh-ost automatically backs off. If things get critical, it aborts entirely rather than making the situation worse. This self-throttling behavior is what makes it safe to run during business hours on production databases.

CI/CD Pipeline Integration

The patterns we’ve discussed work manually, but the real value comes from encoding them into your deployment pipeline. A well-designed pipeline makes safe migrations the default path and forces dangerous ones through review gates.

Migration Pipeline Design

The key insight is that migrations and application deploys are different operations with different risk profiles and rollback characteristics. Your pipeline should treat them that way.

The flow handles three scenarios: code-only changes skip migration steps entirely, safe migrations run automatically before application deployment, and potentially dangerous migrations pause for human review. This balances velocity (most deploys are code-only) with safety (risky changes get scrutiny).

Separating Migration and Application Deploys

In GitHub Actions, you can detect whether a PR includes migrations and route accordingly. The dorny/paths-filter action watches for changes in your migrations directory and sets output variables that control which jobs run.

name: Deploy

on:
  push:
    branches: [main]

jobs:
  detect-changes:
    runs-on: ubuntu-latest
    outputs:
      has_migrations: ${{ steps.changes.outputs.migrations }}
      has_code: ${{ steps.changes.outputs.code }}
    steps:
      - uses: actions/checkout@v4
      - uses: dorny/paths-filter@v2
        id: changes
        with:
          filters: |
            migrations:
              - 'migrations/**'
            code:
              - 'src/**'
              - 'package.json'

  run-migrations:
    needs: detect-changes
    if: needs.detect-changes.outputs.has_migrations == 'true'
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Safety check migrations
        run: python ./scripts/check-migration-safety.py
      - name: Run migrations
        run: npm run migrate
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
      - name: Verify migration
        run: npm run migrate:verify

  deploy-application:
    needs: [detect-changes, run-migrations]
    if: |
      always() &&
      needs.detect-changes.outputs.has_code == 'true' &&
      (needs.run-migrations.result == 'success' || needs.run-migrations.result == 'skipped')
    runs-on: ubuntu-latest
    steps:
      - name: Deploy to Kubernetes
        run: kubectl apply -f k8s/

The deploy-application job’s conditional is the interesting part: it runs if there’s code to deploy and either migrations succeeded or there weren’t any migrations to run. This ensures we never deploy application code if a migration failed.

Migration Safety Checks

Automated safety checks can catch the most common mistakes before they reach production. This script scans migration files for patterns that typically require the expand-contract pattern:

#!/usr/bin/env python3
# Use this in CI to gate migrations that need human review.
from __future__ import annotations

from pathlib import Path
import re
import sys

MIGRATION_DIR = Path("./migrations")
UNSAFE_PATTERNS = [
  r"DROP\s+COLUMN",
  r"DROP\s+TABLE",
  r"RENAME\s+COLUMN",
  r"ALTER\s+.*TYPE",
  r"NOT\s+NULL",
  r"DROP\s+INDEX",
  r"TRUNCATE",
]

def find_sql_files(directory: Path) -> list[Path]:
  return [path for path in directory.rglob("*.sql") if path.is_file()]

def file_has_pattern(path: Path, pattern: re.Pattern[str]) -> bool:
  try:
    content = path.read_text(encoding="utf-8", errors="ignore")
  except OSError:
    return False
  return bool(pattern.search(content))

def main() -> int:
  sql_files = find_sql_files(MIGRATION_DIR)
  if not sql_files:
    print("✅ No migration files found")
    return 0

  needs_review = False
  for raw_pattern in UNSAFE_PATTERNS:
    pattern = re.compile(raw_pattern, re.IGNORECASE)
    for sql_file in sql_files:
      if file_has_pattern(sql_file, pattern):
        print(f"⚠️  Found potentially unsafe pattern: {raw_pattern} in {sql_file}")
        needs_review = True
        break

  if needs_review:
    print("❌️ Migration requires human review before deployment")
    return 1

  print("✅ Migration passes automated safety checks")
  return 0

if __name__ == "__main__":
  sys.exit(main())

The script isn’t trying to be smart — it just flags keywords that warrant a second look. A DROP COLUMN might be the final step of a well-executed expand-contract migration, completely safe and intentional. Or it might be someone who didn’t realize the column was still in use. The script can’t tell the difference, but it can force the conversation.

Rollback Strategy

Not all migrations can be rolled back, and knowing the difference ahead of time determines your incident response options.

Migration Framework Configuration

Most full-stack frameworks and ORMs include migration tooling out of the box. They all solve the same fundamental problem — tracking which schema changes have been applied to which environments — but they approach it differently.

Two Paradigms

Most tools share a common CLI pattern: a command to generate a new migration file, a command to apply pending migrations, a command to check status, and optionally a command to rollback. The table below shows how this looks across popular frameworks.

When evaluating migration tooling, look for idempotency (can you safely run the same migration twice?), dry-run support (can you preview what SQL will execute?), transaction wrapping (does a failed migration leave your database in a broken state?), and rollback generation (does the tool create down migrations automatically, or do you write them manually?).

Greenfield Projects

If you’re building an application without a full-stack framework — maybe a microservice, a CLI tool with persistent state, or an API built on a minimal framework — adding migration tooling early pays dividends. It’s tempting to just run ALTER TABLE manually during early development, but that approach doesn’t survive the first production deployment.

The investment is small: Flyway, Liquibase, or a language-specific tool like Knex takes an hour to set up. What you get back is a repeatable deployment process, a history of every schema change, and the ability to spin up fresh environments that match production. The alternative — a wiki page of SQL commands to run in order, or worse, tribal knowledge about what the schema “should” look like — becomes technical debt that compounds with every team member and every environment.

Monitoring Migrations

Migrations that work perfectly in staging can still cause problems in production. The difference is scale: a backfill that takes 2 seconds against 10,000 rows might take 20 minutes against 10 million. Monitoring tells you when a migration is taking longer than expected, when it’s impacting application performance, or when you need to abort.

What to Watch During Migrations

Migration duration is your primary metric. Establish baselines in staging and alert if production takes significantly longer. A migration that usually completes in 30 seconds but is still running after 5 minutes deserves investigation.

Lock wait time reveals contention. In PostgreSQL, you can query pg_stat_activity to see queries waiting on locks:

SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

If you see application queries piling up with multi-second waits, your migration is blocking production traffic. This is exactly the situation you’re trying to avoid with the patterns in this article.

Application error rate should be part of your deployment dashboard regardless, but pay special attention during migrations. A spike in 500 errors or database connection timeouts immediately after a migration starts is a strong signal to abort.

Replication lag matters if you’re running read replicas. Large migrations can cause replicas to fall behind, which means your application might read stale data or — worse — data that doesn’t match the schema your code expects.

Pre-Migration Checklist

Before running any migration in production, verify the basics:

Conclusion

Database migrations don’t have to be the scariest part of your deployment pipeline. The patterns here — expand-contract changes, live schema change tools, separated migration jobs, automated safety checks — transform migrations from high-risk events into routine operations.

The core principles: treat migrations as a distinct deployment concern with their own safety gates. Maintain backward compatibility so old and new code can coexist during rolling deployments. Use non-blocking DDL operations wherever possible. Always have a rollback plan, even if that plan is “restore from backup.” And test migrations against production-like data volumes before they reach production — a migration that works on 10,000 rows can behave very differently against 10 million.