Schema migrations that don't take the site down

The mental model most people carry is that a deploy replaces version N with version N+1 at an instant. It doesn't. During any deploy that avoids downtime, there's a window — sometimes seconds, sometimes minutes — where both versions are running and both are connected to the same database.

Which means every schema change has to satisfy an awkward constraint: it must be compatible with the code you are replacing and the code you are shipping, simultaneously. Once you internalise that, most zero-downtime migration advice stops being a list of rules to memorise and becomes obvious.

The overlap window is not optional

On a platform doing blue-green deploys — including ours — a new version boots on fresh infrastructure, gets health-checked, and only then does traffic flip to it. The old version stays alive during that period and often for a grace period afterwards, so in-flight requests finish rather than being severed.

That's a strictly better deploy model than stopping the old version first, but it means you cannot ship a migration and the code that depends on it as one atomic unit. Something is always briefly running against a schema it wasn't written for.

Expand and contract

The pattern that solves this is old and boring and works: never change a thing, add the new thing, move over, remove the old thing. Three deploys instead of one.

Renaming `users.name` to `users.full_name` is the canonical example, and the naive `ALTER TABLE users RENAME COLUMN` breaks every running instance of the old code the moment it commits. The safe version:

1. Expand. Add `full_name` as a nullable column. Deploy code that writes to both columns and reads from `name`. Backfill `full_name` in batches. Nothing has broken — old code doesn't know the column exists and doesn't care.
2. Migrate reads. Deploy code that reads from `full_name` and still writes both. Now the read path is switched, but a rollback to the previous version still works because both columns are populated.
3. Contract. Once you're confident you won't roll back, deploy code that stops writing `name`, then drop the column in a later migration.

It feels like ceremony for a rename. It is. It's also the difference between a schema change being routine and a schema change being a scheduled maintenance window with people on a call.

The locks that actually hurt

In Postgres, the operation that ruins your afternoon is one that takes an ACCESS EXCLUSIVE lock on a busy table. That lock blocks reads as well as writes, and — this is the part that turns a slow migration into an outage — it queues behind existing transactions and every subsequent query queues behind it.

So a migration waiting on a long-running SELECT doesn't merely wait. It parks in the lock queue and blocks everything that arrives after it. Your monitoring shows the whole table becoming unavailable while the migration itself hasn't started doing anything.

-- Always. Fail fast instead of queueing behind a long transaction.
SET lock_timeout = '3s';
SET statement_timeout = '30s';

-- Safe on modern Postgres: metadata-only, no table rewrite
ALTER TABLE orders ADD COLUMN notes text;
ALTER TABLE orders ADD COLUMN status text DEFAULT 'pending';  -- 11+ is fine

-- Dangerous: rewrites the whole table under ACCESS EXCLUSIVE
ALTER TABLE orders ALTER COLUMN amount TYPE bigint;

-- Safe NOT NULL, the long way
ALTER TABLE orders ADD CONSTRAINT orders_notes_nn
  CHECK (notes IS NOT NULL) NOT VALID;   -- instant, no full scan
ALTER TABLE orders VALIDATE CONSTRAINT orders_notes_nn;  -- scans, but no exclusive lock

A `lock_timeout` of a few seconds is the single most valuable line in this post. With it, a migration that can't get its lock quickly fails cleanly and you retry later. Without it, that same migration silently becomes a site-wide outage and nobody realises the deploy caused it.

Indexes and backfills

Two more rules with the same underlying shape — don't hold a lock for the length of a large operation.

-- CONCURRENTLY takes far weaker locks, but cannot run inside a transaction
-- block. Most migration tools wrap everything in one — check yours.
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

-- If it fails partway it leaves an INVALID index behind. Clean up, retry.
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer;

Backfills follow the same logic: never `UPDATE` a large table in one statement. A single statement touching ten million rows holds locks, generates enormous WAL, and can't be interrupted without losing all the work. Batch it — a few thousand rows at a time, committing between batches, with a short sleep so other traffic gets a turn.

-- Run repeatedly until it reports 0 rows
WITH batch AS (
  SELECT id FROM users
  WHERE full_name IS NULL AND name IS NOT NULL
  ORDER BY id LIMIT 5000 FOR UPDATE SKIP LOCKED
)
UPDATE users u SET full_name = u.name
FROM batch b WHERE u.id = b.id;

Where the migration should run

Not in your build. Build environments are ephemeral, parallel, and automatically retried — three properties that are individually fine and collectively catastrophic for something that mutates shared state. Two builds racing the same migration is a real failure mode, not a theoretical one.

Run migrations as a distinct step: after the build succeeds, before the new version starts taking traffic. Take an advisory lock so concurrent deploys serialise rather than collide.

#!/usr/bin/env bash
set -euo pipefail
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
SELECT pg_advisory_lock(4815162342);   -- only one deploy migrates at a time
SET lock_timeout = '3s';
SQL
npx prisma migrate deploy

Rehearse on real data, not an empty schema

Every one of these problems is invisible on a development database with a hundred rows. `ALTER TABLE ... TYPE bigint` is instantaneous on an empty table and takes eleven minutes under an exclusive lock on a table with forty million rows. Testing a migration against a schema you seeded this morning proves it parses, and nothing else.

The useful test is against a copy of production. Copy-on-write database clones make this practical rather than a two-hour restore job — clone the production database into a scratch instance, run the migration, time it, look at what it locked, then throw the clone away. Point-in-time restore into a fresh instance gives you the same thing from a chosen moment.

# Clone prod into a throwaway database and rehearse there
pandastack db clone db_prod --label migration-rehearsal
pandastack db get db_xxxx --json | jq -r .connection_url

# Time the real thing against real data volumes
time psql "$CLONE_URL" -f migrations/0042_add_full_name.sql

That loop — clone, rehearse, measure, discard — turns migration risk from a judgement call into a measurement. It's also the only way to answer 'how long will this lock the table for?' with a number instead of a shrug.

The short version

• Assume old and new code run simultaneously. Every migration must be compatible with both.
• Expand, migrate, contract. Three deploys. Never rename or drop in the same deploy that changes the code.
• Set `lock_timeout` on every migration. Fail fast rather than queueing.
• `CREATE INDEX CONCURRENTLY`, outside a transaction block.
• Batch backfills. Never one statement over a large table.
• Run migrations as a release step under an advisory lock, not during the build.
• Rehearse against a clone of production and time it before it goes anywhere near production.

The uncomfortable truth is that the deploy platform can't do this for you. It can give you blue-green, health checks, fast rollback, and cheap database clones to rehearse against — but the compatibility of your schema with two versions of your own code is a design decision, and it has to be made when you write the migration.