Concurrent Indexes (CREATE INDEX CONCURRENTLY)
Build indexes without blocking writes using CREATE INDEX CONCURRENTLY, and recover from interrupted builds.
The Problem with CREATE INDEX
A regular CREATE INDEX takes a SHARE lock — blocks writes (but not reads) for the entire build. On a billion-row table, that's minutes of write outage.
CONCURRENTLY: The Fix
CREATE INDEX CONCURRENTLY builds the index without blocking writes:
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);All lessons in this course
- Online Migrations: Why ALTER TABLE Locks
- Concurrent Indexes (CREATE INDEX CONCURRENTLY)
- Zero-Downtime Column Renames
- Tools: Flyway, Liquibase, Sqitch