You ask your coding agent for a unique index on users.email. It writes CREATE UNIQUE INDEX CONCURRENTLY, which is the right call on a live table. The deploy fails. The agent reruns the migration, gets told the index already exists, adds IF NOT EXISTS, and CI goes green. Weeks later someone runs \d users and sees the word INVALID at the end of the index line.

None of this is a bug. The Postgres CREATE INDEX docs say that if a concurrent build hits "a deadlock or a uniqueness violation in a unique index", the command fails "but leave[s] behind an 'invalid' index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead."

Queries get nothing from it. Depending on where the build died, your writes may still be paying for it. Both kinds of leftover are easy to reproduce in a throwaway Docker container, and so is the other error agent-written migrations hit with CONCURRENTLY: cannot run inside a transaction block.

What "failed halfway" leaves behind

A concurrent build is not one transaction. Per the same docs, the index "is actually entered as an 'invalid' index into the system catalogs in one transaction, then two table scans occur in two more transactions." The catalog row commits first. Anything that kills the command after that point leaves the row in place: a duplicate key, a deadlock, a statement_timeout, a cancelled deploy.

Two flags in pg_index tell you which kind of leftover you have. indisvalid = false means the index "cannot safely be used for queries." indisready = false means "the index must be ignored by INSERT/UPDATE operations."

The order in which those flags flip decides the cost. In the Postgres source, index_concurrently_build() runs the first table scan and only afterwards marks the index ready, with a comment saying that once this commits, new transactions "must insert new entries into the index for insertions and non-HOT updates." That gives two outcomes:

Where the build died indisvalid indisready Writes maintain it
First scan (a duplicate already in the table) false false No
Second scan or final wait (late duplicate, deadlock, timeout, cancel) false true Yes

The second row is the expensive one. A January 2026 postgres.ai write-up showed an invalid-but-ready index gaining leaf entries on INSERT and UPDATE, blocking HOT updates on its column (0% HOT when updating the indexed column in their test), and adding WAL and VACUUM work. Their test marked the index invalid by hand rather than by a real failed build, but the write path is the same. For unique indexes the docs add one more cost: "if a failure does occur in the second scan, the 'invalid' index continues to enforce its uniqueness constraint afterwards." You can get unique-violation errors from an index the planner will never use.

The first row is cheaper, but it still blocks a retry under the same name and shows up in \d looking like a real index.

A library card-catalog cabinet with one drawer drawn as a hollow dashed outline, while new cards keep sliding into it
An invalid index answers no queries, but a ready one still takes every new entry.

Reproduce the cheap kind: a duplicate in a unique build

Start a throwaway Postgres 18 from the official Docker image and open psql:

docker run --name pg-cic -e POSTGRES_PASSWORD=demo -d postgres:18 docker exec -it pg-cic psql -U postgres

Build a table with a duplicate nobody knows about, then ask for a unique index:

CREATE TABLE users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); INSERT INTO users (email) SELECT 'user' || g || '@example.com' FROM generate_series(1, 200000) AS g; -- the duplicate the migration does not know about INSERT INTO users (email) VALUES ('user42@example.com'); CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);

The last statement fails with could not create unique index "users_email_key" and a DETAIL line naming the duplicated key, the same shape as this 2019 pgsql-bugs report. Run \d users and the index is still listed, with INVALID at the end of its line, as in the docs' own example. This is not a lab-only case: GitLab's tracker has an issue about a unique index found invalid in production, where the rebuild failed on duplicated values.

Now do what a retry loop does. Run the same statement again and it fails because a relation named users_email_key already exists. Add IF NOT EXISTS and it passes. The docs for that clause say Postgres only issues a notice when "a relation with the same name already exists", and that "there is no guarantee that the existing index is anything like the one that would have been created." Nothing in that check looks at validity. The migration is recorded as applied and the index is still invalid.

Reproduce the expensive kind: a timeout in the final wait

After the second scan, the build "must wait for any transactions that have a snapshot ... predating the second scan to terminate." A long report or a transaction someone left open is enough to hold that wait. Add a statement_timeout (golang-migrate exposes one as x-statement-timeout) and the build gets cancelled after it already marked the index ready.

Open a second terminal with docker exec -it pg-cic psql -U postgres and hold a snapshot there:

-- session B BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM users; -- leave this transaction open

Back in the first session:

-- session A SET statement_timeout = '5s'; CREATE INDEX CONCURRENTLY users_created_at_idx ON users (created_at);

On 200,000 rows the two scans should finish well inside five seconds. The build then sits waiting for session B. If you query pg_stat_progress_create_index from a third session while it waits, the phase column reads waiting for old snapshots. When the timeout fires, run COMMIT; in session B. You now have one leftover of each kind.

Find every leftover in pg_index

This query lists every invalid index, whether writes maintain it, and its size:

SELECT n.nspname AS schema, c.relname AS index_name, t.relname AS table_name, i.indisready AS maintained_on_write, pg_size_pretty(pg_relation_size(c.oid)) AS size 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;

