Add Index Concurrently Recover From Failed Cic
A CREATE INDEX CONCURRENTLY or CREATE UNIQUE INDEX CONCURRENTLY command ends with an ERROR rather than CREATE INDEX -- for a unique build the message is...
In 10 seconds
CREATE INDEX CONCURRENTLY (and its unique cousin CREATE UNIQUE INDEX CONCURRENTLY) builds an index without taking a long write lock, which is why it is the standard way to add an index on a busy production table. The catch is in its failure mode: if the build fails -- most commonly because a UNIQUE build hits data that already contains duplicates -- the statement errors out but DOES NOT clean up after itself. It leaves an INVALID index behind in the catalog. That invalid index is the trap: it shows up in \d and in pg_indexes as if the index exists, but the planner refuses to use it, so queries keep doing sequential scans while everyone assumes the index is working. Worse, you cannot simply re-run the same CIC -- the leftover name is already taken, and the duplicate data is still there. In the lab we reproduce exactly this on an orders table: 20000 distinct order_ref values plus two duplicated ones (REF-000001 and REF-000002 each appear twice). Running CREATE UNIQUE INDEX CONCURRENTLY uq_orders_ref fails with 'could not create unique index ... Key (order_ref)=(REF-000002) is duplicated', and leaves uq_orders_ref behind with indisvalid = f and indisready = f. The job: recognise the invalid index for what it is (a failed-CIC leftover, not a working index), find and remove the duplicate rows that caused the failure, DROP the invalid index, and retry the build cleanly -- then prevent the whole class of incident with a duplicate pre-flight that runs before any unique CIC. The failure, the invalid catalog state, and the recovery are all real; nothing is simulated.
Why This Happens
CREATE INDEX CONCURRENTLY trades a long lock for a multi-pass build: it adds a catalog entry first (so concurrent writes start maintaining the new index), then scans the table to populate it, then validates. To make that safe it must be able to abort partway through, and the design choice is that a failed CIC leaves its catalog entry in place but marked invalid (indisvalid = false) rather than rolling the whole thing back -- partly because CIC runs outside a single transaction and cannot be cleanly undone, and partly so an operator can inspect what happened. For a UNIQUE build, the most common abort is duplicate data: PostgreSQL only discovers the duplicates while building the unique index, so the build gets far enough to error with the specific clashing key and then marks the index invalid.
The planner deliberately skips any index with indisvalid = false, which is exactly why your queries keep sequential-scanning even though \d shows the index -- it is present but unusable. The recovery has to address both halves: the invalid index must be DROPped (you cannot reuse the name otherwise), and the duplicate rows that caused the failure must be removed (or the retry fails the same way). The lab removes duplicates deterministically with row_number OVER (PARTITION BY order_ref ORDER BY id), keeping the lowest id per key and deleting the rest (DELETE 2), then retries the CIC, which now completes with indisvalid = true.
The prevention closes the loop: a pre-flight query that groups by the target column and reports any key with COUNT(*) > 1 tells you in advance that the unique CIC will fail, so you fix the data first and the build succeeds on the first attempt -- which the lab demonstrates on a clean shipments table. Every step is the genuine behaviour and its real remedy; the only thing arranged is the two duplicate rows that make the failure deterministic. Every step is the genuine behaviour and its real remedy; the only thing arranged is the two duplicate rows that make the failure deterministic.
Diagnose
Free Tier -- Basic Approach
The on-call's first read -- is there an invalid index, and is it actually being used? Two queries answer it: one over the catalog, one EXPLAIN.
== FREE: find every INVALID index left behind by a failed CREATE INDEX CONCURRENTLY ==
schema | table | index | indisvalid | index_size
--------+--------+---------------+------------+------------
public | orders | uq_orders_ref | f | 0 bytes
(1 row)
-- the planner IGNORES an invalid index, so a lookup still does a Seq Scan even though the index exists:
QUERY PLAN
--------------------------------------------
Seq Scan on orders
Filter: (order_ref = 'REF-005000'::text)
(2 rows)
Scanning pg_index for indisvalid = false surfaces the leftover immediately: public.orders.uq_orders_ref, indisvalid = f, index_size 0 bytes (the build died in its first phase, before any rows were written). The decisive confirmation is the plan: an EXPLAIN of a lookup on order_ref shows 'Seq Scan on orders / Filter: (order_ref = 'REF-005000′)' -- the planner is ignoring the index entirely, which is exactly why queries stayed slow even though \d shows the index. That is the free diagnosis: this is a failed-CIC invalid index, not a working index and not a missing one. The premium step names the precise rows you must fix before you can rebuild it.
Fix
Recover in three deliberate steps -- drop the invalid leftover, remove the duplicates that caused the failure, then retry the build cleanly:
== FIX 1: drop the invalid index left behind by the failed CIC ==
DROP INDEX
== FIX 2: remove the duplicate rows (keep the lowest id per order_ref) ==
DELETE 2
-- duplicates remaining (should be none):
dup_groups_left=0
== FIX 3: retry CREATE UNIQUE INDEX CONCURRENTLY -- now it completes ==
CREATE INDEX
index | indisvalid | indisready
---------------+------------+------------
uq_orders_ref | t | t
(1 row)
Step 1 drops the invalid index (DROP INDEX) -- mandatory, because the leftover already owns the name and the retry would otherwise fail with 'already exists'. Step 2 removes the duplicate rows with a window-function delete that keeps the lowest id per key (row_number OVER (PARTITION BY order_ref ORDER BY id), delete where rn > 1): the capture shows DELETE 2 and dup_groups_left = 0, so the uniqueness violation is genuinely resolved rather than masked. Step 3 retries CREATE UNIQUE INDEX CONCURRENTLY, which now completes (CREATE INDEX) with the catalog showing uq_orders_ref indisvalid = t and indisready = t. This is the real production remedy -- no REINDEX guesswork, no pg_resetwal, no catalog surgery -- and the dedup rule (keep lowest id) is a safe default you can adapt to your own 'which row to keep' logic.
Verify
Three-part proof that the index is genuinely fixed: no invalid indexes remain, uniqueness is enforced, and the planner now uses it:
== VERIFY: no invalid indexes remain in the database ==
invalid_indexes_remaining=0
== VERIFY: the unique index now ENFORCES uniqueness -- a duplicate insert is rejected ==
ERROR: duplicate key value violates unique constraint "uq_orders_ref"
DETAIL: Key (order_ref)=(REF-000500) already exists.
== VERIFY: the planner now USES the valid index (Index/Index Only Scan, not Seq Scan) ==
QUERY PLAN
------------------------------------------------
Index Scan using uq_orders_ref on orders
Index Cond: (order_ref = 'REF-000500'::text)
(2 rows)
First, the audit comes back clean: invalid_indexes_remaining = 0, so the failed-CIC leftover is gone and nothing else is invalid. Second, the index now ENFORCES uniqueness -- attempting to insert a duplicate order_ref is rejected with 'duplicate key value violates unique constraint "uq_orders_ref" / Key (order_ref)=(REF-000500) already exists', which an invalid index could never do. Third, the planner USES it: an EXPLAIN of the same lookup that previously did a Seq Scan now shows 'Index Scan using uq_orders_ref on orders / Index Cond: (order_ref = 'REF-000500′)'. Together these confirm the recovery is real on all three axes -- catalog validity, constraint enforcement, and query acceleration -- not merely a rebuilt-but-unused index.
⏪ Rollback
Nothing here touches the shared test environment and there is no state to roll back: the whole reproduction runs in ephemeral, self-removing containers (a scratch database, a scratch database, a scratch database*) on throwaway named volumes, while a PostgreSQL instance lab is never started, stopped, or reconfigured -- the closing RESTORE check confirms PostgreSQL still reports server_version 16.14. The fix is forward-only and safe on a real system: DROP INDEX removes only the invalid, planner-ignored leftover (dropping an already-unusable index costs nothing in query terms), and the deduplication deletes only surplus duplicate rows, keeping the lowest id per key -- it never removes a distinct value. The retried CREATE UNIQUE INDEX CONCURRENTLY is itself non-blocking. If you wanted to undo the new index you would simply DROP INDEX CONCURRENTLY uq_orders_ref; there is no pg_resetwal, no catalog hand-editing, and no destructive table rewrite anywhere in the recipe. The one real-world caution is to be sure the duplicate rows you delete are genuinely redundant (the lab's keep-lowest-id rule is a safe default, but a real table may need an application-aware choice of which row to keep).
📦 Version Matrix (PG 14--18)
The failed-CIC-leaves-an-invalid-index behaviour and its recovery are identical on every supported major version. Each row is an independent, self-contained run: boot a fresh cluster, seed duplicates, fail a unique CIC, then DROP + dedup + retry and report the index validity before and after:
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
My CREATE INDEX CONCURRENTLY failed -- why does \d still show the index?
Because a failed CONCURRENTLY build does not roll back its catalog entry; it leaves the index in place marked invalid (pg_index.indisvalid = false). \d and pg_indexes list it, but the planner refuses to use it, so your queries keep doing sequential scans. In the lab, after the unique build fails on duplicate data, uq_orders_ref is present with indisvalid = f and indisready = f, and an EXPLAIN of a lookup shows a Seq Scan, not an Index Scan. Treat any invalid index as an unfinished build that still needs cleaning up, not a working index.
Why did my unique build fail with 'Key (...) is duplicated'?
Because the column you are making unique already contains duplicate values, and PostgreSQL only discovers them while building the unique index. The build gets far enough to identify a clashing key, then errors and marks the index invalid. In the lab the orders table has REF-000001 and REF-000002 each appearing twice, so CREATE UNIQUE INDEX CONCURRENTLY uq_orders_ref fails with 'Key (order_ref)=(REF-000002) is duplicated.' The fix is to remove the duplicate rows before the index can be built -- the index error is a symptom; the duplicate data is the cause.
How do I recover from the invalid index left behind?
Three steps. First DROP the invalid index (DROP INDEX uq_orders_ref, or DROP INDEX CONCURRENTLY to avoid locks) -- you must do this because the leftover already owns the name, so you cannot re-run the same CIC otherwise. Second, remove the duplicate rows that caused the failure; the lab keeps the lowest id per key with row_number() OVER (PARTITION BY order_ref ORDER BY id) and deletes the rest (DELETE 2), leaving zero duplicate groups. Third, retry CREATE UNIQUE INDEX CONCURRENTLY -- it now completes (CREATE INDEX) with indisvalid = t and indisready = t. The verify step confirms a duplicate insert is then rejected and the planner uses an Index Scan.
Can I just re-run the same CREATE INDEX CONCURRENTLY?
No -- not until you drop the invalid leftover. The failed build left an index of that exact name in the catalog, so re-running the identical statement fails with 'relation "uq_orders_ref" already exists'. And even after you drop it, re-running without fixing the duplicate data just reproduces the original failure. The correct order is: DROP the invalid index, remove the duplicates, then retry the CIC. The lab follows exactly that sequence and the retry succeeds on the first attempt once the data is clean.
Is the invalid index doing any harm while it sits there?
It is not speeding anything up -- the planner never chooses an invalid index, so reads still sequential-scan (EXPLAIN shows Seq Scan, never Index Scan). In the lab the leftover is 0 bytes because the unique build died in its first phase, before populating the index, and it is reported with used_by_planner = f. The real harm is operational: it masquerades as a working index in \d, it blocks re-running the build under the same name, and it signals that the intended index is NOT actually enforcing or accelerating anything. Drop it and rebuild cleanly so the catalog reflects reality.
How do I stop this from happening again?
Pre-flight for duplicates before every unique CONCURRENTLY build: run GROUP BY <column> HAVING COUNT(*) > 1 first, and if it returns any rows, fix the data before you run the CIC. In the lab the prevention step pre-checks a shipments table, finds duplicate_groups_found = 0, and the unique CIC therefore succeeds on the FIRST attempt with indisvalid = t. Back it with a scheduled audit that scans pg_index for indisvalid = false so any failed CIC anywhere in the database is caught early (invalid_indexes_in_db = 0 when clean) instead of being discovered later as an unexplained slow query.