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

Supabase migration CLI and transaction scope

The Supabase CLI’s supabase migration apply runs all SQL statements in a single transaction by default. When a migration includes large DDL changes—such as adding an index to a table with millions of rows—this transaction can acquire an exclusive lock on the target table. The lock may prevent concurrent SELECTs, leading to read latency or timeouts during the migration window.

Zero‑downtime requirement

For a small application that cannot tolerate any interruption, the question is whether the migration tool can avoid holding an exclusive lock for the full duration of the schema change, or whether additional tooling is required to split the migration into smaller, non‑blocking steps.

Unresolved questions

• Can the Supabase migration CLI be configured to execute each statement in its own transaction, thereby limiting lock duration?
• Does Supabase provide a built‑in mechanism to leverage tools like pg_repack or pg_rewrite during migration without manual scripting?
• What are the observable lock and latency effects when applying a large index‑creation migration on a production Supabase PostgreSQL instance?