Invalid Indexes: The Quiet Debris of Failed CREATE INDEX CONCURRENTLY in PostgreSQL

The storage audit was supposed to be routine — a quarterly look at which tables and indexes had grown, ahead of a disk resize decision. The surprise was not a table. It was an index named idx_events_tenant_created_idx_ccnew, 9 GB, attached to our busiest table, that appeared in no query plan and in no migration file. Digging turned up ten more like it across the cluster, forty gigabytes in total, every one of them the residue of a CREATE INDEX CONCURRENTLY that had failed partway through — killed by a lock timeout, canceled by an operator, or disconnected when a laptop lid closed. PostgreSQL leaves the half-built index in place, marks it invalid, and then does the worst possible thing with it: ignores it for reads while maintaining it for writes. Every insert into the events table had been paying to update an index no query would ever use, for months.

Why does a failed concurrent build leave an invalid index behind?

Because CONCURRENTLY trades atomicity for availability, and the trade has a price list. A plain CREATE INDEX is one transaction: it either exists fully or not at all. A concurrent build runs in multiple transactions — catalog the index, take a snapshot, scan the table into it, wait for old transactions, validate against concurrent writes, mark it valid — specifically so it never holds a lock that blocks writers. If the build dies anywhere in that sequence, the catalog entry already exists but the index contents cannot be trusted, so PostgreSQL leaves it with indisvalid = false rather than guess at correctness. The index is real on disk, receives write maintenance because indisready is already true for most failure points, and is excluded from planning because an index that might miss rows must never answer queries. The failure modes that produce this are all mundane: statement_timeout firing during the scan phase, lock_timeout during the brief catalog-update windows — the same lock-queue dynamics from the DDL lock queue notes — an operator pressing Ctrl-C, or a network drop between the migration runner and the database. Our nine-gigabyte example traced back to a lock_timeout of three seconds that someone had added to the deploy pipeline, which is an excellent setting for migrations generally and a reliable invalid-index generator for large concurrent builds specifically.

How do you find them, and how do you tell them apart from healthy indexes?

The catalog tells you everything; the problem is that nothing looks at the catalog until you do. pg_index carries the two flags that matter — indisvalid, whether the index can answer queries, and indisready, whether it receives writes — and the debris pattern is exactly ready-but-not-valid. The audit query we now run monthly:

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

Two subtleties are worth the pixels. First, a freshly created, still-building index is also indisvalid = false, so this query will show you in-flight builds alongside debris — check for an active backend running CREATE INDEX before condemning anything. Second, invalid indexes are invisible in psql’s d output on some versions and absent from most "unused index" queries, because the usual unused-index hunt via pg_stat_user_indexes finds indexes that are valid but never scanned; invalid debris is a different category entirely, which is why it survived every prior cleanup pass we had run. It also never appears in the bloat reviews from the table bloat measurement guide, because those reviews start from pg_stat_user_tables and its indexes — valid ones.

What is the correct cleanup — and what did we get wrong?

Drop, then rebuild deliberately. The drop must itself be DROP INDEX CONCURRENTLY, because a plain DROP INDEX takes an ACCESS EXCLUSIVE lock on the table, which on the events table at midday would have recreated the incident that spawned the debris. Our first mistake was attempting REINDEX CONCURRENTLY directly on the invalid index, which is legal and rebuilds the contents, but rebuilds onto the same catalog entry — including, in one case, an index definition we no longer wanted, a partial predicate from a superseded design. The pattern that survived review: inspect the index definition with pg_get_indexdef, decide whether the index should exist at all — three of our eleven were for queries the application had stopped issuing, and deleting them was the whole cleanup — then DROP INDEX CONCURRENTLY the debris and, if the index earns its place, build it fresh with a proper runbook: no statement_timeout, a generous lock_timeout paired with retries, and a migration runner that stays connected for the duration. The write amplification reclaimed was measurable: inserts per second on the events table rose about six percent once it stopped maintaining two orphaned indexes, and the storage audit finally reconciled with reality.

-- Inspect before condemning: what was this index trying to be?
SELECT pg_get_indexdef('idx_events_tenant_created_idx_ccnew'::regclass);

-- Clean removal without blocking writers
DROP INDEX CONCURRENTLY IF EXISTS idx_events_tenant_created_idx_ccnew;

How do you stop generating new debris?

By treating CREATE INDEX CONCURRENTLY as an operation with failure modes, not a statement. The migration tooling now runs concurrent index builds in a dedicated step with statement_timeout disabled and lock_timeout set with a retry loop rather than a hard fail, because a timeout killing the build is how the debris is born. The audit query above runs on a schedule and pages nobody — it files a ticket — because debris accumulates slowly and a weekly diff catches it while it is still megabytes. And every incident review that involved a canceled build now ends with "check for invalid indexes" as a standing checklist item, the same discipline the reindex concurrently notes apply to the other end of an index’s life. The deeper lesson generalizes: in PostgreSQL, the concurrent variant of any DDL is a protocol, and a protocol interrupted halfway leaves state behind. The catalog is honest about it if you ask; the cost of the debris is what you pay for never asking.

Watching index hygiene with MonPG

Invalid indexes are a slow leak — a percent here of write I/O, a gigabyte there of storage — which makes them exactly the kind of thing that only a trend line catches. MonPG monitors PostgreSQL in production today, and its index advisor and PostgreSQL monitoring surface per-index size and write activity alongside the query plans that reference them, so an index that grows but is never read stands out in the weekly review instead of hiding until a disk-resize meeting. Debris is inevitable when humans run concurrent DDL; silent debris is optional.

Related documentation

• PostgreSQL: indexes