require-analyze-after-backfill

What triggers it

A bulk UpdateStmt, or an InsertStmt whose select has a fromClause rather than a literal valuesLists (i.e. INSERT ... SELECT, not INSERT ... VALUES), when no later statement in the same migration runs ANALYZE (bare, or naming that table, or VACUUM ANALYZE) covering it.

What does not

INSERT ... VALUES with a literal values list, treated as seeding rather than a backfill. An INSERT with neither a fromClause nor a values list. When a later statement backfills the same table again, only that later one is checked — the earlier one is skipped so the table is reported once, not per statement. And when a later ANALYZE, targeted ANALYZE table, or VACUUM ANALYZE follows the backfill.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

None — the failure mode is stale planner statistics, not a blocked lock.

Why it matters

The planner does not look at your data, it looks at pg_statistic — and a backfill can invalidate all of it at once. A column that was entirely NULL before the UPDATE is fully populated afterwards while the statistics still say it is empty, so the planner keeps choosing plans built for a table that no longer exists. Autovacuum fixes this eventually, but it triggers on a row-change threshold rather than on your migration finishing, so latency degrades some time after the deploy went green.

Unsafe, and safe

UPDATE users SET status = 'active' WHERE status IS NULL;
-- Migration ends here. Planner still thinks status is entirely NULL.

UPDATE users SET status = 'active' WHERE status IS NULL;
ANALYZE users;

What it assumes

Assumes the migration file itself is where the follow-up ANALYZE would appear. An ANALYZE run separately — by deploy tooling, a scheduled job, or autovacuum finishing before anyone notices — satisfies the real requirement but isn't visible to a rule that only reads statements in this file, so it can flag a backfill that's actually fine in practice.

What the CLI prints

⚠ [MP088] WARNING (line 1)
  Backfill on "users" is not followed by ANALYZE. The planner will keep using pre-backfill statistics until autovacuum catches up, which can mean bad plans on "users" well after the migration reports success.

  Safe alternative:
  -- Refresh the statistics once the backfill is done:
  ANALYZE users;
  
  -- ANALYZE takes only a ShareUpdateExclusiveLock, so it does not block
  -- reads or writes and can run outside the migration transaction.

  Why: After a backfill the statistics in pg_statistic still describe the table as it was before. The planner trusts them, so it keeps choosing plans built for the old null fraction and the old row count: sequential scans over a column that is now selective, nested loops sized for a fraction of the rows that are actually there. Autovacuum will fix it eventually, but it triggers off a row-change threshold rather than the end of your migration, so on a large table the gap is long enough to matter. A single ANALYZE closes it deterministically and takes a sample-sized fraction of the time the backfill just took.
  Docs: https://migrationpilot.dev/rules/mp088

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP088
UPDATE users SET status = 'active' WHERE status IS NULL;

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP088: false

# or keep it, and downgrade it
rules:
  MP088:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP011unbatched-data-backfillUPDATE with no WHERE rewrites everything at once
• MP067warn-backfill-no-batchingunbounded DELETE
• MP080ban-data-in-migrationkeeping DML out of DDL migrations entirely