CREATE INDEX CONCURRENTLY: zero-downtime indexing in PostgreSQL

A plain CREATE INDEX takes a lock that blocks writes to the table for the entire build. On a tiny table you never notice; on a hot table with millions of rows, freezing every INSERT and UPDATE for minutes is an incident. CREATE INDEX CONCURRENTLY builds the index without blocking writes, and it is the workhorse of zero-downtime migrations.

What a plain CREATE INDEX locks

A regular CREATE INDEX takes a SHARE lock on the table: reads keep flowing, but any write queues up until the build finishes.

-- locks out writes to orders until the index is built
CREATE INDEX idx_orders_user_id ON orders (user_id);

On an orders table with tens of millions of rows the build takes minutes, and the application cannot insert orders the whole time. The CONCURRENTLY variant fixes exactly that:

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Postgres makes two passes over the table and waits for in-flight transactions, so the build is slower and harder on the disk, but writes never stall. The syntax is otherwise identical, including composite indexes:

CREATE INDEX CONCURRENTLY idx_orders_user_status
    ON orders (user_id, status);

Why it can't run inside a transaction

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. The command manages several internal transactions and waits on other sessions, so wrapping it in BEGIN ... COMMIT is an error:

BEGIN;
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);  -- ERROR
COMMIT;

The practical consequence for migrations: this statement must run outside a transaction. Many migration tools wrap each step in a transaction by default, so for a CONCURRENTLY step you have to turn that off (disable_ddl_transaction! in Rails, atomic = False in Django, a dedicated non-transactional step elsewhere).

• One index per migration step.
• Do not mix CONCURRENTLY with other DDL in the same step.
• Budget time: on large tables this is tens of minutes.

The INVALID index a failure leaves behind

This is the sneakiest part. If a CONCURRENTLY build fails (duplicate rows for a UNIQUE index, a cancel, a dropped connection), the index does not vanish; it stays in the catalog marked INVALID. The planner ignores it, but it still slows writes, takes space, and blocks recreating an index under the same name.

Find them:

SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE indisvalid = false;

It also helps to watch a live build's progress:

SELECT phase, blocks_done, blocks_total
FROM pg_stat_progress_create_index;

Fixing an INVALID index is straightforward: drop it and rebuild it.

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Gotcha: do not blindly "repair" an INVALID index with REINDEX. Before Postgres 12 a REINDEX on a regular index takes a heavy lock; from 12 on there is REINDEX INDEX CONCURRENTLY, which is built precisely for a no-downtime rebuild.

DROP INDEX CONCURRENTLY and idempotency

Dropping an index also takes a lock: a plain DROP INDEX on a hot table freezes both reads and writes for the moment of the drop. DROP INDEX CONCURRENTLY removes that lock the same way the build does:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_status;

It has the same constraints: no transaction block, and you cannot drop several indexes in one command. But IF EXISTS makes the step idempotent, so a re-run will not fail.

The full safe "recreate an index with no downtime" pattern:

DROP INDEX CONCURRENTLY IF EXISTS idx_employees_dept;
CREATE INDEX CONCURRENTLY idx_employees_dept ON employees (dept, salary);

How other engines differ

• MySQL/InnoDB: there is no CONCURRENTLY keyword, but online DDL is built in. CREATE INDEX ... ALGORITHM=INPLACE, LOCK=NONE (or ALTER TABLE ... ADD INDEX) usually builds the index while writes continue. The engine decides whether LOCK=NONE is possible and errors out if it is not.
• ClickHouse: secondary skip indexes are added with ALTER TABLE ... ADD INDEX, which is cheap, but the index only applies to existing data after MATERIALIZE INDEX, which does the background work.

For PostgreSQL the rule is simple: in production, build and drop every index with CONCURRENTLY, as its own non-transactional step, and always check pg_index.indisvalid after the migration.