Concurrent Indexes (CREATE INDEX CONCURRENTLY)
Build indexes without blocking writes using CREATE INDEX CONCURRENTLY, and recover from interrupted builds.
Concurrent Indexes (CREATE INDEX CONCURRENTLY) is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
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);How It Works
Two table scans:
- First pass: build the index from the current rows
- Second pass: catch any rows changed during the first pass
Plus a brief wait for any concurrent transaction that started before the build.
Cannot Run Inside Transaction
CONCURRENTLY commits twice — so it can't be inside an explicit BEGIN/COMMIT block:
BEGIN;
CREATE INDEX CONCURRENTLY ...; -- ERROR
COMMIT;Slow, but Online
CONCURRENTLY takes longer than a regular CREATE INDEX (two scans, lock waits). The benefit: zero downtime.
Failure Recovery
If CONCURRENTLY fails (interrupted, system error), the index is left in an INVALID state:
SELECT relname FROM pg_class
WHERE relkind = 'i' AND NOT pg_index.indisvalid;
-- Fix: drop and re-create:
DROP INDEX CONCURRENTLY my_idx;
CREATE INDEX CONCURRENTLY my_idx ON ...;REINDEX CONCURRENTLY
Rebuild without blocking, since PG 12:
REINDEX INDEX CONCURRENTLY orders_user_id_idx;
REINDEX TABLE CONCURRENTLY orders;DROP INDEX CONCURRENTLY
Also non-blocking:
DROP INDEX CONCURRENTLY old_idx;Unique Indexes
UNIQUE indexes can also be built CONCURRENTLY. Useful for adding UNIQUE constraints online:
CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON users(email);
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_uidx;Foreign Key Indexes
Standard pattern when adding an FK to a big table:
-- 1. Build the index online:
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders(user_id);
-- 2. Add the FK with NOT VALID (instant):
ALTER TABLE orders ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
-- 3. Validate in the background:
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;Concurrent Index Building Cost
Two full table scans + bookkeeping. Eats CPU and IO. Run during low-traffic hours when possible.
Monitor Progress
PG 12+ exposes progress in pg_stat_progress_create_index:
SELECT phase,
blocks_done, blocks_total,
tuples_done, tuples_total
FROM pg_stat_progress_create_index;Recap
CONCURRENTLY is the production-friendly way to manage indexes.
- CREATE/DROP/REINDEX CONCURRENTLY don't block writes
- Can't be inside a transaction
- Failures leave INVALID indexes — clean up
- Monitor with pg_stat_progress_create_index
Quick Check
Why can't CREATE INDEX CONCURRENTLY run inside an explicit BEGIN/COMMIT block?
Frequently asked questions
Is the “Concurrent Indexes (CREATE INDEX CONCURRENTLY)” lesson free?
Yes — the full text of “Concurrent Indexes (CREATE INDEX CONCURRENTLY)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Concurrent Indexes (CREATE INDEX CONCURRENTLY)”?
Build indexes without blocking writes using CREATE INDEX CONCURRENTLY, and recover from interrupted builds. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Concurrent Indexes (CREATE INDEX CONCURRENTLY)” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Online Migrations: Why ALTER TABLE Locks
- Concurrent Indexes (CREATE INDEX CONCURRENTLY)
- Zero-Downtime Column Renames
- Tools: Flyway, Liquibase, Sqitch