Skip to main content

PG.L7.INDEX_INVALID — An index a concurrent build left invalid

  • Category: performance
  • Level: 7
  • Confidence: deterministic
  • Downtime class: none — the finding reports a state and changes nothing
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

What it reports​

An index whose pg_index.indisvalid is false while no index build is running on its table. PostgreSQL leaves such an index behind when a CREATE INDEX CONCURRENTLY or a REINDEX CONCURRENTLY does not finish: a uniqueness violation, a deadlock, a canceled statement or a lost session.

The index is…Answer
validnothing
invalid, and no build is running on its tableflagged
invalid, and a build is running on its tableundetermined, reason index_build_in_progress
invalid, and a build in this database hides its table from this sessionundetermined, reason invalid_index_found_but_builds_unverifiable

What it costs depends on how far the build got​

PostgreSQL ignores an invalid index for every query. Whether writes still pay for it is decided by pg_index.indisready:

  • Ready. A build that failed while validating, after writes had reached the index, leaves an index that every INSERT and UPDATE maintains. Measured on PostgreSQL 18: such an index grew from 456 to 896 KiB over 20,000 inserts.
  • Not ready. A build that failed while building, such as a unique index over data that already holds a duplicate, leaves an index nothing writes to. Measured on PostgreSQL 18: it stayed at zero bytes through the same inserts, and it did not reject a duplicate either.

The finding says which of the two it is. Either way the index holds its name. The migration that created it is usually the one that runs next, and CREATE INDEX refuses a name that already exists, valid or not, so the second run fails halfway through a deploy for a reason that reads like a bug in the migration.

A build in flight is not wreckage​

An index is invalid on purpose while it is being built. The catalog reader therefore asks pg_stat_progress_create_index whether a build is running on the index's table, and a running build makes the answer undetermined instead of a finding. It matches by table rather than by index: measured on PostgreSQL 18, a running REINDEX CONCURRENTLY reports the index it replaces, not the transient _ccnew index that is invalid while it works.

That view hides another role's build from a session without pg_read_all_stats: the row is listed, its table is not. While such a build runs in the same database, this rule cannot tell the remains of a failed build from an index that is being built, and it says so instead of guessing. Granting the auditing role pg_read_all_stats gives it a definite answer.

Flagged​

// A unique index built concurrently over data that already holds a duplicate, in a migration
// that sets public bool $withinTransaction = false; as CONCURRENTLY requires. The statement
// fails and leaves users_email_unique behind, invalid.
DB::statement('CREATE UNIQUE INDEX CONCURRENTLY users_email_unique ON users (email)');

Preferred​

Remove the remains first, then build again, both outside a transaction block:

// Set public bool $withinTransaction = false; on the migration class, as CONCURRENTLY
// requires, and remove what an earlier failed build left before building again:
DB::statement('DROP INDEX CONCURRENTLY IF EXISTS users_email_unique');
DB::statement('CREATE UNIQUE INDEX CONCURRENTLY users_email_unique ON users (email)');

For a unique index, remove the duplicate rows before building again, or the second build fails like the first.

False positives​

A partitioned index built in steps. An index created with CREATE INDEX … ON ONLY on a partitioned table stays invalid until every partition has an index attached to it. While that sequence is under way and no build is running, the parent's index is reported. Once the last partition's index is attached, it is valid and the finding goes away.

What it does not do​

  • Drop anything. The finding names the statements, and SQLens never runs them. DROP INDEX CONCURRENTLY takes a SHARE UPDATE EXCLUSIVE lock and cannot run inside a transaction block, which is a decision for whoever owns the database.
  • Judge a partition's own indexes one by one. They are folded onto the partitioned table, like every other member of a partition.
  • Report an extension's indexes. A table owned by an extension the configuration does not audit is skipped, together with its indexes.

The deploy-time half​

DEPLOY.LEGACY.INVALID_INDEX reports the same state when a deploy is about to run, because the migration that was interrupted is usually the one about to run again. This rule reports every such index in the database, which is the shape an audit needs and a deploy does not.

Why there is no MySQL twin​

InnoDB has no such state: an index build is either committed or rolled back, and its catalog has no flag equivalent to indisvalid = false.

Sources​

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • concurrently — Build the index without taking the write lock the ordinary form takes.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.