Indexes
DROP INDEX without CONCURRENTLY
Lock mode ACCESS EXCLUSIVE
Risk MEDIUM
Blocks reads, writes, other DDL
pgfence rule
drop-index-not-concurrent DROP INDEX idx_orders_status;What this does
Takes ACCESS EXCLUSIVE on the owning table, briefly, but that is enough to queue behind any long-running query and block everything behind it in turn.
Safe rewrite
Use DROP INDEX CONCURRENTLY to avoid the ACCESS EXCLUSIVE lock
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;