Postgres pg_stat_progress_create_index: Watching Index Builds in Real Time

Postgres version12+ (progress view introduced in 12)Commands coveredCREATE INDEX, CREATE INDEX CONCURRENTLY, REINDEX, REINDEX CONCURRENTLYViewpg_stat_progress_create_indexKey columnsphase, blocks_total, blocks_done, tuples_total, tuples_done

You kick off a CREATE INDEX CONCURRENTLY on a 200GB table. The command is running. Nothing is returned. You have no idea whether it's halfway done or spinning on the same block it started with two hours ago. This is the problem pg_stat_progress_create_index exists to solve.

The View

Postgres 12 added a family of pg_stat_progress_* views covering VACUUM, ANALYZE, CLUSTER, COPY, and index builds. The index build view has these columns worth understanding:

• pid — backend PID of the index build process
• relid — OID of the table being indexed
• index_relid — OID of the index being created
• command — one of CREATE INDEX, CREATE INDEX CONCURRENTLY, REINDEX, REINDEX CONCURRENTLY
• phase — current phase (see below)
• lockers_total / lockers_done — for concurrent builds, how many concurrent transactions have been waited on
• current_locker_pid — the PID of the transaction currently being waited on
• blocks_total / blocks_done — table scan progress in 8KB blocks
• tuples_total / tuples_done — tuple-level progress during sort and load phases
• partitions_total / partitions_done — for partitioned tables, how many child indexes have been built

A simple monitoring query:

SELECT
  pid,
  relid::regclass AS table_name,
  index_relid::regclass AS index_name,
  command,
  phase,
  blocks_done,
  blocks_total,
  CASE WHEN blocks_total > 0
    THEN round(100.0 * blocks_done / blocks_total, 1)
    ELSE 0
  END AS pct_done,
  tuples_done,
  tuples_total
FROM pg_stat_progress_create_index;

The Seven Phases

Index builds in Postgres proceed through distinct phases. Knowing which phase you're in tells you a lot about what's actually happening.

1. initializing — setup, acquiring locks, preparing the sort buffer
2. waiting for writers before build — CIC waits for concurrent write transactions to finish before starting the scan
3. building index: scanning table — sequential scan of the heap; blocks_done and blocks_total are live here
4. building index: sorting tuples — sort phase using maintenance_work_mem; tuples_done counts progress
5. building index: loading tuples — inserting sorted tuples into the index structure; tuples_done is live
6. waiting for writers before validation — CIC only: waits again before the validation scan
7. index validation: scanning index — CIC only: second pass to validate entries against heap visibility
8. waiting for old snapshots — CIC only: waits for long-running transactions that started before the build began

For a plain CREATE INDEX (non-concurrent), you'll see phases 1, 3, 4, 5, and done. For CREATE INDEX CONCURRENTLY, you get all eight — including the three waiting phases that explain why CIC is slower than non-concurrent on the same table.

Why CREATE INDEX CONCURRENTLY Touches the Table Three Times

CIC doesn't take an ACCESS EXCLUSIVE lock. Instead it performs three passes across the heap:

1. First scan — builds the index from the current state of the table
2. Validation scan — checks that all heap entries are properly represented in the index
3. Snapshot wait — waits for any transaction that started before the first scan to finish, ensuring no rows are invisible to the index

This is why CIC takes roughly 3x longer than non-concurrent: it's doing the equivalent of three full passes plus coordination overhead. But it's still the correct choice for production because a plain CREATE INDEX holds ACCESS EXCLUSIVE for the entire duration of the build — blocking all reads and writes on a large table for potentially hours.

ETA Estimation from the View

During phase 3 (scanning table), you can estimate time remaining:

SELECT
  phase,
  blocks_done,
  blocks_total,
  blocks_total - blocks_done AS blocks_remaining,
  CASE WHEN blocks_done > 0 AND blocks_total > 0
    THEN
      (now() - query_start) *
      (blocks_total::float / NULLIF(blocks_done, 0) - 1)
    ELSE NULL
  END AS estimated_remaining
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a USING (pid)
WHERE command LIKE '%INDEX%';

This calculation assumes linear progress, which is roughly correct for sequential heap scans. The sort and load phases are harder to estimate because they depend on data distribution and sort memory, not just block count.

Partitioned Tables

For partitioned tables with many child partitions, partitions_total and partitions_done show progress at the partition level. Each child index build also gets its own row in pg_stat_progress_create_index, so you'll see one row for the partitioned index and one for each child being built. Match them by relid to understand which partition you're currently indexing.

What the View Does Not Show

There are meaningful gaps:

• Small table builds — index builds on small tables complete before the view can be polled. You'll only catch builds that are actually in progress at the moment you query.
• Internal sort memory usage — the view doesn't expose how much of maintenance_work_mem is being used or whether the sort spilled to disk. For that, check pg_stat_activity or look at temp file usage in pg_stat_io (Postgres 16+).
• Parallel worker progress — parallel index builds spawn worker processes visible in pg_stat_activity, but their progress isn't aggregated into the main backend's row in pg_stat_progress_create_index. You can see the workers as separate PIDs but not their individual contribution.

REINDEX CONCURRENTLY

Postgres 12 also added REINDEX CONCURRENTLY for rebuilding bloated indexes without locking. It appears in pg_stat_progress_create_index with command REINDEX CONCURRENTLY and goes through the same phase sequence as CIC. Use it when your index is bloated (unusually large relative to what it indexes, typically after heavy update/delete workloads) but the table itself is healthy. Check bloat first:

SELECT
  relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE NOT indisvalid;

An invalid index is the residue of a failed or cancelled REINDEX CONCURRENTLY. Drop it and rebuild.

Operational Guidance

Before large index builds:

-- More memory for the sort phase reduces spill to disk
SET maintenance_work_mem = '2GB';

-- Then build
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

During the CIC validation phase, watch current_locker_pid. If it's stuck on the same PID for more than a few minutes, that transaction is blocking the build from completing. Check what that transaction is doing:

SELECT pid, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE pid = current_locker_pid;

Long-running transactions — idle in transaction, long queries, or forgotten open connections — are the most common reason CIC builds stall in the validation phase. The index build can't complete until those transactions finish, even if they aren't touching the indexed table.

The view shows you what's happening. It doesn't tell you what to do about it. But knowing you're in phase 7 waiting on a single PID that's been idle in transaction for 45 minutes is enough information to make a decision.

Working with Postgres at scale? See how we track infrastructure progress publicly at builds.anethoth.com — proof that a product is really being built.