Why a naive schema migration causes downtime

A zero downtime database migration changes the schema while the application keeps serving traffic, with no maintenance window and no burst of failed requests. The hard part is usually not the database. A rename can finish in milliseconds and still take your service down, because the outage comes from the order in which code and schema change.

Two versions, one schema

During a rolling deploy, instances are replaced a few at a time. For several minutes, some instances run the old release and some run the new one, and all of them query the same database. Blue-green and canary deploys have the same overlap. Both environments, or both cohorts, share a single schema until the old version is fully retired. If you roll back, the overlap happens again in the other direction.

Now suppose a migration renames a column at the start of the deploy. The new code expects the new name, so it works. The old code still selects, inserts and updates the old name, so every request on an old instance fails until that instance is replaced. Dropping a column, tightening a constraint or changing a type breaks the old code in the same way. How fast the DDL runs makes no difference. The schema is already incompatible with code that is still serving users.

The core rule

At any moment, assume the database is shared by at least two versions of your code. Every schema state must work with both the release being retired and the release being rolled out. This applies to any backend or platform team that runs rolling, blue-green or canary deploys against a single database. Expand and contract is a way to order your changes so that this rule always holds.

How expand and contract works

every application version that can be live at the same moment works against the schema that exists at that moment. You never change the schema and the code in one step. Each step below is a separate deploy, and each one must finish rolling out before the next one starts.

Step            Schema state            Live code (old → new during rollout)
1 Expand        old + new (nullable)    v1 → v1    (v1 ignores new column)
2 Dual-write    old + new               v1 → v2    (v2 writes both, reads old)
3 Backfill      old + new, filled       v2         (job copies existing rows)
4 Switch reads  old + new               v2 → v3    (v3 reads new, writes both)
5 Stop old      old + new               v3 → v4    (v4 writes new only)
6 Contract      new only                v4         (old column dropped)

During each rollout, two adjacent versions run against one schema state. That overlap is why the steps are ordered this way. v3 keeps writing the old column so that a rollback to v2, which still reads it, gets current data. The drop waits until no live version references the old column, and until you have decided you will not roll back to one that does.

Rules for a backward compatible schema change

  • Add only nullable columns or columns with defaults. Code that does not know a column exists will insert rows without it. A NOT NULL column with no default rejects those inserts.
  • Never rename or drop in the same release that stops using something. Old instances are still serving traffic during the rollout and will query the name they know.
  • Tighten constraints only after all writers comply. Add NOT NULL, CHECK or foreign keys once every live version and the backfill write valid values. If you add them earlier, inserts from the older version fail.
               LIVE CODE                              SCHEMA STATE
               ┌─────────────────┐                    ┌──────────────────────┐
1 Expand       │ v1              │─ writes old ──────▶│ old + new (nullable) │
               └─────────────────┘                    └──────────────────────┘
                        ▼
               ┌─────────────────┐                    ┌──────────────────────┐
2 Dual-write   │ v1 → v2 rollout │─ w both, r old ───▶│ old + new            │
               └─────────────────┘                    └──────────────────────┘
                        ▼
               ┌─────────────────┐                    ┌──────────────────────┐
3 Backfill     │ v2 + backfill   │─ copy old → new ──▶│ old + new (filled)   │
               └─────────────────┘                    └──────────────────────┘
                        ▼
               ┌─────────────────┐                    ┌──────────────────────┐
4 Switch reads │ v2 → v3 rollout │─ w both, r new ───▶│ old + new            │
               └─────────────────┘                    └──────────────────────┘
                        ▼
               ┌─────────────────┐                    ┌──────────────────────┐
5 Stop old     │ v3 → v4 rollout │─ w new only ──────▶│ old + new (old idle) │
               └─────────────────┘                    └──────────────────────┘
                        ▼
               ┌─────────────────┐                    ┌──────────────────────┐
6 Contract     │ v4              │─ r/w new ─────────▶│ new only             │
               └─────────────────┘                    └──────────────────────┘

There are six separate deploys in expand and contract. Each one shows the code versions live during the rollout, what they read and write, and the schema state they run against.

