---
title: "Retrying a failed PostgreSQL CREATE INDEX CONCURRENTLY | Proofline"
description: "When CREATE INDEX CONCURRENTLY fails, it leaves an invalid index behind."
url: https://proofline.sh/learn/postgres-create-index-concurrently-retry/
---

[Learning center](/learn/)

# Retrying a failed PostgreSQL CREATE INDEX CONCURRENTLY

When CREATE INDEX CONCURRENTLY fails, it leaves an invalid index behind. A migration that retries with IF NOT EXISTS then reports success, and the table still has no usable index. Check pg\_index.indisvalid after the migration, and drop the invalid index before you retry.

Breaking migrations The Proofline team November 20, 2026 3 min read

## Build a unique index on data with a duplicate

A users table should get a unique index on `email`. One address already appears twice. We ran this on PostgreSQL 16.15.

```
CREATE TABLE users (id integer PRIMARY KEY, email text NOT NULL);
INSERT INTO users VALUES
  (1, 'ana@example.com'),
  (2, 'ben@example.com'),
  (3, 'ana@example.com');
```

Migration tools often wrap each file in a transaction. `CONCURRENTLY` refuses to run there:

```
BEGIN;
CREATE INDEX CONCURRENTLY users_email_key ON users (email);
-- ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
ROLLBACK;
```

That failure leaves nothing behind. Most tools have a per-migration switch that turns the transaction off. With it off, the build runs and meets the duplicate:

```
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);
```

```
ERROR:  could not create unique index "users_email_key"
DETAIL:  Key (email)=(ana@example.com) is duplicated.
```

## The failed build leaves an index behind

A concurrent build runs in several transactions. The first commits the index's catalog entry before any scan starts, so that later transactions can wait for other sessions to see it. When a later step fails, nothing rolls that entry back:

```
SELECT indexrelid::regclass AS index, indisvalid, indisready
FROM pg_index WHERE indrelid = 'users'::regclass;
```

| index | indisvalid | indisready |
| --- | --- | --- |
| users\_pkey | t | t |
| users\_email\_key | f | f |

The [CREATE INDEX documentation](https://www.postgresql.org/docs/16/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY) says such an index "will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead." In our run the build failed before the index was marked ready, so `indisready` is also false. A failure later in the build, for example a duplicate inserted while the index is being validated, leaves an index that every write maintains and no query uses.

## The retry that hides the failure

A common way to make a migration rerunnable is `IF NOT EXISTS`:

```
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS users_email_key ON users (email);
```

```
NOTICE:  relation "users_email_key" already exists, skipping
CREATE INDEX
```

The command succeeds, the migration tool records the migration as applied, and the index is still invalid. The documentation warns about this case: with `IF NOT EXISTS`, the existing index may be nothing like the one the statement would have created. The table accepts another duplicate, and a query on `email` still scans the whole table, even with sequential scans disabled:

```
INSERT INTO users VALUES (4, 'ana@example.com');
-- INSERT 0 1

SET enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email = 'ben@example.com';
--  Seq Scan on users
--    Filter: (email = 'ben@example.com'::text)
```

## Recover and retry

Fix the data, drop the invalid index without blocking writes, and build it again:

```
DELETE FROM users WHERE id IN (3, 4);
DROP INDEX CONCURRENTLY users_email_key;
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);
```

`indisvalid` is now `t`. Deleting the duplicates is only right for this example. A real table needs a decision about which row survives, which [finding duplicates before a unique constraint](/learn/postgres-unique-constraint-existing-duplicates/) covers.

## What to rehearse

Run the migration once against a copy with the production data, and once more after forcing a failure, to see what your migration tool records. After every concurrent index migration, in the rehearsal and in production, look for invalid indexes:

```
SELECT c.relname AS invalid_index, t.relname AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
WHERE NOT i.indisvalid;
```

Make the migration fail when this returns a row, rather than relying on `IF NOT EXISTS`. A concurrent build also waits for transactions that started before it, so a long transaction can hold it for a long time; it does not block the application's reads and writes while it waits.

Find the other changes that need a rehearsal in [which PostgreSQL migration needs a rehearsal](/learn/postgres-migration-rehearsal/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Find the duplicates a PostgreSQL unique constraint will let through](/learn/postgres-unique-constraint-existing-duplicates/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
    
-   ### [Which PostgreSQL migration needs a rehearsal before the next deploy](/learn/postgres-migration-rehearsal/)
    
    Breaking migrations The Proofline team November 20, 2026 4 min read
