Postgres REINDEX and DROP INDEX Without Blocking Writes

A standard REINDEX blocks writes for the duration of the rebuild. A normal DROP INDEX is shorter, but it takes ACCESS EXCLUSIVE on the table and can wait behind old queries while placing new traffic behind it. Production index maintenance should normally use REINDEX ... CONCURRENTLY or DROP INDEX CONCURRENTLY, with the restrictions understood before the migration runs.

The word CONCURRENTLY is not a free speed improvement. Concurrent reindexing performs multiple phases, waits for existing transactions, uses more work than a standard rebuild, and can leave a transient invalid index if it fails. The benefit is availability: application reads and writes keep moving.

Quick reference: index maintenance locks

Statement	Table impact	Reads	Writes	Transaction block
REINDEX TABLE orders	SHARE on table while indexes rebuild	Continue	Block	Allowed
REINDEX TABLE CONCURRENTLY orders	SHARE UPDATE EXCLUSIVE session locks	Continue	Continue	Not allowed
DROP INDEX idx_orders_status	ACCESS EXCLUSIVE on owning table	Block	Block	Allowed
DROP INDEX CONCURRENTLY idx_orders_status	Waits for conflicting transactions without locking out traffic	Continue	Continue	Not allowed

The official REINDEX documentation says a standard rebuild locks out writes but not reads. PostgreSQL’s lock-mode reference identifies SHARE as the mode that protects a table from concurrent data changes. The DROP INDEX documentation is more severe: normal DROP INDEX takes ACCESS EXCLUSIVE on the table and blocks other access.

Why a routine REINDEX becomes an outage

Assume orders has 80 million rows and four indexes. This maintenance command looks harmless:

SET lock_timeout = '2s';
SET statement_timeout = '2h';

REINDEX TABLE orders;

The rebuild can take minutes or hours. For that entire period, application INSERT, UPDATE, and DELETE statements wait. Reads can continue, but an order-processing system that cannot write is still unavailable.

REINDEX TABLE takes a SHARE lock on the table and rebuilds its indexes one at a time. The pgfence rule also records the internal ACCESS EXCLUSIVE lock on each index during its rebuild. At the table level, SHARE conflicts with ROW EXCLUSIVE, the lock normal writes take. That is why the visible production symptom is a growing queue of writes rather than blocked plain SELECT queries.

Watch the work through PostgreSQL’s progress view:

SELECT
  pid,
  command,
  phase,
  relid::regclass AS table_name,
  index_relid::regclass AS index_name,
  blocks_done,
  blocks_total
FROM pg_stat_progress_create_index;

If writes are already queued, cancelling the maintenance statement can release the lock, but that is incident response, not a deployment strategy.

Use REINDEX CONCURRENTLY on PostgreSQL 12+

PostgreSQL 12 introduced REINDEX CONCURRENTLY. Run it outside an explicit transaction:

SET lock_timeout = '2s';
SET statement_timeout = '4h';

REINDEX TABLE CONCURRENTLY orders;

The concurrent path keeps ordinary reads and writes available. PostgreSQL takes SHARE UPDATE EXCLUSIVE session locks on the table and indexes while it builds replacements, which prevents competing schema work but does not conflict with the locks used by normal application queries.

The availability tradeoff is more work. PostgreSQL builds a replacement, performs additional scans, waits for transactions that could still use the old index, swaps the definitions, then waits again before dropping the old copy. A concurrent rebuild can therefore take longer than the standard form and create meaningful CPU, memory, I/O, and temporary disk pressure.

Only one concurrent index build can run on a table at a time. Do not start several concurrent reindexes against the same table and expect them to progress in parallel.

REINDEX CONCURRENTLY also cannot rebuild system catalogs or exclusion-constraint indexes concurrently. When targeting a table or database, PostgreSQL skips exclusion-constraint indexes. Review the object set rather than treating a successful command as proof that every index was rebuilt.

A failed concurrent reindex can leave cleanup work

If a uniqueness violation or another error interrupts the rebuild, PostgreSQL can leave a second invalid index next to the valid original. The temporary name normally ends in _ccnew, sometimes with a number appended.

Find those leftovers with:

SELECT
  indexrelid::regclass AS index_name,
  indrelid::regclass AS table_name,
  indisvalid,
  indisready
FROM pg_index
WHERE NOT indisvalid
ORDER BY indrelid::regclass::text, indexrelid::regclass::text;

An invalid replacement is ignored for reads but still consumes write overhead. PostgreSQL recommends dropping the _ccnew index, then retrying the concurrent reindex. If the suffix is _ccold, the new index was installed but PostgreSQL could not remove the old copy, so the old invalid index is the cleanup target.

Do not automate deletion based only on the suffix. Inspect pg_index, the table definition, and dependencies first.

DROP INDEX has a shorter but sharper lock window

Dropping an index is usually fast, but the nonconcurrent form still requests ACCESS EXCLUSIVE on the owning table:

SET lock_timeout = '2s';
SET statement_timeout = '30s';

DROP INDEX IF EXISTS idx_orders_status;

That lock blocks both reads and writes. If a long transaction is already using the table, the drop waits. New traffic can then queue behind the waiting DDL. This is why a statement that takes milliseconds after lock acquisition can still trigger an outage.

Use the concurrent form for a live table:

SET lock_timeout = '2s';
SET statement_timeout = '30min';

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;

DROP INDEX CONCURRENTLY has four restrictions worth reviewing:

1. It must run outside an explicit transaction block.
2. It accepts only one index name per statement.
3. It does not support CASCADE.
4. It cannot drop an index that backs a UNIQUE or PRIMARY KEY constraint, or an index on a partitioned table.

For a constraint-backed index, remove or change the constraint through an explicit compatibility plan. Do not reach for a nonconcurrent drop merely because the concurrent form rejects it.

What pgfence reports

For a standard table rebuild, pgfence reports the write-blocking lock and the PostgreSQL 12 version gate:

npx @flvmnt/pgfence explain "REINDEX TABLE orders"

Statement:
  REINDEX TABLE orders;

[HIGH] reindex-non-concurrent
  REINDEX TABLE "orders": acquires SHARE lock on the table (blocking writes) and ACCESS EXCLUSIVE on each index
  Lock: SHARE
  Blocks: writes, other DDL

  Safe rewrite:
  Use REINDEX CONCURRENTLY (PG12+) to avoid ACCESS EXCLUSIVE lock
    REINDEX TABLE CONCURRENTLY orders;
    -- Note: REINDEX CONCURRENTLY must run outside a transaction block

The separate drop-index-not-concurrent rule rewrites a normal index drop to DROP INDEX CONCURRENTLY IF EXISTS, preserving the original schema-qualified and quoted identifier.

Review checklist

1. Use CONCURRENTLY for index maintenance on live application tables.
2. Confirm PostgreSQL 12 or later before using REINDEX CONCURRENTLY.
3. Disable ORM transaction wrappers for concurrent reindex and drop statements.
4. Monitor pg_stat_progress_create_index and temporary disk capacity during long rebuilds.
5. Check pg_index.indisvalid after a failed concurrent operation and inspect leftovers before retrying.

Related

• CREATE INDEX CONCURRENTLY and invalid indexes
• Why CONCURRENTLY cannot run in a transaction
• REINDEX TABLE lock and rewrite reference
• Safe index migration recipe