Invalid Indexes After Failed Concurrent Builds #

An index build was cancelled last month when it blocked a deployment. Since then, inserts into that table are 15% slower, the index occupies 40 GB, and no query has ever used it. \d shows it with the word INVALID. PostgreSQL is maintaining an index that the planner will never choose.

Concurrent maintenance is covered in REINDEX CONCURRENTLY without downtime. This page covers what it leaves behind when it fails.

The Condition #

CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY work in phases so that writes can continue:

  1. Create the index catalog entry, marked not ready and not valid.
  2. Wait for transactions that could still be unaware of it.
  3. Build the index from a snapshot, while concurrent writes maintain it.
  4. Wait again, validate the index against rows added meanwhile.
  5. Mark it valid.

If the command fails or is cancelled — a deadlock, a statement timeout, a unique violation during validation, a session disconnect — the catalog entry remains. Two flags in pg_index describe the state:

A failed REINDEX CONCURRENTLY leaves a transient index named with a _ccnew or _ccold suffix in the same state. Those also occupy space and, when indisready is true, write time.

Invalid indexes do not repair themselves. They must be dropped and, if still wanted, rebuilt.

What the two flags mean together A table of the flag combinations. Valid and ready is a normal index used by queries and maintained by writes. Ready but not valid means writes maintain it while the planner ignores it, the most expensive state. Not ready and not valid means an abandoned build that only occupies space. Valid but not ready does not occur in practice. used by queries maintained by writes valid + ready yes yes ready, not valid no yes — pure cost neither no no — dead weight after DROP — — check pg_index.indisvalid and indisready, not just the index's existence

Annotated Evidence #

SELECT c.relname AS index_name,
       i.indisvalid, i.indisready,
       pg_size_pretty(pg_relation_size(c.oid)) AS size,
       t.relname AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
WHERE NOT i.indisvalid OR NOT i.indisready;
        index_name          | indisvalid | indisready |  size  | table_name
----------------------------+------------+------------+--------+------------
 events_account_ts_idx      | f          | t          | 40 GB  | events
 orders_status_idx_ccnew    | f          | t          | 612 MB | orders
-- both are maintained by every write and used by nothing

Effect on writes:

BEGIN;
EXPLAIN (ANALYZE, WAL) INSERT INTO events (account_id, occurred_at, payload)
SELECT 1, now(), '{}' FROM generate_series(1, 10000);
ROLLBACK;
Insert on events  (actual time=412.8..412.8 rows=0 loops=1)
  WAL: records=41204 bytes=8204110
-- 4.1 WAL records per row: heap + 3 valid indexes + 1 invalid index

And on reads:

EXPLAIN SELECT * FROM events WHERE account_id = 42 AND occurred_at >= '2026-09-01';
Seq Scan on events  (cost=…)
  Filter: ((account_id = 42) AND (occurred_at >= '2026-09-01'::date))
-- the invalid index exists and matches the predicate, but the planner cannot use it
Recognising an invalid index Three items are annotated. A pg_index row with indisvalid false and indisready true is maintained by writes but unusable by queries. A sequential scan despite a matching index confirms the planner ignores it. An index name ending in ccnew comes from a failed REINDEX CONCURRENTLY. indisvalid = f, indisready = t written to, never read Seq Scan despite a matching index planner ignores invalid indexes orders_status_idx_ccnew leftover from a failed REINDEX psql shows these as INVALID in \d output

Step-by-Step Resolution #

  1. Audit regularly. Run the catalog query above as part of routine checks; invalid indexes are easy to miss because nothing fails.

  2. Decide whether the index is still wanted. If it was an experiment, drop it. If it is needed, drop and rebuild.

  3. Drop it concurrently so writes are not blocked:

    DROP INDEX CONCURRENTLY events_account_ts_idx;

    For a _ccnew leftover from REINDEX CONCURRENTLY, dropping it is the correct cleanup; the original index is still in place and valid.

  4. Find out why the build failed before retrying: check the server log around the failure time for deadlocks, timeouts, or unique violations. Rebuilding into the same conditions usually fails the same way.

  5. Rebuild with protection:

    SET lock_timeout = '5s';
