Indexes
REINDEX TABLE without CONCURRENTLY
REINDEX TABLE orders;What this does
REINDEX TABLE takes SHARE on the table itself (blocking writes, not reads), plus ACCESS EXCLUSIVE on each index while it is rebuilt. A cheat sheet that lists this as a flat ACCESS EXCLUSIVE on the table is wrong; the table-level lock really is SHARE.
Safe rewrite
Use REINDEX CONCURRENTLY (PostgreSQL 12+) to avoid blocking writes
REINDEX TABLE CONCURRENTLY orders;
-- Note: REINDEX CONCURRENTLY must run outside a transaction block