Alerting on Replication Lag During Backfills

The backfill has been running for twenty minutes, chewing through a 300-million-row orders table in 5,000-row batches, and the primary looks healthy — CPU is fine, no lock waits, the job log scrolls steadily. Then the support queue lights up: users report that an order they just placed “disappeared,” a dashboard shows yesterday’s totals, and a read-your-writes check in the checkout flow starts failing intermittently. Nothing is broken on the primary. The damage is on the read replicas, which have fallen ninety seconds behind because your batched UPDATE is producing write-ahead log faster than the slowest standby can replay it. This page is about catching that gap the instant it opens: measuring replica lag as a live number, setting a budget it must stay under, and wiring the backfill so it pauses itself the moment lag spikes — instead of you finding out from a customer. It is the lag-specific half of migration observability, focused entirely on the backfill phase.

Symptom / Error Signatures

Replication lag rarely announces itself as a single error string; it shows up as a scatter of stale-read symptoms plus a climbing number in one specific column. The confirming signals are:

  • The primary is healthy — pg_stat_activity shows no Lock waits, no idle-in-transaction pileup — yet reads served from a replica return rows that are seconds or minutes out of date.
  • On PostgreSQL, pg_stat_replication reports a growing replay_lag interval, and the byte delta pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) climbs monotonically for the duration of the backfill.
  • On MySQL, SHOW REPLICA STATUS\G reports a rising Seconds_Behind_Source, often after sitting deceptively at 0 while a large transaction is still being fetched.
  • Application logs surface read-your-writes failures: record not found immediately after a successful write, or an optimistic-lock/stale object exception when a replica-served read is written back.
  • A pushed lag metric flatlines then jumps, or your alert channel shows MigrationReplicationLagHigh firing partway through the job.

The distinguishing feature is the asymmetry: write path fine, read path stale. If both the primary and replicas are slow you are looking at a lock or resource problem, not lag. Lag is specifically the replica falling behind a healthy primary.

Replication-lag symptom map The primary is healthy, but the slow replica falls behind and serves stale reads — write path fine, read path stale. primary healthy PRIMARY no lock waits CPU normal WAL / binlog stream REPLICA A replay_lag ~0s · in sync REPLICA B replay_lag 90s and rising APP READ stale rows asymmetry: write path fine, read path stale
The primary stays green while the slow replica drifts behind and serves out-of-date rows — lag is a read-path failure, not a primary problem.

Root Cause Analysis

A backfill is, by construction, a large volume of writes compressed into a short window. Each batched UPDATE commits on the primary and generates WAL (PostgreSQL) or binary-log events (MySQL) that every replica must fetch and replay serially. Standby replay is largely single-threaded per stream, so when the primary commits batches faster than a replica applies them, the unapplied backlog — the lag — grows without bound. The primary never feels it; the replica silently drifts. This is exactly the trade-off explored in tuning backfill batch size against replication lag: bigger batches finish sooner but push lag higher per commit.

The two engines expose the same physics through different columns, and the difference matters for alerting because they measure different things:

Aspect PostgreSQL MySQL 8.0
Primary-side view pg_stat_replication (replay_lag, replay_lsn) SHOW BINARY LOG STATUS + replica-reported data
Replica-side view pg_last_wal_replay_lsn() SHOW REPLICA STATUS
Lag as time replay_lag interval (apply lag) Seconds_Behind_Source (apply lag)
Lag as bytes pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) Source_Log_File / Read_... position delta
Per-worker detail one WAL replay stream performance_schema.replication_applier_status_by_worker
Trap replay_lag is NULL when nothing replayed since last checkpoint Seconds_Behind_Source reads 0 while a big trx is still fetched, then jumps

The two traps drive alerting design. PostgreSQL’s replay_lag can be NULL during idle moments, so an alert that treats NULL as “healthy” will miss a disconnected standby entirely — fall back to the byte delta, which is always defined. MySQL’s Seconds_Behind_Source measures apply lag relative to received events, so a single fat transaction hides inside the fetch phase and reports 0 right up until it lands, producing a saw-tooth that a naive threshold reads as fine. The fix on MySQL is to keep transactions small (which the backfill already controls via batch size) and read per-worker apply status for accuracy.

