DROP INDEX on an index the planner is actively using
Warning — this works, but blocks traffic or rewrites data at scale.
What it catches
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.
Fires on
DROP INDEX CONCURRENTLY idx_orders_region;Do this instead
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; This rule runs in the hosted service on Startup and above — add --remote with a team token, or use the GitHub Action: npx bolvrk check migration.sql