PostgreSQL CREATE INDEX CONCURRENTLY: what you pay for not blocking writes

Adding CONCURRENTLY is the usual way to build an index on a PostgreSQL table that is taking live traffic. It removes the write block, but the build takes longer, has to wait on other transactions, and can leave something behind to clean up if it fails. This post walks through that behavior based on the PostgreSQL 18 documentation (18.6 as of 2026-10-13).

How it differs from a plain CREATE INDEX

A plain CREATE INDEX takes a SHARE lock on the table. That lock conflicts with ROW EXCLUSIVE, so reads continue but INSERT, UPDATE, and DELETE wait until the build finishes. The table is scanned once.

CREATE INDEX CONCURRENTLY takes a SHARE UPDATE EXCLUSIVE lock instead. It does not conflict with ROW EXCLUSIVE, so writes keep going. According to the documentation, the costs are:

The table is scanned twice.
The build waits for every existing transaction that could modify or use the index to finish.
As a result it does more total work and takes significantly longer, and the extra CPU and I/O load can slow other operations.

SHARE UPDATE EXCLUSIVE conflicts with itself, which is why only one concurrent index build can run on a table at a time. VACUUM (without FULL) and ANALYZE take the same lock, so going by the conflict table alone, a manual VACUUM or ANALYZE on the same table and a concurrent build will wait on each other (an inference from the conflict table). With either build method, schema changes to the table are not allowed while the index is being built.

What happens inside

The documentation describes this sequence:

In one transaction, the index is entered into the system catalogs as “invalid”.
In two further transactions, the table is scanned once each. Before each scan, the build waits for existing transactions that have modified the table to finish.
After the second scan, it waits for transactions holding a snapshot older than that scan to finish.
Finally the index is marked “valid” and the command returns.

Returning does not guarantee that queries will use the index right away. In the worst case, it stays unusable as long as transactions exist that predate the start of the build.

Parallel index builds also work with CONCURRENTLY, but only the first table scan runs in parallel.

Watching progress: pg_stat_progress_create_index

While CREATE INDEX or REINDEX runs, the pg_stat_progress_create_index view has one row per backend. The phase column maps to the sequence above.

phase	Meaning
`waiting for writers before build`	Before the build, waiting for transactions holding write locks
`building index`	First scan and index build
`waiting for writers before validation`	Before validation, waiting for transactions holding write locks
`index validation: scanning index` / `sorting tuples` / `scanning table`	Second stage, validating changes that arrived during the build
`waiting for old snapshots`	Waiting for transactions with old snapshots to finish

During the waiting phases, lockers_total, lockers_done, and current_locker_pid are filled in. This query joins the view with pg_stat_activity to show what the build is waiting on:

SELECT p.pid,
       p.phase,
       p.lockers_done,
       p.lockers_total,
       p.current_locker_pid,
       a.state,
       a.xact_start,
       left(a.query, 60) AS locker_query
FROM pg_stat_progress_create_index AS p
LEFT JOIN pg_stat_activity AS a
       ON a.pid = p.current_locker_pid;

If the session named by current_locker_pid has state set to idle in transaction and an old xact_start, an open transaction that is not running any query is probably what is holding up the build. That reading combines the documented wait conditions with the pg_stat_activity column definitions. Whether to end that session depends on what it means for the application.

A failed build leaves an INVALID index

If a problem such as a deadlock or a uniqueness violation occurs during a scan, the command fails but leaves an “invalid” index behind. Queries ignore it because it may be incomplete, but INSERT and relevant UPDATE operations still have to maintain it, so it keeps adding update overhead. psql’s \d marks it INVALID.

In the catalog, check pg_index.indisvalid. False means the index cannot safely be used for queries but must still be maintained by INSERT and UPDATE.

SELECT i.indexrelid::regclass AS index_name,
       i.indrelid::regclass   AS table_name
FROM pg_index AS i
WHERE NOT i.indisvalid;

