# CREATE INDEX CONCURRENTLY deadlocked against autovacuum

**TL;DR:** Cancel the stuck build, then retry it with autovacuum paused on that table or in a quieter window. CONCURRENTLY avoids the write-blocking lock but still needs brief exclusive moments that collide with autovacuum's own locks. Serialize the two and the build completes cleanly.

```text
agent's index recommendation raced with autovacuum and CREATE INDEX CONCURRENTLY deadlocked
```

## Steps

1. Identify the deadlock participants:
   ```sql
   SELECT pid, query, state, wait_event_type, wait_event
   FROM pg_stat_activity
   WHERE query ILIKE '%CREATE INDEX%' OR query ILIKE '%autovacuum%';
   ```
   Expected: the CREATE INDEX pid waiting on a lock, and an autovacuum worker on the same table holding the conflicting lock.

2. Cancel the index build (the autovacuum worker will finish on its own; killing vacuum mid-run just restarts it later):
   ```sql
   SELECT pg_cancel_backend(stuck_build_pid);
   ```
   Expected: the build aborts and rolls back its partial work; check for an INVALID index left behind.

3. Clean up any INVALID index from the failed build - the planner ignores it but it still occupies space:
   ```sql
   SELECT indexrelid::regclass FROM pg_index WHERE indisvalid = false;
   DROP INDEX CONCURRENTLY invalid_index_name;
   ```
   Expected: no invalid indexes remain on the table.

4. Pause autovacuum on just this table for the build window, then rebuild:
   ```sql
   ALTER TABLE your_table SET (autovacuum_enabled = false);
   CREATE INDEX CONCURRENTLY idx_name ON your_table (col);
   ALTER TABLE your_table SET (autovacuum_enabled = true);
   ```
   Expected: the build completes without contention; re-enable autovacuum immediately after - leaving it off lets bloat accumulate.

5. Verify the index is valid and used:
   ```sql
   SELECT indisvalid FROM pg_index WHERE indexrelid = 'idx_name'::regclass;
   EXPLAIN SELECT ... ; -- the target query
   ```
   Expected: indisvalid is true, and the plan shows the new index.

6. Make the agent's migration template disable autovacuum around CONCURRENTLY builds on large tables, with the re-enable as a mandatory second step.
   Expected: future recommendations include the lock-safe sequence, not just the CREATE statement.

## Use this when

- CREATE INDEX CONCURRENTLY deadlocks, cancels itself, or stalls for far longer than expected.
- Logs show deadlock errors mentioning the index build and autovacuum.
- A previous concurrent build left an INVALID index behind.

## Not for this skill when

- A plain (non-concurrent) CREATE INDEX blocked writes - that is the expected SHARE lock, and the fix is to switch to CONCURRENTLY, not to fight autovacuum.
- The deadlock is between two index builds or two DDL statements - serialize them instead.
- Autovacuum itself is stuck (not just contending) - investigate why vacuum cannot finish first.

## Variant phrasings

- "CREATE INDEX CONCURRENTLY deadlock detected"
- "concurrent index build stuck waiting autovacuum"
- "invalid index after failed CREATE INDEX CONCURRENTLY"
- "how to build index while autovacuum running"

## Why it happens

CONCURRENTLY builds the index in multiple phases with weaker locks, but each phase transition briefly needs a lock that conflicts with autovacuum's ShareUpdateExclusiveLock on the same table. On busy tables autovacuum runs often, so the build's wait window keeps colliding until Postgres declares a deadlock and cancels one side. The agent recommended the right syntax (CONCURRENTLY) but did not account for vacuum contention on a hot table.

## Edge cases

- Disabling autovacuum even briefly on a high-churn table lets dead tuples pile up; keep the window as short as possible and run a manual VACUUM after if needed.
- If the table has long-running transactions, the build waits at phase transitions for those too - check for idle-in-transaction sessions.
- Two CONCURRENTLY builds on the same table at once will deadlock each other; run them sequentially.
- On managed services you may not be able to toggle autovacuum per table - schedule the build in the lowest-traffic window instead.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_6Y4wx8Ds_GpsNqWUFZ_uSA