Postgres migrations that lock your tables

1. CREATE INDEX blocks writes

A plain CREATE INDEX blocks inserts, updates and deletes on the table until the index is built. On a large table that can take minutes.

create index orders_customer_id_idx on orders (customer_id);

create index concurrently orders_customer_id_idx on orders (customer_id);

CONCURRENTLY cannot run inside a transaction, so most migration tools need it in a migration of its own with the transaction turned off. If it fails halfway it leaves an invalid index; drop it and run it again.

2. SET NOT NULL scans the whole table under the strictest lock

ALTER TABLE ... ALTER COLUMN ... SET NOT NULL takes an ACCESS EXCLUSIVE lock, which blocks reads too, and keeps it while Postgres checks every row. On Postgres 12 and later you can do the check first without blocking anyone.

alter table orders alter column shop_id set not null;

alter table orders add constraint orders_shop_id_not_null check (shop_id is not null) not valid;
alter table orders validate constraint orders_shop_id_not_null;  -- scans, but allows reads and writes
alter table orders alter column shop_id set not null;             -- uses the valid constraint, no scan
alter table orders drop constraint orders_shop_id_not_null;

Adding a new column with a constant default (add column status text not null default 'new') is fast on Postgres 11 and later because existing rows are not rewritten. A volatile default such as now() or gen_random_uuid() still rewrites the table.

3. The lock queue: a quick ALTER can still freeze the site

Even an ALTER that needs its lock for a millisecond has to wait for running queries to finish. While it waits, every new query on that table queues behind it. One slow report query plus one migration is enough to stop checkout. Set a lock timeout so the migration gives up and retries instead of holding everyone up.

set lock_timeout = '3s';
set statement_timeout = '15min';

Test it before you ship