PostgreSQL Concurrent Indexes: Waits and Failed Build Recovery

Consider a hypothetical migration retry that reports an existing relation. The migration moves on, but nobody has checked whether the index is usable. PostgreSQL can leave an invalid index after failed concurrent creation; IF NOT EXISTS does not validate the existing definition. The command documentation explains both behaviors.

Start with the backend. Is it still building, or waiting? Establish whether construction has ended before considering recovery. Then check the actual index against the intended migration. A name-exists notice is too weak a completion check.

This guide covers ordinary, non-partitioned application tables. The SQL templates are source-reviewed but unexecuted. Adapt the hypothetical public.orders names and check the database and required privileges. Consult the deployed major version's documentation and rehearse changes in isolation.

What CREATE INDEX CONCURRENTLY changes, and what it does not

Write-compatible does not mean lock-free or wait-free

Ordinary CREATE INDEX blocks table writes while allowing ordinary reads. CREATE INDEX CONCURRENTLY permits normal writes during construction, but still requires coordination and waiting. Only one concurrent build can run on a given table at a time. It must run outside a transaction block. PostgreSQL documents these restrictions.

Two scans, transaction waits, and the validity transition

Concurrent creation registers an invalid catalog entry before performing two table scans in separate transactions. It waits for relevant modifying transactions before the scans and for older snapshots after the second scan. That invalid entry may belong to a healthy build. Leave it alone until you know otherwise. Extra scans and waits also mean concurrent creation can take longer. See the build sequence.

Diagnose PostgreSQL index build waiting by phase and backend

Read pg_stat_progress_create_index before estimating progress

Connect to the target database. This read-only template filters by table, retaining raw identifiers and converting unavailable zero identifiers to null:

SELECT p.pid, p.command, p.phase, p.relid,
       NULLIF(p.index_relid, 0) AS index_relid,
       p.lockers_total, p.lockers_done,
       NULLIF(p.current_locker_pid, 0) AS current_locker_pid,
       p.blocks_total, p.blocks_done
FROM pg_stat_progress_create_index AS p
WHERE p.datname = current_database()
  AND p.relid = to_regclass('public.orders');

A missing target resolves to null and produces no match. Verify the identifier before interpreting an empty result. See the to_regclass documentation.

Interpret counters within the reported phase. PostgreSQL's progress definitions distinguish the following work.

In the validation row above, "sorting tuples" and "scanning table" also have the full "index validation:" phase prefix. Concurrent reindex can report "waiting for readers before marking dead" and "waiting for readers before dropping"; the same progress reference describes those waits.

Block totals describe the current phase. They cannot establish end-to-end progress, and zero or unavailable totals cannot support a percentage estimate.

Correlate the waiting backend with transaction and lock evidence

Inspect build backends alongside their reported current lockers with this separate read-only template.

WITH progress AS (
  SELECT pid, current_locker_pid
  FROM pg_stat_progress_create_index
  WHERE datname = current_database()
    AND relid = to_regclass('public.orders')
), participants AS (
  SELECT pid FROM progress
  UNION
  SELECT current_locker_pid FROM progress
  WHERE current_locker_pid > 0
)
SELECT a.pid, a.state, a.xact_start, a.backend_xmin,
       a.wait_event_type, a.wait_event,
       pg_blocking_pids(a.pid) AS lock_blocking_pids
FROM pg_stat_activity AS a
JOIN participants AS t ON t.pid = a.pid;

pg_blocking_pids reports lock blockers but cannot explain every snapshot-related delay. An empty array does not rule out a wait. Likewise, an old xact_start or backend_xmin warrants investigation; it does not identify a session to terminate. Coordinate with the transaction owner. This query includes the build backend and its reported current locker, not a recursive blocker tree. Follow returned blocker PIDs separately. The function can return zero for a prepared-transaction blocker; zero is not a backend to terminate. See the same function reference.

For a comparison outside PostgreSQL, Optijara's TensorRT build-observability guide examines how to distinguish long-running work from failure.

Interpret missing rows and partial visibility cautiously

An absent progress row settles nothing. Check the retained backend PID and client outcome, then correlate server errors with catalog state. Confirm the database selection and monitoring role's permissions. Other sessions' details may be restricted. Long monitoring transactions can also leave observations stale; repeat them outside that transaction. Activity-monitoring documentation explains both limits.

Inspect invalid indexes before retrying with IF NOT EXISTS

Check indisvalid, indisready, indislive, and the actual definition

Inspect the named index and the table it actually belongs to. A migration ledger cannot establish either. The joins below follow the documented relation catalog and namespace catalog.

SELECT ns.nspname AS index_schema, ix.relname AS index_name,
       tn.nspname AS table_schema, t.relname AS table_name,
       i.indisunique, i.indisvalid, i.indisready, i.indislive,
       pg_get_indexdef(i.indexrelid) AS definition
FROM pg_index AS i
JOIN pg_class AS ix ON ix.oid = i.indexrelid
JOIN pg_namespace AS ns ON ns.oid = ix.relnamespace
JOIN pg_class AS t ON t.oid = i.indrelid
JOIN pg_namespace AS tn ON tn.oid = t.relnamespace
WHERE ns.nspname = 'public'
  AND ix.relname = 'orders_customer_id_idx';

Compare the definition with the approved migration, including uniqueness and access method. Check key order, expressions, included columns, and predicate. pg_get_indexdef reconstructs that definition.

