REINDEX TABLE Blocks All Reads and Writes During Rebuild

Scenario

A DBA notices that the orders table has a bloated index (dead tuple ratio over 40%) after a large bulk delete. To reclaim space and improve query performance, the DBA runs:

REINDEX TABLE orders;

This acquires an AccessExclusiveLock on the table for the entire duration of the rebuild. The orders table has 50 million rows; the REINDEX takes approximately 20 minutes. During that window, every application query that touches orders — including simple SELECT statements — hangs waiting for the lock. The application becomes effectively unavailable.

The correct approach is REINDEX INDEX CONCURRENTLY (or REINDEX TABLE CONCURRENTLY, PG14+), which acquires only a ShareUpdateExclusiveLock and allows reads and writes to continue throughout the rebuild.