A PostgreSQL INVALID Index Can Add Write Overhead

A PostgreSQL index marked INVALID is unavailable to normal query planning, yet it can still add maintenance work for inserts and index-relevant updates. That contradiction appears after a concurrent build is cancelled, deadlocks, loses its backend, or fails while checking uniqueness. The object remains in the catalog, can occupy substantial disk, and may add write-path overhead without making reads faster; HOT updates and deletes do not justify treating that cost as universal across every write.

Do not begin with DROP INDEX. First determine whether a build is still active, preserve the exact definition, read the three catalog flags, and identify any constraint dependency. Invalid is a state, not a complete diagnosis. Recovery is complete only when the intended index is valid and usable, no abandoned copy remains, and representative writes no longer pay for an object the planner ignores.

INVALID changes two contracts at once

PostgreSQL’s current CREATE INDEX documentation explains why concurrent creation has unusual failure residue. Instead of one locked table scan, PostgreSQL creates an invalid catalog entry, performs two scans in separate transactions, waits for transactions and older snapshots at several boundaries, and marks the index valid only at the end.

If the command fails, indisvalid=false prevents normal queries from trusting incomplete contents. A separate flag answers the write question. The pg_index catalog reference defines indisready as whether the index is ready for inserts. When an abandoned index has indisvalid=false and indisready=true, PostgreSQL will not use it to answer a query, but data changes can continue maintaining it.

That write cost is not automatically the whole incident. An invalid unique index can still enforce uniqueness if the concurrent build reached its second scan before failing. PostgreSQL documents that other transactions may see uniqueness violations before the index becomes valid, and enforcement can continue after a late failure. Never assume “ignored by reads” means “inert.”

Freeze the catalog truth before removing anything

Capture the object in the affected database with a UTC timestamp. This read-only query records table ownership, size, state, uniqueness, and the reconstructed definition:

SELECT clock_timestamp() AS captured_at,
       n.nspname AS schema_name,
       t.relname AS table_name,
       i.relname AS index_name,
       x.indisvalid,
       x.indisready,
       x.indislive,
       x.indisunique,
       pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
       pg_get_indexdef(i.oid) AS index_definition
FROM pg_index AS x
JOIN pg_class AS i ON i.oid = x.indexrelid
JOIN pg_class AS t ON t.oid = x.indrelid
JOIN pg_namespace AS n ON n.oid = i.relnamespace
WHERE NOT x.indisvalid
   OR NOT x.indisready
   OR NOT x.indislive
ORDER BY pg_relation_size(i.oid) DESC;

Healthy ordinary indexes normally show all three state flags as true. indisvalid controls query use, indisready controls maintenance by new writes, and indislive tells whether the object remains alive. Transitional combinations can appear during active concurrent work, so the result is evidence to correlate—not a bulk-cleanup list.

Next, preserve dependencies before choosing a drop:

SELECT c.conname,
       c.contype,
       c.conrelid::regclass AS constrained_table,
       c.condeferrable,
       c.condeferred
FROM pg_constraint AS c
WHERE c.conindid = 'public.example_index'::regclass;

A row here means the index supports a constraint. DROP INDEX CONCURRENTLY cannot casually remove a constraint-owned index, and changing the constraint broadens the application contract. Stop and plan through the table/constraint owner rather than converting a catalog cleanup into an unreviewed integrity change.

The failed phase determines what remains

An index is entered as invalid at the beginning of a healthy concurrent build. Finding that flag while the command is running is therefore expected. Before treating it as abandoned, query current progress:

SELECT p.pid,
       p.datname,
       p.relid::regclass AS table_name,
       p.index_relid::regclass AS index_name,
       p.command,
       p.phase,
       p.lockers_total,
       p.lockers_done,
       p.blocks_total,
       p.blocks_done,
       p.tuples_total,
       p.tuples_done
FROM pg_stat_progress_create_index AS p
ORDER BY p.pid;

The official progress view reports create and reindex phases, including waits for writers or old snapshots. Progress counters are phase-specific; a zero tuple total is not universal proof of a stall. Compare repeated samples with pg_stat_activity, database logs, deployment timing, and the migration tool that issued the DDL.

A build waiting on an old transaction

Concurrent creation must wait at several transaction boundaries. Preserve old sessions and their owners rather than killing the oldest PID automatically:

SELECT pid,
       usename,
       application_name,
       state,
       xact_start,
       wait_event_type,
       wait_event,
       left(query, 160) AS query_sample
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST, pid;

