CREATE INDEX CONCURRENTLY can appear stuck while PostgreSQL waits for another transaction, scans a large table, or validates the new index. Those cases need different responses. A long elapsed time alone does not tell you which one is happening, and canceling the command before checking can leave an invalid index behind.
Start with the progress view while the command is running. Its phase says whether the build is waiting or working; its locker columns identify the session currently holding up a wait. The checks below read server state and catalogs. They do not change the index or end a session.
Read the build phase
Run this from the same database as the index build. Capture more than one sample if the command is still active. The block counters describe work in a phase; they are not an overall percentage for the entire command.
SELECT p.pid AS build_pid,
p.relid::regclass AS table_name,
p.index_relid::regclass AS index_name,
p.command, p.phase,
p.lockers_done, p.lockers_total, p.current_locker_pid,
p.blocks_done, p.blocks_total,
a.wait_event_type, a.wait_event,
now() - a.query_start AS elapsed
FROM pg_stat_progress_create_index AS p
JOIN pg_stat_activity AS a USING (pid)
WHERE p.datname = current_database()
ORDER BY a.query_start;Interpret the phase before looking at the elapsed time:
| Phase | What it means | Next check |
|---|---|---|
waiting for writers before build or before validation | PostgreSQL is waiting for transactions that could have modified the table. | Inspect current_locker_pid, transaction age and owner. |
waiting for old snapshots | A transaction can still see a snapshot that predates the second scan. | Inspect the reported locker; it may not have written to this table. |
building index or index validation: … | PostgreSQL is scanning, sorting, loading or checking tuples. | Compare phase and counters across samples; check I/O and system load. |
| No progress row | The command may have completed, failed, or not reached the view. | Check the client result and index catalog state before retrying. |
A concurrent build performs more work than a regular index build: it scans the table twice and waits at transaction boundaries. It generally permits ordinary writes during the build, but still consumes I/O, CPU and storage and can wait on other sessions. PostgreSQL documents the phases in its progress reporting reference.
Find the transaction behind a wait
For a waiting phase, current_locker_pid points to the session currently being waited on. lockers_total and lockers_done show how many of the phase's lockers have been processed. Check pg_blocking_pids() as well; it accounts for lock queues. These are live snapshots, so a blocker may disappear between queries.
SELECT p.pid AS build_pid, p.phase,
p.current_locker_pid,
pg_blocking_pids(p.pid) AS blocking_pids,
b.application_name, b.state,
now() - b.xact_start AS blocker_transaction_age,
b.wait_event_type, b.wait_event
FROM pg_stat_progress_create_index AS p
LEFT JOIN pg_stat_activity AS b
ON b.pid = p.current_locker_pid
WHERE p.datname = current_database();An idle in transaction writer can hold up a build even when it is no longer running SQL. In the old-snapshot phase, a long-lived transaction may be relevant even if its last query touched another table. Identify the application and transaction owner before asking for it to finish. A PID is evidence for diagnosis, not permission to terminate a backend. The wider PostgreSQL lock-contention guide explains how to trace a blocking chain.
Activity views can expose query text and other sensitive details. These examples deliberately omit it. Access to full statistics and other users' activity depends on the role and managed-service permissions; arrange an authorized view or client-run capture when necessary. See the evidence-collection guidance for handling production data.
Decide whether the build is still working
If the phase is building index or index validation, sample it again rather than assuming a lock. blocks_done may advance during table or index scans; during a sort or load, these block counters can be zero or unchanged while work continues. Read the phase and wait event together. For example, an I/O wait during a scan points to a different bottleneck than Lock:virtualxid during a writer wait.
Check the size and write rate of the table and whether the build competes with the application for storage throughput. On RDS or Aurora PostgreSQL, combine the SQL view with your existing instance and storage metrics. Avoid declaring the command stalled from a single progress sample or applying a generic timeout to a build whose duration is not yet understood.
Check an interrupted or failed build
A failed concurrent build can leave an index entry with indisvalid = false. The planner will not use an invalid index for normal queries, but one that is ready for inserts can still add write overhead. A failed concurrent unique index can even continue enforcing uniqueness after the failure. Inspect its state before retrying or removing it.
-- Replace public.orders with the intended table.
SELECT idx.relname AS index_name,
i.indisvalid, i.indisready, i.indisunique,
pg_size_pretty(pg_relation_size(idx.oid)) AS index_size
FROM pg_index AS i
JOIN pg_class AS idx ON idx.oid = i.indexrelid
WHERE i.indrelid = 'public.orders'::regclass
ORDER BY idx.relname;IF NOT EXISTS only checks whether an object with the name exists; it does not certify that an existing index is valid or has the definition you intended. Review the index definition, constraint dependencies and failure cause before choosing a recovery. PostgreSQL documents dropping the invalid index and retrying, or rebuilding it with REINDEX INDEX CONCURRENTLY, as possible paths. Both are schema changes that need a separate change plan. CREATE INDEX CONCURRENTLY also cannot run inside a transaction block, which matters for migration tools.
RDS and Aurora considerations
The PostgreSQL progress and catalog queries apply to RDS and Aurora PostgreSQL, subject to the engine version and your role's visibility. You may need the database owner or a role with suitable statistics access to see another session's details. Coordinate with the application owner before changing timeouts, canceling a build or ending a transaction. The RDS and Aurora capability guide covers the broader platform context.
Choose a safe response
- Record the evidence: command start time, progress phase, locker PID, transaction age, index state and the business reason for the build.
- For a writer or snapshot wait: identify the transaction owner and whether it can finish normally. Escalate cancellation only through the team's incident and change process.
- For active work: watch repeated progress samples and I/O pressure. Decide whether to let the build finish or reschedule it based on the workload.
- After failure: inspect validity and dependencies; choose a reviewed cleanup or rebuild plan before rerunning the migration.
- After completion: verify
indisvalid, then compare the intended query plan and write cost against the pre-build baseline. A valid index does not guarantee the planner will use it or that the overall workload improves.
For a full evidence-to-decision example, see the synthetic sample report. If the index is meant to address slow queries, the performance investigation scope shows what a bounded diagnosis includes. You can inspect a captured execution plan with PlanReader; a plan by itself does not show why an index build is waiting.
For a planned index build, a PostgreSQL migration review can examine the transaction wrapper, retry path and deployment sequence before release.
Sources and related guides
- PostgreSQL 18: building indexes concurrently — scans, transaction waits, invalid indexes and transaction-block restrictions.
- PostgreSQL 18: CREATE INDEX progress reporting — phase and locker column definitions.
- PostgreSQL 18: session information functions —
pg_blocking_pids(). - PostgreSQL 18: pg_index catalog —
indisvalidandindisready. - PostgreSQL concurrency and locking — broader lock-queue diagnosis.