Skip to main content

PG.L2.INDEX_NOT_CONCURRENT — Building 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

A plain CREATE INDEX takes a SHARE lock on the table and holds it until the index is built. Reads continue; every write waits. On a small table nobody notices. On the table you actually wanted an index for, the build takes minutes and every insert, update and delete queues behind it.

This is the one on the list most likely to have been fine in staging.

What CONCURRENTLY costs​

It is not free, and the price is what makes this a rule rather than a lint preference:

  • It cannot run inside a transaction. Laravel wraps a migration in one by default on PostgreSQL, so the migration has to opt out — public bool $withinTransaction = false;.
  • It can fail and leave an INVALID index behind. A canceled or violated build leaves a complete-looking catalog row that the planner ignores. That leftover is a real trap: it looks like index coverage and provides none, which is why the audit suite's PG.L5.FK_NO_INDEX refuses to count an invalid index.
  • It is slower. It scans the table twice.

Flagged​

Schema::table('orders', fn (Blueprint $table) => $table->index('customer_id'));

Preferred​

// CONCURRENTLY cannot run inside a transaction, so set
// public bool $withinTransaction = false; on the migration:
DB::statement('CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id)');

The two rules that come with it​

Turning off the transaction wrapper is not a detail — it changes what a failure means. Half a migration can now stay applied. Two neighboring rules exist because of it:

  • PG.L3.CONCURRENTLY_IN_TRANSACTION fires when CONCURRENTLY is used inside the wrapper, where PostgreSQL will simply reject it.
  • PG.L3.RISKY_OPS_SINGLE_TX fires when several risky operations share one transaction, which is the mirror-image mistake.

When a plain build is right​

A table you know to be small — a lookup table, a fresh table created in the same migration — does not need the ceremony, and the index is built before anything writes to it. SQLens cannot see the row count of a table it never connected to, so it flags the statement and lets you answer:

#[SqlensIgnore('PG.L2.INDEX_NOT_CONCURRENT', 'countries is a 250-row lookup table')]

The reason is the deliverable. An unexplained ignore hides the next one too.

Sources​

  • PostgreSQL 18 — CREATE INDEX — a plain build blocks writes until it completes; CONCURRENTLY avoids that at the cost of running outside a transaction and possibly leaving an invalid index

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.