Learning center

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.

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;
indexindisvalidindisready
users_pkeytt
users_email_keyff

The CREATE INDEX documentation 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 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.

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.

Start free with GitHub

Related reading