Monitoring PostgreSQL Index Builds With Progress Views

When PostgreSQL creates an index on a database that is still serving queries, pg_stat_progress_create_index shows the current stage and counters that track work during that stage. A stage, or phase, is what the server is doing now, such as scanning a table or waiting for a transaction. Compare snapshots taken at different times to see whether the server is making progress or waiting long enough to investigate.

The view reports one row for each server process running CREATE INDEX or REINDEX. It shows current progress, not records of builds that finished or failed. PostgreSQL 18 documents the view and its phases in CREATE INDEX progress reporting. Check the documentation for your PostgreSQL version before relying on specific columns or phases.

Query the build and its wait state

Use this query to join progress data to pg_stat_activity, which reports what each server process is doing and what it is waiting for:

SELECT
    p.pid,
    p.datname,
    p.relid::regclass AS table_name,
    CASE
        WHEN p.index_relid  0 THEN p.index_relid::regclass::text
    END AS index_name,
    p.command,
    p.phase,
    p.lockers_done,
    p.lockers_total,
    p.current_locker_pid,
    p.blocks_done,
    p.blocks_total,
    round(
        100.0 * p.blocks_done / NULLIF(p.blocks_total, 0),
        1
    ) AS phase_blocks_pct,
    p.tuples_done,
    p.tuples_total,
    p.partitions_done,
    p.partitions_total,
    a.state,
    a.wait_event_type,
    a.wait_event,
    now() - a.query_start AS elapsed
FROM pg_stat_progress_create_index AS p
LEFT JOIN pg_stat_activity AS a USING (pid)
ORDER BY p.pid;

The phase_blocks_pct value covers only the current phase, and only when PostgreSQL reports block counters for that phase. It does not show overall completion. If blocks_total is zero, the query returns no percentage; this does not mean the build failed. PostgreSQL reports different counters in different phases, and some index methods leave progress fields empty.

Use these identifiers to locate the work: pid is the server process ID, datname is the database name, and relid identifies the table. Casting relid to regclass displays the table name. index_relid identifies the index when PostgreSQL reports it. For a non-concurrent CREATE INDEX, PostgreSQL reports zero.

Read the phase before the counters

Read the phase column to see what the PostgreSQL server process is doing. Block and tuple counters measure different things depending on the phase. The same is true of locker and partition counters. The index method, or type of index being built, also determines which counters PostgreSQL reports. See the PostgreSQL phase and column definitions.

An unchanged counter means different things during a scan and during a wait. In a waiting phase, PostgreSQL is still running the command but waiting for transactions. During a work phase, take another sample before deciding that progress has stopped. The index method affects which counters PostgreSQL reports, and a long task can leave a counter unchanged between samples.

Investigate waits and apparent stalls

Run the query more than once and compare phase and the counters for that phase. If a counter advances while the server process stays in the same work phase, PostgreSQL is making progress on that work. If the process stays in a wait phase, inspect the related transaction or reader. An unchanged work counter alone does not prove that the build has stalled.

The state and wait-event columns give you more context. PostgreSQL documents that a backend can be active while waiting. A non-null wait_event means the process is waiting somewhere in the system. To investigate locks, use pg_blocking_pids() rather than trying to identify blockers with a self-join on pg_locks, as PostgreSQL explains in its activity and lock monitoring documentation. For example:

SELECT
    p.pid AS build_pid,
    p.phase,
    p.current_locker_pid,
    pg_blocking_pids(p.pid) AS blocking_pids
FROM pg_stat_progress_create_index AS p;

Use the result to find blocking sessions, then inspect what they are doing. The progress view shows that PostgreSQL is waiting, but it cannot tell you whether it is safe to interrupt a blocking transaction.

The view does not give an overall completion percentage or estimated finish time. To estimate the rate for one phase, compare its counter in samples taken a known time apart. That rate does not predict how long later phases or waits will take. PostgreSQL does not prescribe how often to check or set a universal threshold for calling a build stalled. Choose a sampling interval that fits your monitoring needs, and consider the phase and workload when you interpret the results.

Use progress data to plan capacity

A concurrent index build keeps ordinary reads and writes available. It performs two table scans and waits for relevant transactions, while its use of CPU and I/O can slow other work. A regular index build generally uses one table scan. It prevents writes to the table until it finishes. PostgreSQL describes these tradeoffs in its CREATE INDEX documentation.

To plan future builds, compare rates for specific phases across similar builds, along with database and host measurements of CPU and I/O. The progress view shows index work but does not explain every cause of slowdowns. PostgreSQL recommends combining database statistics with operating-system monitoring to get a fuller picture of I/O performance.

maintenance_work_mem and parallel workers also affect how long a build takes. PostgreSQL says that more maintenance memory can reduce build time for most index methods when the machine has enough memory. However, setting it too high can cause swapping. Parallel index builds are supported for B-tree, GIN, and BRIN, and concurrent builds parallelize only the first table scan. Before changing these settings, compare them with available memory, CPU capacity, and the workload you observed. See the PostgreSQL CREATE INDEX performance notes.

If a concurrent build fails, PostgreSQL can leave an invalid index behind. The planner ignores that index, but updates still have extra overhead. After a failure, check the index status and follow PostgreSQL’s documented recovery options, such as dropping the index and trying again or using REINDEX INDEX CONCURRENTLY.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com