Supabase migration CLI: Does a single-transaction apply block reads during large DDL?

Answer

Yes. By default supabase migration apply wraps the entire migration script in one PostgreSQL transaction. If the migration contains a non‑concurrent DDL operation such as CREATE INDEX on a large table, that statement acquires an exclusive lock on the table for the duration of the transaction. While the lock is held, concurrent SELECT (and other read‑write) statements are blocked, which can cause latency or timeouts during the migration window.

Confirmed facts

• The Supabase CLI does not expose a flag to run each statement in its own transaction; the migration is always executed as BEGIN; … COMMIT; unless you manually split the script.
• A standard CREATE INDEX (without CONCURRENTLY) takes an AccessExclusiveLock on the target table, blocking AccessShareLock held by readers.
• Because the lock persists until the transaction commits, a large index build will block reads for the entire time the index is being created.

Steps to verify or mitigate

1. Observe the lock: While the migration runs, query SELECT * FROM pg_locks WHERE relation = 'your_table'::regclass; to see an ExclusiveLock held by the migration backend PID.
2. Measure read impact: From another connection, run a simple SELECT COUNT(*) FROM your_table; and note that it stalls until the migration transaction ends or times out.
3. Mitigation options:
   • Use CREATE INDEX CONCURRENTLY for index creation. Note that this command cannot run inside a transaction block, so you must split the migration into separate steps or disable the CLI’s single‑transaction mode manually.
   • If you need to keep the migration as a single script, consider applying the index during a maintenance window or using a tool like pg_repack outside of the CLI migration flow.

If you are unsure whether your migration contains only concurrent‑safe DDL, please share the exact SQL statements (or the migration file) so we can confirm whether lock‑free execution is possible.