← Back to TIL

create index concurrently

Jul 6, 2026

postgresqlsqlindexperformance

the problem

a regular create index takes a SHARE lock on the table. that lock blocks every insert, update, and delete for the duration of the build. on a table with writes, this means downtime.

what CIC does

create index concurrently uses a ShareUpdateExclusiveLock instead — blocks schema changes and other concurrent index builds, but allows reads and writes.

-- this blocks writes
create index idx_email on users (email);

-- this doesn't
create index concurrently idx_email on users (email);

how it works

CIC is multi-phase, not a single operation:

  1. creates index metadata with indisvalid = false (planner ignores it)
  2. first scan — builds the initial index while writes continue
  3. waits for all transactions that started before phase 2 completed
  4. second scan — catches up on changes made during the first scan
  5. another brief wait, then one more mini-scan
  6. takes a momentary AccessExclusiveLock to mark indisvalid = true

you can watch the phases in pg_stat_progress_create_index.

the catches

long-running transactions are poison. CIC waits for all open transactions that predate it. an analyst's 45-minute select blocks your index build for 45 minutes. on hot tables it can need a third, fourth scan — thrashing until the long query ends.

failure leaves garbage. if CIC fails (unique violation, lock timeout, server restart), the index stays with indisvalid = false. the planner ignores it, but writes still maintain it — burning cpu and i/o on a dead index. you must drop index concurrently before retrying.

can't run in a transaction block. CIC commits its own internal transactions during the build. wrapping it in begin/commit throws an error.

unique indexes are extra fragile. a regular create unique index locks out writes so duplicates can't sneak in. CIC allows writes, so a concurrent duplicate insert fails the whole build retroactively.

2–3× slower than a regular build, and more resource-intensive.

companion: reindex concurrently

same multi-phase approach, but for rebuilding an existing index (e.g., to fix bloat). creates a temp index concurrently, swaps it in, drops the old one.

read more:

the thought is mine. the words are written by janis, my hermes agent.