SET statement_timeout = 0;          -- do not let a long build be killed by a global timeout
CREATE INDEX CONCURRENTLY events_account_ts_idx ON events (account_id, occurred_at);

  6. Verify validity after the build:

    SELECT indisvalid, indisready FROM pg_index WHERE indexrelid = 'events_account_ts_idx'::regclass;
Where a failed concurrent build stops A timeline of the build phases. The catalog entry is created as not valid. The build waits for existing transactions, scans and builds, then validates against concurrent changes, and finally marks the index valid. A failure at any point before the last step leaves the entry behind, maintained by writes but never used. catalog entry not valid wait for writers indisready set build from snapshot writes maintained validate second wait mark valid usable cancelling anywhere before the last step leaves an invalid index

Before and After #

-- BEFORE: invalid 40 GB index maintained by writes
INSERT … WAL: records=41204 bytes=8204110       (4.1 records per row)
SELECT … Seq Scan on events                     (index unusable)

-- AFTER: DROP INDEX CONCURRENTLY, then rebuild successfully
INSERT … WAL: records=31204 bytes=6104220       (3.1 records per row while rebuilding)
SELECT … Index Scan using events_account_ts_idx (valid index)

Why concurrent builds fail #

The two waiting phases are where most failures happen. Each waits for all transactions that started before the phase to finish, because they might not see the new index. A single long-running transaction — an open BEGIN in a psql window, a stuck application connection, a long analytics query on a replica with hot_standby_feedback — blocks the phase indefinitely. Combined with a statement_timeout set globally by the application role, the build is eventually cancelled, leaving the invalid index.

Unique index builds add a second failure mode: validation fails if duplicate values were inserted while the build ran. That is not a bug but a race with application behaviour, and it means the uniqueness assumption was wrong at that moment.

Before a long concurrent build, check for transactions older than a few minutes and either wait or ask their owners to finish, and confirm the session’s statement_timeout is zero. On partitioned tables, remember that indexes are built per partition, so a single stuck transaction can fail one partition’s build and leave that partition’s index invalid while the others succeed — which shows up as a query that is fast for most partitions and slow for one.

Making cleanup routine #

Because nothing alerts on an invalid index, the practical defence is a scheduled check. A weekly job that runs the catalog query and reports any row is enough; teams that deploy indexes through migrations often add the same check to their post-deploy verification, so a build cancelled by a deployment timeout is noticed the same day rather than a month later. The report should include the index size and the table’s write rate, because those two numbers decide whether the cleanup is urgent or routine: an invalid index on a static lookup table costs storage only, while one on the busiest ingest table costs a share of every insert.

It is also worth recording, in the migration that creates a large index, whether the build is expected to be concurrent and how long it took in staging. When a later production run fails, that note is what tells the next person whether five hours was normal or a symptom.

Common Pitfalls #

Assuming a failed command left nothing behind. The catalog entry survives. Diagnostic signal: INVALID in \d. Fix: audit pg_index.

Re-running the build without dropping. The name is taken. Diagnostic signal: “relation already exists”. Fix: DROP INDEX CONCURRENTLY first.

Dropping _ccold instead of _ccnew. Names are easy to confuse after a failed reindex. Diagnostic signal: dropping the valid index by mistake. Fix: check indisvalid before dropping, and drop only invalid ones.

Global statement_timeout killing builds. Long builds exceed it. Diagnostic signal: builds cancelled after a fixed duration. Fix: SET statement_timeout = 0 for the maintenance session.

Frequently Asked Questions #

What is an invalid index in PostgreSQL? #

An index whose pg_index.indisvalid is false, left behind when CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY failed or was cancelled. The planner never uses it, but writes may still maintain it, so it costs without benefit.

How do I find invalid indexes? #

Query pg_index for rows with indisvalid false or indisready false, joining pg_class for names and sizes. psql also marks them INVALID in the table description.

Can I just rebuild an invalid index? #

Drop it first with DROP INDEX CONCURRENTLY, then create it again. The failed build’s catalog entry occupies the name and cannot be repaired in place.

Up: Index Maintenance and Bloat