CREATE INDEX CONCURRENTLY: The Complete PostgreSQL Guide

CREATE INDEX CONCURRENTLY is the single most important command for safely adding indexes to production PostgreSQL tables. This guide covers how it works, why it sometimes fails, how to handle failures, and the related REINDEX CONCURRENTLY command. Why Regular CREATE INDEX Is Dangerous A regular CREATE INDEX acquires a SHARE lock on the table. This lock blocks all INSERT, UPDATE, and DELETE operations for the entire duration of the index build. On a large table, that can mean minutes or even hours of write downtime. -- DANGEROUS: Blocks all writes for the entire build duration CREATE INDEX idx_orders_customer_id ON orders (customer_id); -- On a 50M row table, this might take 3-5 minutes -- Every INSERT, UPDATE, DELETE queues behind this lock -- Connection pools fill up, application errors cascade How CREATE INDEX CONCURRENTLY Works CREATE INDEX CONCURRENTLY builds the index without holding a lock that blocks writes. Instead of scanning the table once under a SHARE lock, it performs the