The documented recovery is to drop the index and run CREATE INDEX CONCURRENTLY again. Rebuilding it with REINDEX INDEX CONCURRENTLY is another option.

-- Example names. This example was not executed.
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;
CREATE INDEX CONCURRENTLY orders_customer_id_idx
    ON orders (customer_id);

Instead of taking an ACCESS EXCLUSIVE lock on the table, DROP INDEX CONCURRENTLY waits for conflicting transactions to finish. It accepts only one index name, does not support CASCADE, and cannot be used on indexes of partitioned tables.

When REINDEX CONCURRENTLY fails, look at the name suffix. An invalid index ending in _ccnew is the transient index being built, so drop it and try again. One ending in _ccold is the original index that could not be dropped after a successful rebuild, so dropping it is all that is needed. A number may be appended, as in _ccnew1.

The IF NOT EXISTS trap in retry scripts

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx ... only issues a notice if a relation with that name already exists. The documentation states there is no guarantee the existing index resembles the one that would have been created. So if an earlier attempt left an invalid index, a retry can appear to succeed while the only index present is one that queries never use (an inference from the documented behavior). Running the indisvalid query above before retrying is the safer path.

Unique indexes need extra care

When a unique index is built concurrently, the uniqueness constraint is already enforced against other transactions once the second scan starts. Other queries can therefore get constraint violation errors before the index is usable, or even if the build eventually fails. If the failure happens in the second scan, the leftover invalid index keeps enforcing uniqueness afterwards. Errors while evaluating expressions in expression or partial indexes can cause similar behavior.

The pg_index.indisvalid description says the same thing from the other side: for an invalid unique index, the uniqueness property is not guaranteed to hold either.

Where and on what it can run

Not inside a transaction block. A plain CREATE INDEX can run inside BEGIN ... COMMIT; CONCURRENTLY cannot. If a migration tool wraps each migration in a transaction, this statement needs separate handling (tool-specific settings are out of scope here). DROP INDEX CONCURRENTLY and REINDEX CONCURRENTLY have the same restriction.
Not directly on partitioned tables. The documented workaround is to build the index concurrently on each partition, then create the partitioned index non-concurrently at the end. That last step becomes a metadata-only operation, which shortens the time writes are locked out.
Always non-concurrent on temporary tables. No other session can access them, so the plain method is cheaper.

-- Partitioned table example. This example was not executed.
CREATE INDEX CONCURRENTLY events_2026_09_created_at_idx
    ON events_2026_09 (created_at);
CREATE INDEX CONCURRENTLY events_2026_10_created_at_idx
    ON events_2026_10 (created_at);
-- Create the parent index once every partition has a matching index.
CREATE INDEX events_created_at_idx
    ON events (created_at);

Version note: 14.0-14.3

The PostgreSQL 14.4 release notes (released 2022-06-16) say that an optimization added in v14 sometimes caused CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY to miss rows updated during the build, and 14.4 reverted it. The notes recommend rebuilding, after the update, any index built with CONCURRENTLY on PostgreSQL 14 before 14.4 was installed, that is, on 14.0-14.3.

On the official versioning page checked on 2026-10-13, the supported major versions are 14 through 18, with current minors 18.6, 17.11, 16.15, 15.19, and 14.24. The behavior cited here comes from the PostgreSQL 18 documentation. The “Building Indexes Concurrently” section of the 14 documentation states the same points on two scans, invalid indexes, one concurrent build per table, the transaction-block restriction, and no support for partitioned tables. The 15-17 documentation was not opened separately.

Related: a HOT update is only possible when it does not modify columns referenced by the table’s indexes (excluding summarizing indexes such as BRIN). Adding a new index therefore takes UPDATEs that change that column out of HOT eligibility. See PostgreSQL HOT updates for the conditions.

The SQL examples here were written for illustration and were not run against a database. No diagram is included; the step list and phase table cover the flow.

Sources

These links and their contents were checked on 2026-10-13.