In pg_index, indisvalid describes query validity. indisready tracks readiness for insert/update maintenance; indislive records liveness. A false liveness flag means the index is being dropped and must be ignored for all purposes. An invalid index can still add write overhead. The invalid flag alone does not identify a failed concurrent build.

Unique builds need particular care. Uniqueness enforcement against other transactions begins when the second table scan starts, before validity. A failure during that scan can leave an invalid index that still rejects duplicate writes. The invalid flag also cannot certify that existing data satisfy uniqueness. The concurrent-build caveats explain the timing.

Why name-based idempotency is not a successful migration

IF NOT EXISTS suppresses an error for an existing relation name. It neither rebuilds an invalid index nor guarantees an equivalent definition. Rails issue #52583 reports the resulting timeout/retry trap. That public report does not establish a current Rails fix. PostgreSQL's command reference defines the underlying behavior.

The PACE recovery playbook: Phase, Assess, Choose, Establish readiness

Phase and Assess: capture evidence before changing state

PACE is an editorial framework introduced here. Before changing state, save the original error and current phase. Retain the index identity and definition, along with transaction-owner context.

Before removal, check whether a constraint uses the named index.

SELECT conname, contype, conrelid::regclass AS owning_table
FROM pg_constraint
WHERE conindid = to_regclass('public.orders_customer_id_idx');

pg_constraint.conindid identifies supporting indexes. An empty result leaves other dependencies and ownership to investigate. Adding CASCADE is no substitute for that review.

Choose: wait, drop and recreate, or reindex

Choose a branch from established state. An unconditional retry loop skips that decision.

Use the following unexecuted drop/recreate template only after the failed build has ended and dependency checks permit removal. It assumes the approved definition is exactly a non-unique B-tree on public.orders(customer_id).

DROP INDEX CONCURRENTLY public.orders_customer_id_idx;
CREATE INDEX CONCURRENTLY orders_customer_id_idx
  ON public.orders USING btree (customer_id);

Submit separate top-level commands outside transaction blocks. If the drop fails, stop and inspect again. CREATE INDEX does not accept a schema-qualified index name; qualifying public.orders places the index in that schema. Check the distinct restrictions for creation and concurrent removal.

For an eligible invalid index whose definition should be preserved, the alternative branch is below.

REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;

Resolve the cause first. Reindexing cannot fix duplicate data or change an incorrect definition, and it does not remove the blocking workload. Target the index explicitly: only the index-level form can perform a concurrent rebuild of an invalid index. Do not substitute a table-wide concurrent reindex. Check the process and exclusions.

A failed concurrent reindex can leave an invalid _ccnew replacement, which the documented branch drops before retrying. An invalid _ccold may instead be the old copy left after a successful rebuild. Numeric suffixes are possible. Verify the surviving valid index and dependencies before approving removal. Never bulk-drop by suffix. PostgreSQL documents the different cleanup paths.

Establish readiness: verify the intended postconditions

After recovery, require command success and repeat the catalog query. Match the schema and table to the intended definition; confirm the valid/ready/live flags. Validity does not force planner selection, and older transactions can delay index usability. Optijara's Kubernetes probe decision guide explores the broader difference between process health and readiness.

Keep an observation record instead of inventing a universal elapsed-time threshold.

These are proposed measurements, not benchmark results. The JSON below summarizes the framework and completion rule.

{
  "framework": "pace",
  "layers": ["phase", "assess", "choose", "establish_readiness"],
  "completionRequires": ["successful_command", "intended_definition", "valid_ready_live"],
  "automaticDestructiveRetry": false
}

What teams get wrong in migration retries

Mistaking a successful retry notice for a valid index

A retry notice answers a narrower question than the migration needs answered. Check the catalog and definition separately. Keep that acceptance check in AI-assisted CI/CD workflows, including migrations proposed by automation.

Retrying before fixing the failed build's cause

Another attempt does not resolve duplicate data or an open transaction. Keep the initial error so recovery addresses the failure you actually observed. Use the following checklist before proceeding.

Treating destructive cleanup as generic idempotency

Keep destructive cleanup out of generic retry logic. Require human approval and a fresh state check before acting on sessions or invalid indexes. The concurrent-build and reindex references explain why names and intermediate flags are insufficient grounds for removal.

Caveats: partitions, constraints, monitoring access, and operational cost

Where the ordinary-table playbook stops

The cited documentation does not support concurrent creation on a partitioned parent. Build leaf-partition indexes concurrently, then plan parent-index assembly separately. Do not assume that parent operation is lock-free. PostgreSQL describes this route.

DROP INDEX CONCURRENTLY accepts one index and prohibits CASCADE. It must run outside a transaction block. It excludes partitioned-table indexes and direct removal of indexes supporting UNIQUE or PRIMARY KEY constraints. Check removal eligibility. Concurrent reindex excludes system catalogs and exclusion-constraint indexes. Its eligibility checks are separate.

Why concurrency is a trade-off, not a performance guarantee

Extra scans consume CPU and I/O. Budget engineering time for rehearsal and monitoring, then set an observation window and response plan for the workload. Concurrent-build documentation warns about operational load.

Protect query text and sensitive activity details when sharing incident evidence. Account for monitoring-access restrictions. These templates were not executed against a PostgreSQL fixture in this review. No wait or recovery was observed, and no production or benchmark result is claimed. Rehearse the selected branch before operational use.