Indexes
REINDEX SCHEMA or REINDEX DATABASE without CONCURRENTLY
Lock mode ACCESS EXCLUSIVE
Risk CRITICAL
Blocks reads, writes, other DDL
pgfence rule
reindex-non-concurrent REINDEX SCHEMA public;What this does
Rebuilds every index on every table in the schema (or database) in sequence, taking ACCESS EXCLUSIVE on each one as it goes. On any real production schema this is a long, rolling outage, not a single brief lock.
Safe rewrite
Use REINDEX ... CONCURRENTLY (PostgreSQL 12+), or reindex tables individually during low-traffic windows
REINDEX SCHEMA CONCURRENTLY public;
-- Note: REINDEX CONCURRENTLY must run outside a transaction block