Scenario
A CREATE INDEX CONCURRENTLY (or REINDEX CONCURRENTLY) is running — track its phase and progress to estimate completion time and detect stalls. Diagnose it -- Available since PostgreSQL 12: SELECT p.pid, p.datname, p.command, p.phase, p.blocks_total, p.blocks_done,…
Investigation Path
A CREATE INDEX CONCURRENTLY (or REINDEX CONCURRENTLY) is running — track its phase and progress to estimate completion time and detect stalls.
Diagnose it
-- Available since PostgreSQL 12:
SELECT p.pid,
p.datname,
p.command,
p.phase,
p.blocks_total,
p.blocks_done,
round(
100.0 * p.blocks_done / NULLIF(p.blocks_total, 0),
1) AS pct_done,
p.tuples_total,
p.tuples_done,
p.lockers_total, -- number of old transactions to wait for
p.lockers_done,
p.partitions_total, -- for partitioned tables
p.partitions_done
FROM pg_stat_progress_create_index p;The phases for CREATE INDEX CONCURRENTLY are: initializing →
waiting for writers before build → building index → waiting for writers after build →
waiting for readers before validation → index validation: scanning index →
index validation: sorting tuples → index validation: scanning table →
waiting for old snapshots → waiting for readers before marking dead →
waiting for references to become invisible → waiting for cleanup.
Phases named “waiting for…” can take longer than expected if long-running
transactions are holding old snapshots.
Why it happens
CREATE INDEX CONCURRENTLY works in multiple passes to avoid locking. It must
wait for existing transactions to complete at several points before it can advance. A long
wait in a “waiting for writers” phase usually means there is an old open transaction
— check pg_stat_activity for sessions with old xact_start.
How to fix it
-- Find transactions blocking the index build:
SELECT pid, usename, state,
now() - xact_start AS txn_age,
LEFT(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY txn_age DESC NULLS LAST;Prevent it next time
Run CREATE INDEX CONCURRENTLY during periods of low long-transaction activity.
Always verify the index is valid after completion by checking pg_index.indisvalid.
If the build fails, the invalid index entry must be dropped before retrying.
Related & next steps
Career Impact
This scenario builds production judgment and operational confidence under pressure.