Zero Downtime Database Migrations Playbook

I have run zero downtime schema changes on Postgres and MySQL in systems that processed tens of thousands of writes per second. The pattern that saves you every time is expand and contract, guarded by flags, with backfills that you can pause and resume. This is the playbook I wish someone had handed me the first time.

Constraints You Cannot Hand Wave

• Any DDL that blocks writes will take you down. In Postgres, CREATE INDEX without CONCURRENTLY blocks. In MySQL, ALTER TABLE without online support blocks.
• Long transactions keep old snapshots alive. Postgres cannot vacuum or complete some DDL while a long transaction is open. Kill them before you start.
• Defaults that rewrite the table hurt. Adding a column with a non null default rewrites in Postgres before v11. Even later, it still has pitfalls on large tables.
• Replication lag hides bugs. Migrate with awareness of standby lag and logical consumers.

If you are uncertain which path is safe for your stack and traffic, get a second set of eyes. A tight architecture review of your migration plan catches most footguns before they land in prod.

The Expand Phase: Add, Do Not Break

You add new structures, never remove or repurpose existing ones. Reads and writes keep using the old shape during this phase.

Example change: rename a column user_name to username and change type from TEXT to CITEXT in Postgres.

Step 1. Add the new column as nullable and without a default.

-- Postgres
ALTER TABLE users ADD COLUMN username citext; -- keep it nullable for now

-- Indexes must be created concurrently or online
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_username ON users (username);

Step 2. Add dual write logic so new writes populate both columns. You can do this in application code or with a trigger. Triggers centralize correctness but add overhead, so I prefer app level dual writes when the code surface is small.

Application write path with a feature flag:

// Node example with a simple flag
const DUAL_WRITE = process.env.DUAL_WRITE_USERNAME === '1';

async function updateUserName(db, userId, newName) {
  await db.tx(async t => {
    await t.query('UPDATE users SET user_name = $1 WHERE id = $2', [newName, userId]);
    if (DUAL_WRITE) {
      await t.query('UPDATE users SET username = $1 WHERE id = $2', [newName, userId]);
    }
  });
}

If you cannot safely touch all writers, add a trigger instead:

-- Postgres trigger to mirror writes from old column to new
CREATE OR REPLACE FUNCTION users_username_mirror() RETURNS trigger AS $$
BEGIN
  IF NEW.user_name IS DISTINCT FROM OLD.user_name THEN
    NEW.username := NEW.user_name;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_users_username_mirror ON users;
CREATE TRIGGER trg_users_username_mirror
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION users_username_mirror();

Step 3. Teach reads to prefer the new column behind a flag, but fall back to the old until cutover:

// Read path with guarded preference
const READ_NEW = process.env.READ_USERNAME_NEW === '1';

async function getUserName(db, userId) {
  const row = await db.one('SELECT user_name, username FROM users WHERE id = $1', [userId]);
  if (READ_NEW && row.username != null) return row.username;
  return row.user_name;
}

This pattern is boring on purpose. Additive DDL, dual writes, guarded reads. No outages.

Backfill Without Burning The Database

Now you need to copy historical data from user_name to username. You must do it in small, pauseable chunks. Use a job table or just select by primary key ranges. Avoid ORDER BY on the hot table. Avoid massive UPDATEs that touch the whole table at once.

Postgres version, page through primary keys and lock rows only when updating. SKIP LOCKED lets multiple workers collaborate without stepping on each other.

-- One time helper index if you lack a good paging key
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_pk ON users (id);

# Python backfill worker using SKIP LOCKED
import os
import time
import psycopg

BATCH = int(os.getenv('BATCH', '1000'))
SLEEP = float(os.getenv('SLEEP', '0.05'))  # seconds between batches

while True:
    with psycopg.connect(os.getenv('DATABASE_URL')) as conn:
        with conn.cursor() as cur:
            cur.execute("""
                WITH cte AS (
                  SELECT id FROM users
                  WHERE username IS NULL
                  LIMIT %s
                  FOR UPDATE SKIP LOCKED
                )
                UPDATE users u
                SET username = u.user_name
                FROM cte
                WHERE u.id = cte.id
                RETURNING u.id
            """, (BATCH,))
            rows = cur.fetchall()
            if not rows:
                break
    time.sleep(SLEEP)

