Before a schema change runs, StitchDB tells you which tables it locks, for how long given their size, and offers a version that keeps your app running.
Some schema changes lock a table while they work. While it is locked, your app's queries on that table wait, and on a large table that can be minutes. The SQL Editor, Database → Migrations and stitchdb db push check each statement first, with the real Postgres parser and your tables' current sizes, and warn you before anything runs.
#What is checked
| Change | Lock while it runs | Safer version offered |
|---|---|---|
create index | Blocks writes while the index is built | create index concurrently |
add foreign key, add check | Blocks writes (a check: reads and writes) while every row is checked | … not valid, then validate constraint, which does not block |
alter column … set not null | Blocks reads and writes while every row is checked | A validated check (col is not null) first, so set not null skips the scan |
add unique, add primary key | Blocks reads and writes while the index is built | create unique index concurrently, then add constraint … using index |
alter column … type | Blocks reads and writes while the table is rewritten (not for varchar → text or a longer varchar) | A new column, filled in batches |
add column … default gen_random_uuid() (any per-row default, serial, identity) | Blocks reads and writes while the table is rewritten (a constant default or now() does not) | Add the column, set the default, fill old rows in batches |
drop index, reindex, refresh materialized view | Blocks reads and/or writes | The concurrently form |
vacuum full, cluster | Blocks reads and writes while the table is rewritten | A plain vacuum |
update / delete of 100,000 rows or more | Each changed row stays locked until the transaction ends | A batch loop of 10,000 rows |
Changes are rated Check first (about a second or more of blocking, or rows deleted) or Blocks your app (10 seconds or more, a statement that would fail on existing rows, or an open transaction it would queue behind). Tables created earlier in the same script are empty, so changes to them are never flagged. Estimates come from the table and index sizes and are approximate.
#Waiting behind an open transaction
A change that needs a strong lock has to wait for every open transaction on that table, and while it waits, every new query on the table waits behind it. A forgotten begin in a SQL client can stop your app this way even for an instant change such as add column. The check lists such transactions (open for more than 5 seconds), and every safer version starts with:
set lock_timeout = '5s';With it, the change gives up after 5 seconds instead of queueing, and you can run it again a moment later. To find and end the transaction that holds the lock:
select pid, now() - xact_start as open_for, state, query from pg_stat_activity
where xact_start < now() - interval '1 minute' order by xact_start;
select pg_terminate_backend(<pid>);#In the SQL Editor and Migrations
When you run a schema change or a large update/delete, a dialog lists the flagged statements, why, and their safer form. Use safer version puts the whole safer script in the editor (only the selected part, if you ran a selection) so you can read it before you run it; Run anyway runs what you wrote. Read-only runs are not checked. Scripts with concurrently run one statement at a time outside a transaction, which concurrently needs; if one fails, the statements before it stay applied.
#With the CLI
stitchdb db push prints the same warnings and the safer version for each pending migration before it asks to apply them. When a migration can block your app, the question defaults to no; --yes (CI) still applies. stitchdb db push --dry-run shows the warnings without applying anything. The checks are also in the management API:
POST /v1/projects/<ref>/migrations/check {"migrations": [{"version": "…", "name": "…", "sql": "…"}]}
POST /v1/projects/<ref>/database/query/check {"query": "create index on orders (email)"}Checking only reads the catalog (table sizes, columns, locks) and asks the planner how many rows an update or delete would change; nothing is run.
#Batches for large updates
-- run until it reports 0 rows; each run is a short transaction
update public.orders set status = 'archived'
where ctid in (select ctid from public.orders where created_at < '2025-01-01' and status <> 'archived' limit 10000);