Creating an index on a large table can take minutes or even hours. For GIN, trigram, or multi-million row tables, it can feel like nothing is happening at all.

PostgreSQL provides built-in visibility into index creation progress. You don’t need external tools — just the right system view.

The pg_stat_progress_create_index view

PostgreSQL exposes index build progress via:

• PostgreSQL

The key view is:

SELECT *
FROM pg_stat_progress_create_index;

This shows all currently running CREATE INDEX operations.

If nothing is building, it returns zero rows.

Example output explained

A typical query looks like this:

SELECT
    pid,
    datname,
    relid::regclass AS table_name,
    index_relid::regclass AS index_name,
    phase,
    lockers_total,
    lockers_done,
    blocks_total,
    blocks_done,
    tuples_total,
    tuples_done
FROM pg_stat_progress_create_index;

Important columns:

phase

Tells you what stage the build is in. Examples:

• initializing
• building index
• waiting for writers before validation
• index validation
• waiting for old snapshots

If you're using CREATE INDEX CONCURRENTLY, you’ll see additional validation phases.

blocks_total and blocks_done

These indicate:

• Total table blocks to scan
• How many have been processed

Progress percentage:

(blocks_done::float / NULLIF(blocks_total, 0)) * 100

This is usually the most reliable indicator during the scan phase.

tuples_total and tuples_done

Shows:

• Estimated total rows
• Rows processed so far

Useful, but block progress is often more stable.

Monitoring concurrent index builds

When using:

CREATE INDEX CONCURRENTLY ...

PostgreSQL performs multiple scans and validation steps.

You’ll see phases like:

• building index
• waiting for writers before validation
• index validation
• waiting for old snapshots

If it appears stuck in:

waiting for old snapshots

That usually means a long-running transaction is preventing completion.

You can inspect active transactions:

SELECT pid, state, query, xact_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_start;

Calculating progress percentage

A simple progress query:

SELECT
    relid::regclass AS table_name,
    index_relid::regclass AS index_name,
    phase,
    ROUND(
        100.0 * blocks_done / NULLIF(blocks_total, 0),
        2
    ) AS progress_percent
FROM pg_stat_progress_create_index;

This gives you a clean percentage during the main build phase.

When progress appears frozen

Common reasons:

1. Very small maintenance_work_mem
2. Disk I/O saturation
3. WAL bottlenecks
4. Waiting on long-running transactions (concurrent builds)
5. CPU-heavy index types (e.g. GIN with trigrams)

If blocks aren’t increasing, check:

SELECT wait_event_type, wait_event
FROM pg_stat_activity
WHERE pid = <index_pid>;

This tells you whether it’s waiting on:

• Lock
• IO
• WAL
• Buffer pin
• etc.

Estimating total duration

A rough estimate during build:

SELECT
    blocks_done,
    blocks_total,
    now() - backend_start AS elapsed
FROM pg_stat_progress_create_index
JOIN pg_stat_activity USING (pid);

You can extrapolate remaining time from block progress.

Version requirement

pg_stat_progress_create_index is available since:

• PostgreSQL 12

If you’re on an older version, you won’t have native progress tracking.

Practical workflow

When building a large index in production:

1. Run CREATE INDEX (or CONCURRENTLY)
2. Monitor pg_stat_progress_create_index
3. Watch for blocking transactions
4. Track I/O saturation
5. Increase maintenance_work_mem if needed

This gives you visibility instead of guessing.

Final thoughts

Large index builds are expensive operations. Monitoring progress:

• Reduces uncertainty
• Helps detect blockers
• Allows time estimation
• Makes concurrent builds safer in production

If you regularly build large GIN or trigram indexes, having a monitoring query ready in your toolbox saves a lot of stress.