Is CREATE TABLE AS safe inside a migration?

CREATE TABLE AS copying data inside the migration

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

What happens

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.

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

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

The safe pattern

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

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)

ctas

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

Stays silent (1)

empty table

CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);

How to check locally

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. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV031 BV033 →