How do you create an index concurrently in PostgreSQL?
Run CREATE INDEX CONCURRENTLY idx_name ON table (col); outside any transaction block. PostgreSQL takes a SHARE UPDATE EXCLUSIVE lock instead of SHARE, so reads and writes continue. It costs two table scans and extra waiting. If the build fails it leaves an invalid index behind, which you drop and retry.
Key facts
| Fact | Value | |---|---| | Lock taken | SHARE UPDATE EXCLUSIVE (not SHARE), so INSERT/UPDATE/DELETE keep running (CREATE INDEX) | | Table passes | Two scans, plus waits for all in-flight transactions that could touch the table (CREATE INDEX) | | Transaction block | Not allowed — cannot run inside BEGIN … COMMIT (CREATE INDEX) | | Concurrency limit | One concurrent build per table at a time (CREATE INDEX) | | On failure | An index with pg_index.indisvalid = false is left behind: ignored by the planner, still maintained on every write (CREATE INDEX) | | Partitioned tables | Not supported directly; build per-partition concurrently, then create the parent index (CREATE INDEX) | | Progress view | pg_stat_progress_create_index, one row per backend running a build (Progress Reporting) | | maintenance_work_mem | Default 64MB; raise it per-session for a large build (Resource Consumption) | | max_parallel_maintenance_workers | Default 2 (Resource Consumption) |
Why this happens
A plain CREATE INDEX takes a SHARE lock on the table. Reads carry on; every write blocks until the index is finished. On a busy table that is an outage with a progress bar.
CREATE INDEX CONCURRENTLY (often shortened to CIC) trades that outage for time. The documented mechanism is:
1. A first pass adds the index to the catalogs and scans the table once to build it. 2. PostgreSQL then waits for every transaction that could still be modifying the table to finish, so that nothing is in flight with an older view of the data. 3. A second pass scans the table again and adds any rows that appeared or changed since the first pass. 4. It waits once more — this time for transactions holding snapshots that predate the second pass — before marking the index valid and usable.
Because the lock is SHARE UPDATE EXCLUSIVE rather than SHARE, ordinary DML is not blocked for the duration. That lock does still conflict with other SHARE UPDATE EXCLUSIVE holders, which is why VACUUM, ANALYZE and a second concurrent build on the same table have to queue.
The cost is exactly what the extra work implies: the docs state plainly that the total build time is longer and the total CPU and I/O consumed is higher than a normal build. You are paying more resources overall to avoid concentrating the pain into one blocking window.
The two waiting steps are the part that surprises people. They are not proportional to table size — they are proportional to how long your oldest relevant transaction runs. A single session sitting idle in transaction can hold a concurrent build at step 2 or step 4 for as long as it stays open, with no progress and no error. Finding and clearing that session is a pg_stat_activity job; see the pg_stat_activity guide.
How to detect it
Two things are worth watching: builds that are currently running, and builds that failed and left something behind.
For an in-flight build, pg_stat_progress_create_index has one row per backend doing the work:
SELECT p.pid,
c.relname AS table_name,
p.phase,
p.blocks_done, p.blocks_total,
p.tuples_done, p.tuples_total,
p.lockers_done, p.lockers_total,
p.current_locker_pid,
a.query_start,
now() - a.query_start AS elapsed
FROM pg_stat_progress_create_index p
JOIN pg_class c ON c.oid = p.relid
JOIN pg_stat_activity a ON a.pid = p.pid;
Read phase first. building index means real work. Anything beginning waiting for — waiting for writers before build, waiting for writers before validation, waiting for old snapshots — means the build is idle, and current_locker_pid names the session it is idle behind. lockers_done out of lockers_total tells you how far through the queue it is.
For a failed build, the evidence is in pg_index:
SELECT n.nspname AS schema_name,
c.relname AS index_name,
t.relname AS table_name,
i.indisvalid,
i.indisready,
pg_size_pretty(pg_relation_size(c.oid)) AS index_size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid
ORDER BY pg_relation_size(c.oid) DESC;
Any row here is a liability. The documentation is explicit: an invalid index is ignored for querying because it might be incomplete, but it is still updated on every insert and update to the table. You pay the write cost and get none of the read benefit. Nothing in PostgreSQL will warn you — no error, no log line at the time you next query the table, just a slightly slower write path forever.
One footnote that catches people out: if a concurrent unique index build fails during its second scan, the invalid index continues to enforce its uniqueness constraint against newly inserted rows even though it is unusable for queries.
Across 31 monitored instances where MyDBA scored the indexes health domain in the last seven days, 67.7% had at least one duplicate index and 58.1% had unused indexes flagged — the two most common findings in that domain by a wide margin. Invalid indexes are much rarer than either, which is precisely why nobody goes looking for them.
How MyDBA shows this


