CREATE INDEX CONCURRENTLY: Building Indexes Online
A plain CREATE INDEX takes a SHARE lock on the table.
Reads continue, but every INSERT, UPDATE and DELETE waits until the build finishes. On a large table that wait can be long. CREATE INDEX CONCURRENTLY builds the same index while writes continue. It scans the table twice and waits for older transactions to finish, so it takes longer. A failed build leaves an INVALID index that you must drop. It cannot run inside a transaction block.
Pro members see the rest of this lesson
- A 4-step state diagram: Announce → Build → Wait → Validate
- The full mechanism: what PostgreSQL does internally, in source terms
- 3 SQL queries you can run, each labelled by how it was verified
- 3 transcripts captured on PostgreSQL 17.10
- The closing insight, the mistake it prevents, and what it changes in your work
Check it against the source1 citation in postgres/postgres · file, symbol and line verified on REL_17_STABLE
Primary symbol: DefineIndex · line 540
Anchored to postgres/postgres on REL_17_STABLE and cross-checked against the manual for PostgreSQL 15–18. Every query here was run and its output captured on a throwaway PostgreSQL 17.10 lab; corrections are noted inline. CREATE INDEX (CONCURRENTLY) in the official docs →
Connected
Where this lesson sits
What comes before, after, and alongside it.
Part of these pathways
Same learning track
Finished a free lesson?
Pro opens the rest of the engine course
You felt one mechanism. Pro is the full bodies, interview depth, and the tracks that build on this session.