Skip to main content

PG.L2.REINDEX_NOT_CONCURRENT — Rebuilding an index blocks every write until it finishes

  • Category: safety
  • Level: 2
  • Confidence: deterministic
  • Downtime class: blocking
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18

REINDEX rebuilds an index that already exists, and without CONCURRENTLY it does so the blocking way. It holds a SHARE lock on the table until the rebuild is done, so every INSERT, UPDATE and DELETE waits, and an ACCESS EXCLUSIVE lock on each index it rebuilds, so every read that would use one waits as well. REINDEX TABLE does that for every index of the table, one after the other.

Measured on PostgreSQL 18.0, with the locks read from pg_locks inside the statement's own transaction: REINDEX TABLE orders held ShareLock on orders and AccessExclusiveLock on each of its indexes, and REINDEX INDEX orders_n_idx held ShareLock on the table and AccessExclusiveLock on that index.

Flagged​

DB::statement('REINDEX INDEX orders_created_at_index');

Preferred​

// public $withinTransaction = false; on the migration class, because
// PostgreSQL refuses the concurrent rebuild inside a transaction block:
DB::statement('REINDEX INDEX CONCURRENTLY orders_created_at_index');

REINDEX … CONCURRENTLY builds each replacement beside the old index and swaps them, so neither reads nor writes wait. It takes longer, and PostgreSQL refuses it inside a transaction block, which PG.L3.CONCURRENTLY_IN_TRANSACTION reports. An interrupted concurrent rebuild leaves an invalid index whose name ends in _ccnew, which PG.L7.INDEX_INVALID reports once it exists.

What is read​

REINDEX INDEX and REINDEX TABLE, with options in parentheses and with CONCURRENTLY in either place. REINDEX SCHEMA, DATABASE and SYSTEM rebuild every index they reach, which no target can name, and PostgreSQL refuses them inside a transaction block: they are not recognized, and their migration stays undetermined.

Sources​

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • concurrently — Build the index without taking the write lock the ordinary form takes.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.