Changing a Postgres schema without taking the app down: expand and contract.

Every ALTER TABLE takes a lock. The Postgres docs say ACCESS EXCLUSIVE is taken "unless explicitly noted", and that it is the only mode that blocks a plain SELECT. What matters is how long the lock is held: a catalog-only change holds it for milliseconds, a scan or rewrite holds it for as long as the table takes to read.

An ALTER TABLE that holds its lock for two milliseconds can still stop every query on the table for minutes. It has to wait for transactions already using the table to finish. While it waits, new queries that need any conflicting lock, plain SELECTs included, queue behind it. One report or job holding a transaction open for five minutes becomes a five-minute outage for that table. Xata and Postgres.ai both walk through it step by step.

The fix is lock_timeout. Postgres aborts any statement that waits longer than that for a lock, so a blocked migration fails and the queue behind it drains at once. The docs note the limit "applies separately to each lock acquisition attempt".

• Pick a short value. A few seconds is plenty for catalog-only changes. If it fails, find the blocker and run the migration again.
• Set it per session, not server-wide. The Postgres docs advise against setting lock_timeout in postgresql.conf "because it would affect all sessions", your app included.
• Find what is in the way before you retry, with the query in the next section.

-- At the top of each migration that touches a busy table
SET lock_timeout = '3s';

ALTER TABLE orders ADD COLUMN status_code text;

pg_blocking_pids lists the sessions holding a lock that a given session waits for. Run it while the migration is stuck, or right before a deploy that touches a busy table.

-- Who has had a transaction open the longest, and who is waiting on whom
SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       now() - xact_start AS open_for, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

NOT VALID adds the constraint without checking existing rows, so it is instant, and new writes are checked from then on. VALIDATE CONSTRAINT scans the table later under SHARE UPDATE EXCLUSIVE, which lets reads and writes continue. For SET NOT NULL, the Postgres docs say the scan is skipped when a valid CHECK constraint already proves no NULL can exist.

-- Postgres 17: a CHECK constraint proves it, SET NOT NULL then skips the scan
ALTER TABLE orders ADD CONSTRAINT orders_status_nn
  CHECK (status_code IS NOT NULL) NOT VALID;          -- instant
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn; -- scans, writes keep going
ALTER TABLE orders ALTER COLUMN status_code SET NOT NULL; -- no scan
ALTER TABLE orders DROP CONSTRAINT orders_status_nn;

-- Postgres 18: NOT NULL itself can be NOT VALID
ALTER TABLE orders ADD CONSTRAINT orders_status_nn NOT NULL status_code NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;

A plain CREATE INDEX lets reads continue but makes every INSERT, UPDATE and DELETE on the table wait until the build ends. CONCURRENTLY lets writes continue at the price of two table scans and waits for older transactions, so it takes longer overall.

Two rules from the Postgres docs. It cannot run inside a transaction block, so it needs a migration that is not wrapped in one. And if it fails (a deadlock, a duplicate value for a unique index), it leaves an INVALID index that queries ignore but writes still maintain. Drop it and run again, or REINDEX INDEX CONCURRENTLY.

It waits for every transaction that might use the table, so a short lock_timeout from an earlier migration in the same session can cancel it. Turn the timeout off for this one statement, as below.

-- Its own migration, outside a transaction
-- (Rails: disable_ddl_transaction!, Django: atomic = False)
SET lock_timeout = 0;
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

-- If it failed, it left an INVALID index behind. Drop it and run again:
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;

ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both the table and the table it references, which blocks writes to both while it scans. Add it NOT VALID and validate in a second step; validation takes SHARE UPDATE EXCLUSIVE on your table and ROW SHARE on the referenced one.

ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
-- A later migration (or the same one, after the first commits):
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

A rename is instant in Postgres and still breaks production, because during the deploy the old code is running and asking for the old name. Expand and contract keeps every deploy compatible with the code before it. To rename orders.status to status_code:

• Deploy 1, expand. The migration adds status_code. The code writes both columns and still reads status.
• Backfill the old rows in batches (next section).
• Deploy 2. The code reads status_code and still writes both.
• Deploy 3. The code stops writing status. In Rails, add it to ignored_columns so Active Record forgets it.
• Deploy 4, contract. The migration drops status. The version still serving while it runs is deploy 3, which no longer touches it.

One UPDATE over ten million rows is one long transaction: it holds row locks on everything it touched until the end, and leaves ten million dead rows for vacuum. Small batches that each commit keep locks short and let autovacuum keep up.

• Start writing both columns first (deploy 1 above), or rows created during the backfill come back NULL.
• Batch on an indexed key. ORDER BY id LIMIT 5000 on the primary key stays fast as the backfill moves through the table.
• Keep long backfills out of the deploy. On Canner the release command is stopped after 15 minutes. Run the loop yourself through the database’s external access, which is limited to the IP addresses you allow.

# Run from your machine through the database's external access,
# or from a protected route in your app. Each UPDATE commits on its own.
while true; do
  out=$(psql "$DB_URL" -c "
    UPDATE orders SET status_code = status
    WHERE id IN (
      SELECT id FROM orders
      WHERE status_code IS NULL AND status IS NOT NULL
      ORDER BY id LIMIT 5000
    )")
  echo "$out"
  [ "$out" = "UPDATE 0" ] && break
  sleep 1
done

Migrations go in the release command (Settings → Build). It runs once per production deploy, after the build and before the new version starts, while the current version keeps serving. That is why every migration has to suit the code already running. If it fails, a lock_timeout included, the deploy stops and the current version keeps running. Find the blocker and deploy again. With Prisma, mark the failed migration with prisma migrate resolve --rolled-back <name> first; Prisma’s docs say migrate deploy refuses to continue until you do.

Then Canner stops the running process (it gets up to 30 seconds to finish its requests) and starts the new one, which must answer its health check within 30 seconds. Requests that arrive during the switch can fail. So a Canner deploy has a short interruption even when the schema change has none. What this page removes is the longer outage: a table locked for minutes, or old code meeting a column that no longer exists. Details in the deploy docs.

On the dedicated instance, you can set statement, idle-in-transaction and lock-wait timeouts for the whole instance (canner db tuning --lock-timeout 5, in seconds). They take effect without a restart. They apply to your app’s sessions too, so keep them at values the app can live with and set a tighter lock_timeout inside migrations. New databases run Postgres 17 by default, or 18 if you choose it; the NOT NULL … NOT VALID form needs 18.