Will this backfill scan the whole table?

Backfill filters a large table on columns no index leads with

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

What happens

An UPDATE or DELETE with a WHERE clause looks bounded, but if no index leads with any of the filtered columns, Postgres reads the whole table to find the rows, once per statement, and once per batch when the backfill is looped. On a large table that is minutes of I/O and a long-held lock per pass. Only fires with a live snapshot proving both the size and the absence of the index.

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

UPDATE orders SET legacy = false WHERE legacy_flag = 'y';

The safe pattern

Create an index (CONCURRENTLY, in its own migration) that leads with the column the backfill filters on, or filter by the primary key in ranges (WHERE id BETWEEN … AND …) so each batch uses the primary-key index. A partial index over just the rows to fix is the smallest option.

-- Own migration, outside a transaction:
CREATE INDEX CONCURRENTLY idx_orders_legacy_flag ON orders (legacy_flag) WHERE legacy_flag = 'y';

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)

unindexed filter

SET lock_timeout = '5s';
UPDATE orders SET legacy = false WHERE legacy_flag = 'y';

Stays silent (4)

filter on indexed column

SET lock_timeout = '5s';
UPDATE orders SET legacy = false WHERE user_id = 42;

index created first

SET lock_timeout = '5s';
CREATE INDEX idx_orders_legacy_flag ON orders (legacy_flag);
UPDATE orders SET legacy = false WHERE legacy_flag = 'y';

or predicate unreadable

SET lock_timeout = '5s';
UPDATE orders SET legacy = false WHERE legacy_flag = 'y' OR legacy_flag = 'Y';

primary key range

SET lock_timeout = '5s';
UPDATE orders SET legacy = false WHERE id BETWEEN 1 AND 10000;

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

← BP007 BP009 →