Should a backfill run inside the schema migration?
INSERT ... SELECT backfill inside the migration
Note: this works and blocks nothing, but a performance regression is likely.
What happens
An unbounded INSERT ... SELECT runs to completion inside the migration's transaction: its duration scales with the source data, the transaction stays open the whole time (holding locks and holding back vacuum), and a failure rolls back everything, a backfill wearing a migration's clothes.
Why it is dangerous on a populated table
Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.
Fires on
INSERT INTO orders_archive SELECT * FROM orders WHERE closed_at < '2024-01-01';The safe pattern
Keep the migration to schema and move the rows from a batched operational job afterwards: each batch commits on its own, the copy can pause and resume, and no migration transaction is held open for the duration. Seed rows via VALUES stay fine, this is about copies that scale with live data.
-- Move the rows with a batched job, not the migration:
-- INSERT INTO orders_archive
-- SELECT * FROM orders WHERE closed_at < '2024-01-01'
-- ORDER BY id LIMIT 10000 OFFSET ...; -- or keyset-paginate
-- (commit each batch; repeat until no rows remain)Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (1)
INSERT INTO orders_archive SELECT * FROM orders WHERE closed_at < '2024-01-01';Stays silent (1)
-- Seed rows via VALUES are normal migration content.
INSERT INTO plans (id, name) VALUES (1, 'free'), (2, 'startup'), (3, 'scale');How to check locally
This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:
npx bolvrk check migration.sql --remote