Postgres invalid indexes: recover a concurrent build

Recover a failed concurrent index build by inspecting the saved index, not by blindly replaying the migration. PostgreSQL can leave an invalid artifact after CREATE INDEX CONCURRENTLY fails; it cannot serve queries and may still add maintenance work ^[1]. Establish whether a build is active, resolve the original error, then require the intended index to be valid.

Why does the index exist when the migration failed?#

A concurrent index build is not one indivisible change. PostgreSQL first records an invalid index, performs its build and validation work across internal transactions, then makes the finished index available. A failure can therefore leave a catalog entry without a usable result ^[1]. The migration tool’s exit status and the database’s remaining objects answer different questions.

The important catalog flag is indisvalid: it tells you whether the index is valid for queries. A separate flag, indisready, concerns maintenance by inserts and updates ^[2]. Do not collapse them into a single “index exists” condition. An in-progress build and a failed build can both deserve investigation even though their names appear in the catalog.

IF NOT EXISTS does not validate the existing object’s definition or complete an interrupted build ^[1]. That makes it useful for a narrowly defined existence check, but insufficient as a migration completion rule. Check this index’s definition and validity against the migration that was supposed to create it.

Reproduce the trap in a disposable schema#

Scenario: a subscription table is about to gain a unique index on an external reference, but historical duplicate references remain. The following laboratory uses a permanent table in a new disposable schema. Run statements individually in a scratch PostgreSQL database with autocommit; do not place CREATE INDEX CONCURRENTLY inside a transaction block ^[1]. These are illustrative commands, not results from a database we ran.

-- Stop if this schema name is already in use; choose a fresh name.
CREATE SCHEMA editorial_index_lab;
CREATE TABLE editorial_index_lab.subscriptions (
  id integer PRIMARY KEY,
  external_ref text NOT NULL
);
INSERT INTO editorial_index_lab.subscriptions (id, external_ref)
VALUES (1, 'partner-a'), (2, 'partner-a'), (3, 'partner-b');

-- Run separately with autocommit. Expected: duplicate-key failure.
CREATE UNIQUE INDEX CONCURRENTLY external_ref_uidx
  ON editorial_index_lab.subscriptions (external_ref);

The expected failure is a uniqueness violation. Keep the error text, then inspect the artifact rather than assuming rollback removed it. The next query records the index identity, its relation and its flags. The reconstructed definition comes from pg_indexes, which exposes useful index metadata without telling you that the definition matches your migration ^[6].

SELECT i.indexrelid::regclass AS index_name,
       i.indrelid::regclass AS table_name,
       i.indisvalid, i.indisready, i.indisunique,
       v.indexdef
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
JOIN pg_indexes AS v
  ON v.schemaname = n.nspname AND v.indexname = c.relname
WHERE n.nspname = 'editorial_index_lab'
  AND c.relname = 'external_ref_uidx';

In this case, an invalid row is the observation to investigate; do not assume the readiness flag always has one fixed value after every failure. Now replay the command below, still as a separate autocommit statement. The expected notice says the relation already exists. That notice is not evidence that uniqueness was established or that the invalid artifact became valid ^[1][2].

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS external_ref_uidx
  ON editorial_index_lab.subscriptions (external_ref);

-- Run inspect-index-contract.sql again and compare the flags.

Rehearse that retry. It can finish without establishing the uniqueness the migration was meant to add. A guard against a duplicate object name is not the same as a guard against an incomplete migration. Preserve both the original error and the later notice in the migration record.

Is the build abandoned, or still doing useful work?#

Check active work before intervention. PostgreSQL exposes index-build phases in pg_stat_progress_create_index, including phases that wait for other transactions ^[3]. Query it from an authorized monitoring connection in the same database. An empty result is only one observation; confirm permissions, the migration runner’s state and its logs before declaring an artifact abandoned.

SELECT pid, command, phase,
       relid::regclass AS table_name,
       index_relid::regclass AS index_name,
       current_locker_pid, lockers_done, lockers_total
FROM pg_stat_progress_create_index
WHERE datname = current_database()
  AND relid = 'editorial_index_lab.subscriptions'::regclass;

If the phase names a wait, identify the owning transaction and the application responsible for it. A concurrent build avoids the ordinary write-blocking behavior of a regular build, but still performs work and may wait; it is not a promise of no operational cost ^[1][3].

