The expand-contract pattern: zero-downtime schema migrations that do not break production

A startup in Lisbon lost four hours of revenue on a Tuesday because someone added a NOT NULL column to a Postgres table with 12 million rows. The migration ran in a deploy pipeline. The pipeline waited. The table locked. Every request that touched that table queued behind the lock. The health check failed. The pod restarted. The new pod tried to run the migration again. The table was still locked. The load balancer pulled the pod. Users saw 502s for 47 minutes.

The fix they applied afterward was to add a statement timeout to the migration runner. That is not a fix. It is a tripwire. The next migration that takes too long will fail instead of hanging, and you will still be down, just faster.

I have seen this pattern in almost every company I have worked with. Teams treat schema migrations like code deployments. They are not the same thing. Code is replaceable. You can deploy it, roll it back, deploy a different version. Schema changes are not replaceable. Once you have altered a table, the old schema is gone. Rolling back means writing a new migration that reverses the change, and by the time you realize you need to roll back, the new schema has already been in production long enough that data has been written against it.

The pattern that prevents this is called expand-contract. It is not new. It has been documented since at least 2009 in articles by Gojko Adzic and Martin Fowler. But I have never walked into a company that was using it. Everyone knows about it. Nobody does it. The reason is that it takes three deploys instead of one, and engineers would rather risk a 47-minute outage than wait two days for a safe migration.

I am going to walk through the pattern with real SQL, a real migration scenario, and the failure modes that the pattern prevents. Then I will tell you when not to use it, because there are cases where it is the wrong choice.

The pattern

Expand-contract has three phases. Each phase is a separate deploy.

Expand. You add the new schema without removing the old. New columns are nullable. New tables are created empty. New indexes are built concurrently. The application code at this point does not use the new schema. It still reads and writes the old way. The deploy is safe because nothing has changed from the application’s perspective. The database has more structure, but no code depends on it.

Migrate. You deploy code that writes to both the old and the new schema. Reads still come from the old schema. You backfill the new schema for existing rows. This is the phase where data moves. It can take hours or days depending on table size. It runs in the background, in batches, with throttling.

Contract. You deploy code that reads from the new schema and stops writing to the old. Then you remove the old schema. Drop the old column, drop the old index, remove the old table. This deploy is also safe because no code depends on the old schema anymore.

The key insight is that at no point does the application code depend on a schema state that does not exist. The old code works with the expanded schema. The new code works with the expanded schema. The contract phase only runs after the new code is deployed and confirmed working.

A real migration, step by step

The scenario: a startup has a users table with a full_name column. They want to split it into first_name and last_name because the product team needs to send personalized emails and the monolithic name field makes that awkward.

The naive migration is a single ALTER TABLE that drops full_name and adds first_name and last_name. This breaks every running instance of the application that tries to read or write full_name. If the deploy is rolling, the old pods will crash on the next query. If the migration takes 30 seconds on a large table, the table locks and everything queues.

Here is the expand-contract version.

Phase one: expand

-- migration_001_expand.sql
-- Deploy 1: add new columns, do not remove old one

ALTER TABLE users ADD COLUMN first_name TEXT;
ALTER TABLE users ADD COLUMN last_name TEXT;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_first_name
  ON users (first_name);

The ADD COLUMN without a default is metadata-only in Postgres 11+. It does not rewrite the table. It takes milliseconds regardless of table size. The CREATE INDEX CONCURRENTLY builds the index without taking an exclusive lock. Reads and writes continue normally during the build. The index build takes longer, but it does not block traffic.

The application code deployed alongside this migration is unchanged. It still reads and writes full_name. The new columns sit there empty. This deploy is boring, and boring is the point.

There is one subtlety. If your ORM or query builder validates the schema at startup, it might complain about unknown columns. Most ORMs do not, because they map columns they know about and ignore the rest. But if you are using a schema validator like Prisma with a locked schema file, you need to update the schema to include the nullable columns before this deploy. The schema file change is additive only. No removals.

Phase two: migrate

-- migration_002_backfill.sql
-- Deploy 2: backfill new columns from old data
-- Run in batches, not one big UPDATE

DO $$
DECLARE
  batch_size INTEGER := 5000;
  offset_val INTEGER := 0;
  rows_affected INTEGER := 1;
