Zero-Downtime Database Migration with Expand-and-Contract

TL;DR — Use expand-and-contract in three releases: first add the new schema without removing the old one, then deploy app code that writes to both old and new shapes and backfills existing rows, and only after verification remove the old schema. The most common failure is dropping or renaming a column before every app instance has stopped reading it; avoid that by treating destructive DDL as the final step only. Reading time: ~5 min

Goal

When you finish, your production app will be serving traffic continuously while a schema change is completed in place: old code keeps working during rollout, new code reads/writes the new schema, existing data is backfilled, and the old schema is removed only after verification.

Prerequisites

• Production database access with DDL rights on the target schema.
• Ability to deploy application code in at least two releases.
• A staging environment with production-like traffic patterns.
• PostgreSQL 13+ — check with:

psql --version

• psql configured for the target database via DATABASE_URL or connection flags.
• Your migration tool or a way to run SQL in deployment automation.
• The exact change you are making written down, including old column/table names and new ones.
• A rollback plan for application code and a recent database backup or PITR enabled.

Steps

Step 1: Pick a compatible migration shape

Use a change that can coexist temporarily. Example: split users.full_name into users.first_name and users.last_name.

-- old shape
-- users(full_name text not null)

-- new shape to add first
ALTER TABLE users ADD COLUMN first_name text;
ALTER TABLE users ADD COLUMN last_name text;

Success looks like: the new columns exist, and the old column still exists.

Step 2: Run the expand migration

Apply only additive, backward-compatible DDL. Do not rename or drop anything here.

⚠️ Adding a column is usually safe; adding a column with a volatile default or a NOT NULL constraint on a large table can still lock or rewrite data depending on engine/version. In Postgres, add nullable columns first, then backfill, then add constraints later.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
ALTER TABLE users ADD COLUMN IF NOT EXISTS first_name text;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_name text;
COMMIT;
SQL

Success looks like:

BEGIN
ALTER TABLE
ALTER TABLE
COMMIT

Step 3: Deploy application code that dual-writes

Release app code that writes both old and new schema fields in the same transaction. Reads should still prefer the old field until backfill is complete, or use a fallback.

// write path
await db.tx(async (tx) => {
  const [firstName, ...rest] = fullName.trim().split(/\s+/);
  const lastName = rest.join(" ") || null;

  await tx.query(
    `UPDATE users
     SET full_name = $2,
         first_name = $3,
         last_name = $4
     WHERE id = $1`,
    [userId, fullName, firstName || null, lastName]
  );
});

// read path during transition
const row = await db.query(
  `SELECT COALESCE(first_name || CASE WHEN last_name IS NOT NULL THEN ' ' || last_name ELSE '' END, full_name) AS display_name
   FROM users
   WHERE id = $1`,
  [userId]
);

Success looks like: new writes populate both full_name and first_name/last_name for freshly updated rows.

Step 4: Backfill existing rows in batches

Run a batched backfill so you do not hold long locks or create replication lag. For Postgres, update by primary key ranges or LIMIT via CTE.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
DO $$
DECLARE
  batch_size integer := 1000;
  rows_updated integer;
BEGIN
  LOOP
    WITH batch AS (
      SELECT id, full_name
      FROM users
      WHERE first_name IS NULL AND full_name IS NOT NULL
      ORDER BY id
      LIMIT batch_size
    )
    UPDATE users u
    SET first_name = split_part(batch.full_name, ' ', 1),
        last_name = NULLIF(substr(batch.full_name, length(split_part(batch.full_name, ' ', 1)) + 2), '')
    FROM batch
    WHERE u.id = batch.id;

    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;
SQL

Success looks like:

DO

Then confirm remaining rows:

psql "$DATABASE_URL" -c "SELECT count(*) AS remaining FROM users WHERE full_name IS NOT NULL AND first_name IS NULL;"

Expected output shape:

 remaining
-----------
         0
(1 row)

Step 5: Switch reads to the new schema

Deploy a second app release that reads only first_name and last_name, but still writes both old and new fields for one more rollout window.

