Skip to main content

DEPLOY.LEGACY.INVALID_INDEX — An index a `CREATE INDEX CONCURRENTLY` never finished

  • Category: safety
  • Severity: medium — debt somebody can carry another week. high belongs to the collision finding below, where the next deploy is certain to fail
  • Level: 0
  • Downtime class: online — a deploy check reports on the STATE of a target rather than on a DDL operation, and an invalid index locks nothing and slows nothing. What it does is make a later deploy FAIL, which is a fact about severity rather than about this axis
  • Stability: stable
  • Suites: deploy
  • Applies to: PostgreSQL

What it reports​

An index the catalog marks invalid: the wreckage a CREATE INDEX CONCURRENTLY leaves behind when it is canceled, killed, or lost with its session.

The query reads the catalog and nothing else:

pg_index      i  -- the finding: indisvalid = false
pg_class      c  -- the index
pg_class      t  -- the table it sits on
pg_namespace  n  -- the schema
pg_depend     d  -- excluded: extension-owned objects
pg_inherits   h  -- excluded: partition children

The predicate is i.indisvalid = false, and rows come back ordered by schema, then table, then index name — so two runs against the same state report in the same order.

i.indisready is read as well, but it does not produce a second rule id. When it is false, the build never even reached the point of tracking new rows, and the finding says so inside its own message: (it never reached the point of tracking new rows). It is the earlier and more clearly dead of the two states, and the action for both is identical — two ids for one action is a distinction a reader has to look up.

Nothing found is a pass, not a skip. The distinction matters more here than usual: this check answers is there wreckage from last time, and a skip would leave that question open on exactly the run that could have closed it. If the catalog cannot be read at all, the result is undetermined with the driver's own message attached — never a quiet pass.

The louder sibling: DEPLOY.LEGACY.INVALID_INDEX_NAME_COLLISION​

Same catalog state, different certainty, so it gets its own id rather than only a louder severity on this one. When a migration in this deploy creates an index by the name the leftover already occupies, the finding is reported under DEPLOY.LEGACY.INVALID_INDEX_NAME_COLLISION instead — same category, same level, same online downtime class, and high where the plain finding carries medium.

The match is made against the pending statements the run was handed — CREATE INDEX, CREATE FULLTEXT INDEX and CREATE SPATIAL INDEX, and only their index targets. Each pending name is kept exactly as its statement spelled it, and every catalog row is then compared in both spellings: as schema.index and as the bare index. The asymmetry is real rather than caution — CREATE INDEX idx ON public.orders (…) names the index without a schema, PostgreSQL puts it in the table's, and the catalog reports it back as public.idx. Comparing only the qualified form would miss every unqualified statement, which is most of them.

Why it matters​

Three things at once, and the first two are why nobody ever notices:

  • No query uses it. An invalid index is not used by the planner, so it helps nobody.
  • Every write still maintains it. It costs on every INSERT and every UPDATE, for as long as it exists. The one exception is the indisready = false state above: an index that never reached the point of tracking new rows is not maintained either, which is what makes it the deader of the two.
  • It blocks the re-run. CREATE INDEX refuses a name that already exists — invalid or not.

That combination is why it survives: nothing is broken, and nothing points at it.

The third item is why this belongs to the deploy gate rather than to audit. The audit suite would report it as a schema problem, which it is. The deploy gate reports it as a problem about to become an error: the migration that was interrupted is usually the one about to be re-run, so the second attempt fails on a name conflict, halfway through a deploy, for a reason that reads like a bug in the migration.

What to do about it​

Decide, then act — and the decision is when, not whether.

DROP INDEX CONCURRENTLY public.orders_idx;

The finding names that statement for the exact index it found, fully qualified. Two properties make it a decision rather than a step:

  • it cannot run inside a transaction block, and
  • it takes a SHARE UPDATE EXCLUSIVE lock.

That is a call for whoever owns the deploy window, which is why the finding carries the sequence and stops there. When the collision id is the one you got, the order is fixed: drop the leftover first, then run the deploy.

Why nothing is dropped for you​

Dropping an index is a schema change, and this command is the one that promises never to make one. DROP INDEX CONCURRENTLY is also not unconditionally safe to automate, for the two reasons above. This command drops nothing itself — the finding says so in its own message, so nobody reading a deploy log has to wonder whether the tool already acted.

What it does not claim​

  • It cannot tell a build in progress from a build that failed. Both are indisvalid = false while they exist. An index being built right now is reported the same way as one abandoned last month.
  • It reports debt from an EARLIER deploy, not a property of the migration at hand. The one exception is the collision arm, which is the only part of this check that looks at the pending statements at all.
  • The collision arm matches an unqualified statement on its bare name alone. A pending CREATE INDEX idx ON … carries no schema, so it collides with a leftover named idx in any schema the query read, not only in the one that statement's table lives in. The comparison has no schema to check on that side, and reporting the leftover one schema over is the direction that costs a reader a look rather than a failed deploy.
  • Extension-owned indexes are not reported. An object pg_depend ties to an extension belongs to that extension's own migration story, and a project cannot act on it — reporting one is asking somebody to fix what they do not own. Their absence from the report is not a statement that they are valid.
  • Partition children are not reported individually. A row with a pg_inherits parent is skipped in favor of that parent, because a hundred identical findings for one partitioned table is the shape that teaches a reader to skim the report.
  • On MySQL it produces no result at all — not a pass, not a skip, no fourth outcome value. The runner asks whether the check applies before running anything, and the answer is pgsql only. InnoDB's online index build is not a separate catalog state, so there is nothing left behind to find and no MySQL counterpart to write.

An outage on the record​

A concurrent index build hit a defect in PostgreSQL 9.6.6 and took comments down for roughly ninety minutes. No rule in this package would have prevented it — the migration was written exactly as recommended. What this check offers is the part afterwards: the failed build leaves an INVALID index behind, and it is found and named rather than discovered months later by a query plan.

See outages on the record, where that entry is kept precisely because it is not a win.