PostgreSQL interview question · Most asked
How does CREATE INDEX CONCURRENTLY work, and what are its caveats?
Summary
It builds the index without blocking writes, using multiple passes over the table and waiting for existing transactions to finish. It takes longer, cannot run inside a transaction block, and if it fails it leaves an INVALID index that you must drop and rebuild.
Longer Answer
Check for leftovers with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;. Many migration frameworks wrap migrations in a transaction, so concurrent index creation needs a non-transactional migration. REINDEX CONCURRENTLY (PostgreSQL 12+) rebuilds bloated or corrupted indexes online. DROP INDEX CONCURRENTLY avoids blocking on removal.
Key Points
- No write blocking; slower.
- Not inside a transaction block.
- Failure leaves an INVALID index.
- REINDEX CONCURRENTLY for rebuilds.
More most-asked PostgreSQL questions
- How does MVCC work in PostgreSQL?
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- What does VACUUM do, and how does autovacuum work?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?