SDB201[high]Indexes

Index built without CONCURRENTLY

CREATE INDEX on an existing table blocks writes until the build finishes.

Why it's risky in PostgreSQL

A plain CREATE INDEX takes a SHARE lock: reads continue, but every INSERT, UPDATE and DELETE on the table waits until the index is built. On a large table that is minutes of blocked writes.

CREATE INDEX CONCURRENTLY builds the index without blocking writes. It can't run inside a transaction block, so put it in its own migration file without a transaction.

What triggers it, and the safer way

unsafe.sql

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

safer.sql

-- migration file without a transaction (tool-specific setting)
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

Suppress it when you're sure

Put this comment on the line before the statement. The reason is required and shows in the report.

suppression

-- shadowdb:allow SDB201 <why this is safe here>

Applies to PostgreSQL 14, 15, 16, 17.