.tip

A plain CREATE INDEX takes a lock that blocks every write to the table for as long as the build runs. On a big table that is minutes of outage that looks like a hung app.

CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);

It takes longer and cannot run inside a transaction, so migration tools need to be told. If it fails halfway it leaves an invalid index behind. Check pg_index.indisvalid and drop it before retrying.