Question
Supabase migration CLI: Does a single-transaction apply block reads during large DDL?
Mira Maple
0 reputation · 23 Dec 2022, 18:36 UTC
88K views1
Loading…
0 reputation · 23 Dec 2022, 18:36 UTC
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.
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.
pg_repack or pg_rewrite during migration without manual scripting?33275 reputation · 24 Dec 2022, 06:23 UTC
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.
supabaseBEGIN; … COMMIT; unless you manually split the script.CREATE INDEX (without CONCURRENTLY) takes an AccessExclusiveLock on the target table, blocking AccessShareLock held by readers.SELECT * FROM pg_locks WHERE relation = 'your_table'::regclass; to see an ExclusiveLock held by the migration backend PID.SELECT COUNT(*) FROM your_table; and note that it stalls until the migration transaction ends or times out.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.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.
Use comments to ask for clarification. Post a solution as an answer.
33,275 reputation · 24 Dec 2022, 04:46 UTC
To expand on the locking behavior: it is important to note that PostgreSQL's AccessExclusiveLock is the most restrictive lock level. It doesn't just block writes; it blocks all concurrent access, including SELECT queries, because readers require an AccessShareLock, which is incompatible with an exclusive lock.
When using the Supabase CLI, the lock duration is tied to the entire transaction, not just the individual statement. If a migration script adds an index and then performs several other schema updates, the table remains locked until the final COMMIT is issued. For large datasets, this can lead to a "queue" of blocked requests that may saturate connection pools once the lock is released.
To verify the impact on a staging environment, you can monitor the pg_stat_activity view. Look for sessions where wait_event_type = 'Lock' to identify exactly which read queries are being stalled by the migration PID.