Does CREATE INDEX CONCURRENTLY lock the table in PostgreSQL?

Short answer

CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id) is the way to add an index to a busy table. It takes a SHARE UPDATE EXCLUSIVE lock, which doesn’t conflict with reads or writes, so SELECT, INSERT, UPDATE and DELETE keep running for the whole build.

In return:

• It can’t run inside a transaction block.
• It waits for transactions already writing to the table, and later for any transaction holding an older snapshot, including ones that never touch this table. A forgotten transaction can hold it up for as long as it stays open.
• If it fails or is cancelled, it leaves an INVALID index behind. Queries don’t use it, but it may still be updated on every write. Drop it and try again.

What it locks

• SHARE UPDATE EXCLUSIVE on the table for the whole build. It blocks other schema changes, VACUUM (including autovacuum), ANALYZE and a second concurrent index build on the same table. It doesn’t block SELECT, INSERT, UPDATE or DELETE.
• While it waits for other transactions, it waits on their transaction IDs, not on a table lock. With an uncommitted INSERT open in another session, our observer session printed this on PostgreSQL 18.6 (from pg_stat_progress_create_index and pg_locks):

  progress: waiting for writers before build
  CIC lock: relation seo_mig.big ShareUpdateExclusiveLock
  CIC lock: virtualxid 7/239 ExclusiveLock
  CIC lock: virtualxid 263/333 ShareLock (waiting)

During that wait, a SELECT, an INSERT and an UPDATE on the table from a third session all ran.

The build goes through phases, which you can watch in pg_stat_progress_create_index:

1. Wait for transactions that are writing to the table (waiting for writers before build).
2. Build the index with one scan of the table.
3. Wait for writers again, then scan the table a second time to add rows that changed meanwhile.
4. Wait for every transaction with a snapshot older than that scan (waiting for old snapshots). This includes transactions on other tables in the same database. In our test, a REPEATABLE READ transaction that had only read a different table held the build up until it committed.

SELECT pid, phase, blocks_done, blocks_total, tuples_done, tuples_total
FROM pg_stat_progress_create_index;

Does it rewrite the table?

No. It reads the table twice and writes a new index file. With nothing else running, it took about as long as a plain CREATE INDEX on our 3-million-row table (1.0 to 1.3 s, against 0.6 to 1.5 s for the plain build, one run each); on a busy server, the second scan and the waits make it slower.

When it fails: the INVALID index

If the build fails part-way (a duplicate value in a UNIQUE index, a deadlock, a cancel, a statement_timeout or a lock_timeout), the command errors but the index stays, marked invalid:

ERROR:  could not create unique index "big_n_key"
DETAIL:  Key (n)=(409) is duplicated.

    "big_n_key" UNIQUE, btree (n) INVALID

Queries never use an invalid index. Whether writes still maintain it depends on how far the build got: in our tests, an index that failed during the first scan was empty and not maintained (indisready = false), while one cancelled in the last phase was 20 MB and maintained on every write (indisready = true). The PostgreSQL documentation adds that a unique build which fails in its second scan keeps enforcing uniqueness afterwards.

Find invalid indexes with:

SELECT indexrelid::regclass AS index, indrelid::regclass AS table
FROM pg_index
WHERE NOT indisvalid;

Then either drop it and run the build again, or rebuild it in place:

DROP INDEX CONCURRENTLY orders_customer_id_idx;
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);
-- or
REINDEX INDEX CONCURRENTLY orders_customer_id_idx;

Watch out for IF NOT EXISTS: CREATE INDEX CONCURRENTLY IF NOT EXISTS sees the invalid index’s name, prints NOTICE: relation "big_n_key" already exists, skipping, and leaves it invalid. A migration tool that retries this way thinks it succeeded.

Run it safely

1. Run it on its own, outside a transaction. Inside BEGIN … COMMIT (or a migration framework that wraps each migration in one) it fails at once:

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Most frameworks have a switch to turn the transaction off for one migration.

2. Don’t give it a short lock_timeout. Because it waits on other transactions through locks, lock_timeout applies to those waits as well. With lock_timeout = '1s' and one transaction open for three seconds, the build failed with ERROR: canceling statement due to lock timeout and left an invalid index behind, on every version. Its own table lock doesn’t block reads or writes, so it doesn’t need a short timeout to protect traffic:

SET lock_timeout = 0;
SET statement_timeout = 0;
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

3. Clear long transactions first. Before starting, look for anything old:

SELECT pid, now() - xact_start AS open_for, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 10;

4. Check the result, and retry if needed. After it returns, confirm the index is valid (the query above returns nothing). If it isn’t, drop it with DROP INDEX CONCURRENTLY and run the build again at a quieter time.

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac), with a 3-million-row table:

CREATE TABLE seo_mig.big (id bigint, n int, s text);
INSERT INTO seo_mig.big SELECT g, g % 1000, md5(g::text) FROM generate_series(1, 3000000) g;

Session A kept a write open: BEGIN; INSERT INTO seo_mig.big VALUES (0, 0, 'w'); SELECT pg_sleep(4); ROLLBACK;. Session B ran CREATE INDEX CONCURRENTLY big_n_idx ON seo_mig.big (n) half a second later. Session C read pg_stat_progress_create_index and session B’s rows in pg_locks, then ran statements with lock_timeout = '1s'.

Failure cases, identical on all five versions:

REINDEX INDEX CONCURRENTLY on the 20 MB invalid index made it valid on every version. On 18.6 the duplicate-key error also printed CONTEXT: parallel worker, because the build ran in parallel.

In Inlet

While a concurrent build runs, the Activity monitor shows sessions, locks and which query blocks which, so you can find the old transaction it’s waiting for, and cancel or terminate it.