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

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.

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

Steps

1. Identify the deadlock participants:

   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.

1. Cancel the index build (the autovacuum worker will finish on its own; killing vacuum mid-run just restarts it later):

   SELECT pg_cancel_backend(stuck_build_pid);

Expected: the build aborts and rolls back its partial work; check for an INVALID index left behind.

1. Clean up any INVALID index from the failed build - the planner ignores it but it still occupies space:

   SELECT indexrelid::regclass FROM pg_index WHERE indisvalid = false;
   DROP INDEX CONCURRENTLY invalid_index_name;

Expected: no invalid indexes remain on the table.

1. Pause autovacuum on just this table for the build window, then rebuild:

   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.

1. Verify the index is valid and used:

   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.

1. 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/pst6Y4wx8DsGpsNqWUFZ_uSA