Zero-Downtime Database Migrations

Overview

The first time I ran a migration on a production Database with a live application, I added a column with a default value. It locked the table for 90 seconds. The site was down for 90 seconds. Nobody told me that on Postgres 11 and earlier, adding a column with a default rewrites the entire table.

Zero-downtime migrations are a discipline, not a tool. The tools — Flyway, Alembic, Liquibase — execute migrations. They don't stop you from writing one that takes an hour and locks everything.

The core constraint

During a deploy, both the old code and the new code run simultaneously. Old pods are being drained while new pods start. Any database change must work with both versions, at every moment of the rollout.

This "expand and contract" pattern is the foundation of every zero-downtime migration:

Each phase is a separate deploy. That's the cost. It's also what makes it safe.

Adding a column

On Postgres 11 and later, adding a nullable column with no default is a metadata-only operation. It's instant.

-- Instant on all modern versions
ALTER TABLE users ADD COLUMN phone_number text;

Adding a column with a default used to rewrite the table. Postgres 11 changed this — the default is stored in the catalog and applied on read. But "modern version" is the key phrase. If you're on Postgres 10 or earlier, or MySQL 8.0.12 or earlier, adding a column with a default will lock the table.

-- Safe on PG 11+, MySQL 8.0.13+
ALTER TABLE users ADD COLUMN status text NOT NULL DEFAULT 'active';

-- Safe on ALL versions: three steps
ALTER TABLE users ADD COLUMN status text;
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
-- Backfill in batches (see below), then:
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

The three-step version works everywhere and is what I use by default. The extra typing is cheaper than remembering which version introduced what.

Adding an index

-- Blocks writes for the duration of the build
CREATE INDEX idx_users_email ON users (email);

-- Doesn't block writes; takes longer
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY builds the index without locking out writes. It's slower, and if it fails it leaves an invalid index that you have to drop and retry. But it's the only acceptable option for a production table.

MySQL's equivalent is ALTER TABLE ... ADD INDEX ..., ALGORITHM=INPLACE, LOCK=NONE. Same idea, different syntax, and it doesn't work for all index types.

The failure modes:

• CONCURRENTLY cannot run inside a transaction. If your migration tool wraps everything in a transaction, disable that for this migration.
• If it fails, drop the invalid index first: DROP INDEX CONCURRENTLY idx_users_email;
• On a busy table, it can take hours. Schedule it during low traffic.

Renaming a column: don't

Renaming a column breaks the old code the moment the migration runs. There is no safe way to do it in a single step.

The expand-and-contract version:

-- Deploy 1: add new column, dual-write
ALTER TABLE users ADD COLUMN full_name text;

-- Application code: write to both columns
INSERT INTO users (name, full_name) VALUES ($1, $1);
UPDATE users SET name = $1, full_name = $1 WHERE id = $2;
-- And read from both with a fallback
SELECT COALESCE(full_name, name) AS display_name FROM users WHERE id = $1;

-- Deploy 2: backfill in batches (see below)

-- Deploy 3: switch reads to new column, stop writing old
-- (only after all old pods are gone)

-- Deploy 4: drop old column
ALTER TABLE users DROP COLUMN name;

Four deploys to rename a column. It feels excessive. It's also the only way to do it without downtime. Most teams batch the middle steps or accept a shorter window of downtime if the table is small.

Backfilling in batches

A single UPDATE users SET full_name = name on a 50-million-row table will run for hours, hold locks, and bloat the WAL. Do it in batches:

DO $$
DECLARE
    batch_size int := 10000;
    rows_updated int;
BEGIN
    LOOP
        UPDATE users
        SET full_name = name
        WHERE id IN (
            SELECT id FROM users
            WHERE full_name IS NULL
            LIMIT batch_size
        );

        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        RAISE NOTICE 'Updated % rows', rows_updated;

        EXIT WHEN rows_updated = 0;

        PERFORM pg_sleep(0.1);  -- let other transactions breathe
    END LOOP;
END $$;

The 100ms sleep between batches matters. Without it, the backfill saturates I/O and every other query slows down. With it, the backfill takes longer in wall-clock time but doesn't affect production traffic.

For very large tables, run the backfill as a background job that processes a chunk every minute, rather than a single long-running script.

Dropping a column

-- PostgreSQL: instant, marks column as dropped
ALTER TABLE users DROP COLUMN legacy_field;

-- But the space isn't reclaimed until you do:
VACUUM FULL users;  -- takes an exclusive lock, DO NOT run in production

Postgres marks the column as dropped, but the data stays on disk until a vacuum. VACUUM FULL rewrites the table and takes an exclusive lock — it's the thing that will cause downtime if you run it carelessly.

Better: use pg_repack, which does the same thing without an exclusive lock (it uses triggers to keep the copy in sync). It's slower but doesn't block reads or writes.

pg_repack -t users -d mydb

Adding a NOT NULL constraint

-- Blocks writes while it scans every row
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

On Postgres 12 and later, you can do this without a scan if a matching CHECK constraint already exists:

-- Step 1: add a CHECK constraint that doesn't require a scan
ALTER TABLE users
  ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: validate it (takes a weaker lock, doesn't block writes)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Step 3: now SET NOT NULL is instant because the constraint proves it
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4: drop the now-redundant CHECK constraint
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

Four statements to avoid one table scan. This pattern shows up everywhere in Postgres migration advice and it's worth internalizing.

Changing a column type

Type changes are the hardest case. Most require a table rewrite. Some are safe:

For the ones that rewrite, the expand-and-contract approach:

-- Add new column with the target type
ALTER TABLE events ADD COLUMN user_id_big bigint;

-- Backfill in batches
UPDATE events SET user_id_big = user_id WHERE id IN (...);

-- Switch reads and writes (deploy)
-- Drop old column (deploy)
-- Rename new column (deploy)

It's tedious, but on a multi-terabyte table there's no alternative that doesn't involve a maintenance window.

The migration tooling matters less than the discipline

I've used Flyway, Alembic, Rails migrations, Django migrations, and hand-written SQL applied by a shell script. The tooling affects how migrations are organized and tracked. It doesn't affect whether they lock tables.

What matters is:

1. Every migration is reviewed by someone who understands the locking behavior.
2. CONCURRENTLY for every index on a production table.
3. No renames, no type changes, no drops in a single step.
4. Backfills are batched and rate-limited.
5. Migrations run in CI against a production-sized dataset before they run in production.

That last point is the one people skip. A migration that takes 100ms on your dev database with 1,000 rows will take 20 minutes on production with 50 million rows. Test against realistic data or don't test at all.

What actually causes downtime

The trick is recognizing these before they run. Most migration tools will happily execute whatever SQL you give them, and the lock they take is determined by the SQL, not the tool.

A pre-flight checklist

• Is this migration a rename, type change, or drop? If so, split into phases.
• Does it create an index? Use CONCURRENTLY.
• Does it add a NOT NULL constraint? Use the CHECK NOT VALID pattern.
• Does it backfill more than 10,000 rows? Batch it.
• Has it been tested on a production-sized copy?
• Can it be rolled back? Migrations that can't be reversed need a manual plan.
• What happens if it fails halfway? Every migration should be either atomic (transactional) or written to be resumable.

Zero-downtime migrations aren't difficult once you know the patterns. They're just slower to write and require more deploys. The first time you run one on a billion-row table and nobody notices, it stops feeling like overhead.