PostgreSQL vs MySQL lag signal shapes PostgreSQL replay_lag climbs smoothly but reads NULL when idle; MySQL Seconds_Behind_Source is a flat-then-jump saw-tooth. Both cross the 5s budget. PostgreSQL · replay_lag (apply lag) 5s budget budget crossed NULL when idle time → MySQL · Seconds_Behind_Source (fetch-then-apply) 5s budget reads 0 while fetching big trx jumps when trx lands time →
Two engines, two failure shapes: PostgreSQL's smooth climb hides a NULL-when-idle gap; MySQL's saw-tooth reads zero during the fetch phase, then leaps past budget when the transaction applies.

Immediate Mitigation

If a backfill is running right now and lag is climbing, the goal is to stop making it worse without corrupting the job. A backfill built on idempotent script design is safe to pause or kill at any batch boundary, because every batch is re-runnable and resumes from a durable cursor.

1. Read the live lag to confirm the diagnosis. Get the current worst-replica lag as a single number before acting.

-- PostgreSQL · run as a monitoring role with pg_monitor · read-only, safe anywhere
-- Context: MAX across all standbys; COALESCE guards the NULL-when-idle trap.
SELECT client_addr,
       COALESCE(EXTRACT(epoch FROM replay_lag), 0) AS replay_lag_s,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication
ORDER BY lag_bytes DESC;
-- MySQL 8.0 · run as a user with REPLICATION CLIENT · read-only, safe anywhere
-- Context: read Seconds_Behind_Source; cross-check per-worker apply progress.
SHOW REPLICA STATUS\G
SELECT WORKER_ID, APPLYING_TRANSACTION,
       LAST_APPLIED_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker;

2. Pause the backfill at the current batch. If your runner supports a pause signal, use it — it stops after the in-flight batch commits and holds the cursor. This is the least disruptive lever.

# bash · run from the ops host that controls the backfill job
# Context: SIGUSR1 tells the runner to finish the current batch, checkpoint, and idle.
kill -USR1 "$(cat /var/run/backfill_orders.pid)"   # graceful pause, cursor preserved

3. If no pause signal exists, stop the job cleanly. Killing between batches loses only progress, never correctness, because the next run resumes from the persisted cursor.

# bash · run from the ops host · safe only because each batch is idempotent
# Context: SIGTERM lets the runner finish the current batch before exiting.
kill -TERM "$(cat /var/run/backfill_orders.pid)"    # resume later from the cursor

4. Wait for replicas to drain before resuming. Watch the lag fall back under budget, then restart the job at a smaller batch size so the same table finishes without re-spiking.

# bash · run as the monitoring role against the primary
# Context: block until worst-replica lag is under 2s, then it is safe to resume.
until [ "$(psql "$PRIMARY_URL" -tAc \
  "SELECT COALESCE(MAX(EXTRACT(epoch FROM replay_lag)),0) FROM pg_stat_replication;" \
  | cut -d. -f1)" -lt 2 ]; do
  echo "draining… lag still high"; sleep 5
done
echo "replicas caught up — safe to resume backfill at a smaller batch"

Permanent Fix / Long-Term Pattern

The durable pattern is a lag-aware backfill that never needs a human to pause it: the job reads replica lag before each batch, and when the number exceeds the budget it throttles itself — sleeping until replicas catch up — so the alert becomes a backstop rather than the primary control. The alert and the throttle share one budget, but the throttle reacts first at a lower threshold. This is the idiomatic solution described in depth under backfill optimization; here is the control loop reduced to its core.

