Schema migrations without downtime

Every team learns this the same way: a migration that ran in half a second on staging causes a two-minute outage in production. The change wasn't the problem. The lock queue was.

The thing nobody tells you about locks

Most ALTER TABLE forms need an ACCESS EXCLUSIVE lock, which conflicts with everything — including a plain SELECT. Now the important part: lock requests queue in order. If a 90-second analytics query is holding the table when your ALTER arrives, the ALTER waits, and every query that arrives afterwards waits behind the ALTER. One slow reader plus one instant DDL statement equals a full stall.

The defence is to refuse to wait:

set lock_timeout = '3s';
alter table orders add column channel text;

If the lock can't be acquired in three seconds the statement fails, the queue drains, and you try again in a minute. Wrap it in a retry loop in your migration tool. Combine with statement_timeout so nothing runs away.

Adding a column

Since PostgreSQL 11 a constant default no longer rewrites the table — the value is recorded in the catalogue and applied on read. So this is fast on a table of any size:

alter table orders add column channel text default 'web';

Still brief-lock territory (so keep lock_timeout on), and note the exceptions: a volatile default such as gen_random_uuid(), or a stored generated column, does require a rewrite. Add those as a plain nullable column and backfill.

Making a column NOT NULL

A naive set not null scans the entire table under an exclusive lock. Split it:

-- 1. brief lock, no scan
alter table orders
  add constraint orders_channel_not_null check (channel is not null) not valid;

-- 2. scans, but under a weaker lock: reads and writes continue
alter table orders validate constraint orders_channel_not_null;

-- 3. now cheap — Postgres 12+ trusts the validated constraint and skips its own scan
alter table orders alter column channel set not null;
alter table orders drop constraint orders_channel_not_null;

Adding a foreign key

Same trick, same reason:

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;

The constraint is enforced for new and changed rows immediately; VALIDATE then checks the existing ones without blocking traffic. And index the referencing column — see the indexing rules — or every parent delete scans the child table.

Adding an index

create index concurrently orders_channel_idx on orders (channel);

Slower and doubles the passes over the table, but doesn't block writes. Two things to know: it can't run inside a transaction block (so your migration tool needs to be told not to wrap it), and if it fails part-way it leaves an invalid index behind that must be dropped and rebuilt:

select indexrelid::regclass as idx from pg_index where not indisvalid;
drop index concurrently orders_channel_idx;

Backfilling data

One giant UPDATE is the worst option: a long transaction that holds locks, generates enormous WAL, bloats the table and blocks VACUUM from cleaning up anything for its whole duration. Go in batches, each its own transaction:

update orders
set channel = 'web'
where id in (
  select id from orders where channel is null order by id limit 5000
);

Loop until it affects no rows, with a short pause between batches so autovacuum can keep pace. A partial index on where channel is null makes each batch cheap to find and can be dropped when the backfill finishes.

Renames, drops and type changes: expand-contract

These are the ones that genuinely can't be done atomically, because old and new application code run simultaneously during a rolling deploy. Renaming total to total_cents in one migration breaks every instance still running the old code. Instead, do it in phases:

1. Expand. Add the new column. Deploy code that writes to both and reads the old one.
2. Migrate. Backfill in batches until the new column is complete.
3. Switch. Deploy code that reads the new column, still writing both.
4. Contract. Once nothing references the old column, stop writing it, then drop it.

Changing a column's type follows the same shape, because most type changes rewrite the table (int to bigint does; widening a varchar(n) or converting it to text doesn't). Dropping a column is fast in itself — it's only a catalogue change — but wait until you're certain no deployed code, view, or report references it.

Run migrations on a direct connection

Don't push DDL through a transaction-mode pooler. Session state isn't guaranteed between statements, advisory locks used by migration tools may not behave, and CREATE INDEX CONCURRENTLY can't run in a transaction block at all. On WattleDB that's exactly what the second connection string is for: pooled DATABASE_URL for the app, direct DIRECT_URL for migrations — the split is explained in the pooling guide, and both Prisma and Drizzle model it natively.

The pre-flight checklist

• lock_timeout set, with a retry, on every DDL statement.
• No ALTER that rewrites a large table in one shot.
• NOT NULL and foreign keys added NOT VALID, then validated.
• Indexes created CONCURRENTLY.
• Backfills batched, each in its own transaction.
• Renames and drops split into expand → migrate → switch → contract.
• Rehearsed against a realistic copy of production — a masked clone gives you production shape without production PII.
• A known-good recovery point before you start.