Do not repeatedly cancel a build solely because its elapsed time is longer than expected. Decide who owns the maintenance window, which workload signal would justify stopping it and how an interrupted artifact will be handled. A timeout can bound a deploy’s wait while still leaving recovery work behind. Write the timeout and artifact-recovery decision into the migration plan.

Choose the recovery after fixing the data#

For the laboratory, inspect the duplicate references and decide how the business data should be corrected. The update below assigns a different reference to the illustrative duplicate row. It is an example-specific repair, not a production deduplication policy. In a real system, changing an external identifier without its owner’s agreement can break integration contracts. If identifiers belong to tenants, settle their uniqueness scope before choosing this index. Our row-level security guide addresses row visibility as a separate boundary.

SELECT external_ref, count(*)
FROM editorial_index_lab.subscriptions
GROUP BY external_ref
HAVING count(*) > 1;

-- Illustrative data repair, valid only for this disposable example.
UPDATE editorial_index_lab.subscriptions
SET external_ref = 'partner-a-second'
WHERE id = 2;

Once the original cause is resolved and the build is confirmed stopped, choose a targeted recovery. PostgreSQL documents dropping and recreating the failed index, or rebuilding it with REINDEX INDEX CONCURRENTLY ^[1][5]. A concurrent drop has restrictions, including transaction and dependency restrictions, and requires ownership ^[4]. Have the database owner approve the specific object and preserve its definition first.

Choose one recovery path for the lab. A direct concurrent reindex retains the intended definition while rebuilding its contents. Run it separately with autocommit, then repeat the catalog query. The expected completion condition is a valid unique index on the intended table and column ^[2][5]. Do not run both recovery options merely to be thorough.

REINDEX INDEX CONCURRENTLY editorial_index_lab.external_ref_uidx;

-- Then rerun inspect-index-contract.sql.
-- Compare the definition with the intended migration, not only its name.

What should the migration assert before it returns success?#

Require exactly the intended object, valid for queries and ready for maintenance, with the expected uniqueness, table, key order, index method and predicate. Include any expression, collation, operator class or included columns your real definition uses ^[2][6]. Comparing a saved definition during review and checking catalog flags afterwards are complementary checks.

For this lab, the expected record is a unique btree index on external_ref in editorial_index_lab.subscriptions, with no predicate or included columns. A different definition under external_ref_uidx is a conflict to resolve, not something IF NOT EXISTS should silently accept. Keep a machine-readable expected definition beside the migration rather than reconstructing its purpose from an object name.

After a valid build, test the application’s intended data property in the scratch environment. Attempting to insert the same external reference should be rejected. Query-performance verification is separate: a tiny laboratory table may reasonably use a sequential scan. Do not force an index plan and call that a benchmark, or infer a production speedup from successful index creation.

The examples target PostgreSQL 18 documentation and a nonpartitioned laboratory table ^[1]. No database execution or performance measurement was performed for this article. Rehearse your migration runner’s transaction behavior and interruption handling before using the procedure on a production dataset. Our upgrade rehearsal guide uses the same principle: executable acceptance evidence before a production change.

Questions to settle before a retry#

Can an invalid unique index still affect writes?

Yes. PostgreSQL documents that a unique concurrent build can begin enforcing uniqueness before it becomes valid, and a failure during the later scan can leave enforcement in place ^[1]. Inspect the actual artifact and failure stage; do not infer write behavior from query invalidity alone.

Does IF NOT EXISTS resume the build?

No. It avoids an error when an object with the same name already exists; it does not establish that the existing object matches the intended index ^[1]. Read validity and definition explicitly, then choose recovery after understanding the original failure.

Should every invalid index be dropped immediately?

No. An index may be invalid while a concurrent build is still active. Check progress, runner state, permissions and dependencies first ^[2][3][4]. An unreviewed cleanup loop can interfere with maintenance or remove an object whose behavior the application still relies on.

Can the migration wrap concurrent creation in BEGIN and COMMIT?

No. CREATE INDEX CONCURRENTLY cannot run inside a transaction block ^[1]. Check how your migration runner submits statements and scopes its transactions. A script that works in an autocommit psql session does not prove that a framework will execute it with the same boundaries.