Zero-Downtime Database Migration: A Step-by-Step Tutorial

Why Zero-Downtime Matters

Maintenance windows are a relic of a slower era. Modern SaaS products serve global customers across time zones — there is no convenient window where nobody is using the system. Gartner estimates the average cost of IT downtime at EUR 5,600 per minute. For a database migration that takes 2 hours, that is EUR 672,000 in lost revenue, plus the reputational damage.

This tutorial covers the patterns and techniques for migrating database schemas and data without taking your application offline. The examples use PostgreSQL, but the patterns apply to MySQL, SQL Server, and other relational databases.

The Expand-Contract Pattern

The core principle of zero-downtime database changes: never make a change that is incompatible with the currently running application code. Instead, expand the schema to support both old and new code, deploy new code, then contract the schema to remove old columns.

Phase 1: Expand

Add new columns, tables, or indexes alongside existing ones. The old application code continues working because you have not removed or renamed anything it depends on.

Phase 2: Migrate

Deploy new application code that writes to both old and new structures, and reads from the new structure with fallback to the old.

Phase 3: Backfill

Populate the new structure with historical data from the old structure.

Phase 4: Contract

Once all data is in the new structure and all application instances are running the new code, remove the old columns/tables.

Example: Renaming a Column

Renaming a column seems simple but is one of the most dangerous zero-downtime changes. Here is the safe approach:

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

-- Step 2: BACKFILL - Copy existing data (do this in batches for large tables)
UPDATE users SET email_address = email WHERE email_address IS NULL;
-- For large tables, batch it:
-- UPDATE users SET email_address = email
--   WHERE email_address IS NULL AND id BETWEEN 1 AND 10000;

-- Step 3: Deploy application code that:
--   - Writes to BOTH email and email_address
--   - Reads from email_address with fallback to email

-- Step 4: Add trigger to keep columns in sync during transition
CREATE OR REPLACE FUNCTION sync_email_columns()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    IF NEW.email_address IS NOT NULL AND NEW.email IS DISTINCT FROM NEW.email_address THEN
      NEW.email := NEW.email_address;
    ELSIF NEW.email IS NOT NULL AND NEW.email_address IS DISTINCT FROM NEW.email THEN
      NEW.email_address := NEW.email;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER email_sync
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION sync_email_columns();

-- Step 5: Verify all application instances use the new column
-- Step 6: CONTRACT - Remove old column and trigger
DROP TRIGGER email_sync ON users;
DROP FUNCTION sync_email_columns();
ALTER TABLE users DROP COLUMN email;

Online Schema Changes for Large Tables

Standard ALTER TABLE in PostgreSQL acquires an ACCESS EXCLUSIVE lock on the table for certain operations (adding a column with a default value in older versions, adding a NOT NULL constraint). On a table with millions of rows, this lock can block all reads and writes for minutes.

Safe Operations (No Lock Issues in PostgreSQL 11+)

• ADD COLUMN without a default value or with a constant default (PG 11+)
• DROP COLUMN (marks column as invisible, does not rewrite table)
• CREATE INDEX CONCURRENTLY
• ALTER COLUMN SET/DROP DEFAULT

Dangerous Operations (Require Workarounds)

• ADD COLUMN ... NOT NULL — Use: add nullable column, backfill, add constraint with NOT VALID, then validate
• ALTER COLUMN TYPE — Use: add new column, backfill, swap reads, drop old column
• CREATE INDEX (without CONCURRENTLY) — Always use CONCURRENTLY

-- Safe way to add a NOT NULL column to a large table
-- Step 1: Add nullable column with default
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- Step 2: Backfill in batches (avoid long-running transactions)
DO $$
DECLARE
  batch_size INT := 10000;
  max_id BIGINT;
  current_id BIGINT := 0;
