Should a migration ever TRUNCATE a table?

TRUNCATE in a migration

Critical: this fails outright or takes production down.

What happens

TRUNCATE deletes every row irreversibly, takes ACCESS EXCLUSIVE on the table, and cascades to referencing tables when asked. A migration that truncates in dev fixtures does the same to production data.

Why it is dangerous on a populated table

On an empty table this finishes before anyone notices. On a populated one the lock is held for the whole operation, every query on the table queues behind it, and the connection pool fills from the back.

Fires on

TRUNCATE orders CASCADE;

The safe pattern

Keep data removal out of schema history. Archive first, then delete in bounded batches from a job that commits per batch, reversible in review, resumable on failure, and never holding ACCESS EXCLUSIVE. If a table is genuinely scratch data, truncate it manually in a maintenance window with a backup taken, not from a migration.

-- From a job, not a migration, bounded batches, commit each:
--    DELETE FROM orders WHERE id IN (
--      SELECT id FROM orders WHERE created_at < '2024-01-01' LIMIT 5000);
-- loop until 0 rows deleted

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)

truncate

TRUNCATE orders CASCADE;

Stays silent (1)

delete with where

DELETE FROM orders WHERE created_at < now() - interval '90 days';

How to check locally

Catch this before it ships

This rule runs locally in the free CLI, or with the full corpus through the hosted service. No install, nothing leaves your machine:

npx bolvrk check migration.sql

Browse all 135 rules

← BV012 BV014 →