BEGIN
  WHILE rows_affected > 0 LOOP
    UPDATE users
    SET first_name = split_part(full_name, ' ', 1),
        last_name = CASE
          WHEN position(' ' in full_name) > 0
          THEN substring(full_name from position(' ' in full_name) + 1)
          ELSE ''
        END
    WHERE id IN (
      SELECT id FROM users
      WHERE first_name IS NULL
      LIMIT batch_size
      OFFSET offset_val
    );
    GET DIAGNOSTICS rows_affected = ROW_COUNT;
    offset_val := offset_val + batch_size;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

This backfill runs in batches of 5,000 rows with a 100ms pause between batches. On a table with 12 million rows, that is 2,400 batches and roughly 4 minutes of total runtime, spread out so the database never sees sustained load from the backfill alone. The pg_sleep(0.1) is crude but effective. For larger tables, you would use a background job queue with explicit rate limiting, but the principle is the same.

The application code deployed in this phase starts writing to both schemas. Every INSERT or UPDATE that writes full_name also writes first_name and last_name. Every read still comes from full_name. This is the dual-write phase.

# Application code during phase two
def update_user(user_id, full_name, first_name, last_name):
    db.execute("""
        UPDATE users
        SET full_name = %s,
            first_name = %s,
            last_name = %s,
            updated_at = NOW()
        WHERE id = %s
    """, (full_name, first_name, last_name, user_id))

The dual-write is the critical safety net. While the backfill is running, new rows are being written. If the backfill only processed existing rows, new rows would have full_name but NULL first_name and last_name. The dual-write ensures every new row has both schemas populated from the moment the phase two deploy goes out.

Phase three: contract

-- migration_003_contract.sql
-- Deploy 3: remove old column, after reads have switched

ALTER TABLE users DROP COLUMN full_name;
DROP INDEX IF EXISTS idx_users_full_name;

This deploy is safe because the application code deployed before it (or alongside it) has already switched reads from full_name to first_name and last_name. No running code references full_name. Dropping the column is metadata-only in Postgres. It does not rewrite the table. It takes milliseconds.

The application code in this phase stops the dual-write. It only writes first_name and last_name. It only reads from them. The full_name column is dead code in the schema, and the migration removes it.

# Application code during phase three
def update_user(user_id, first_name, last_name):
    db.execute("""
        UPDATE users
        SET first_name = %s,
            last_name = %s,
            updated_at = NOW()
        WHERE id = %s
    """, (first_name, last_name, user_id))

Three deploys. Two to four days of calendar time depending on your deploy cadence. Zero downtime. Zero locked tables. Zero risk of the old code crashing against the new schema or the new code crashing against the old schema.

The failure modes this prevents

The 47-minute outage in Lisbon was caused by a violation of the expand phase. The migration added a NOT NULL column with no default. Postgres had to rewrite the entire table to add the column, because NOT NULL without a default means every existing row needs a value, and Postgres cannot add a value without touching every row. The table locked. Traffic queued.

With expand-contract, the column is added as nullable. No rewrite. No lock. The NOT NULL constraint is added later, in a separate migration, after the backfill has populated every row and the dual-write ensures new rows always have a value.

-- migration_002b_add_constraint.sql
-- After backfill, add NOT NULL with a check first
ALTER TABLE users ADD CONSTRAINT first_name_not_null
  CHECK (first_name IS NOT NULL) NOT VALID;

-- Then validate it without blocking writes
ALTER TABLE users VALIDATE CONSTRAINT first_name_not_null;

-- Finally, convert to NOT NULL column constraint
ALTER TABLE users ALTER COLUMN first_name SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT first_name_not_null;

The NOT VALID check is the key. It adds the constraint for new rows immediately but does not scan existing rows. The VALIDATE CONSTRAINT scans existing rows but does not block writes. The final SET NOT NULL is fast because Postgres knows the constraint is already valid. Three steps, no locks, no downtime.

Another failure mode this prevents is the rollback problem. When you run a single migration that drops a column, you cannot roll back. The data is gone. The old code expects the column, and the column does not exist. You are stuck. You have to write a new migration to add the column back, repopulate it from a backup if you have one, and pray the backup is recent enough.

