warn-replication-lag-risk

What triggers it

ctx.cluster.replication must exist with replicaCount above 0. The statement then has to be WAL-heavy: anything classifyRewrite() recognises, any IndexStmt, any ReindexStmt, or an UpdateStmt / DeleteStmt / InsertStmt whose target comes from dmlTargetTable(). Finally the table has to be large — totalBytes of at least 1_000_000_000 or rowCount of at least 5_000_000, either one on its own being enough.

What does not

A cluster with no connected standby in pg_stat_replication, which is also exactly what a run without --database-url looks like (requiresDatabaseUrl is set). Tables under both size bars. An INSERT ... VALUES, which is excluded explicitly by requiring a non-empty selectStmt.SelectStmt.fromClause. Statements outside the WAL-heavy set, and DML whose table has no stats entry.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It needs --database-url: without a connection it has nothing to read and stays silent.

The replay-lag figure in the message needs pg_monitor or pg_read_all_stats on the connecting role. Without those privileges the replica count still works and the lag is simply left out.

The lock, and what it blocks

No lock of its own — the rule deliberately spans operations with unrelated lock footprints, from ACCESS EXCLUSIVE for a rewrite through SHARE for a plain index build down to ROW EXCLUSIVE for a backfill. What they share is WAL volume, and the cost it reports is not a lock at all: a standby replays with a single startup process, so work the primary spread across many backends arrives serially at the replica.

Why it matters

All of that work goes through WAL, and a standby replays WAL with a single startup process — work the primary spread across many backends arrives at the replica serially, so lag grows for as long as the operation runs and for some time after. If any reads are served from replicas they serve stale data for that whole window, and a failover while lag is high loses whatever has not been replayed. Replication slots turn the pressure around: if a replica cannot keep up, the primary keeps WAL for it until the disk fills.

Unsafe, and safe

-- events is 60 GB, two streaming replicas connected
UPDATE events SET processed = true WHERE processed IS NULL;

-- Batch the work, and let replicas catch up between batches:
DO $$
DECLARE
  rows_updated INT;
BEGIN
  LOOP
    UPDATE events SET processed = true
    WHERE ctid IN (
      SELECT ctid FROM events WHERE processed IS NULL LIMIT 10000
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    COMMIT;
    PERFORM pg_sleep(0.5);
  END LOOP;
END $$;

Deploy and transaction boundaries

The batching the rule points at only helps if replicas can catch up between batches, which needs a commit per batch — so it cannot live inside a single migration transaction.

What it assumes

There is no model of how much WAL the statement will produce and no estimate of lag in seconds — the finding is "this is WAL-heavy and standbys are attached", nothing more precise. maxLagBytes is the lag at analysis time rather than during the operation, and it is absent entirely unless the connecting role holds pg_monitor or pg_read_all_stats. slotCount counts every slot on the server, including logical decoding slots for CDC that have nothing to do with the streaming replicas. Replica hardware, max_slot_wal_keep_size, and whether anything actually reads from the replicas are all invisible to it.

This rule reads live catalogue state, so it says nothing at all without --database-url. That is the trade: no connection, no guess.

What the CLI prints

⚠ [MP103] WARNING (line 2)
  UPDATE on "events" (200,000,000 rows, 64.4 GB) with 2 streaming replicas connected. The whole change set goes through WAL and each replica replays it serially, so lag will grow for the duration and reads served from replicas will be stale until they catch up. 2 replication slots are defined, so WAL is retained on the primary until the replicas have consumed it.

  Safe alternative:
  -- Split the work and let replicas catch up between batches:
  --   run one batch, then wait until lag is back under your threshold.
  
  -- Watch lag while it runs:
  SELECT client_addr,
         state,
         pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag
  FROM pg_stat_replication;
  
  -- If reads are served from replicas, run this in a window where stale reads are
  -- acceptable, and confirm max_slot_wal_keep_size is set so a lagging replica
  -- cannot fill the primary's disk.

  Why: A table rewrite, an index build, or a large backfill writes the whole change set to WAL. Standbys replay that WAL with a single startup process, so work the primary spread across many backends arrives serially. Replication lag grows for as long as the operation runs and for some time after; read replicas serve stale data meanwhile, and a failover during the lag window loses whatever has not been replayed. Replication slots make it worse in the other direction: if a replica cannot keep up, the primary retains WAL for it and the disk fills.
  Docs: https://migrationpilot.dev/rules/mp103

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. The catalogue figures come from the production context this rule documents.

Turning it off

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

-- migrationpilot-disable MP103
UPDATE events SET processed = true WHERE processed IS NULL;

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

rules:
  MP103: false

# or keep it, and downgrade it
rules:
  MP103:
    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-backfilla WHERE-less UPDATE, flagged without a database
• MP067warn-backfill-no-batchingthe same for DELETE
• MP102warn-rewrite-disk-headroomthe disk side of a rewrite
• MP055drop-pk-replica-identityreplication correctness rather than lag