CREATE TABLE AS copying data inside the migration

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

What it catches

CREATE TABLE AS runs its query to completion inside the migration's transaction: the copy's duration scales with the source data, the transaction stays open throughout (holding back vacuum and locks), and the copied rows are frozen at migration time — usually a backfill pretending to be schema.

Fires on

CREATE TABLE orders_archive AS SELECT * FROM orders WHERE closed_at < '2024-01-01';

Do this instead

Create the empty table in the migration — schema is the migration's job — and copy the rows from a batched job afterwards. The migration transaction stays short, and the copy commits incrementally instead of holding one transaction open for the duration.

CREATE TABLE orders_archive (
  id bigint PRIMARY KEY,
  closed_at timestamptz,
  total numeric(12,2)
);
-- copy rows from a batched job afterwards:
--    INSERT INTO orders_archive SELECT ... FROM orders
--    WHERE closed_at < '2024-01-01' ... LIMIT per batch, commit each

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

← BV031 BV033 →