Migrations that do not take the site down

Most DDL takes an ACCESS EXCLUSIVE lock, which conflicts with everything — including plain SELECTs. If the statement is instant, nobody notices. If it rewrites the table, every query queues behind it until it finishes.

• Safe and instant: adding a nullable column, adding a column with a non-volatile default (Postgres 11+), dropping a column, renaming.
• Rewrites the table: changing a column type in most cases, adding a column with a volatile default.
• Blocks writes for a full scan: `ADD CONSTRAINT` without `NOT VALID`, `SET NOT NULL` without a supporting check.
• Blocks writes for the whole build: `CREATE INDEX` without `CONCURRENTLY`.

-- builds without blocking writes. cannot run inside a transaction,
-- so most migration tools need this flagged explicitly.
create index concurrently idx_orders_customer on orders (customer_id);

-- a failed CONCURRENTLY build leaves an INVALID index behind.
-- check for it, drop it, retry.
select indexrelid::regclass from pg_index where not indisvalid;

Constraints get the same treatment in two steps. `NOT VALID` adds the constraint for new rows immediately, taking only a brief lock. `VALIDATE` then checks the existing rows under a much weaker lock that permits reads and writes.

alter table orders
  add constraint orders_total_positive check (total_minor >= 0) not valid;

-- separate transaction, weak lock, scans existing rows
alter table orders validate constraint orders_total_positive;

Any breaking change — renaming a column, splitting a field, changing a type — becomes safe if you refuse to do it in one step. The rule is that the schema must be compatible with both the old and the new application version at every moment, because during a rolling deploy both are running.

1. Expand — add the new column, nullable. Deploy. Nothing reads it yet.
2. Dual-write — application writes both old and new. Deploy. Backfill existing rows in batches, not one statement.
3. Migrate reads — application reads the new column, still writes both. Deploy. This is the reversible checkpoint.
4. Contract — stop writing the old column. Deploy. Wait a release, then drop it.

It is four deploys instead of one. It is also the difference between a rename you can roll back at any point and a rename that has a five-minute window where half your traffic gets a 500.