skip to content

A PostgreSQL CREATE INDEX CONCURRENTLY on the 400 GB orders table has run for an hour; how do you see its phase and what it is waiting for?

level: seniorimportance: should knowfreq 34%

answer

  1. a progress view per backend
  2. phase text plus block counters
  3. lockers and current_locker_pid
  4. waiting for old snapshots
  5. idle in transaction, same database

basics

~10 s

Query pg_stat_progress_create_index: phase names the step, blocks_done against blocks_total shows scan progress, and in the waiting phases current_locker_pid names the transaction it waits on, often an idle-in-transaction session holding an old snapshot.

solid answer

~40 s

`pg_stat_progress_create_index` has one row per backend running `CREATE INDEX` or `REINDEX`. `phase` says where it is: `building index: scanning table` and `index validation: scanning table` report `blocks_done` against `blocks_total`, while the three waiting phases (`waiting for writers before build`, `waiting for writers before validation`, `waiting for old snapshots`) fill `lockers_total`, `lockers_done` and `current_locker_pid`. A build sitting in `waiting for old snapshots` waits for every transaction in the same database whose snapshot predates its validation pass, even one that never touches `orders`: typically an idle-in-transaction session or a long report. Look up `current_locker_pid` in `pg_stat_activity`, then let it finish or end it; the build carries on by itself once that transaction is gone.

code

sql · 12 lines
sql
SELECT p.pid,
       p.index_relid::regclass AS index_name,
       p.phase,
       round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 1) AS pct_blocks,
       p.lockers_done || '/' || p.lockers_total AS lockers,
       p.current_locker_pid,
       a.state AS locker_state,
       a.xact_start AS locker_xact_start,
       left(a.query, 60) AS locker_query
FROM pg_stat_progress_create_index AS p
LEFT JOIN pg_stat_activity AS a ON a.pid = p.current_locker_pid
WHERE p.relid = 'marketplace.orders'::regclass;

go deeper

for a junior

Know that PostgreSQL has progress views for long commands, and that pg_stat_progress_create_index is the one for CREATE INDEX and REINDEX.

for a middle

Read phase, blocks_done and blocks_total, and know the order of the concurrent phases, so you can tell the first pass from the validation pass.

for a senior

Diagnose a stalled build: follow current_locker_pid into pg_stat_activity, recognise the idle-in-transaction snapshot holder in the same database, and resolve it without cancelling hours of build work.

for a principal

Set policy so builds rarely stall: session timeouts for idle transactions, scheduling builds away from long reporting jobs, and runbooks that say who may terminate whose sessions.

## The progress view While `CREATE INDEX` or `REINDEX` runs, `pg_stat_progress_create_index` holds one row per backend doing the work. The columns that matter on a long build: | Column | What it tells you | |---|---| | `pid` | the backend running the build | | `relid`, `index_relid` | the table, and the index being built (0 during a non-concurrent `CREATE INDEX`) | | `command` | `CREATE INDEX`, `CREATE INDEX CONCURRENTLY`, `REINDEX` or `REINDEX CONCURRENTLY` | | `phase` | the current step, as text | | `blocks_total`, `blocks_done` | scan progress within the current phase | | `tuples_total`, `tuples_done` | tuple progress where the access method reports it | | `lockers_total`, `lockers_done`, `current_locker_pid` | progress through a waiting phase, and who is being waited on | | `partitions_total`, `partitions_done` | progress across the partitions of a partitioned index | ## The phases of a concurrent build A `CREATE INDEX CONCURRENTLY` moves through these `phase` values in order: 1. `initializing`, which is brief. 2. `waiting for writers before build`: transactions holding write locks on the table must finish; progress shows in the `lockers_*` columns. 3. `building index` with a subphase supplied by the access method. For B-tree that is `building index: scanning table`, then `building index: sorting live tuples` and `building index: loading tuples in tree`. The table scan reports `blocks_done` against `blocks_total`. 4. `waiting for writers before validation`. 5. `index validation: scanning index`, `index validation: sorting tuples`, then `index validation: scanning table`, the second pass, again with block counters. 6. `waiting for old snapshots`: transactions whose snapshots predate the validation pass must finish. `REINDEX CONCURRENTLY` adds two more phases at the end, `waiting for readers before marking dead` and `waiting for readers before dropping`, and reports `REINDEX CONCURRENTLY` in `command`. ## Diagnosing a build that is not moving - If `phase` is a scanning phase and `blocks_done` keeps rising, it is working, just on a big table. Sample it twice a few minutes apart to get a rate. - If `phase` starts with `waiting`, read `current_locker_pid` and look that process up in `pg_stat_activity`: its `state`, `xact_start` and `query` usually explain it. - The classic culprit in `waiting for old snapshots` is an **idle-in-transaction** session or a long-running report. The wait covers every transaction **in the same database** whose snapshot is older than the validation pass, even if it never reads `orders`. Vacuum processes are not waited for, nor are other concurrent builds of plain-column, non-partial indexes; a concurrent build of an expression or partial index elsewhere is. - In `pg_stat_activity` the building backend itself shows `wait_event_type` `Lock` and `wait_event` `virtualxid` while it waits for another transaction to end. ## Reading the numbers correctly - `blocks_done` and `blocks_total` are **per phase**. Reaching 100% in `building index: scanning table` means the first of two table passes is done, not the build. - `index validation: scanning table` is another full pass over the 400 GB heap, so budget roughly as much time again for it. - `lockers_total` is fixed when a wait begins: the build waits only for transactions that existed at that moment, and newer ones are not added to the list. - `index_relid` lets you name the index with `index_relid::regclass`, which helps when several builds run on different tables. ## A worked reading An hour in, the build on `orders` shows `phase = waiting for old snapshots`, `lockers 0/1` and a `current_locker_pid` whose `pg_stat_activity` row says `state = idle in transaction`, with `xact_start` two hours ago and a last query that read the `customers` table. Both table passes are already done; the only thing left before the index becomes valid is that one forgotten transaction, opened before the validation pass and never committed. Ending it is enough, and the build finishes within seconds. ## Acting on the blocker 1. Identify the session from `current_locker_pid`. 2. Decide whether it will finish soon (a report near its end) or is stuck (an idle-in-transaction session left by a crashed client or a debugging console). 3. If it must go, cancel or terminate it with the session-monitoring tools; the build continues on its own as soon as that transaction ends. 4. Prevent a repeat with a session limit such as `idle_in_transaction_session_timeout`, so forgotten transactions do not live for hours. The view only reports; it never changes the build. Cancelling the build instead of the blocker throws away the finished passes and leaves an `INVALID` index behind.

  • Why can a transaction that never touches orders stall the concurrent build?
    Before marking the index valid, PostgreSQL must be sure no running transaction holds a snapshot older than the validation pass, because such a snapshot could see row versions the index does not describe. It cannot know which tables a transaction will read next, so it waits for every such transaction in the same database, excluding only vacuum processes and other concurrent builds of simple indexes.
  • Does the same view cover REINDEX CONCURRENTLY?
    Yes. `command` shows `REINDEX CONCURRENTLY`, it passes through the same build and validation phases for the transient new index, and it adds `waiting for readers before marking dead` and `waiting for readers before dropping` while it retires the old index. Those last two wait for queries that still hold read locks on the table.

  • A concurrent build only waits for transactions that touch the same table.
  • pg_stat_progress_vacuum is where index build progress appears.
  • blocks_done equal to blocks_total means the whole build is nearly finished.
  • Terminating the blocking session also aborts the index build.
  • A stuck build should be cancelled and restarted to clear the wait.