Supabase Releases Performance Stability

CREATE INDEX on a table without CONCURRENTLY — locks writes during deploy

Part of the Migration & schema safety check · fix arrives as a pull request

What it is

A migration creates an index without the CONCURRENTLY keyword.

Why it matters

A plain CREATE INDEX takes a write lock for the whole build. On a large table that is minutes of failed writes during deploy — an outage caused by a change intended to improve performance.

The two errors on either side of this

Write the migration the obvious way and the deploy locks writes; add CONCURRENTLY inside a migration and it refuses to run. Both error texts, since both get pasted into Google:

-- plain CREATE INDEX on a busy table: every INSERT/UPDATE/DELETE waits
-- (SHARE lock held for the whole build; on a big table, minutes)

-- CONCURRENTLY inside a transaction:
ERROR:  25001: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Why Supabase migrations hit this

Supabase runs each migration file inside a single transaction — that is what makes a failed migration roll back cleanly. But CREATE INDEX CONCURRENTLY manages its own transactions internally, so it categorically cannot run there. That leaves the trap: the version that is safe for your users cannot go in the migration, and the version that fits the migration is not safe for your users.

Fix it manually

For a small or new table, plain CREATE INDEX in the migration is fine — the lock lasts milliseconds. For a table already serving traffic, build the index out-of-band with CONCURRENTLY — the SQL editor or a direct psql session — then keep the migration idempotent so environments converge:

-- run directly (SQL editor / psql), NOT in a migration:
create index concurrently if not exists orders_user_id_idx
  on public.orders (user_id);

-- then in the migration file, for fresh environments:
create index if not exists orders_user_id_idx
  on public.orders (user_id);

If the concurrent build fails, it leaves a broken index

A cancelled or failed CONCURRENTLY build leaves an INVALID index behind — it consumes writes and space but serves no queries. Check and clean up:

select indexrelid::regclass as index
from pg_index where not indisvalid;

drop index concurrently if exists orders_user_id_idx;  -- then rebuild

How lumioguard fixes it

The scan flags CREATE INDEX statements in migration files that target existing tables without CONCURRENTLY. The fix arrives as a pull request restructuring the migration into the pattern above, with a breakage analysis noting the table’s current size and write traffic — you review and merge it.

Run them all on your app

Connect your repo and your live services with read-only scopes. The first scan is free, and nothing changes without your approval.

Start for Free View pricing