With expand-contract, rollback at every phase is trivial. In phase one, you drop the new columns. In phase two, you stop the dual-write and drop the new columns. In phase three, you add the old column back. The old schema was never removed until the new code was confirmed working. Rolling back means going back to a schema that was never deleted.

The moment you should not use it

Expand-contract is not always the right answer. There are three cases where I skip it.

The first case is a brand new table with no existing data. If the table is empty or does not exist yet, there is nothing to expand or contract. You create the table with the final schema. The migration is one step. Expand-contract adds complexity for no benefit.

The second case is a column rename on a table that no code reads from directly. If the column is only accessed through a view or a stored procedure, and you can change the view or the procedure in the same deploy, a single migration works. The indirection layer is your compatibility layer.

The third case is a migration on a table small enough that an exclusive lock finishes in under a second. If your users table has 400 rows, ALTER TABLE users ADD COLUMN email TEXT NOT NULL DEFAULT '' completes in milliseconds. The lock is held so briefly that no request notices. Expand-contract is engineering effort spent on a problem that does not exist.

The judgment call is about scale and risk. A 400-row table does not need expand-contract. A 12-million-row table does. The threshold is somewhere around 100,000 rows, depending on your traffic pattern and how many requests touch the table per second. If your table has a million rows and your application serves 50 requests per second that touch it, you need expand-contract. If your table has 10,000 rows and your application serves 2 requests per second, a direct migration with a short lock is fine.

The mistake is not in choosing the wrong approach for a specific table. The mistake is having one migration strategy for all tables. Small tables get the fast path. Large tables get expand-contract. Most teams pick one strategy, apply it everywhere, and then are surprised when it breaks on the one table that is too big.

What this requires from your team

The barrier to expand-contract is not technical. The SQL is straightforward. The barrier is operational. It requires:

A deploy cadence fast enough that three deploys over two days is feasible. If your team deploys once a week and each deploy requires a change advisory board meeting, expand-contract is a month-long process. This is a signal that your deploy process is the problem, not your migration strategy.

A migration runner that supports running migrations separately from code deploys. If your framework runs migrations automatically on application startup (Rails, Django, Prisma), you need to split the migration from the deploy. Run the migration as a separate step in the pipeline, before the rolling deploy. Most frameworks support this with a flag or a separate command. If yours does not, use a standalone tool like Flyway, Liquibase, or sqitch that runs migrations as a distinct step.

A monitoring system that tells you whether the backfill is progressing and whether the dual-write is working. If the backfill stalls, you need to know before you reach phase three. If the dual-write is failing silently, you need to know before you switch reads. This is where observability pays for itself. A Grafana panel showing the count of rows where first_name IS NULL over time, with an alert when the count stops decreasing, is the difference between a safe migration and a blind one.

The cost of doing it right

A founder asked me once whether expand-contract was worth the engineering time. Three migrations, dual-write logic, batch backfill scripts, monitoring for the backfill. His team had eight engineers. He estimated the pattern would take one engineer two days per migration. His team ran roughly six migrations per month. That is 12 engineer-days per month on migrations.

I asked him how much the 47-minute outage cost. He said the Lisbon incident lost roughly EUR 8,000 in revenue and an additional EUR 4,000 in engineering time spent on incident response and customer communication. That was one incident. His team had two similar incidents in the last year, both from schema migrations on large tables. Total cost: EUR 24,000. Total time spent on expand-contract for a year of migrations: 144 engineer-days, which at his engineer cost of roughly EUR 400 per day is EUR 57,600.

The math does not work if you count incidents as one-off events. The math works when you count them as recurring. A team that does direct migrations on large tables will eventually have an incident. The probability is not zero. It is not even low. It is a function of table size, traffic, and migration frequency, and for a team running six migrations per month on tables that are growing, the probability of at least one bad migration per quarter is high enough that the expected cost exceeds the cost of prevention.

The three-deploy migration is not the clever solution. It is the boring solution. The clever solution is a tool that does online schema changes, like pt-online-schema-change or gh-ost or Bucardo. Those tools work. They also add a layer of infrastructure that most teams do not have and do not want to maintain. Expand-contract requires nothing except discipline and SQL. It works with the database you already have and the deploy pipeline you already run. The cost is patience, and patience is cheaper than downtime.