MySQL version with gh-ost or pt-online-schema-change when you need to change types or add non nullable columns. For pure data copy, do chunked UPDATEs with LIMIT to avoid long running statements.

# Example: change type online in MySQL with gh-ost
GH_OST_FLAGS="--database=appdb --table=users --host=primary.example --max-load=Threads_running=50 --critical-load=Threads_running=100 --chunk-size=1000"

gh-ost $GH_OST_FLAGS \
  --alter="ADD COLUMN username VARCHAR(190) NULL, ADD INDEX idx_users_username (username)" \
  --approve-renamed-columns \
  --cut-over=default \
  --execute

Watch metrics while backfilling. For every 1k rows per batch at 20 batches per second you will see write amplification. Throttle based on replication lag, row lock wait time, and p99 query latency.

If you are planning a big backfill that might run for days, hook it into your job infrastructure and use a progress table. You want retryable, idempotent batches. Pre-allocating ranges by primary key works well.

Cutover: Switch Reads, Then Retire The Old

The safest order of operations looks like this.

• Ensure dual writes are still on.
• Validate backfill completeness by counts and spot checks.
• Switch reads to the new column behind a flag for a small percentage of traffic.
• Watch errors and business KPIs.
• Ramp to 100 percent. Keep dual writes on.
• Remove read fallback. Keep dual writes on.
• Wait for a quiet period with zero errors for at least one business cycle.

Flags let you undo the switch in seconds. If your team does not have a mature flag setup, read our notes on feature flag canaries and rollbacks and get that in place before you attempt this.

At this point, the old column is unused in reads, but still maintained by dual writes. Only then do you proceed to contract.

Contract: Remove The Old In Stages

You remove the old column only after you have hard proof it is not read. Continue to ship small changes and give yourself outs.

• Drop the trigger or remove app level dual write code, but not both at once.
• Delete the old indexes that only serve the old column. In Postgres, drop concurrently.
• Finally, drop the old column. In MySQL, do it with an online alter if needed.

-- Postgres: safe drops
DROP INDEX CONCURRENTLY IF EXISTS idx_users_user_name;
ALTER TABLE users DROP COLUMN user_name; -- fast if table is small, schedule during a calm period if very large

When the contract completes, remove flags and dead code. Ship a cleanup PR and, if you used triggers, VACUUM to reclaim bloat as needed.

If you are understaffed for this kind of work, pairing with the right backend engineers who have done zero downtime work pays for itself in avoided incidents.

Rollback Paths You Can Execute At 2 a.m.

Every step above has a clean undo.

• If backfill hurts latency, pause the workers. They are idempotent and checkpointed.
• If the read switch causes errors, flip READ_NEW off. You are still dual writing and the old reads still work.
• If dual writes are wrong, disable DUAL_WRITE and rely on the old column while you fix the mapper. You can re-run the backfill to repair.
• If a DDL causes unexpected blocking, kill the session and revert the change in a separate window. In Postgres you can use CREATE INDEX CONCURRENTLY ROLLBACK safely, but watch for invalid indexes that need DROP INDEX CONCURRENTLY.

For MySQL online schema tools, know the escape hatches. gh-ost supports --panic-flag-file which you can touch to abort and cut back. pt-online-schema-change can be killed, then run with --resume if configured. Test these aborts in staging.

Verification: Prove It Worked Before You Remove Anything

I use three levels of checks.

• Structural: the new column exists, nullable as intended, indexed, and replication has caught up.
• Content: row counts of not null new column match old column counts. Run checksum spot checks.
• Behavioral: application produces the same responses on old and new reads for a sample of requests.

Examples you can run during a cutover window.

-- Structural
SELECT COUNT(*) FROM pg_indexes WHERE tablename = 'users' AND indexname = 'idx_users_username';

-- Content
SELECT COUNT(*) FROM users WHERE user_name IS NOT NULL;
SELECT COUNT(*) FROM users WHERE username IS NOT NULL;

