Create an Index Without Blocking Writes

You need an index on a table that the application is writing to right now. A plain CREATE INDEX is fast on a small table and a problem on a busy one, because it blocks every INSERT, UPDATE and DELETE until the index is built. CREATE INDEX CONCURRENTLY builds the index without that block, at the cost of more work, more time, and a few rules. This page shows the block, the safe form, the one error you will hit, and the leftover that a failed build can leave behind. It uses two psql sessions, A and B, in the order the commands ran.

Step 1: a table that someone is writing to

Session A

CREATE TABLE big_orders AS
SELECT g                                AS order_id,
       (g % 8) + 1                      AS customer_id,
       DATE '2026-01-01' + (g % 365)    AS order_date,
       ((g % 97) + 0.5)::numeric(8,2)   AS total
FROM generate_series(1, 20000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (order_id);
BEGIN;
INSERT INTO big_orders VALUES (20001, 1, DATE '2026-10-01', 12.00);

SELECT 20000
ALTER TABLE
BEGIN
INSERT 0 1

Session A inserted a row and has not committed. It stands for any writer that is in the middle of a transaction.

Step 2: see what a plain CREATE INDEX does

Session B tries a normal index build. It gives itself 300 milliseconds to get the lock, and \set VERBOSITY terse keeps each error to its first line.

Session B

\set VERBOSITY terse
SET lock_timeout = '300ms';
CREATE INDEX big_orders_order_date_idx ON big_orders (order_date);

SET
ERROR:  canceling statement due to lock timeout

A plain CREATE INDEX takes a SHARE lock, which conflicts with the ROW EXCLUSIVE lock that A's insert holds, and with the one that every other writer would need. Without the timeout, B would wait for A, and every writer that arrived after B would wait for B. CREATE INDEX CONCURRENTLY asks for a weaker lock, SHARE UPDATE EXCLUSIVE. You can test whether it conflicts with A's insert by asking for the same lock without waiting:

BEGIN;
LOCK TABLE big_orders IN SHARE UPDATE EXCLUSIVE MODE NOWAIT;
ROLLBACK;

BEGIN
LOCK TABLE
ROLLBACK

The lock was granted at once, so writers and the concurrent build do not block each other. The build still has to wait for transactions that were already open when it started, such as A, to finish, and it waits for them twice. Finish A now.

Session A

COMMIT;

COMMIT

Step 3: build it with CONCURRENTLY

CREATE INDEX CONCURRENTLY big_orders_order_date_idx ON big_orders (order_date);

CREATE INDEX

It works now that A has committed. The rules to remember are these. It cannot run inside a transaction block, which is the error you will hit when a migration tool wraps every step in BEGIN:

BEGIN;
CREATE INDEX CONCURRENTLY big_orders_total_idx ON big_orders (total);
ROLLBACK;

BEGIN
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
ROLLBACK

Configure the migration tool to run this one step outside a transaction. The build also reads the table twice and takes longer than a plain build, and it uses more I/O while it runs.

Step 4: a failed build leaves an INVALID index

A concurrent build can fail halfway, for example if a unique index finds duplicates. PostgreSQL does not clean up after itself: the index stays in the catalog, marked invalid.

CREATE UNIQUE INDEX CONCURRENTLY big_orders_customer_uidx ON big_orders (customer_id);
SELECT indexrelid::regclass AS index_name, indisvalid AS valid
FROM pg_index WHERE indrelid = 'big_orders'::regclass ORDER BY 1;

ERROR:  could not create unique index "big_orders_customer_uidx"
        index_name         | valid
---------------------------+-------
 big_orders_pkey           | t
 big_orders_order_date_idx | t
 big_orders_customer_uidx  | f
(3 rows)

The unique build failed on the first duplicate it met. Look at valid: the failed index is still there, and false means that queries do not use it, but every write still pays to keep it up to date. Drop it, also without blocking, and fix the data before you try again.

DROP INDEX CONCURRENTLY big_orders_customer_uidx;
SELECT indexrelid::regclass AS index_name, indisvalid AS valid
FROM pg_index WHERE indrelid = 'big_orders'::regclass ORDER BY 1;

DROP INDEX
        index_name         | valid
---------------------------+-------
 big_orders_pkey           | t
 big_orders_order_date_idx | t
(2 rows)

Related course: SQL Advanced.