How to Create a PostgreSQL Index Without Blocking Writes

On a PostgreSQL table serving a live web application, a regular index build can cause an outage because it holds a lock while PostgreSQL scans the table and blocks writes. Use CREATE INDEX CONCURRENTLY to keep INSERT, UPDATE, and DELETE statements running during the build. The command takes longer, uses more database resources, and still waits for some locks.

An index is a separate database structure that helps PostgreSQL find rows without scanning the whole table. A concurrent build creates the index in several phases and tracks changes made during the build. After PostgreSQL marks the index valid, it works like an index created normally.

The basic command is:

CREATE INDEX CONCURRENTLY orders_customer_id_idx
ON disposable_index_demo.orders (customer_id);

The example uses a disposable schema because the commands below create and remove database objects. Replace the schema and table names with your own only after reviewing the operational limits.

Use CREATE INDEX CONCURRENTLY

A regular index build takes a SHARE lock on the table, allowing reads but blocking writes until PostgreSQL finishes creating the index. CREATE INDEX CONCURRENTLY uses less restrictive locks, so normal writes continue. PostgreSQL describes this behavior in its table-level lock documentation.

The command must run as its own statement. Do not wrap it in BEGIN and COMMIT. PostgreSQL rejects CREATE INDEX CONCURRENTLY inside a transaction block. If a migration tool wraps every migration in one transaction, configure it to allow non-transactional migrations or run this statement through a separate autocommit connection.

For a disposable demonstration schema, the setup could look like this:

CREATE SCHEMA disposable_index_demo;

CREATE TABLE disposable_index_demo.orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    status text NOT NULL
);

Run the concurrent index command from a session with autocommit enabled:

CREATE INDEX CONCURRENTLY orders_customer_id_idx
ON disposable_index_demo.orders (customer_id);

The table reference includes its schema name. This prevents the session’s search_path from directing the command to a same-named table in another schema.

After the command completes, PostgreSQL can use the index for suitable queries:

SELECT order_id, created_at, status
FROM disposable_index_demo.orders
WHERE customer_id = 42;

The planner still decides whether the index is faster than another access path. Creating an index does not make PostgreSQL use it for every matching query.

The full syntax and restrictions appear in the PostgreSQL 18 CREATE INDEX documentation.

Understand what “concurrent” still waits for

Concurrent means that ordinary table writes can continue during the build. PostgreSQL still takes locks, and some database operations still wait.

The command still holds a SHARE UPDATE EXCLUSIVE lock on the table for the whole build. This lock conflicts with schema changes and with VACUUM runs on that table. A command that needs a conflicting table lock waits for the index build, and only one concurrent index build can run on a table at a time.

The build also waits for transactions that could still see an earlier version of the table. A long-running transaction or an idle session that started a transaction can extend the build. Another lock holder can also delay it. Check for long-running sessions before starting, and set an appropriate lock_timeout when the deployment must not wait indefinitely:

SET lock_timeout = '5s';

CREATE INDEX CONCURRENTLY orders_customer_id_idx
ON disposable_index_demo.orders (customer_id);

A lock_timeout limits how long a statement waits to acquire a lock. It does not limit the time needed to scan the table or finish the index. Use it only when the client or migration runner can safely handle a failed statement.

Concurrent creation does more work than a regular build. PostgreSQL scans the table more than once and waits for transactions that could see intermediate states. The extra scans, sorting, validation, CPU use, and disk I/O make the command slower and let it compete with production queries for resources. Run it when the database has enough I/O and CPU capacity, even though writes remain available.

Monitor the build by table and index

PostgreSQL exposes active index builds through pg_stat_progress_create_index. Join its relation identifiers to pg_class and pg_namespace so the monitoring query reports schema-qualified names rather than relying on an unqualified relation name.

Run this query from another session while the build is active:

SELECT
    p.pid,
    p.datname,
    p.command,
    p.phase,
    format('%I.%I', nt.nspname, ct.relname) AS table_name,
    CASE WHEN ci.oid IS NOT NULL THEN format('%I.%I', ni.nspname, ci.relname) END AS index_name,
    p.lockers_total,
    p.lockers_done,
    p.current_locker_pid,
    p.blocks_total,
    p.blocks_done,
    p.tuples_total,
    p.tuples_done
FROM pg_stat_progress_create_index AS p
JOIN pg_class AS ct
  ON ct.oid = p.relid
