The Expand/Contract Pattern for Safe Schema Migrations

The scariest deploy I run is not a code change. It is a schema change on a table that a live service is reading and writing thousands of times a second. Rename a column in a single migration and you have a window where the deployed app expects the old name and the database only has the new one, or vice versa. During a rolling deploy both versions of the app run at the same time, so there is no instant where a breaking rename is safe.

The fix is a pattern with several names: expand/contract, or parallel change. Instead of one atomic change, you make the schema hold the old and new shape at the same time, migrate through it in small backward-compatible steps, and only remove the old shape once nothing reads it anymore. I use it for every destructive schema change on a table that matters.

The five steps, and why order matters

The whole pattern is a promise: at no point does the database and the currently deployed app disagree about what must exist. Each step is its own commit and its own deploy. You never combine an expand with a contract in the same release. Here is the shape of it, using a classic example: splitting a single full_name column into first_name and last_name.

Step 1: Expand without locking the table

The expand step must be cheap. On PostgreSQL 11 and later, adding a column with a constant default is a metadata-only operation stored in the system catalog and applied on read, so it no longer rewrites the table. Before 11, that same statement rewrote every row under an ACCESS EXCLUSIVE lock, which on a large table meant minutes of blocked traffic. Even on modern Postgres, a volatile default such as a random UUID still forces a full rewrite, so add the column nullable and backfill separately.

-- Expand: cheap, metadata-only, no table rewrite.
-- Add columns as NULLABLE. Do NOT add NOT NULL yet --
-- an unpopulated NOT NULL column would reject every insert.
ALTER TABLE users ADD COLUMN first_name text;
ALTER TABLE users ADD COLUMN last_name  text;

Step 2: Backfill in batches, never one big UPDATE

A single UPDATE users SET ... across millions of rows takes one long transaction, holds row locks the whole time, and bloats the table with dead tuples that vacuum then has to chase. Batch it. Update a few thousand rows per transaction, commit, sleep briefly, repeat. This keeps each lock short, lets autovacuum keep up, and means a failure only rolls back the current batch instead of hours of work.

-- Backfill loop: run outside a single transaction.
-- Each batch commits on its own.
DO $$
DECLARE
  rows_updated integer;
BEGIN
  LOOP
    UPDATE users
    SET first_name = split_part(full_name, ' ', 1),
        last_name  = substr(full_name, length(split_part(full_name,' ',1)) + 2)
    WHERE first_name IS NULL
      AND id IN (
        SELECT id FROM users WHERE first_name IS NULL LIMIT 5000
      );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    COMMIT;            -- procedural COMMIT, Postgres 11+
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

Steps 3 and 4: Dual-write, then flip reads

Once the backfill covers historical rows, you still have a gap: rows written between the backfill and the switch would only populate the old column. Dual-write closes it. Deploy application code that writes both the old full_name and the new first_name/last_name on every insert and update. Now both representations stay identical going forward. Only after that deploy is fully rolled out do you ship the next one that reads from the new columns.

// Step 3 deploy: write BOTH shapes. Reads still use full_name.
await db.user.update({
  where: { id },
  data: {
    fullName:  `${firstName} ${lastName}`, // old, still authoritative
    firstName,                              // new, now kept in sync
    lastName,
  },
});

// Step 4 deploy (next release): reads switch to the new columns.
// Writes still populate both until the contract step lands.
const { firstName, lastName } = await db.user.findUniqueOrThrow({
  where: { id },
  select: { firstName: true, lastName: true },
});

Step 5: Contract, and add constraints the safe way

Contract is the mirror of expand. First deploy code that stops writing the old column, confirm nothing references it, then drop it in a final migration. This is also where you promote the new columns to NOT NULL if the domain requires it. Adding NOT NULL directly scans the whole table under a heavy lock; instead add a CHECK ... NOT VALID constraint, which skips the scan, then VALIDATE CONSTRAINT separately under a lighter SHARE UPDATE EXCLUSIVE lock that does not block reads and writes.

-- Enforce NOT NULL without a blocking full-table scan:
ALTER TABLE users
  ADD CONSTRAINT users_first_name_not_null
  CHECK (first_name IS NOT NULL) NOT VALID;   -- instant, no scan

ALTER TABLE users
  VALIDATE CONSTRAINT users_first_name_not_null; -- scans, but lighter lock

-- Finally, the contract itself:
ALTER TABLE users DROP COLUMN full_name;

When it is worth the ceremony

Five deploys for one column rename is real overhead, and I do not use it everywhere. On a low-traffic internal table I take a short maintenance window and rename in one shot. Expand/contract earns its cost on tables that are large, hot, and fronted by a service that cannot go down. The list below is roughly how I decide.

• Use it when: the table is large enough that a rewrite locks noticeably, or the service has a strict uptime target and rolling deploys mean two app versions run at once.
• Skip it when: the table is tiny or internal, traffic is low, and a few seconds of maintenance downtime is genuinely acceptable.
• Always: keep each step in its own migration and its own deploy, and never mix an expand and a contract in one release.
• Bonus: the same shape works beyond columns — splitting a table, changing a type, or moving a foreign key all follow expand, migrate, switch, contract.

The mental model that makes all of this click: a deploy is not one atomic thing. Your database and several versions of your app coexist for minutes at a time. Expand/contract simply refuses to ever let those coexisting versions disagree about the schema. Once you internalize that, the scary migration stops being scary — it becomes a boring checklist you run the same way every time.