INSERT ... SELECT backfill inside the migration

Note — this works and blocks nothing, but a performance regression is likely.

What it catches

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.

Fires on

INSERT INTO orders_archive SELECT * FROM orders WHERE closed_at < '2024-01-01';

Do this instead

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)

Catch this before it ships

This rule runs in the hosted service on Startup and above — add --remote with a team token, or use the GitHub Action: npx bolvrk check migration.sql

Browse all 69 rules

← BV045 BV047 →