Database Migration Strategies: Zero-Downtime Schema Changes and Data Backfill

Editorial Team · · 4 min read

The Problem With "Just Run the Migration"

Database migrations are one of those things that work fine in development and terrify everyone in production. Your staging database has 10,000 rows. Production has 500 million. That ALTER TABLE ADD COLUMN that took 200ms locally? It's going to lock the table for 45 minutes on prod.

Zero-downtime schema changes aren't about any single technique. They're about understanding what your database engine actually does when you modify a schema, and choosing the approach that minimizes disruption. Let's get specific.

Which ALTER TABLE Operations Lock in PostgreSQL

Not all schema changes are equally dangerous. In PostgreSQL 14+:

  • Adding a nullable column with no default: — near-instant, no table rewrite. Safe.
  • Adding a column with a volatile default: — table rewrite on older versions, instant on PG 11+. Check your version.
  • Adding a NOT NULL constraint: — full table scan to validate. Dangerous on large tables.
  • Creating an index: — locks writes by default. Use CREATE INDEX CONCURRENTLY instead.
  • Renaming a column: — instant metadata change, but your application code better handle both names during deployment.
  • Changing a column type: — almost always requires a table rewrite. Avoid if possible.

The general rule: anything that requires reading or rewriting existing rows is dangerous at scale. Metadata-only changes are safe.

The Expand-Contract Pattern

This is the workhorse strategy for zero-downtime migrations. Instead of making a breaking change in one step, you split it into three phases that can each be deployed independently.

Phase 1 — Expand: Add the new structure alongside the old one. Both old and new application code can work with the database.

Phase 2 — Migrate: Move data from old structure to new. Backfill in batches, not one giant UPDATE.

Phase 3 — Contract: Remove the old structure once all application code uses the new one.

Concrete example: renaming users.name to users.display_name.

-- Phase 1: Expand (deploy with code that reads from both columns)
ALTER TABLE users ADD COLUMN display_name TEXT;

-- Phase 2: Migrate (run as background job)
UPDATE users SET display_name = name
WHERE display_name IS NULL
LIMIT 10000;  -- repeat in batches

-- Add trigger to keep both columns in sync during transition
CREATE OR REPLACE FUNCTION sync_display_name()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.display_name IS NULL THEN
    NEW.display_name := NEW.name;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Phase 3: Contract (after all code reads display_name)
ALTER TABLE users DROP COLUMN name;

The key insight: each phase is independently deployable and reversible. If Phase 2 fails halfway through, you haven't broken anything — the old column still has all the data.

Batched Data Backfills

Never run a single UPDATE on millions of rows. It'll hold a massive write lock, bloat your WAL, and potentially cause replication lag that triggers alerts at 3 AM.

Instead, process in batches with a delay between them:

-- Backfill in chunks of 5000 rows with a 100ms pause
DO $$
DECLARE
  batch_size INT := 5000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET display_name = name
    WHERE id IN (
      SELECT id FROM users
      WHERE display_name IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );

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

    RAISE NOTICE 'Updated % rows', rows_updated;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

The FOR UPDATE SKIP LOCKED is important — it prevents the backfill from blocking normal application writes. If a row is currently being written to by the app, the backfill skips it and picks it up in the next batch.

For truly massive tables (billions of rows), consider running backfills off a read replica and applying the results to the primary, or using pg_repack for operations that would otherwise require a full table rewrite.

Online Schema Change Tools

For MySQL, pt-online-schema-change from Percona and gh-ost from GitHub are essential. They work by creating a shadow copy of the table, applying the schema change to the copy, then incrementally syncing data using triggers (pt-osc) or binlog replication (gh-ost).

gh-ost is generally preferred now because it doesn't use triggers — triggers add overhead to every write during the migration. The binlog-based approach has minimal impact on the source table.

gh-ost   --host=primary.db.internal   --database=myapp   --table=users   --alter="ADD COLUMN display_name VARCHAR(255)"   --chunk-size=1000   --max-load=Threads_running=25   --critical-load=Threads_running=50   --execute

The --max-load and --critical-load flags are gh-ost's killer feature. It monitors MySQL's thread count and automatically throttles or pauses the migration if the server gets overloaded. Set these based on your production baselines.

Migration Versioning and Rollback

Every migration framework (Flyway, Alembic, Rails migrations, Prisma Migrate) tracks which migrations have been applied. The problem is rollback — most teams write forward migrations but skip the down migration, which means rolling back requires manual intervention.

My recommendation: don't rely on down migrations. Instead, make every forward migration safe to keep even if you roll back the application code. If you add a column, the old code ignores it. If you add a constraint, make sure existing data already satisfies it before deploying the constraint.

This approach treats migrations as append-only. You never run a migration backward — you write a new forward migration that undoes whatever you need. It's simpler, safer, and matches how deployments actually work in production.

Testing Migrations Against Production-Like Data

The #1 cause of migration failures is testing against small datasets. A migration that takes 50ms on your dev database might take 2 hours on production.

Practical approaches:

  • Restore a production backup to a staging environment weekly. Run migrations there first and measure actual execution time.
  • For sensitive data, use a tool like pg_dump with --schema-only and then generate synthetic data at production scale.
  • Monitor replication lag during staging migrations — if the replica falls behind, production will too.

Honestly, the biggest win isn't any technical tool. It's having a migration review checklist that someone besides the author actually reads before the migration hits production.