-- Spot check mismatches
SELECT id, user_name, username FROM users WHERE COALESCE(user_name, '') <> COALESCE(username, '') LIMIT 100;

For APIs, shadow compare responses in a canary. Send 1 percent of requests through both read paths, log diffs, and alert. Keep this during the ramp.

Tooling Notes: Postgres, MySQL, and ORMs

Postgres

• CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY are your friends. Never forget them on large tables.
• Avoid adding NOT NULL with DEFAULT in one shot on large tables. Add the column as NULL, backfill, then add NOT NULL in a second migration using a short lock metadata change.
• Be aware of long transactions. A BEGIN that sits open can stall vacuum and DDL.

MySQL

• Prefer gh-ost for InnoDB with row based replication. pt-online-schema-change also works, but be mindful of triggers and metadata locks.
• Test on a replica with production size. Some MySQL builds have different online DDL behavior.

ORMs and migration frameworks

• Rails: use strong_migrations. It will stop you from table rewrites. Use disable_ddl_transaction! for concurrent index builds.
• Django: split migrations. Use RunSQL for concurrent index creation. Avoid altering columns with default in one go.
• Flyway or Liquibase: structure migrations into expand and contract scripts, with markers so you can pause between phases.

If you need upstream help deciding the right path for your stack and infra, our team that handles DevOps and cloud automation has battle scars to share.

A Concrete Timeline You Can Copy

You can run this as two deploys per day without risking an outage.

Day 1 morning

• Ship DDL to add new column, new indexes, and triggers if needed.
• Enable dual writes in app for 1 percent of traffic. Watch error budget.

Day 1 afternoon

• Ramp dual writes to 100 percent.
• Start backfill workers at BATCH 1000 and SLEEP 50 ms. Adjust based on p99 and replication lag.

Day 2 morning

• Validate backfill. Mismatches under 0.01 percent are usually race conditions during the final batches. Repair with another backfill pass.
• Enable READ_NEW for 5 percent. Shadow compare responses. Hold for one hour.

Day 2 afternoon

• Ramp READ_NEW to 100 percent. Keep dual writes on.
• Remove read fallback code in a PR. Do not merge yet.

Day 3 morning

• Merge removal of read fallback.
• Keep dual writes on for one business cycle. Leave backfill workers off.

End of week

• Drop dual writes and triggers.
• Drop old indexes and column.
• Vacuum as needed in Postgres, or optimize table in MySQL if you saw fragmentation.

Common Failure Modes And How To Avoid Them

• Hot index creation on a column with high update churn. Solution, build concurrently and consider partial indexes.
• Backfill deadlocks. Solution, always lock rows in a consistent order and use SKIP LOCKED.
• Replication lag on cutover. Solution, throttle backfill, monitor seconds behind, and avoid cutover while lag exceeds your SLO.
• App caches masking bugs. Solution, invalidate or bypass caches during canary to observe real behavior.
• Hidden writer outside your app. Solution, audit who writes. Triggers or database level replication help catch stragglers.

If you are not sure your schema can take the load at its next size, read our notes on designing database schemas for scale before you commit to a path.

When You Must Change Tables, Not Columns

Some changes require a new table. Example, splitting a wide table into two. The same expand and contract applies.

• Create the new table.
• Dual write into both tables. Consider a foreign key, but do not enable it until after backfill to avoid write amplification.
• Backfill from the old table to the new in batches.
• Teach reads to join to the new table behind a flag.
• Switch reads, then remove dual writes, then drop the old columns.

You can also do a view or an INSTEAD OF trigger for compatibility during the transition, but be careful with performance.

Operational Hygiene That Makes This Boring

• Keep migrations small and reversible. One DDL per migration script where possible.
• Tag deploys that include schema changes so you can find them quickly.
• Add dashboards for lock wait time, replication lag, autovacuum activity, and migration job throughput.
• Precompute expected counts before you start so you know what success looks like.
• Practice a rollback in staging within a limited time window. Time it.

Zero downtime migration work is mostly discipline and checklists. If you need extra hands to run this across services or to fold it into a broader rewrite, our team that does API and system integrations at scale can help you land it without drama.