Backfill filters a large table on columns no index leads with
Note — this works and blocks nothing, but a performance regression is likely.
What it catches
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.
Fires on
UPDATE orders SET legacy = false WHERE legacy_flag = 'y';Do this instead
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'; 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