BEGIN
  SELECT MAX(id) INTO max_id FROM orders;
  WHILE current_id < max_id LOOP
    UPDATE orders
    SET status = CASE
      WHEN completed_at IS NOT NULL THEN 'completed'
      WHEN cancelled_at IS NOT NULL THEN 'cancelled'
      ELSE 'pending'
    END
    WHERE id > current_id AND id <= current_id + batch_size
      AND status IS NULL;

    current_id := current_id + batch_size;
    COMMIT;
    PERFORM pg_sleep(0.1); -- Brief pause to reduce load
  END LOOP;
END $$;

-- Step 3: Add NOT NULL constraint without full table scan
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;

-- Step 4: Validate constraint (scans table but does not hold lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

-- Step 5: Convert to proper NOT NULL (instant, since constraint exists)
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;

Data Migration Between Databases

Migrating data between database engines (e.g., MySQL to PostgreSQL, or self-managed to managed RDS) requires a different approach. The pattern is dual-write with cutover:

Step 1: Set Up Replication

Use Change Data Capture (CDC) to replicate changes from the source database to the target in real-time:

• AWS DMS (Database Migration Service): Managed CDC for most database engine combinations. Handles schema conversion and ongoing replication.
• Debezium: Open-source CDC platform that reads database transaction logs and streams changes to Kafka.
• pg_logical / BDR: PostgreSQL-native logical replication for PostgreSQL-to-PostgreSQL migrations.

Step 2: Validate Data Consistency

Before cutting over, validate that the target database matches the source:

// Data consistency validation script
async function validateMigration(source: Database, target: Database) {
  const tables = await source.query(
    "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'"
  );

  for (const { table_name } of tables) {
    // Row count comparison
    const sourceCount = await source.query(`SELECT COUNT(*) FROM ${table_name}`);
    const targetCount = await target.query(`SELECT COUNT(*) FROM ${table_name}`);

    if (sourceCount !== targetCount) {
      console.error(`Row count mismatch in ${table_name}: source=${sourceCount}, target=${targetCount}`);
    }

    // Checksum comparison (sample for large tables)
    const sourceChecksum = await source.query(
      `SELECT MD5(STRING_AGG(t::TEXT, '')) FROM (
        SELECT * FROM ${table_name} ORDER BY id LIMIT 10000
      ) t`
    );
    const targetChecksum = await target.query(
      `SELECT MD5(STRING_AGG(t::TEXT, '')) FROM (
        SELECT * FROM ${table_name} ORDER BY id LIMIT 10000
      ) t`
    );

    if (sourceChecksum !== targetChecksum) {
      console.error(`Data mismatch in ${table_name}`);
    }
  }
}

Step 3: Cut Over

The cutover window should be seconds, not minutes:

1. Stop writes to the source database (set it to read-only or pause the application write path)
2. Wait for CDC replication to catch up (typically <5 seconds)
3. Run final consistency validation
4. Update application connection strings to point to the target database
5. Resume writes

With proper preparation, the actual write-outage window is 5-30 seconds, not hours.

Schema Migration Tools

Use migration tools that support zero-downtime patterns:

• strong_migrations (Ruby/Rails) — Catches unsafe migrations and suggests safe alternatives
• django-migration-linter — Same concept for Django
• Flyway / Liquibase — Database version control with migration script management
• gh-ost (MySQL) — GitHub's online schema migration tool for MySQL
• pgroll — Zero-downtime, reversible schema migrations for PostgreSQL

Testing Your Migration

Never run a migration in production without rehearsal:

1. Clone production data to staging — Use a recent backup or snapshot. Test with production-scale data, not a 100-row dev database.
2. Run the migration under load — Use a load testing tool to simulate production traffic during the migration.
3. Measure lock duration — Use pg_stat_activity to monitor lock waits during the migration.
4. Validate rollback — Practice the rollback procedure. Know exactly how to undo every step.
5. Time everything — The backfill of 10 million rows that takes 5 minutes in staging will take 50 minutes in production with concurrent traffic.

Get Expert Help

Database migrations are among the highest-risk engineering operations. A mistake can cause data loss or extended downtime. If you are planning a database migration — whether schema changes, engine changes, or cloud migration — our engineering team has executed zero-downtime migrations on databases ranging from 50 GB to 5 TB. Reach out for a migration planning session.