PostgreSQL concurrent indexes: reason about locks, retries, and invalid states

First principles

An index can make reads faster while adding work to every insert, update, and delete. Building one on a busy table is therefore both a query-planning change and an operational event. PostgreSQL's CREATE INDEX CONCURRENTLY is designed to let ordinary inserts, updates, and deletes continue during most of the build. It does not mean “no locks,” “no extra load,” or “atomic in one short transaction.” The command coordinates multiple scans and waits for transactions that could affect index correctness.

The ordinary CREATE INDEX takes a lock that blocks writes to the table for the duration of the build, although reads continue under the relevant lock rules. The concurrent form uses a weaker lock for normal DML, but does more work, can take longer, and may wait for old snapshots or transactions. A long-running transaction can therefore extend the operation. The build consumes CPU, I/O, and temporary space while production queries are also running. Concurrent is a behavior choice, not a free performance option.

Concurrent index creation cannot run inside a transaction block. It goes through multiple phases, and failure can leave an index relation marked invalid. An invalid index is not a successful result to keep merely because its name exists. Depending on the failure phase, it may still impose update overhead, and a unique concurrent build can have uniqueness effects even if it later fails. Inspect catalog state and the server error before retrying. Do not blindly run the same DDL forever.

A rollout also needs an application compatibility plan. If a new index supports a query introduced in a later release, the index can be built before that code is deployed. If it enforces a new uniqueness rule, existing duplicate rows must be reconciled first, and application writes may need to stop creating new duplicates during cleanup. DDL success does not prove the optimizer will choose the index or that the workload will improve.

Runnable example

On a disposable PostgreSQL database, this sequence creates a table and builds an index concurrently. Run the CREATE INDEX CONCURRENTLY statement as a standalone command, not inside an explicit transaction. The example uses a predictable data set and should not be pasted directly into production.

CREATE TABLE index_demo (
  account_id bigint NOT NULL,
  created_at timestamptz NOT NULL,
  event_id bigint PRIMARY KEY
);

INSERT INTO index_demo (account_id, created_at, event_id)
SELECT n % 100, timestamptz '2026-01-01 00:00:00+00' + n * interval '1 second', n
FROM generate_series(1, 10000) AS series(n);

CREATE INDEX CONCURRENTLY index_demo_account_created
ON index_demo (account_id, created_at DESC, event_id DESC);

SELECT indexrelid::regclass, indisready, indisvalid
FROM pg_index
WHERE indexrelid = 'index_demo_account_created'::regclass;

The final query should show both indisready and indisvalid as true for a completed index. The composite order supports queries that filter by account_id and then traverse recent events; it is not automatically useful for every query that mentions one of its columns. Drop the sample table after inspecting it. In production, capture the DDL duration, waits, I/O impact, and query plans before and after.

Contextual failure case

A deployment pipeline starts a concurrent index build, hits a statement timeout, and reports failure. The operator immediately retries with the same name. PostgreSQL reports that the relation already exists. The team drops the name without inspecting validity, then reruns the build during peak load. Repeated attempts add load and obscure whether the first index was usable, invalid, or still building.

The safer recovery starts with state. Determine whether the DDL command is still active, inspect pg_index validity flags, check the exact server error, and review locks and long transactions. If an invalid index remains, follow the documented recovery for that index: remove the invalid artifact when appropriate, then retry during an approved window. Use a bounded lock_timeout to avoid waiting indefinitely for a conflicting lock and a separately chosen statement_timeout that reflects the maintenance plan. Timeouts should produce an observable, actionable failure rather than an unbounded migration.

Unique indexes add a stronger caution. A concurrent unique build can begin enforcing uniqueness for concurrent writes before the build finishes successfully. If validation later fails, the operation can leave surprising constraints on behavior despite indisvalid being false. Before production use, verify duplicate cleanup and write-path controls, and rehearse failure recovery on a database with a similar workload and version.

Verification

Before the change, record table size, write rate, query plans, and relevant lock waits. After completion, verify catalog validity, index size, and the intended query plan using representative parameter values. EXPLAIN (ANALYZE, BUFFERS) executes the query, so use it carefully on production and avoid ANALYZE for queries with side effects. Compare read improvement against write amplification and maintenance cost; an unused index is not a success.

Exercise the rollback path in staging: cancel a concurrent build, inspect the resulting catalog row, and document how to remove or retry it. A database lock diagnosis helps interpret waits, but the concurrent-build phases have their own state and resource profile. Keep migration telemetry separate from application request errors so a slow index build is not misdiagnosed as a query regression.

Exercise

Design a migration that adds a unique index to a table with historical duplicates while application writes continue. Specify how duplicates are found and resolved, how new duplicates are prevented during cleanup, which exact DDL runs outside a transaction block, and what you will inspect after cancellation. Include a rollback or forward-recovery plan for an invalid index and explain what observable evidence allows deployment to continue.

Safety and caveats

PostgreSQL behavior and catalog details are version-specific; validate syntax and failure semantics against the server version in use. Concurrent index builds can still consume substantial resources and wait on transactions. Avoid running production DDL without monitoring, a tested recovery plan, and an appropriate maintenance policy. A valid index does not guarantee a faster query; statistics, selectivity, write cost, and the planner's cost model still matter.