agent's suggested index fixed the SELECT but the migration locked the table for 40 minutes during the ALTER

Index migration locked the table for 40 minutes

TL;DR: Build the index with CREATE INDEX CONCURRENTLY so it never takes the write-blocking lock, and never run a plain ALTER on a large table in production. The SELECT got fixed, but the deploy method took the table down. Split schema changes into lock-safe steps.

agent's suggested index fixed the SELECT but the migration locked the table for 40 minutes during the ALTER

Steps

1. Check whether the migration is still holding a lock, and what it is blocking:

   SELECT pid, query, state, wait_event_type, wait_event
   FROM pg_stat_activity
   WHERE wait_event_type = 'Lock' AND query ILIKE '%your_table%';

Expected: the migration pid shows a lock wait, with application queries queued behind it.

1. If the migration is stuck and traffic is piling up, cancel it - a cancelled plain CREATE INDEX leaves no usable index and releases the lock:

   SELECT pg_cancel_backend(stuck_pid);

Expected: the migration rolls back, the lock releases, and queued queries drain. The table is unchanged.

1. Re-run the build the safe way, in its own session (never inside a transaction):

   CREATE INDEX CONCURRENTLY idx_your_table_col ON your_table (col);

Expected: the command takes longer wall-clock (two table scans) but writes keep flowing - no blocking lock.

1. Verify the concurrent build actually finished valid. A failed CONCURRENTLY leaves an INVALID index that the planner ignores:

   SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'your_table';

Expected: your index is listed. Also confirm no INVALID entries with: SELECT * FROM pg_index WHERE indisvalid = false; - drop and rebuild any invalid ones.

1. Confirm the SELECT still uses it:

   EXPLAIN SELECT ... ; -- the original slow query

Expected: Index Scan or Index Only Scan on the new index, with timing close to the agent's benchmark.

1. Make the agent emit CONCURRENTLY for every index recommendation on tables over your size threshold (e.g. 1M rows), and forbid DDL inside multi-statement migrations on hot tables.

Expected: future recommendations ship with the safe syntax by default.

Use this when

• A migration adding an index locked the table and blocked writes.
• You need to add the recommended index without downtime.
• A previous CREATE INDEX CONCURRENTLY failed and left an INVALID index.
• You want the rule that stops this from recurring.

Not for this skill when

• The table is small (locks held for milliseconds) - plain CREATE INDEX is fine there.
• The lock came from something else (long-running transaction holding ACCESS EXCLUSIVE from an earlier ALTER) - find the real lock holder first.
• You are on a database without concurrent index builds (some managed/MySQL variants) - the fix there is a replica-promotion or pt-online-schema-change style tool instead.

Variant phrasings

• "create index locked table postgres production"
• "ALTER TABLE ADD INDEX blocked writes for 40 minutes"
• "how to add index without locking table"
• "CREATE INDEX CONCURRENTLY left invalid index"

Why it happens

Plain CREATE INDEX takes a SHARE lock that blocks all writes for the whole build, and on a big table the build takes tens of minutes. Agents test the index on a staging copy where the lock is invisible (no concurrent traffic), so the recommendation never mentions the deploy hazard. The SELECT improvement is real; the delivery method is what hurt.

Edge cases

• CONCURRENTLY is slower and uses more I/O; on an already-saturated primary it can still hurt - build on a replica or in a quiet window.
• It cannot run inside a transaction block, so migration frameworks need a separate non-transactional step.
• Two concurrent CONCURRENTLY builds on the same table can deadlock; serialize them.
• If the build fails midway (out of disk, deadlock with vacuum), the INVALID index still occupies space - drop it before rebuilding.

Provenance

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