Here is the failure.
You ship a migration that adds an index on a busy table:
CREATE INDEX idx_orders_customer_id ON orders ( customer_id );
Enter fullscreen mode
Exit fullscreen mode
Postgres builds the index with a SHARE lock. Other sessions can still SELECT . They cannot INSERT , UPDATE , or DELETE until the build finishes. On a large table that can be minutes or hours of blocked writes. The PostgreSQL CREATE INDEX docs say so outright: a standard index build "locks out writes (but not reads) on the table until it's done."
The fix is also in those docs:
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders ( customer_id );
Enter fullscreen mode
Exit fullscreen mode
CONCURRENTLY builds without taking locks that block concurrent inserts, updates, or deletes.…