Will the planner use my partial or expression index?

Partial or expression index the migration's own queries cannot use

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

What happens

A partial index only serves queries whose WHERE clause provably implies its predicate; an expression index only serves queries that use the identical expression. An index on lower(email) does nothing for WHERE email = …, and an index WHERE status = 'archived' does nothing for WHERE status = 'active'. When the migration itself contains such a query, the mismatch is visible before it ships.

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_users_lower_email ON users (lower(email));
UPDATE users SET verified = true WHERE email = 'a@example.com';

The safe pattern

Match the shapes: query through the same expression (WHERE lower(email) = …) or index the plain column; give the partial index the predicate the queries actually use. Queries elsewhere in the application are not visible here, the rule only reads this file.

CREATE INDEX CONCURRENTLY idx_users_lower_email ON users (lower(email));
-- and query through the same expression: WHERE lower(email) = lower($1)

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)

expression vs plain

SET lock_timeout = '5s';
CREATE INDEX idx_users_lower_email ON users (lower(email));
UPDATE users SET verified = true WHERE email = 'a@example.com';

partial constant mismatch

SET lock_timeout = '5s';
CREATE INDEX idx_orders_archived ON orders (id) WHERE status = 'archived';
DELETE FROM orders WHERE status = 'cancelled';

Stays silent (4)

expression matches

SET lock_timeout = '5s';
CREATE INDEX idx_users_lower_email ON users (lower(email));
UPDATE users SET verified = true WHERE lower(email) = 'a@example.com';

no query evidence

SET lock_timeout = '5s';
CREATE INDEX idx_users_lower_email ON users (lower(email));

parameterized query

SET lock_timeout = '5s';
CREATE INDEX idx_orders_archived ON orders (id) WHERE status = 'archived';
DELETE FROM orders WHERE status = $1;

partial constant matches

SET lock_timeout = '5s';
CREATE INDEX idx_orders_archived ON orders (id) WHERE status = 'archived';
DELETE FROM orders WHERE status = 'archived';

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

← BP006 BP008 →