Which column should lead a composite index, the range or the equality?

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 happens

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.

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

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';

The safe pattern

CREATE INDEX CONCURRENTLY idx_orders_status_created ON orders (status, created_at);

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.

SET lock_timeout = '5s';
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';

SET lock_timeout = '5s';
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
UPDATE orders SET archived = true WHERE status = 'closed' AND created_at < '2024-01-01';

SET lock_timeout = '5s';
CREATE INDEX idx_orders_created_status ON orders (created_at, status);
UPDATE orders SET archived = true WHERE created_at = '2024-01-01' AND status = 'closed';

SET lock_timeout = '5s';
CREATE INDEX idx_orders_created_status ON orders (created_at, status);

How to check locally

npx bolvrk check migration.sql --remote