How to Migrate a Production Database Without Downtime in 2026: Complete Guide

The migration that takes down production is rarely complex. It is usually a single ALTER TABLE that took an exclusive lock on a large table while the application waited. Zero-downtime migration is a discipline rather than a tool: break every change into steps that are individually safe with old and new code running at once.

The Core Principle: Expand and Contract

You cannot deploy a schema change and the code that needs it at the same instant. During any rolling deployment, old and new application versions run simultaneously. Every migration must therefore be compatible with both.

The expand-contract pattern splits every change into three deployments:

1. Expand — add the new structure. Old code ignores it; new code can use it.
2. Migrate — backfill data and switch the application to the new structure.
3. Contract — remove the old structure once nothing references it.

Each step deploys separately, and each is individually reversible. That is what makes the whole sequence safe.

Example: Renaming a Column

A rename looks trivial and is one of the most dangerous operations, because it breaks old code the instant it runs.

-- ❌ Never do this on a live system.
ALTER TABLE users RENAME COLUMN email TO email_address;
-- Every running instance still querying "email" fails immediately.

The safe sequence:

-- Step 1 (expand): add the new column. Nullable, no default — instant.
ALTER TABLE users ADD COLUMN email_address TEXT;

-- Step 2: deploy code that WRITES both columns and READS the old one.
--   UPDATE users SET email = $1, email_address = $1 WHERE id = $2

-- Step 3: backfill existing rows in batches (see below).

-- Step 4: deploy code that READS the new column and still writes both.

-- Step 5: deploy code that only uses the new column.

-- Step 6 (contract): drop the old column, once nothing references it.
ALTER TABLE users DROP COLUMN email;

Six deployments for a rename feels excessive until the first time a one-step rename causes an outage. Most teams eventually decide renames are not worth it and simply keep the original name.

Which Operations Lock

Knowing which statements take a blocking lock is most of the skill. On modern PostgreSQL:

Always verify against your specific database version. The behaviour of these operations has improved substantially over recent releases, and advice written for older versions is often needlessly cautious — or dangerously outdated in the other direction.

Adding Indexes Safely

-- ❌ Blocks all writes to the table for the duration.
CREATE INDEX idx_users_email ON users(email);

-- ✅ Builds without blocking writes.
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Two things to know about CONCURRENTLY. It cannot run inside a transaction block, which means most migration frameworks need explicit configuration to allow it. And it can fail partway, leaving an invalid index behind that must be cleaned up.

-- Find invalid indexes left behind by a failed concurrent build
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

-- Drop and retry
DROP INDEX CONCURRENTLY idx_users_email;

Adding NOT NULL Without a Long Lock

Adding NOT NULL directly scans the entire table while holding a lock. A validated CHECK constraint achieves the same guarantee in two non-blocking steps.

-- 1. Add the constraint without validating existing rows — instant.
ALTER TABLE users
  ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- 2. Validate separately. Takes a weaker lock that allows reads and writes.
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

The same NOT VALID / VALIDATE pattern applies to foreign keys, and for the same reason.

Backfilling Large Tables

A single UPDATE across millions of rows holds locks, generates enormous write-ahead log volume, and can stall replication. Batch it.

-- ❌ One statement, one very long transaction, many locked rows.
UPDATE users SET email_address = email;

-- ✅ Batched, resumable, and gentle on replication.
DO $$
DECLARE
  batch_size INT := 5000;
  affected   INT;
BEGIN
  LOOP
    UPDATE users
    SET email_address = email
    WHERE id IN (
      SELECT id FROM users
      WHERE email_address IS NULL AND email IS NOT NULL
      ORDER BY id
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );

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

    COMMIT;
    PERFORM pg_sleep(0.1);   -- let replicas catch up
  END LOOP;
END $$;

FOR UPDATE SKIP LOCKED prevents the backfill from blocking on rows your application is currently updating. The short sleep between batches is what keeps replication lag from growing, and it is the step people omit and regret.

For very large tables, run the backfill as a separate job rather than inside a migration, so a deployment is never waiting on it.

Set a Lock Timeout

This single setting prevents most migration outages. Without it, a migration that cannot acquire a lock waits indefinitely — and every query queued behind it waits too, which is how one ALTER TABLE stalls an entire application.

-- Fail fast instead of queueing behind a long-running query.
SET lock_timeout = '3s';
SET statement_timeout = '30s';

ALTER TABLE users ADD COLUMN email_address TEXT;

If it times out, retry later. A failed migration you can rerun is vastly better than a locked table during peak traffic.

Migrating Between Databases

Moving to a new database engine or instance uses the same principle at a larger scale.

1. Replicate — set up logical replication or change-data-capture from old to new. Let it catch up fully.
2. Dual read — read from the old database, and read from the new one in the background to compare results. Log every mismatch and fix the causes before proceeding.
3. Dual write — write to both. The old one remains authoritative.
4. Cut over — switch reads to the new database. Keep writing to both.
5. Decommission — stop writing to the old database once you are confident, having kept it as a rollback path for a meaningful period.

The comparison phase in step 2 is what makes this safe. Skipping it means discovering data differences after cutover, when rolling back is expensive.

Rollback Planning

Every migration needs an answer to “what if this is wrong?” before it runs.

Additive changes roll back trivially — dropping a column you just added is safe because nothing depends on it.

Destructive changes do not roll back. Once a column is dropped, the data is gone. This is exactly why contract steps come last and only after a period of confidence.

Backfills need reversibility too. If you overwrite a column, keep the original values somewhere until you are certain.

Never write a migration whose down step deletes data. If a rollback would lose information, the migration should not be reversible automatically — make the recovery path a deliberate, manual decision.

Pre-Flight Checklist

• Test on a restored copy of production data, at production scale
• Know whether each statement takes a blocking lock
• Set lock_timeout and statement_timeout
• Batch every backfill and run it outside the deployment
• Confirm old and new application code both work with the intermediate schema
• Watch replication lag during and after
• Have a verified backup, and know how long a restore takes
• Deploy during low traffic even when you expect no lock

Conclusion

Zero-downtime migration comes down to a few disciplines: split every change into expand, migrate, and contract phases deployed separately; know which statements take blocking locks and use CONCURRENTLY and NOT VALID to avoid them; batch backfills with pauses so replication keeps up; and always set a lock timeout so a migration fails fast instead of queueing your entire application behind it. The extra deployments feel like overhead right up until the first migration that would otherwise have caused an outage.