The migration was one line, CREATE INDEX on the orders table, and it took the site down for eleven minutes. Every write to orders hung until the index finished building.
How do you add an index, a column or a constraint to a big, busy PostgreSQL table without an outage? Most schema changes can be made online. You need to know which statements lock what, and one non-obvious thing about lock queues.
The lock queue problem
Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with everything, including plain SELECTs. Even when the change itself is instant, it has to wait for current transactions on the table to finish. While it waits, every new query on the table queues behind it.
So a "metadata-only" ALTER that takes 5 ms can still cause an outage if a 3-minute report happens to be reading the table: for 3 minutes, nothing can read or write it.
Always set a lock timeout for migrations and retry:
SET lock_timeout = '3s';
SET statement_timeout = '15min';
ALTER TABLE orders ADD COLUMN note text;
If the lock isn't granted within 3 seconds, the ALTER gives up and traffic continues. Retry a few times with a short pause. Many migration tools (for example strong_migrations and pgroll) automate this.
Indexes: CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
A plain CREATE INDEX blocks writes (it takes a SHARE lock) for the whole build. CONCURRENTLY allows reads and writes during the build in exchange for scanning the table twice and taking longer.
