Under the Hood: Non-Blocking Index Creation in Production
Executing a standard CREATE INDEX idx_orders ON orders(created_at); on a 500,000,000-row table takes a SHARE lock for the whole build. This blocks all incoming INSERT, UPDATE, and DELETE statements (reads still proceed). In production, this causes application connection pools to exhaust within seconds, triggering an immediate site outage.
PostgreSQL CREATE INDEX CONCURRENTLY Pipeline
flowchart TD
Start["CREATE INDEX CONCURRENTLY"] --> Scan1["Phase 1: Register index in catalog as invalid (SHARE UPDATE EXCLUSIVE lock), then wait for transactions that modified the table"]
Scan1 --> Scan2["Phase 2: Full table scan to build B-Tree from existing tuples"]
Scan2 --> Scan3["Phase 3: Index marked ready so ordinary writes maintain it, then a second scan adds tuples missed by Phase 2"]
Scan3 --> Validate["Phase 4: Wait for older snapshots, then mark index VALID for the query planner"]
Under the Hood: The Cost and Failure Modes of CONCURRENTLY
- Two Full Table Scans: Because it avoids taking a lock that blocks writes,
CONCURRENTLYmust scan the entire multi-gigabyte table twice, taking significantly longer than a standard index build. - Invalid Indexes (Failed Builds): If a deadlock, a uniqueness violation, a cancellation, or a lock or statement timeout interrupts the build, the index is left in an
INVALIDstate. It does not speed up reads, but continues to slow down every future write. You must detect and drop invalid indexes:SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; DROP INDEX CONCURRENTLY IF EXISTS idx_orders_broken;
Worked example: adding an index to a 500M-row table
Suppose the orders table is 500M rows and about 120 GB, with 4,000 writes/s.
| Approach | Lock held | Writes blocked? | Approximate duration |
|---|---|---|---|
CREATE INDEX | SHARE (whole build) | Yes, for the whole build (reads OK) | About 20-40 min at 100-200 MB/s scan plus sort |
CREATE INDEX CONCURRENTLY | SHARE UPDATE EXCLUSIVE | No | About 2-3x longer: two table scans plus waits for old transactions |
MySQL 8 ALTER TABLE ... ADD INDEX, ALGORITHM=INPLACE, LOCK=NONE | Brief metadata lock at start and end | No (changes buffered in an online log) | Similar to a normal build, plus log apply |
| gh-ost / pt-online-schema-change | Brief lock at cut-over | No | Hours: copies the whole table |
With 4,000 writes/s, a blocking 30-minute build would stall about 7.2M writes. Connection pools fill within seconds and the site goes down, which is why production uses the concurrent form.
Safe runbook (PostgreSQL)
SET lock_timeout = '5s';so the build gives up instead of queuing everyone behind it.SET maintenance_work_mem = '2GB';for faster sorts, and considermax_parallel_maintenance_workers.CREATE INDEX CONCURRENTLY idx_orders_created ON orders(created_at);- Verify:
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;If the new index is listed,DROP INDEX CONCURRENTLYit and retry. - Watch progress in
pg_stat_progress_create_index.
flowchart TD
A["Create catalog entry, index marked not ready"] --> B["Wait for all transactions that could miss the index"]
B --> C["Scan 1: build index from a snapshot"]
C --> D["Mark ready: new writes now maintain it"]
D --> E["Scan 2: validate and insert rows missed by scan 1"]
E --> F["Wait for older snapshots to finish"]
F --> G["Mark valid: planner may use it"]
Failure modes
- Waiting forever: CIC waits for every transaction older than its snapshot. One forgotten idle-in-transaction session stalls the build indefinitely.
- Unique index violations: a concurrent unique build fails if duplicates arrive mid-build, leaving an invalid index.
- Replica lag: the build generates heavy WAL, and replicas may lag during it.