Is the index I am dropping still used by the planner?
DROP INDEX on an index the planner is actively using
Warning: this works, but blocks traffic or rewrites data at scale.
What happens
pg_stat_user_indexes counts how many times the planner chose each index. Dropping one with thousands of scans since the last stats reset sends every one of those queries to a sequential scan or a worse index, a regression that lands the moment the migration applies, invisible in a dev database where nothing has run. Silent when the counters are too young to trust, or when another index with the same leading columns remains.
Why it is dangerous on a populated table
It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.
Fires on
DROP INDEX CONCURRENTLY idx_orders_region;The safe pattern
Check usage before dropping: an index with zero scans over a long window is safe to remove; one with heavy use needs a replacement (same leading columns) created CONCURRENTLY first. If the index is being replaced by a wider one, create the wider one in an earlier migration, the rule sees it.
-- Only after confirming idx_scan is ~0 over a long window, or after a covering replacement exists:
SET lock_timeout = '5s';
DROP INDEX CONCURRENTLY idx_orders_legacy_flag;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)
SET lock_timeout = '5s';
DROP INDEX CONCURRENTLY idx_orders_region;Stays silent (1)
SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_region_created ON orders (region, created_at);
-- next migration: DROP INDEX CONCURRENTLY idx_orders_region;How to check locally
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