Worked example: renaming users.fullname to users.display_name

Start from v1 of the application, which reads and writes fullname. Each step below assumes the previous code version is fully rolled out, including every replica, worker and cron job.

  1. Expand. Run ALTER TABLE users ADD COLUMN display_name text;. The column is nullable with no default, so v1 keeps working.
  2. Dual-write. Deploy v2, which writes both columns and still reads fullname.
  3. Backfill. Copy fullname into display_name where it is null, in batches. Section 5 covers how.
  4. Switch reads. Deploy v3, which reads display_name and still writes both columns, so v2 instances stay correct during the rollout.
  5. Stop old writes. Deploy v4, which writes only display_name.
  6. Contract. Drop fullname once v4 is the only version running.

For step 2 there are two ways to dual-write. Application-level dual writes are easy to review. They miss rows written by anything that bypasses the application, such as admin scripts, other services or manual SQL. A BEFORE INSERT OR UPDATE trigger that copies NEW.fullname into display_name also catches those writers, including v1 code that has never heard of the new column. The cost is a function call on every write and sync logic that lives outside the codebase. PostgreSQL does not track the columns a PL/pgSQL function body references, so you must drop the trigger before dropping fullname. Otherwise every write fails at runtime.

Step Schema state Read path Write path Rollback
1 Expand both columns fullname fullname drop display_name
2 Dual-write both, partially synced fullname both redeploy v1
3 Backfill both, synced fullname both none needed
4 Switch reads both display_name both redeploy v2
5 Stop old writes both display_name display_name redeploy v3, re-backfill
6 Contract display_name only display_name display_name restore from backup 
-- Step 2, trigger option: keep display_name in sync for every writer,
-- including v1 code and scripts that only know about fullname.
CREATE OR REPLACE FUNCTION users_sync_display_name() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    -- Old writers leave display_name null, so fill it from fullname.
    NEW.display_name := COALESCE(NEW.display_name, NEW.fullname);
  ELSIF NEW.fullname IS DISTINCT FROM OLD.fullname
        AND NEW.display_name IS NOT DISTINCT FROM OLD.display_name THEN
    -- The writer changed fullname but left display_name alone. Copy it over.
    -- When v4 writes only display_name, this branch never runs, so nothing gets overwritten.
    NEW.display_name := NEW.fullname;
  END IF;
  RETURN NEW;
END;
$$;

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

-- Step 6, contract: drop the trigger and function before the column.
-- PostgreSQL does not record that the function body references fullname.
-- If you drop the column first, the DROP succeeds and every later write fails.
BEGIN;
SET LOCAL lock_timeout = '5s';  -- give up instead of queueing behind long transactions
DROP TRIGGER users_sync_display_name ON users;
DROP FUNCTION users_sync_display_name();
ALTER TABLE users DROP COLUMN fullname;
COMMIT;

This example shows a PostgreSQL trigger that copies fullname into display_name for writers that don't know about the new column, and the order to remove it in during contract: trigger, then function, then column.

Lock-aware DDL in PostgreSQL

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with every other lock, including the one a plain SELECT takes. The ALTER itself may finish in milliseconds. The danger is the wait before it starts. If a long-running query holds a lock on the table, the ALTER queues behind it, and every query that arrives afterward queues behind the ALTER. A metadata-only change can stall all traffic to the table for as long as that one slow query runs.

Set lock_timeout on the migration session, for example SET lock_timeout = '2s'. If the lock is not granted in that window, the statement fails instead of blocking others. The migration runner should catch that error and retry with backoff.

Build indexes with CREATE INDEX CONCURRENTLY. It avoids blocking writes, but it cannot run inside a transaction block, so turn off your tool's per-migration transaction for that step. If the build fails, it leaves an INVALID index. That index still adds write overhead, so drop it before you retry.

Add CHECK and foreign key constraints with NOT VALID. Existing rows are not scanned, but new writes are enforced. Then run VALIDATE CONSTRAINT as a separate step. Validation scans the table under a weaker lock that permits reads and writes.

