warn-matview-with-data

What It Detects

CREATE MATERIALIZED VIEW ... WITH DATA runs the full query inside the migration, holding locks on every source table.

Why It's Dangerous

WITH DATA is the default, so this usually happens without anyone choosing it. The statement looks like a definition and behaves like a batch job: the migration runs the view's query to completion before returning, holding locks on every table the query reads. A materialized view is generally materialized because the query is expensive, so the build is expensive by construction — and the transaction stays open throughout, which keeps xmin pinned so vacuum cannot clean up rows anywhere in the database.

Bad Example

CREATE MATERIALIZED VIEW daily_revenue AS
  SELECT date_trunc('day', created_at) AS day, sum(total)
  FROM orders GROUP BY 1;
-- Migration blocks until the aggregate finishes

Good Example

-- Returns immediately; the expensive part becomes a REFRESH you can
-- schedule and retry. Note the view is not queryable until that runs,
-- and the first REFRESH cannot use CONCURRENTLY.
CREATE MATERIALIZED VIEW daily_revenue AS
  SELECT date_trunc('day', created_at) AS day, sum(total)
  FROM orders GROUP BY 1
  WITH NO DATA;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP096: false

Or change its severity:

# .migrationpilotrc.yml
rules:
  MP096:
    severity: warning