What does CREATE INDEX CONCURRENTLY do and what are its trade-offs?
It builds the index without blocking writes to the table, by scanning the table in multiple passes. It takes longer, cannot run inside a transaction block, and can leave an INVALID index behind if it fails.
Explain it simply
A plain CREATE INDEX is like closing a shop to rearrange the shelves: no new orders can be inserted, updated or deleted until it finishes (reads still work). CREATE INDEX CONCURRENTLY rearranges shelves while the shop stays open. It has to walk through the table twice and wait for old transactions, so it is slower and uses more resources.
Example
-- Blocks writes for the whole build
CREATE INDEX orders_date_idx ON orders (created_at);
-- Keeps writes flowing
CREATE INDEX CONCURRENTLY orders_date_idx ON orders (created_at);
-- Check for a failed build
SELECT indexrelid::regclass, indisvalid
FROM pg_index WHERE NOT indisvalid;
-- Clean up and retry
DROP INDEX CONCURRENTLY orders_date_idx;Limits: it cannot run inside BEGIN ... COMMIT, so many migration tools need a setting to run it outside a transaction. If it fails, for instance on a unique violation, the index remains but is marked invalid and is not used.
What to say in the interview
"On a live production table I use CREATE INDEX CONCURRENTLY so writes are not blocked. It is slower, cannot run in a transaction, and a failure leaves an INVALID index that I must drop and recreate."
Common mistakes
- Forgetting to check for invalid indexes after a failed concurrent build.