Migration Guard › sample report 2
CREATE INDEX without CONCURRENTLY
Illustrative: written by hand to show the report format. The Action is not built yet, and this statement is a minimal synthetic example, not taken from a real repository.
| Statement | CREATE INDEX idx_orders_customer ON orders (customer_id); |
|---|---|
| Lock taken | SHARE on orders. It blocks INSERT, UPDATE and DELETE on the table for the whole index build; reads are allowed. |
| Rewrites the table | No. The table is not rewritten, but it is scanned, and the build time grows with table size. |
| Verdict | BLOCK-RISK |
Why
Writes to orders wait until the index build finishes. On a table of unknown size this can be a long write outage.
Lower-risk rewrite
Build the index with CONCURRENTLY, which does not block writes. It cannot run inside a transaction block, so run it in its own migration with transactions disabled. A failed concurrent build leaves an invalid index that must be dropped and retried.
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
Sources: PostgreSQL docs: explicit locking; PostgreSQL docs: CREATE INDEX (building indexes concurrently).
Table size is unknown to this report. Statements outside the fixed rule list would be marked REVIEW.