Cookbook recipe

Add an index concurrently and recover from a failed CIC

Applies to PostgreSQL 13–17 Last reviewed May 2026 Grounded in source

What This Is

A production experience capsule based on real incident patterns.

Why It Matters

Builds production judgment under pressure.

Next Step

Run the decision point → · Enter full simulator →

Estimated investigation4 min

Scenario

You built an index with CREATE INDEX CONCURRENTLY to avoid locking writes, but it failed and left an INVALID index behind. Diagnose it Find invalid indexes: SELECT c.relname AS index, i.indisvalid FROM pg_index i JOIN pg_class…

Investigation Path

You built an index with CREATE INDEX CONCURRENTLY to avoid locking writes, but it failed and left an INVALID index behind.

Diagnose it

Find invalid indexes:

SELECT c.relname AS index, i.indisvalid
FROM pg_index i JOIN pg_class c ON c.oid=i.indexrelid
WHERE NOT i.indisvalid;

Why it happens

CREATE INDEX CONCURRENTLY builds without an exclusive lock but in two passes. If it fails (deadlock, cancel, conflict) it leaves an invalid index that still costs write overhead but is not used for reads.

This is a Pro lesson

Get every Learning Pathway and cookbook recipe — grounded in PostgreSQL source code, with diagnostics, fixes, and prevention for each topic.

Continue this lesson to learn:

  • How to fix it
  • Prevent it next time
  • Related & next steps
  • All 36 Learning Pathway lessons
  • 170+ cookbook recipes
  • Source-grounded diagnostics & fixes
Unlock everything — $24.99/month $199/year — save 35%

Secure checkout Cancel anytime Source-grounded

Career Impact

This scenario builds production judgment and operational confidence under pressure.

Open Career Dashboard →

Keep going

Related & next steps

More like this

Partitioning: How Declarative Partitioning Prunes Work Checkpoint frequency too high — I/O spike pattern Covering indexes with INCLUDE Choosing a Managed PostgreSQL: A Decision Framework and Cost Model Anatomy of a WAL Record: What PostgreSQL Writes Before It Touches Your Data

Concepts on this page

Transaction Deadlock Index Lock
Browse the cookbook Step-by-step recipes Follow a pathway Learn it in order Open the glossary Every concept, plainly
Was this helpful?

← All cookbook recipes