Schema changes without downtime: expand, migrate, contract.

Renaming a column looks like a one-line change: ALTER TABLE customers RENAME COLUMN name TO full_name. In production it is two problems. First, during a rolling deploy, old and new versions of the application run side by side, and one of them is always querying a column that does not exist. Second, most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock, which blocks every read and write on the table until the statement finishes.

The lock is worse than it looks. The ALTER must first wait for every transaction already using the table, and while it waits in the lock queue, every new query on that table queues behind it. A statement that needs ten milliseconds can stall all traffic to a busy table for as long as the slowest open transaction, perhaps a report or an idle session someone forgot to close. Teams usually discover this as an unexplained outage during an otherwise routine deploy.

The fix is not a better migration tool. It is a sequencing discipline: never make a change the running code cannot tolerate, and never hold a strong lock for long.

Before any specific technique, set a lock timeout for migration sessions. If a statement cannot get its lock quickly, it should give up and be retried later rather than wait in the queue with production traffic piling up behind it.

-- At the start of every migration session.
SET lock_timeout = '2s';          -- give up rather than queue behind a long transaction
SET statement_timeout = '15min';  -- validations and index builds get a ceiling too

A migration that fails with a clear lock timeout is a minor inconvenience. One that silently holds the lock queue is an incident.

Any breaking schema change can be rewritten as a sequence of non-breaking ones. The general technique is known as parallel change; for databases it is usually called expand and contract. Each phase is its own deploy, and each deploy is compatible with the code running before and after it.

1. Expand

ALTER TABLE customers ADD COLUMN full_name text;

Adding a nullable column without a default only changes the catalog, so the exclusive lock is held for milliseconds. Since PostgreSQL 11, a column with a constant default is also a catalog-only change. A volatile default, such as clock_timestamp() or gen_random_uuid(), still rewrites the whole table.

2. Dual write

Deploy application code that writes both name and full_name on every insert and update, while still reading name. Writing from the application keeps the logic visible and testable. A trigger can do the same job when several applications write to the table, at the cost of logic that lives outside the codebase.

3. Backfill in batches

UPDATE customers
SET    full_name = name
WHERE  id >= :from AND id < :from + 5000
AND    full_name IS NULL;

A single UPDATE over the whole table would hold a lock on every row until it commits and produce one enormous transaction for replicas to replay. Walking the primary key in fixed ranges keeps each transaction short, never rescans finished rows, and lets the job pause when replica lag or load climbs. The full_name IS NULL guard makes every batch safe to rerun. Keep backfills out of schema migrations: one is a data job that may take hours, the other should take seconds.

4. Enforce the invariant, then switch reads

If the new column must be NOT NULL, do not add that directly. SET NOT NULL scans the whole table while holding an exclusive lock. Add a CHECK constraint as NOT VALID instead, which is instant, then validate it, which scans the table under a lock that lets reads and writes continue. Since PostgreSQL 12, a valid check constraint lets SET NOT NULL skip its scan entirely.

ALTER TABLE customers
  ADD CONSTRAINT customers_full_name_not_null
  CHECK (full_name IS NOT NULL) NOT VALID;            -- instant; enforced for new writes

ALTER TABLE customers
  VALIDATE CONSTRAINT customers_full_name_not_null;   -- scans under SHARE UPDATE EXCLUSIVE

ALTER TABLE customers ALTER COLUMN full_name SET NOT NULL;  -- proven by the check: no scan
ALTER TABLE customers DROP CONSTRAINT customers_full_name_not_null;

PostgreSQL 18 also accepts NOT VALID on a not-null constraint itself, which shortens this to two statements; the version above works on every release from 12 onward. With the invariant enforced, deploy the code that reads full_name. It still writes both columns, so rolling back is a redeploy.

5. Contract

Stop writing the old column in one deploy, and drop it in a later one, after the previous application version can no longer be rolled back to. DROP COLUMN is fast, because PostgreSQL only marks the column as dropped, but it is the one step you cannot undo. Take a backup, and confirm that nothing else still reads the column: reports, exports, and the data warehouse included.

A plain CREATE INDEX blocks all writes to the table for the entire build. CREATE INDEX CONCURRENTLY builds without blocking writes, at the cost of more total work and two passes over the table. It cannot run inside a transaction block, so most migration tools need the step marked as non-transactional.

CREATE INDEX CONCURRENTLY customers_full_name_idx ON customers (full_name);

-- Foreign keys: skip the scan when adding, then validate under a lighter lock.
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)
  REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

• Does every statement run with a lock_timeout?
• Can the currently deployed code run against the schema after this migration, and can the next version run against the schema before it?
• Does any statement rewrite or scan a large table under an exclusive lock? Type changes, SET NOT NULL without a proving check, volatile defaults, and non-concurrent indexes all do.
• Are backfills batched, restartable, and kept separate from schema changes?
• Is the destructive step in its own later release, with a backup?

Stripe's account of online migrations at scale follows the same four-phase shape across services and data stores. The specifics change with the database; the discipline does not. Schema changes become routine when every step on its own is boring.