const row = await db.query(
  `SELECT first_name, last_name
   FROM users
   WHERE id = $1`,
  [userId]
);

Success looks like: all app instances are on the new read path, and no errors mention full_name in query logs.

Step 6: Enforce constraints on the new schema

After backfill and read switch, add constraints and indexes needed by the new shape.

⚠️ ALTER TABLE ... SET NOT NULL scans the table. On large tables, run it during a low-traffic window and watch lock wait time. If you need a lower-risk path, add a CHECK (first_name IS NOT NULL) NOT VALID, validate it, then convert later if your standards require NOT NULL.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
ALTER TABLE users ADD CONSTRAINT users_first_name_not_null CHECK (first_name IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_first_name_not_null;
COMMIT;
SQL

Success looks like:

BEGIN
ALTER TABLE
ALTER TABLE
COMMIT

Step 7: Stop dual-write, then contract

Deploy a final app release that stops writing full_name. After that release is fully rolled out, remove old schema objects.

⚠️ This is the destructive step. If any old app instance, background worker, cron job, or ad-hoc script still references full_name, this will break it immediately with SQL errors.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
ALTER TABLE users DROP COLUMN full_name;
COMMIT;
SQL

Success looks like:

BEGIN
ALTER TABLE
COMMIT

Verify it works

Run these checks after the final deploy.

psql "$DATABASE_URL" -c "\d+ users"
psql "$DATABASE_URL" -c "SELECT count(*) AS null_first_names FROM users WHERE first_name IS NULL;"
psql "$DATABASE_URL" -c "SELECT id, first_name, last_name FROM users ORDER BY id DESC LIMIT 5;"

Expected results:

• \d+ users shows first_name and last_name, and does not show full_name.
• null_first_names is 0 if the new field is required.
• Recent rows show values in the new columns.

Also check application logs after rollout. The failure shape for a missed contract dependency usually looks like:

ERROR: column "full_name" does not exist
LINE 1: SELECT full_name FROM users WHERE id = $1
               ^
SQLSTATE: 42703

If you use Postgres replication, verify lag did not spike during backfill:

psql "$DATABASE_URL" -c "SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;"

Common pitfalls

Dropping the old column in the same release as the code change

Mistake: running DROP COLUMN before every app instance and worker is on the new code.

Symptom: production errors like:

ERROR: column "full_name" does not exist
SQLSTATE: 42703

Fix: restore the old column from backup only if necessary; otherwise redeploy old-compatible code and redo the migration as expand → dual-write → backfill → read switch → contract.

Adding NOT NULL before backfill

Mistake: ALTER TABLE users ADD COLUMN first_name text NOT NULL; on a table with existing rows.

Symptom:

ERROR: column "first_name" of relation "users" contains null values
SQLSTATE: 23502

Fix: add the column nullable, backfill it, then add a validated check or SET NOT NULL.

One-time backfill query that runs for hours

Mistake: a single UPDATE users SET ... WHERE ...; against a large table.

Symptom: lock waits, slow queries, rising replication delay, deployment timeout.

Fix: rerun the backfill in batches of 500-5000 rows with a short sleep between batches.

Forgetting background workers and scripts

Mistake: updating only the web app while queue consumers, cron jobs, ETL jobs, or admin scripts still read/write the old column.

Symptom: intermittent 42703 errors after contract, often only from one service.

Fix: grep all repos and job definitions for the old column name before Step 7, then deploy those components first.

Renaming instead of expanding

Mistake: ALTER TABLE users RENAME COLUMN full_name TO display_name; on a live system.

Symptom: old code breaks immediately because renames are not backward-compatible at the SQL layer.

Fix: add display_name, dual-write, backfill, switch reads, then drop full_name later.

Assuming ORM auto-migrations are safe in production

Mistake: letting an ORM generate destructive DDL without reviewing lock behavior or rollout order.

Symptom: migration succeeds in staging but causes lock contention or runtime errors in production.

Fix: inspect the generated SQL, split it into explicit expand and contract migrations, and run destructive DDL only after code rollout and verification.

This article was written by an AI system and published pending human review. Verify anything you intend to act on.