These techniques take longer, use more I/O, and can fail partway. Migration tooling must detect and resume from partial state. Behavior also varies by version. For example, which ALTER forms avoid a full table rewrite has changed across releases. Check the documentation for the version you run.

-- Run each step as its own statement in autocommit mode, not inside BEGIN/COMMIT.
-- If the lock is not granted within 2s, the statement fails instead of
-- making every later query on the table wait. The runner retries with backoff.
SET lock_timeout = '2s';

-- Step 1: build the index without blocking writes.
-- This statement fails if it runs inside a transaction block.
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx
    ON orders (customer_id);

-- Step 1 recovery: a failed concurrent build leaves an INVALID index behind.
-- IF NOT EXISTS would skip it on retry, so look for it explicitly.
SELECT c.relname
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'orders_customer_id_idx' AND NOT i.indisvalid;
-- If that query returns a row, drop the index and rerun step 1.
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;

-- Step 2: add the constraints without scanning existing rows.
-- New inserts and updates are checked from now on.
ALTER TABLE orders
    ADD CONSTRAINT orders_total_nonnegative CHECK (total_cents >= 0) NOT VALID;
ALTER TABLE orders
    ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id)
    REFERENCES customers (id) NOT VALID;

-- Step 3, in a separate migration: scan existing rows.
-- Validation uses a weaker lock, so reads and writes keep running.
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_nonnegative;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;

A PostgreSQL migration split into steps that can be retried: it sets lock_timeout so a stuck lock fails fast, builds an index concurrently and drops it if the build left it INVALID, and adds constraints as NOT VALID before validating them in a separate step.

Backfilling large tables safely

With the new column and dual writes in place, existing rows still need display_name populated. A single UPDATE users SET display_name = fullname does that in one transaction. It holds row locks on every row until commit, generates the table's entire WAL volume at once, and leaves replicas replaying a huge backlog. If it fails near the end, all of that work rolls back.

Instead, walk the primary key in bounded batches of a few thousand rows. Select the next range by id > last_id ORDER BY id LIMIT n, update it, and commit. Each commit releases locks, gives autovacuum a chance to reclaim dead tuples, and caps the cost of a failure at one batch. Add the predicate display_name IS DISTINCT FROM fullname so rerunning a batch changes nothing. That makes the job idempotent. Persist last_id after each commit so an interrupted run resumes where it stopped instead of rescanning from the start.

Between batches, sleep, and back off further when replica lag rises. You can read lag from pg_stat_replication on the primary. The sleep bounds WAL generation per second, which keeps replicas, archiving and backups from falling behind.

Before reads switch to the new column, confirm completeness with SELECT count(*) FROM users WHERE display_name IS DISTINCT FROM fullname. Proceed only when it returns zero.

Budget for the costs. Every updated row writes a new tuple version, so expect WAL and I/O roughly proportional to table size and temporary bloat until vacuum catches up. At conservative throttle settings, the backfill is often the longest phase of the entire migration.

import time
import psycopg2

JOB = 'users_display_name'
BATCH = 5000       # rows per transaction; bounds lock time and rollback cost
BASE_SLEEP = 0.2   # seconds between batches; caps WAL generated per second
MAX_LAG = 5.0      # replica replay lag (seconds) that triggers backoff

conn = psycopg2.connect("dbname=app user=migrator host=db-primary.internal")
cur = conn.cursor()
# Assumes: CREATE TABLE backfill_progress (job text PRIMARY KEY, last_id bigint NOT NULL);
cur.execute("INSERT INTO backfill_progress VALUES (%s, 0) ON CONFLICT DO NOTHING", (JOB,))
cur.execute("SELECT last_id FROM backfill_progress WHERE job = %s", (JOB,))
last_id = cur.fetchone()[0]  # resume point after an interruption
conn.commit()

