Indexes
REINDEX INDEX without CONCURRENTLY
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
reindex-non-concurrent REINDEX INDEX idx_orders_status;What this does
Takes ACCESS EXCLUSIVE on the index being rebuilt. Any query whose plan would use that index blocks for the duration, even though the table itself is only implicitly SHARE-locked.
Safe rewrite
Use REINDEX INDEX CONCURRENTLY (PostgreSQL 12+) to avoid the ACCESS EXCLUSIVE lock
REINDEX INDEX CONCURRENTLY idx_orders_status;
-- Note: REINDEX CONCURRENTLY must run outside a transaction block