Composite index led by a range column ahead of an equality column
Note — this works and blocks nothing, but a performance regression is likely.
What it catches
A B-tree answers a query by walking leading columns with equality first, then scanning one range. With the range column first — (created_at, status) for WHERE status = 'x' AND created_at > … — the index has to scan every row in the date range and filter status afterwards. Put the equality column first and the scan shrinks to exactly the matching rows. Only fires when a query in the migration itself shows that shape.
Fires on
CREATE INDEX idx_orders_created_status ON orders (created_at, status);
UPDATE orders SET archived = true WHERE status = 'closed' AND created_at < '2024-01-01';Do this instead
Order the index equality-columns-first, range-column-last, so the range scan only covers rows that already match the equality. If the range column really must lead (for ORDER BY on it alone), say so with a second index — this rule only reads the queries in the file.
CREATE INDEX CONCURRENTLY idx_orders_status_created ON orders (status, created_at); 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