Expect users_email_key with maintained_on_write = false and users_created_at_idx with true. To see the difference, insert another batch of rows and run the query again:

INSERT INTO users (email) SELECT 'late' || g || '@example.com' FROM generate_series(1, 100000) AS g;

The ready index grows with the table. The other one is ignored by inserts, so it does not.

One caution before you drop anything: an index that another session is building right now is also invalid. Check pg_stat_progress_create_index first. If it has a row for that table, leave the index alone.

A migration conveyor belt where one crate labelled only by an orange warning stripe is pulled aside under a magnifying glass
Query pg_index for invalid entries before any retry.

Clean up without locking the table

DROP INDEX CONCURRENTLY removes an index "without locking out concurrent selects, inserts, updates, and deletes." It takes one index name per command and cannot run in a transaction block, so send each one on its own:

DROP INDEX CONCURRENTLY IF EXISTS users_email_key; DROP INDEX CONCURRENTLY IF EXISTS users_created_at_idx;

A unique build that failed on real duplicates will fail the same way next time, so fix the data before you retry. Deleting the newer row is the quick demo fix. In a real table, decide which row wins (or merge them) before you delete anything:

DELETE FROM users a USING users b WHERE a.email = b.email AND a.id > b.id; CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);

The docs name a second recovery path: rebuild in place with REINDEX INDEX CONCURRENTLY. The REINDEX page says "only REINDEX INDEX is able to perform a concurrent build on an invalid index." If that rebuild fails too, it leaves its own invalid index suffixed _ccnew (drop it and retry) or _ccold (drop it, the rebuild itself succeeded).

Then make the deploy refuse to finish with a leftover in place. Run this as the last step, after every migration has completed:

DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_index WHERE NOT indisvalid) THEN RAISE EXCEPTION 'invalid index left behind'; END IF; END $$;

"cannot run inside a transaction block": the opt-out per tool

The docs are blunt: "a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot." The error reads CREATE INDEX CONCURRENTLY cannot run inside a transaction block, as in this Prisma discussion. You hit it two ways. Either the migration tool wraps each file in BEGIN/COMMIT, or the file holds several statements sent as one query, which the Postgres protocol docs say run "as a single transaction" in an implicit transaction block.

Agents walk into this honestly. golang-migrate's getting-started guide tells you to wrap multi-command migrations in a transaction, so an agent that read the docs will do exactly that.

Rails. Active Record wraps every migration in a transaction when the adapter supports DDL transactions. disable_ddl_transaction! turns that off for one migration, and the add_index docs add: "Concurrently adding an index is not supported in a transaction."

class AddUniqueIndexToUsersEmail < ActiveRecord::Migration[8.0] disable_ddl_transaction! def change add_index :users, :email, unique: true, algorithm: :concurrently end end

Knex. "By default, each migration is run inside a transaction." Export config.transaction = false from the file (or set disableTransactions globally):

exports.config = { transaction: false }; exports.up = (knex) => knex.raw( 'CREATE UNIQUE INDEX CONCURRENTLY users_email_key ' + 'ON users (email)' ); exports.down = (knex) => knex.raw('DROP INDEX CONCURRENTLY IF EXISTS users_email_key');

golang-migrate. It does not add a transaction itself, but its Postgres driver README warns that "running multiple SQL statements in one Exec executes them inside a transaction." Put the statement alone in its own file, with no BEGIN/COMMIT, or enable x-multi-statement so statements run one by one:

-- 000042_users_email_key.up.sql CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email); -- 000042_users_email_key.down.sql DROP INDEX CONCURRENTLY IF EXISTS users_email_key;

If a run fails anyway, the guide says the database is marked dirty and you must check whether the migration partly applied before migrate force VERSION. For this migration, "partly applied" means an invalid index: drop it first.

Prisma. There is no per-migration opt-out. The closest open issue, #9154 ("Run a migration by hand and update the migration table manually"), has users asking for CONCURRENTLY support, and no fix has shipped. One user in the discussion linked above reports it works when each CREATE INDEX CONCURRENTLY sits alone in its own migration. The other route: prisma migrate dev --create-only, run the statement by hand, then prisma migrate resolve --applied <migration_name>. Verify either route on your Prisma version before you rely on it.

Whichever tool you use, paste rules like these into the file your coding agent reads:

- One CONCURRENTLY statement per migration file. - Turn the tool's transaction wrapper off for that file. - After a failed run, query pg_index for invalid indexes. - Drop leftovers with DROP INDEX CONCURRENTLY, then retry. - Never answer "already exists" with IF NOT EXISTS. - End every deploy with the invalid-index check.

If this was useful

Index builds, migration tooling and the locks behind them are the part of Postgres that bites hardest once a table is big and live. The Database Playbook covers how to choose a store and how to change its schema without taking writes down, which is the job an agent-written migration has to get right.

Database Playbook: Choosing the Right Store for Every System You Build