CREATE INDEX CONCURRENTLY: Building Indexes Online

The one thing to understand first

An ordinary CREATE INDEX takes a SHARE lock on the table for its entire duration. That lock permits reads but blocks all writes — every INSERT, UPDATE, and DELETE waits until the index finishes building, which on a large table can be many minutes. CREATE INDEX CONCURRENTLY (CIC) exists to build the index without blocking writes.

CONCURRENTLY trades wall-clock speed for availability: it scans the table twice and waits for old transactions to finish, but never blocks your writes — and if it fails it leaves an INVALID index you must clean up. Those two facts (the wait, and the INVALID-index aftermath) are where every CIC surprise comes from.

How CONCURRENTLY works

CIC trades speed for availability by doing two passes over the table with only a weak SHARE UPDATE EXCLUSIVE lock (which allows concurrent DML), defined in src/backend/commands/indexcmds.c:

• Build pass. Record a starting snapshot, then scan the table and build the index from all rows visible to that snapshot. The index is marked not-yet-ready.
• Wait. Wait for all transactions that could be using the old schema (that started before the index was registered) to finish.
• Validation pass. Scan the table again to add any rows that changed during the build, then mark the index valid and usable.

Because it scans twice and waits for old transactions, CIC is slower in wall-clock time than a plain build — but it never blocks your application's writes.

The wait that surprises people

Step 2 means CIC can hang behind a single long-running transaction — even one unrelated to the table. If a reporting query or a forgotten idle in transaction session started before your CIC, CIC waits for it to end before it can finish. Always check for long transactions first:

SELECT pid, now() - xact_start AS age, state, query
FROM   pg_stat_activity
WHERE  state <> 'idle'
ORDER  BY age DESC;

Handling failure: INVALID indexes

If CIC fails (a deadlock, a uniqueness violation discovered in the validation pass, cancellation), it leaves behind an INVALID index — present in the catalog but not used by queries and not maintained for new rows correctly. You must clean it up before retrying:

-- Find invalid indexes
SELECT c.relname AS index, i.indisvalid
FROM   pg_index i
JOIN   pg_class c ON c.oid = i.indexrelid
WHERE  NOT i.indisvalid;

-- Drop it (also concurrently, to avoid blocking)
DROP INDEX CONCURRENTLY idx_orders_status;
-- Then re-run CREATE INDEX CONCURRENTLY.

Rules and constraints

• CIC cannot run inside a transaction block — it manages its own snapshots. Migration tools must run it outside a transaction.
• Only one CIC per table at a time interacts safely; serialise them.
• To add a unique constraint online, build a unique index with CIC, then attach it: ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX ....

REINDEX CONCURRENTLY

Bloated indexes can be rebuilt online too. REINDEX INDEX CONCURRENTLY builds a fresh copy alongside the old one and swaps them, again under a weak lock. This is the standard remedy for index bloat without the blocking of a plain REINDEX:

REINDEX INDEX CONCURRENTLY idx_orders_customer;
REINDEX TABLE CONCURRENTLY orders;   -- all indexes on the table

Layer 3 — Watch it happen on your own database

-- 1. Before starting: any old transaction will stall CIC's wait phase
SELECT pid, now() - xact_start AS age, state, query
FROM   pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC;

-- 2. Build it (must NOT be inside a transaction block)
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

-- 3. Confirm it actually succeeded before relying on it
SELECT c.relname AS index, i.indisvalid
FROM   pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE  NOT i.indisvalid;          -- any row here = a leftover INVALID index

The discipline is bookended by two checks. Before: a long-running or idle in transaction session — even one unrelated to this table — will make CIC's wait phase hang, so clear the runway first. After: query pg_index.indisvalid; if CIC failed it leaves an INVALID index that queries silently ignore. Never trust a CIC until you've confirmed indisvalid = true.

Layer 4 — The levers this hands you

The operational recipe, and the rules that constrain it:

• Confirm no long transactions are open, then run CIC outside a transaction block (it manages its own snapshots — migration tools must not wrap it in BEGIN).
• On failure, drop the INVALID index concurrently (DROP INDEX CONCURRENTLY) and retry — never leave it behind.
• Serialise CIC per table — only one concurrent build at a time interacts safely.
• Add unique constraints online by building a unique index with CIC, then ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX ....
• Rebuild bloated indexes online with REINDEX INDEX CONCURRENTLY / REINDEX TABLE CONCURRENTLY — same weak-lock, build-and-swap mechanism.

Layer 5 — What an Oracle DBA should expect vs what they get

Oracle DBAs know online index builds well; the differences are in the failure model:

• **CONCURRENTLY ≈ CREATE INDEX ... ONLINE.** Both build without blocking DML; both are slower than an offline build because they track concurrent changes.
• The INVALID-index aftermath is sharper. A failed Oracle online build also leaves an unusable index, but Postgres's INVALID index is silently ignored by the planner — easy to miss without the indisvalid check.
• Cannot run in a transaction block. Unlike most Postgres DDL (which is transactional), CIC cannot be wrapped in BEGIN/COMMIT — a real gotcha for migration frameworks that wrap everything in a transaction.
• **No ALTER INDEX ... REBUILD ONLINE** — the equivalent is REINDEX ... CONCURRENTLY, and there is no global index to rebuild because Postgres indexes are always local.

Key takeaway

CREATE INDEX CONCURRENTLY builds under a weak SHARE UPDATE EXCLUSIVE lock via two table scans plus a wait for old transactions, so writes never block — at the cost of slower wall-clock time. Clear long transactions first, run it outside a transaction block, and always verify pg_index.indisvalid afterward; if it failed, drop the INVALID index concurrently and retry. REINDEX ... CONCURRENTLY applies the same mechanism to bloat.