JOIN pg_namespace AS nt
  ON nt.oid = ct.relnamespace
LEFT JOIN pg_class AS ci
  ON ci.oid = p.index_relid
LEFT JOIN pg_namespace AS ni
  ON ni.oid = ci.relnamespace
WHERE nt.nspname = 'disposable_index_demo'
  AND ct.relname = 'orders';

The phase column shows the current part of the operation. Depending on the stage, PostgreSQL reports phases such as building index, waiting for writers before validation, or a phase involving index validation. Counters such as blocks_done and blocks_total show whether the build is scanning data. A waiting phase with little progress indicates that you should investigate locks or transactions instead of the scan speed.

PostgreSQL 18 documents the progress view and its columns. Versions can add or change these details, so select only columns supported by the server you monitor.

Check indisvalid before relying on the index

A concurrent build can fail because of a deadlock or a canceled session. A lock timeout and a uniqueness violation can also cause failure. In some cases, PostgreSQL leaves an index entry in the catalog. The leftover index is marked invalid, so the planner ignores it, but later table changes still maintain it.

Check the index catalog state with pg_index.indisvalid:

SELECT
    format('%I.%I', n.nspname, c.relname) AS index_name,
    i.indisvalid,
    i.indisready,
    i.indislive
FROM pg_index AS i
JOIN pg_class AS c
  ON c.oid = i.indexrelid
JOIN pg_namespace AS n
  ON n.oid = c.relnamespace
WHERE n.nspname = 'disposable_index_demo'
  AND c.relname = 'orders_customer_id_idx';

For a completed build, indisvalid should be true. An invalid index has indisvalid = false. indisready shows whether PostgreSQL is maintaining the index for new table changes, but indisvalid tells you whether the planner can use it.

Drop and recreate the failed index

For an ordinary non-unique index, the simplest cleanup is to remove the invalid object and start again:

DROP INDEX CONCURRENTLY IF EXISTS
    disposable_index_demo.orders_customer_id_idx;

DROP INDEX CONCURRENTLY also cannot run inside a transaction block. After the drop finishes, retry the build as a separate statement with autocommit enabled:

CREATE INDEX CONCURRENTLY orders_customer_id_idx
ON disposable_index_demo.orders (customer_id);

Do not issue a second CREATE INDEX CONCURRENTLY with the same name while the invalid index still exists. IF NOT EXISTS can hide the problem by accepting the existing invalid object, leaving the table without a usable index.

PostgreSQL also supports rebuilding an existing index concurrently:

REINDEX INDEX CONCURRENTLY
    disposable_index_demo.orders_customer_id_idx;

REINDEX builds a replacement from the existing index definition, so you do not have to repeat the CREATE INDEX statement. The PostgreSQL REINDEX documentation describes its concurrent form and restrictions. If the rebuild fails, it can leave an extra invalid index with a _ccnew suffix, such as orders_customer_id_idx_ccnew, which the pg_index query above does not match. Drop that index with DROP INDEX CONCURRENTLY before retrying.

For the disposable example, remove everything after testing:

DROP INDEX CONCURRENTLY IF EXISTS
    disposable_index_demo.orders_customer_id_idx;

DROP TABLE IF EXISTS disposable_index_demo.orders;
DROP SCHEMA IF EXISTS disposable_index_demo;

Drop the index before the table so the cleanup remains explicit. In a real schema, confirm that no deployment, query, constraint, or rollback procedure still expects the index before removing it.

Know when this command needs a different plan

The example covers a non-partitioned table and a non-unique index. Unique indexes require additional care because PostgreSQL checks uniqueness during the concurrent build. A failed unique build can leave an invalid index that still enforces uniqueness, so investigate the specific failure and the documented unique-index restrictions before retrying.

A partitioned table has another limitation: PostgreSQL does not create the parent partitioned index directly with CREATE INDEX CONCURRENTLY. The usual approach creates matching indexes concurrently on the individual partitions and then attaches or coordinates the parent definition. Partitioned indexes also require additional decisions about names, order, and constraints, so do not use the non-partitioned example unchanged.

For a normal table, the steps are:

1. Confirm that the index supports an actual query or constraint requirement.
2. Run CREATE INDEX CONCURRENTLY outside a transaction block.
3. Monitor progress and lock waits from another session.
4. Check pg_index.indisvalid after completion.
5. Drop or concurrently rebuild an invalid leftover index before retrying.

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