What Happens During a PostgreSQL CREATE INDEX CONCURRENTLY Build

When you add an index to a PostgreSQL table serving production traffic, PostgreSQL blocks writes to the table until a regular CREATE INDEX finishes. CREATE INDEX CONCURRENTLY lets inserts, updates, and deletes continue during the work, but PostgreSQL still takes locks and waits for transactions at specific points. The PostgreSQL CREATE INDEX documentation describes those waits and the command’s restrictions.

PostgreSQL creates a concurrent index in stages to account for rows that change during the work. These extra steps take longer than a regular build, and a failed command can leave an invalid index. Monitor the stages and check the index afterward to manage a production build safely.

Why PostgreSQL scans the table twice

PostgreSQL first records the index and its status in the database catalog, which stores information about database objects. The index is not yet valid for PostgreSQL to use when choosing how to run queries. PostgreSQL then scans the table and adds entries to the index.

After the first scan, PostgreSQL waits for transactions that could have changed the table in ways the scan missed. It then scans the table again to find rows that changed during the first scan. A snapshot is the view of table contents that a transaction sees. Before marking the index valid, PostgreSQL waits for older snapshots to finish so those transactions do not rely on an incomplete view of the index.

These waits explain why “concurrently” does not mean “without locks” or “without delay.” PostgreSQL allows writes during the build, but a long-running transaction can delay the index becoming usable. Transactions left in a prepared state also matter: PostgreSQL fixed a bug involving waits for prepared transactions in a specific release, as described in the PostgreSQL 12.6 release notes.

Restrictions and costs

Run CREATE INDEX CONCURRENTLY by itself. PostgreSQL rejects it inside a transaction block, so migration tools that automatically put changes in a transaction need to run this command in a migration without a transaction.

PostgreSQL also allows only one concurrent index build on a given table at a time. PostgreSQL takes longer to build the index concurrently and uses resources while scanning and checking rows, so plan when to run the command. For a temporary table, PostgreSQL ignores the concurrent option because other sessions cannot access the table.

Partitioned tables have an additional limitation: PostgreSQL does not support creating an index concurrently on the partitioned parent table. A common approach is to create the index concurrently on each partition, then create the parent index without CONCURRENTLY.

Monitor the build

PostgreSQL exposes active index-build details in pg_stat_progress_create_index. The phase column shows what PostgreSQL is doing, such as scanning the table, validating the index, or waiting for transactions. The progress view is documented alongside index-build progress reporting.

For example, this query shows builds involving a particular table:

SELECT
    pid,
    command,
    phase,
    lockers_done,
    lockers_total,
    blocks_done,
    blocks_total,
    tuples_done,
    tuples_total
FROM pg_catalog.pg_stat_progress_create_index
WHERE relid = 'public.index_demo'::regclass;

The progress counters show work for the current phase. A counter that changes or looks incomplete does not by itself mean PostgreSQL has stopped working. If the phase shows a wait, check for long-running transactions or transactions left in a prepared state that could be delaying it.

What a failed build leaves behind

If PostgreSQL cannot finish a concurrent build, it can leave an invalid index in the catalog. PostgreSQL does not use that index when choosing how to run queries, but it can still add work when the table changes if PostgreSQL is ready to maintain it. If the failed index is unique, it can also continue enforcing uniqueness after the relevant validation stage has begun. The PostgreSQL index documentation describes these failure states and their effects.

Before retrying a failed command, check the catalog. indisvalid shows whether PostgreSQL considers the index valid. indisready shows whether PostgreSQL is ready to update it when the table changes.

SELECT
    ns.nspname AS schema_name,
    idx.relname AS index_name,
    tbl.relname AS table_name,
    i.indisvalid,
    i.indisready
FROM pg_catalog.pg_index AS i
JOIN pg_catalog.pg_class AS idx
    ON idx.oid = i.indexrelid
JOIN pg_catalog.pg_namespace AS ns
    ON ns.oid = idx.relnamespace
JOIN pg_catalog.pg_class AS tbl
    ON tbl.oid = i.indrelid
WHERE ns.nspname = 'public'
  AND idx.relname = 'index_demo_created_at_idx';

If the index is invalid, remove it before trying again:

DROP INDEX CONCURRENTLY IF EXISTS public.index_demo_created_at_idx;

Run that command outside a transaction block too. Once the invalid index is gone, investigate the original error and retry the build if appropriate.

A disposable example

The following commands create a small table, add an index concurrently, and remove both objects. Run each statement separately in autocommit mode, rather than wrapping the commands in a transaction.

CREATE TABLE public.index_demo (
    id bigint,
    created_at timestamp
);

CREATE INDEX CONCURRENTLY public.index_demo_created_at_idx
    ON public.index_demo (created_at);

DROP INDEX CONCURRENTLY IF EXISTS public.index_demo_created_at_idx;

DROP TABLE public.index_demo;

In production, run the progress query while PostgreSQL builds the index. After the command finishes, check that PostgreSQL marked the index valid before relying on it.

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