Skip to main content

PG.L2.REFRESH_MATERIALIZED_VIEW_NOT_CONCURRENT — Refreshing a view blocks every read of it

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

REFRESH MATERIALIZED VIEW runs the view's query again and replaces what the view holds. Without CONCURRENTLY it holds an ACCESS EXCLUSIVE lock on the view for the whole run, so every read of the view waits until the query has finished, however long the query takes.

Measured on PostgreSQL 18.0, with the locks read from pg_locks inside the statement's own transaction: the plain refresh held AccessExclusiveLock on the view, the concurrent one ExclusiveLock, which lets plain reads through.

Flagged​

DB::statement('REFRESH MATERIALIZED VIEW order_totals');

Preferred​

// The view needs a unique index over plain columns that covers every row,
// and it must already hold data:
DB::statement('REFRESH MATERIALIZED VIEW CONCURRENTLY order_totals');

The concurrent refresh keeps the view readable while it runs. It takes longer, because it compares the new result with the old one row by row, and it refuses a view without a unique index over plain columns that covers every row, and a view that holds no data yet. Unlike CREATE INDEX CONCURRENTLY, it runs inside a transaction block, so it may stay in the migration as it is: measured on 18.0, it ran inside BEGIN and ROLLBACK without an error.

What is read​

REFRESH MATERIALIZED VIEW, with CONCURRENTLY and with WITH DATA. WITH NO DATA empties the view, so every later read fails rather than waits until the next refresh; it is not recognized, and its 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.