Does dropping this index leave a foreign key unindexed?

DROP INDEX removes the only index covering a foreign key

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

What happens

Postgres never indexes the referencing side of a foreign key itself; the index someone added later is what keeps parent-side UPDATE and DELETE cheap. Drop it and every change to the referenced table sequential-scans the child table to enforce the constraint, the same regression BV017 warns about, arriving through the back door.

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

DROP INDEX CONCURRENTLY idx_orders_user;

The safe pattern

Keep a covering index whose leading columns match the foreign key, replace before you remove (create the new index CONCURRENTLY first, then drop the old one), or drop the constraint too if the relationship is going away. Both keep this rule silent.

-- Replace first (own migration, outside a transaction):
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at);
-- Next migration: DROP INDEX CONCURRENTLY idx_orders_user;

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)

drop fk covering index

SET lock_timeout = '5s';
DROP INDEX CONCURRENTLY idx_orders_user;

Stays silent (3)

constraint dropped too

SET lock_timeout = '5s';
ALTER TABLE orders DROP CONSTRAINT orders_user_fk;
DROP INDEX CONCURRENTLY idx_orders_user;

replaced first

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at);
DROP INDEX CONCURRENTLY idx_orders_user;

unrelated index

SET lock_timeout = '5s';
DROP INDEX CONCURRENTLY idx_orders_region;

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

← BP003 BP005 →