# bash · migration runner · run outside a maintenance window against live traffic
# Context: reads lag as a control input each batch; sleeps rather than outrunning replicas.
MAX_LAG=2          # throttle threshold (below the 5s alert budget)
while read -r batch; do
  lag=$(psql "$PRIMARY_URL" -tAc \
    "SELECT COALESCE(MAX(EXTRACT(epoch FROM replay_lag)),0) FROM pg_stat_replication;")
  while awk "BEGIN{exit !($lag > $MAX_LAG)}"; do   # over budget → wait, don't push
    echo "lag ${lag}s > ${MAX_LAG}s — pausing batch"; sleep 5
    lag=$(psql "$PRIMARY_URL" -tAc \
      "SELECT COALESCE(MAX(EXTRACT(epoch FROM replay_lag)),0) FROM pg_stat_replication;")
  done
  ./bin/apply-batch "$batch"    # idempotent; safe to retry from the cursor
done < batches.txt

The alert rule is the safety net for everything the throttle cannot see — a replica that drops out of pg_stat_replication entirely, or a monitor loop that dies and leaves a stale metric. Wire it to page during the deploy, and add a freshness guard so a dead sampler cannot masquerade as zero lag.

# Prometheus alerting rules — evaluated while the backfill runs
# Context: configuration; routes must reach on-call during the backfill window.
groups:
  - name: backfill-replication-lag
    rules:
      - alert: MigrationReplicationLagHigh
        expr: replica_replay_lag_seconds > 5
        for: 30s
        labels: { severity: page }
        annotations: { summary: "Backfill outrunning replica by s" }
      - alert: BackfillLagSamplerStale
        expr: time() - push_time_seconds{job="migration_backfill"} > 30
        for: 0s
        labels: { severity: page }
        annotations: { summary: "Lag sampler stale — a dead monitor reads as zero lag" }
Lag-aware backfill control loop with alert backstop The runner throttles itself at 2s and the Prometheus alert at 5s is the backstop when the throttle fails. next batch from cursor read lag MAX(replay_lag) lag > 2s? throttle no apply batch idempotent yes · over budget sleep 5s push metric Prometheus lag metric alert > 5s backstop page on-call throttle at 2s fires before the alert at 5s
The runner throttles itself at 2 seconds so it paces the slowest replica; the Prometheus alert at 5 seconds is the backstop that pages only when the throttle or its sampler has failed.

For the reasoning behind picking the throttle threshold below the alert budget, and how batch size trades off against lag headroom, follow the depth treatment in Migration Observability & Monitoring, which places lag alerting alongside lock-wait and per-phase-metric signals.

Verification Checklist

Up one level: Migration Observability & Monitoring covers the full signal set — lock waits, replication lag, and per-phase migration metrics — that this page’s lag alerting slots into.

Frequently Asked Questions

What lag threshold should I alert on during a backfill? Set it from your read-your-writes requirement, not a round number. If any code path reads from a replica immediately after writing to the primary, your alert budget is roughly the maximum staleness that path tolerates — often 2–5 seconds. Then put the backfill’s self-throttle threshold below that (say 2 seconds when the alert is 5), so the job pauses on its own and the page only fires when the throttle itself has failed. A single global threshold across all replicas measured as the MAX lag is enough; you care about the worst replica, because that is the one serving stale reads.

Why does MySQL’s Seconds_Behind_Source read 0 and then jump during a backfill? Because it measures apply lag relative to events the replica has already fetched, not events still in flight. A large batched transaction sits in the fetch phase reporting 0, then the whole transaction lands and the value leaps to its true size. This saw-tooth fools a naive threshold. The fix is twofold: keep each backfill batch small so no single transaction is large enough to hide, and read performance_schema.replication_applier_status_by_worker for per-worker apply progress that reflects reality between the jumps.

Is it safe to pause or kill a backfill when lag spikes? Yes, provided the backfill is idempotent and cursor-driven. Each batch commits independently and the job records how far it got, so stopping between batches loses only forward progress, never correctness — the next run resumes from the persisted cursor and re-running a completed batch is a no-op. Build this property in with idempotent script design before you rely on the throttle, because an auto-pause is only as safe as the job’s ability to resume cleanly.

Related Articles