A deploy ran a single ALTER TABLE on a table with 50 million rows and locked it for four minutes. For four minutes, every request that touched that table waited; the site was effectively down. The migration itself was correct — the problem was doing it in one step.

The dangerous thing is not the schema change. The dangerous thing is making a schema change all at once, without thinking about backward compatibility.

Who takes the lock

Not every schema change costs the same. In modern PostgreSQL, adding a column with a constant DEFAULT is a metadata operation — it’s fast. The real danger is in operations that lock the table for a long time:

  • CREATE INDEX — without CONCURRENTLY, it closes the table to writes.
  • ALTER COLUMN ... TYPE changes that rewrite the table.
  • NOT NULL, CHECK, or foreign key additions that scan the whole table.

These operations take a strong lock; the ones that take it in ACCESS EXCLUSIVE mode — a table rewrite, or adding NOT NULL/CHECK — queue up every query touching the table, reads included. The rest block writes only. If the table is large, the queue grows.

The dangerous part is the single step

The solution is not to avoid migrations; it’s to break every dangerous migration into small steps, each of which is safe and backward-compatible on its own. This is called the expand-contract pattern.

Suppose you want to rename a column. A single-step RENAME COLUMN instantly breaks running code that reads the old column. Instead, three deploys:

  1. Expand. Add the new column. Have the code write to both old and new, still reading from old.
  2. Migrate. Move the old data into the new column in batches. Now have the code read from new.
  3. Contract. Drop the old column.

Each step works with both the code from the previous release and the new code. At no moment is the running code incompatible with the schema.

Safe recipes

The zero-downtime versions of common changes:

A new NOT NULL column. Adding NOT NULL in one step scans the table. Split it:

-- 1. Add it as nullable first
ALTER TABLE orders ADD COLUMN status text;

-- 2. Backfill existing rows in batches (on the application side)

-- 3. Add the constraint NOT VALID first, then validate in a separate step
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
    CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

A constraint added with NOT VALID applies immediately to new rows but does not scan existing ones; VALIDATE then scans the table with only a SHARE UPDATE EXCLUSIVE lock — it doesn’t block writes.

An index. Always CONCURRENTLY:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

A foreign key. The same two-step pattern: first ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT.

lock_timeout. While a migration waits for a lock behind a long-running query, it blocks everything behind it too. To prevent this, give the migration session a short lock_timeout — if the lock can’t be taken immediately, let the migration fail instead of waiting, and retry it yourself.

Code and schema must be compatible together

The essence of expand-contract is one rule: at every intermediate step, both the old code still running and the new code must be able to work with the current schema.

The database schema is what I called a “one-way door” in the cost of just-in-case code — rolling it back is expensive. So design the change not as one big, irreversible step, but as small steps that can each be rolled back individually.

Zero-downtime migration is not a tool but a discipline: breaking every schema change into steps small enough that the running code never notices.

The dangerous thing is not the change itself, but doing it in one breath.