Adding an index to a busy PostgreSQL table

2026-09-02 · #postgres

A plain CREATE INDEX blocks writes to the table for the whole build. On a large table that is an outage. The concurrent form does not block writes:

CREATE INDEX CONCURRENTLY idx_document_patient
    ON document (patient_id, created_at);

It is slower and has two rules worth remembering.

It cannot run inside a transaction

Migration tools wrap scripts in a transaction by default. With Flyway, put the statement in its own migration and add a script config file next to it (V42__add_index.sql.conf):

executeInTransaction=false

A failed build leaves an invalid index

If the build is cancelled or hits a deadlock, the index stays in the catalog, is ignored by the planner, and still slows down writes. Check for leftovers:

SELECT indexrelid::regclass
FROM pg_index
WHERE NOT indisvalid;

Drop it with DROP INDEX CONCURRENTLY and run the build again.