How dangerous is an UPDATE or DELETE without a WHERE clause in a migration?

Unbounded UPDATE or DELETE

Warning: this works, but blocks traffic or rewrites data at scale.

What happens

A WHERE-less UPDATE/DELETE rewrites or removes every row in a single transaction: it locks all rows for the duration, doubles the table in dead tuples, floods WAL, and stalls replicas. Backfills belong in batched jobs, not migrations.

Why it is dangerous on a populated table

It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.

Fires on

UPDATE orders SET region = 'eu';

The safe pattern

Move the backfill to a batched job: update or delete a bounded set per transaction, commit, repeat until done. Each batch releases its row locks, keeps dead-tuple churn vacuumable, and lets replicas keep up, and a failure resumes instead of rolling back hours of work.

-- From a job, not a migration, bounded batches, commit each:
--    UPDATE orders SET region = 'eu'
--    WHERE id IN (SELECT id FROM orders WHERE region IS NULL LIMIT 5000);
-- loop until 0 rows updated

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 (2)

delete no where

DELETE FROM orders;

update no where

UPDATE orders SET region = 'eu';

Stays silent (1)

bounded update

UPDATE orders SET region = 'eu' WHERE region IS NULL AND id < 10000;

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

← BV013 BV015 →