MyDBA scores an indexes health domain continuously (checks F0–F4: never-analyzed, unused indexes, unused index size, duplicates, missing indexes), and a dedicated invalid-index check that raises a critical finding for any index with indisvalid = false; today that check is scoped to vector indexes (V3 in the pgvector domain), so it is honest to say it covers the pgvector case rather than every index type. Separately, the collector reads pg_stat_progress_create_index on its slow tier, so a build that is running shows live phase and percentage, with a linear ETA derived from whichever counter the build actually populates — and no ETA at all when the counters do not support one. That last part is deliberate: when blocks_total and tuples_total are both zero (the long opaque tail of an HNSW graph build), when fewer than three readings exist yet, or when the counter has held the same value across three consecutive readings, MyDBA returns a machine-readable reason (no_progress_counters, insufficient_snapshots, counters_stalled, zero_progress_yet) instead of a confident number that shrinks forever and never arrives.
Want to see what your own indexes look like? Start with the free PostgreSQL health check.
How to fix it
If you have an invalid index, the recommended recovery is to drop it and try again — not to reindex it:
-- 1. Remove the failed index, without blocking writes either.
DROP INDEX CONCURRENTLY idx_orders_customer_id;
-- 2. Fix the underlying cause first. For a unique build, find the duplicates:
SELECT customer_id, count(*)
FROM orders
GROUP BY customer_id
HAVING count(*) > 1
LIMIT 20;
-- 3. Retry.
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);
REINDEX INDEX CONCURRENTLY can rebuild an invalid index, and the REINDEX docs say so. But it is the wrong first move here for two reasons. It rebuilds whatever definition failed, so a unique build that failed on real duplicate data will fail again in exactly the same way. And it leaves you reasoning about two overlapping invalid-index states rather than one. Drop, fix the data, retry. There is more on when reindexing is the right tool in how and when to use REINDEX.
Before retrying a large build, give it more memory and more workers, in the same session:
SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);
maintenance_work_mem defaults to 64MB, which makes a multi-gigabyte sort spill to disk. max_parallel_maintenance_workers defaults to 2 and applies to the index methods that support parallel builds — B-tree and BRIN, with GIN added in PostgreSQL 18. Note that parallel workers share maintenance_work_mem between them rather than each getting their own allocation.
If the build is stuck rather than failed, the fix is upstream of the index entirely: find the blocking session named in current_locker_pid, work out whether it is a long analytical query or a forgotten idle in transaction connection, and deal with that.
How to prevent it
Never wrap it in a transaction. This is the single most common way CIC breaks in practice. Most migration frameworks run each migration file inside BEGIN … COMMIT by default, and CREATE INDEX CONCURRENTLY errors out immediately in that context. Every major framework has an opt-out (Rails disable_ddl_transaction!, Django atomic = False, Flyway executeInTransaction=false, golang-migrate a separate no-transaction file). Use it, and accept the consequence: a migration that runs outside a transaction cannot roll back cleanly, so it must be idempotent and re-runnable.
Check validity as a separate step. A concurrent build can "succeed" from your client's point of view and still leave an invalid index if it was interrupted. Make the check part of the deploy, not a thing you remember to do:
SELECT count(*) AS invalid_indexes
FROM pg_index
WHERE NOT indisvalid;
-- expect 0
For constraints, build the index first, then attach it. If you want a unique or primary-key constraint without a long write lock, build the index concurrently, verify it is valid, and only then promote it with ALTER TABLE … ADD CONSTRAINT … USING INDEX, which is a fast catalog operation:
CREATE UNIQUE INDEX CONCURRENTLY orders_reference_uk ON orders (reference);
-- verify before promoting
SELECT indisvalid FROM pg_index WHERE indexrelid = 'orders_reference_uk'::regclass;
ALTER TABLE orders
ADD CONSTRAINT orders_reference_uk UNIQUE USING INDEX orders_reference_uk;
More on that pattern in the unique constraint guide and the unique index guide.
On partitioned tables, work bottom-up. Concurrent builds on a partitioned parent are not supported. Build the index concurrently on each partition, then create the matching index on the parent — PostgreSQL recognises the existing per-partition indexes and attaches them, making the parent step a metadata-only operation.
Clear long transactions before you start. Check for anything old enough to stall the waiting phases, and either let it finish or schedule the build around it. idle_in_transaction_session_timeout set to a sane value on the application role removes the worst category permanently.
Budget for the extra load. Two scans plus extra CPU and I/O is not free on a saturated instance. If the box is already at its I/O ceiling, a concurrent build competes with the workload you were protecting.
FAQ
Is CREATE INDEX CONCURRENTLY completely lock-free?
No. It takes a SHARE UPDATE EXCLUSIVE lock for the whole build. That permits SELECT, INSERT, UPDATE and DELETE, but blocks VACUUM, ANALYZE, ALTER TABLE and a second concurrent build on the same table. It also needs a brief conflicting lock at the very start, which can queue behind an existing long transaction.
Why is my concurrent build stuck at 0% with no error?
Almost always a waiting phase. Check phase in pg_stat_progress_create_index — if it starts with waiting for, the build has done its work and is blocked on current_locker_pid. A single idle in transaction session will hold it there indefinitely, because PostgreSQL cannot know that session will never touch the table. The pg_stat_activity guide covers how to find and end it.
Can I build several indexes concurrently on the same table at once?
No. PostgreSQL allows only one concurrent index build per table at a time, so they queue. Regular (non-concurrent) builds on the same table can run simultaneously, which is one reason a maintenance window with a plain CREATE INDEX is sometimes still the better trade for a batch of indexes.
Does an invalid index cost anything if I just leave it?
Yes. It is never chosen by the planner, but it is still maintained on every write to the table, so you get the overhead with none of the benefit. It also consumes disk. Drop it with DROP INDEX CONCURRENTLY — there is more on doing that safely in how to drop an index.
How do I know whether the index was worth building at all?
Once it is valid and has been in production through a representative workload, check pg_stat_user_indexes.idx_scan. An index with zero scans after a full business cycle is a write tax. See index usage optimization and how to create an index in PostgreSQL for choosing the right definition in the first place.
Part of the PostgreSQL index types: the complete guide guide. Last verified against PostgreSQL 18, 2026-09-17.