An idle in transaction session may be a forgotten client, or it may own a legitimate workflow that cannot be discarded. Use the same ownership discipline applied when PostgreSQL maintenance is blocked by old transaction state: identify the application and recovery semantics before cancelling anything.

A backend died or the host contained a fault

Database logs may show cancellation, deadlock, uniqueness failure, lost connection, or a server process termination. If the kernel or a cgroup killed the backend, follow Linux OOM evidence before retrying the same memory and I/O workload. When the filesystem remounted read-only or logged lower-device errors, switch to ext4 fail-closed recovery instead of treating the index as the root cause.

A unique scan found real duplicates

Rebuilding cannot make conflicting rows disappear safely. Preserve the exact predicate, expressions, collation, included columns, and NULLS [NOT] DISTINCT behavior from pg_get_indexdef. Identify duplicate business keys under application ownership, decide which row is authoritative, and repair data through an approved migration. Dropping the invalid unique index first can remove continuing enforcement and admit more conflicts.

Choose recovery by dependency and lock budget

PostgreSQL documents two normal paths for an invalid index left by failed concurrent creation: drop it and repeat CREATE INDEX CONCURRENTLY, or rebuild it with REINDEX INDEX CONCURRENTLY. The right choice comes from definition ownership, dependency, version support, available disk/WAL runway, and the maintenance contract.

Drop and recreate when the migration owns the definition

For a standalone index with a preserved, reviewed definition, drop the abandoned object concurrently and let the migration create it again. Both commands run outside a transaction block:

DROP INDEX CONCURRENTLY public.example_index;

CREATE INDEX CONCURRENTLY example_index
ON public.example_table (example_column);

The example is intentionally generic. Use the captured definition rather than copying these columns. DROP INDEX CONCURRENTLY has restrictions, briefly needs catalog locks, and cannot use CASCADE; verify the installed PostgreSQL documentation and application migration behavior before executing it.

Reindex when preserving the logical definition and name workflow is preferable

Current REINDEX documentation states that only REINDEX INDEX can concurrently rebuild an invalid index:

REINDEX INDEX CONCURRENTLY public.example_index;

Concurrent reindex also runs outside a transaction block, performs multi-phase work, requires extra CPU, memory, I/O, and temporary space, and can wait for transactions. Failed attempts may leave _ccnew, _ccnew1, _ccold, or similar artifacts. A _ccnew invalid object is the failed transient replacement and should be dropped before retry; _ccold identifies an old copy whose replacement succeeded but final drop did not. Confirm each object’s definition and state before removal.

Budget the retry instead of repeating the failure

Estimate table and index size, free space in the index tablespace, workload latency, and WAL growth during the change. If retained WAL is already consuming runway, resolve the separate PostgreSQL replication-slot retention path before launching another large scan. On a software RAID host still rebuilding, coordinate production I/O budgeting for md recovery inside the same change window.

Only one concurrent index build can run on a table at a time. Partitioned indexes, exclusion constraints, system catalogs, and older PostgreSQL releases add restrictions. Those boundaries require version-specific planning; a broad REINDEX TABLE is not a shortcut for one understood invalid object.

Acceptance needs read proof and write proof

After the create or reindex command finishes, rerun the catalog and progress queries. Require indisvalid=true, indisready=true, and indislive=true for the intended object, no matching active progress row, and no unexplained _ccnew or _ccold artifact. Compare the live pg_get_indexdef with the approved definition, including predicate, expressions, collations, operator classes, included columns, uniqueness, and null semantics.

Use EXPLAIN (ANALYZE, BUFFERS) only on a safe representative read in a controlled environment or window. Planner non-use is not automatically another index failure: query predicates, statistics, parameterization, table size, and cost estimates may make another plan cheaper. The acceptance question is whether PostgreSQL *can* trust the index and whether the intended workload benefits—not whether one forced demonstration prints its name.

Exercise representative inserts, updates, deletes, and any unique-conflict path through the application contract. Watch database errors, write latency, WAL rate, free space, replication delay, and host I/O through a normal workload window. Rollback means stopping the rollout and restoring the previous application/migration state; it does not mean resurrecting an incomplete index.

Keep the migration receipt after the object turns valid

Store the before/after flags, captured definition, dependency result, failure log, progress samples, chosen command, operator, PostgreSQL version, start/end time, and acceptance evidence with the deployment record. Add monitoring for invalid live indexes, but alert on the state combination and age rather than deleting every match automatically.

The durable lesson is small: query validity, write readiness, and object liveness are separate contracts. Read all three before recovery, then prove both read and write behavior afterward. Retain this incident’s catalog receipt beside the migration that created it.