PostgreSQL CREATE INDEX CONCURRENTLY failed or stuck: INVALID indexes, _ccnew leftovers and a safe retry

#The symptom

One of these usually brings you here:

• \d table shows an index marked INVALID, and queries aren't using it
• A migration running CREATE INDEX CONCURRENTLY has been running for hours with no progress
• A REINDEX CONCURRENTLY failed and left an index ending in _ccnew or _ccold
• Writes fail with duplicate key value violates unique constraint, naming an index the application doesn't use yet

A concurrent build can't roll back cleanly, because it commits in stages. If it fails or is cancelled, the index stays in the catalog. Queries ignore an invalid index, but writes may still have to maintain it, and an invalid unique index can keep rejecting rows.

#1. List invalid indexes

Run this in each database you suspect. pg_index is per database.

SELECT n.nspname  AS schema_name,
       t.relname  AS table_name,
       c.relname  AS index_name,
       c.relkind,
       i.indisunique,
       i.indisready,
       i.indislive,
       pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
       pg_get_indexdef(i.indexrelid)           AS definition
FROM pg_index i
JOIN pg_class c     ON c.oid = i.indexrelid
JOIN pg_class t     ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid
ORDER BY pg_relation_size(c.oid) DESC;

Save the definition column. You need it for the retry.

#2. Check whether a build is still running

Caution

A build in progress also shows as invalid. Don't drop anything from the list until you've run this check. If a build is still active, let it finish or cancel it first.

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

If the index appears here, go to step 3. If it doesn't, the build is dead and you can go to step 4.

A partitioned parent index (relkind = 'I') can also be invalid on purpose. It stays invalid until every partition has an attached index, so don't count it as a failure. Step 6 covers this.

#3. Unstick a waiting build

The progress numbers tell you what the build is doing:

Look up the blocker:

SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age,
       wait_event_type, wait_event,
       left(query, 100) AS last_query
FROM pg_stat_activity
WHERE pid = (SELECT current_locker_pid
             FROM pg_stat_progress_create_index
             WHERE pid = 12345);  -- pid of the build

The usual culprit is a session in idle in transaction, or a long report. Ask the owner to finish it. If that isn't possible, end the session:

SELECT pg_terminate_backend(67890);  -- the blocker, not the build

Warning

pg_terminate_backend rolls back the blocker's open transaction. Confirm what it is first. pg_cancel_backend won't help on an idle in transaction session, because there's no running statement to cancel.

The next locker may then appear. Run the query again until the phase moves on.

#4. Find out why it failed

Search the PostgreSQL log for the index name, or read the migration's output.

For a unique index, find the duplicates:

SELECT email, count(*)
FROM app.users
GROUP BY email
HAVING count(*) > 1
ORDER BY count(*) DESC
LIMIT 20;

Warning

An invalid unique index can keep enforcing uniqueness after the failure. New duplicates may get duplicate key errors even though the index isn't usable for queries. If production writes are failing, drop it now and clean up afterwards.

#5. Clean up and retry

#Leftovers from CREATE INDEX CONCURRENTLY

DROP INDEX CONCURRENTLY IF EXISTS sales.idx_orders_customer;

DROP INDEX CONCURRENTLY can't run in a transaction block and doesn't support CASCADE.

Then retry from a direct connection, not through a transaction-pooling proxy:

SET statement_timeout = 0;
SET lock_timeout = 0;
SET maintenance_work_mem = '1GB';   -- size this to the host's free memory

CREATE INDEX CONCURRENTLY idx_orders_customer ON sales.orders (customer_id);

REINDEX INDEX CONCURRENTLY sales.idx_orders_customer; also rebuilds an invalid index in place, and keeps the name and definition. If the cause was duplicates, it fails the same way until you've cleaned up the data. To target an invalid index this way, use REINDEX INDEX. Table- and schema-level forms skip invalid indexes when running concurrently.

#Leftovers from REINDEX CONCURRENTLY

The suffix tells you what to do. A number may follow it, such as _ccnew1.

DROP INDEX CONCURRENTLY sales.idx_orders_customer_ccnew;
REINDEX INDEX CONCURRENTLY sales.idx_orders_customer;

#Verify

SELECT indexrelid::regclass, indisvalid, indisready, indislive
FROM pg_index
WHERE indexrelid = 'sales.idx_orders_customer'::regclass;

All three must be t.

#6. Partitioned tables

CREATE INDEX CONCURRENTLY isn't supported on a partitioned parent. Build each partition's index separately and attach it:

-- Creates an invalid parent index, without touching the partitions
CREATE INDEX idx_orders_customer ON ONLY sales.orders (customer_id);

-- Repeat for each partition
CREATE INDEX CONCURRENTLY idx_orders_2026_10_customer
    ON sales.orders_2026_10 (customer_id);

ALTER INDEX sales.idx_orders_customer
    ATTACH PARTITION sales.idx_orders_2026_10_customer;

The parent becomes valid automatically once every partition has an attached index. If a partition build fails, clean up that one child index with the steps above, and confirm it's valid before attaching it.

#Replicas

• Physical standbys replay the catalog changes, so they show the same invalid index. Run all the commands on the primary; the drop reaches the standbys through WAL.
• Logical subscribers don't receive DDL. Check for and fix invalid indexes on each subscriber separately.

#Prevent it next time

• Don't use IF NOT EXISTS on a concurrent build without checking the result. If an invalid index already has that name, the command skips it with a notice and reports success, and your migration passes with an unusable index.
• Add a validity check after each concurrent build in your migrations.
• Run concurrent builds outside a transaction block, or they fail with CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Most migration tools need a per-migration setting for this, so check yours.
• Cap idle transactions, so one forgotten session can't hold up a build:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

• Monitor for leftovers on every database with this query. It skips active builds and partitioned parents:

SELECT count(*) AS invalid_indexes
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid
  AND c.relkind <> 'I'
  AND NOT EXISTS (SELECT 1
                  FROM pg_stat_progress_create_index p
                  WHERE p.index_relid = i.indexrelid);