while True:
    # Walk the PK range; IS DISTINCT FROM skips rows already done (idempotent).
    cur.execute("""
        WITH batch AS (SELECT id FROM users WHERE id > %s ORDER BY id LIMIT %s),
        upd AS (UPDATE users u SET display_name = u.fullname FROM batch b
                WHERE u.id = b.id AND u.display_name IS DISTINCT FROM u.fullname)
        SELECT max(id) FROM batch""", (last_id, BATCH))
    batch_max = cur.fetchone()[0]
    if batch_max is None:
        break  # walked past the highest id
    cur.execute("UPDATE backfill_progress SET last_id = %s WHERE job = %s", (batch_max, JOB))
    conn.commit()  # data and progress commit atomically; row locks released
    last_id = batch_max

    # Throttle harder while replicas are behind (needs pg_monitor to see lag).
    cur.execute("SELECT COALESCE(EXTRACT(EPOCH FROM max(replay_lag)), 0) FROM pg_stat_replication")
    lag = float(cur.fetchone()[0])
    conn.commit()
    time.sleep(BASE_SLEEP if lag < MAX_LAG else BASE_SLEEP * 10)

# Completeness gate: switch reads to display_name only when this prints 0.
cur.execute("SELECT count(*) FROM users WHERE display_name IS DISTINCT FROM fullname")
print("rows still differing:", cur.fetchone()[0])

A resumable, idempotent Python backfill that updates users in primary-key batches, commits each batch together with its progress marker, backs off when replica replay lag rises, and finishes with the count query that gates the read switch.

Gating the contract step and planning rollback

The backfill and the contract step carry most of the risk, for different reasons. The backfill touches every row, so a mistake there shows up as load, replication lag and lock contention across the whole table. The contract step is the only one that destroys anything. Every earlier step adds schema or changes application code. You can undo any of them by redeploying the previous release, because fullname still exists and the dual-write keeps it current. After you drop the column, rolling back means restoring from backup. Any writes made since the drop exist only in display_name.

Confirm nothing still reads the old column

Code you deploy is the easy part. The consumers that cause trouble don't ship with your release: BI and reporting queries, ETL jobs, other services that share the database, and ad-hoc scripts that someone runs once a quarter. If pg_stat_statements is enabled, search it for statements that still mention fullname. Check the catalog for views that depend on the column. DROP COLUMN refuses to drop a column that a view depends on unless you add CASCADE. Treat that error as a finding to investigate, not something to override.

Make the drop its own deploy

Ship the drop on its own, after the new path has run in production through at least one full cycle of your scheduled jobs. Delaying it costs only some storage and the dual-write overhead.

Remove the dual-write code and the sync trigger in the same cleanup, and do it before the drop. PostgreSQL doesn't track which columns a PL/pgSQL function body references. A trigger function that still writes to fullname won't block the drop, but every insert or update that fires it will fail afterward.

When to avoid it, alternatives, and impact on cost and engineering time

Expand and contract only pays for itself when old and new application code read and write the same schema at the same time. If that overlap never happens, skip it.

  • Purely additive changes are already backward compatible, so there is nothing to contract. Examples include a new nullable column, a new table, or an index built concurrently.
  • An agreed maintenance window removes the overlap by stopping traffic.
  • A single-instance deploy that stops the old process before starting the new one never runs two code versions against the database.

Some changes resist the pattern because they touch every row or every reference: changing a column type on a huge table, splitting or merging tables, or changing a primary key. In PostgreSQL, most type changes rewrite the whole table under an ACCESS EXCLUSIVE lock. Instead of an in-place ALTER, add a new column or table, backfill it, and move readers and writers across.

Approach Downtime Effort Risk
Scheduled maintenance window Planned outage Low Low, but overruns extend the outage
In-place ALTER Lock duration, often unpredictable Lowest High on large or busy tables
Expand and contract None if gated correctly Medium: several deploys Low per step, each step reversible
New table plus cutover None, or a brief cutover High Medium, concentrated at cutover

The cost is real. One change becomes several deploys spread over days or weeks. Every team that touches the table has to coordinate. Leftover columns, triggers and dual-write code stay in place until someone removes them, so give the contract step a ticket with an owner and a date.

The return is fewer incident rollbacks, migrations that run during normal working hours, and repeatable steps you can script into your migration tooling.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to zero downtime database migrations, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.