Concurrent Index Build Monitoring

infoProactive Health

Monitoring PostgreSQL CREATE INDEX CONCURRENTLY operations to track progress, manage locks, and ensure successful completion without blocking production traffic.

Prompt: “I'm running CREATE INDEX CONCURRENTLY on a 200GB production table in PostgreSQL. It's been running for 2 hours and I need to know if it's progressing normally or stuck. How do I monitor the operation and what could cause it to fail?”

Agent Playbook

When an agent encounters this scenario, Schema provides these diagnostic steps automatically.

When monitoring CREATE INDEX CONCURRENTLY, start by confirming the operation is actually progressing through block and tuple metrics, then calculate how far along it is. The most common causes of stalls are long-running or idle transactions that prevent the concurrent build from advancing through its phases, so check for blocking sessions before investigating lock contention.

1Verify the index build is making forward progress
Check `block-done` and `postgresql-create-index-tuples-done` in pg_stat_progress_create_index and compare values over a 5-10 minute window. If these numbers aren't increasing, the operation is stuck, not just slow. On a 200GB table you should see thousands of tuples processed per second under normal conditions. No progress means you're blocked waiting for something—move immediately to checking transactions and locks.
2Calculate completion percentage and assess if timing is normal
Divide `block-done` by `postgresql-create-index-blocks-total` to get percent complete. A 200GB table is roughly 25 million 8KB blocks—at 50K blocks/minute (typical for concurrent builds with moderate write traffic), you'd expect 8-9 hours total. If you're 50% done after 2 hours, you're ahead of schedule. Under 20% suggests the operation is being slowed by contention or hardware constraints.
3Hunt for long-running transactions that started before the index build
Query pg_stat_activity for transactions with xact_start or query_start older than when your CREATE INDEX CONCURRENTLY began. The `long-transactions-hold-locks` and `long-running-transactions-preventing-cleanup` insights explain that concurrent index builds must wait for all pre-existing transactions to complete before advancing to later phases. Even a single forgotten read-only transaction from 2 hours ago will block phase transitions. Kill or commit these transactions to unblock progress.
4Check for idle-in-transaction sessions holding snapshots
Look in pg_stat_activity for sessions with state='idle in transaction', especially those with state_change timestamps before your index build started. The `idle-transaction-locks` insight highlights that these sessions hold transaction snapshots that prevent concurrent index operations from progressing, and with default idle_in_transaction_session_timeout=0 they'll block indefinitely. Terminate these sessions or set a timeout to prevent recurrence.
5Monitor lock contention through locker metrics
Compare `postgresql-create-index-lockers-done` to `postgresql-create-index-lockers-total`—if these are far apart, the index build is waiting for locks on the table. Also check `postgresql-locks` for ACCESS EXCLUSIVE or SHARE UPDATE EXCLUSIVE locks on your table. High lock counts indicate write-heavy workloads competing with the concurrent build; this is expected but if locks aren't clearing, you may have a stuck transaction (go back to step 3).
6If partitioned, assess per-partition progress distribution
Check if `partitions` is greater than 1, indicating a partitioned table. Compare `postgresql-create-index-partitions-done` to total partitions—the index builds separately on each partition, so you might be 100% done on some and 0% on others. This is normal, but if one partition is taking much longer, it may have significantly more data or different write patterns affecting the concurrent build.

Technologies

Related Insights

Relevant Metrics

Monitoring Interfaces

PostgreSQL Datadog
PostgreSQL Native
PostgreSQL Prometheus Exporter (pg_exporter)
PostgreSQL Datadog Integration