DROP INDEX without CONCURRENTLY

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

What it catches

DROP INDEX needs ACCESS EXCLUSIVE on the table. The drop itself is instant, but the lock request queues behind any running query touching the table — and every new query then queues behind the lock request. One slow SELECT turns an instant drop into a stall for all traffic.

Fires on

DROP INDEX idx_orders_region;

Do this instead

DROP INDEX CONCURRENTLY waits for conflicting queries instead of queueing all traffic behind an ACCESS EXCLUSIVE request. Run it alone in its own migration, outside a transaction. (It cannot drop indexes that back UNIQUE or PRIMARY KEY constraints — use ALTER TABLE DROP CONSTRAINT for those.)

-- In its own migration file, run outside a transaction:
SET lock_timeout = '5s';
DROP INDEX CONCURRENTLY idx_orders_region;

Catch this before it ships

This rule runs locally in the free CLI — or with the full corpus through the hosted service: npx bolvrk check migration.sql

Browse all 69 rules

← BV015 BV017 →