Migration Observability & Monitoring

A gated, tested migration can still fail after it starts. The pull-request checks proved the script was backward compatible and held no exclusive lock against a snapshot, but production has traffic the snapshot did not, a replica the CI database did not, and a table that grew ten percent since the last restore. Observability is the discipline of watching a migration while it runs against the real system — reading lock waits and replication lag live, emitting a metric for every phase so an SLO can judge it, and firing an alert the moment a backfill or DDL step drifts outside its budget. It addresses the deploy and backfill phases of the migration lifecycle, and it serves the on-call engineer who gets paged at 03:00, the DBA who has to decide in ninety seconds whether to let an ALTER finish or kill it, and the platform team that wants a migration to be as observable as any other production change. Gating stops the obviously-unsafe migration before it ships; observability catches the subtly-unsafe one after it starts, while there is still time to abort.

This work sits under CI/CD & Migration Automation, which treats the deployment pipeline as the unit of safety. Where Migration Pipeline Gating refuses a bad migration at review time and Automated Migration Testing proves lock behavior against a production-size snapshot, this page assumes the migration has passed both and is now live. The question here is narrower and more urgent: once DDL is executing against the primary and a backfill is chewing through rows, how do you see that it is going wrong in time to stop it, and how do you turn that runtime signal into an alert instead of a surprise?

Concept & Mechanism

Migration observability rests on three signal families, each read from a different place in the database engine, and each meaningful only in real time.

Lock waits are the first family. A migration statement acquires a lock; if another session already holds an incompatible lock on the same object, the migration waits. On PostgreSQL an ALTER TABLE that takes an ACCESS EXCLUSIVE lock will queue behind every open transaction touching that table, and — critically — will itself block every new query that arrives while it waits. That queue is the outage. The signal lives in pg_stat_activity (via the wait_event_type and wait_event columns) and in pg_locks, which shows granted versus ungranted lock requests. On MySQL 8.0 the equivalent lives in performance_schema.data_lock_waits and the information_schema.INNODB_TRX view. The mechanism to watch for is not “is the migration running” but “is anything waiting behind the migration” — a long lock the migration holds is only a problem when queries pile up against it.

Replication lag is the second family, and it dominates the backfill phase. Every batched UPDATE a backfill runs against the primary must be shipped to and applied by each replica. When the backfill writes faster than the slowest replica can apply, lag grows without bound, read replicas serve stale data, and any read-your-writes guarantee breaks. PostgreSQL exposes this as the byte delta between pg_current_wal_lsn() on the primary and pg_last_wal_replay_lsn() on each standby, or more directly through the replay_lag interval in pg_stat_replication. MySQL exposes it as Seconds_Behind_Source in SHOW REPLICA STATUS, though the lag-aware backfill described in Backfill Optimization should read it from performance_schema.replication_applier_status_by_worker for per-worker accuracy. The signal is a control input, not just a dashboard number: the backfill throttle reads it and pauses when it climbs.

Migration metrics are the third family — the events the migration itself emits. A gate or a database view tells you what the engine is doing; a metric tells you what the migration is doing: which phase it entered, how many rows a backfill has processed, how long each DDL step held its lock, whether the step succeeded or was aborted. These are emitted by the migration runner as counters and gauges (to Prometheus, StatsD, or an OpenTelemetry collector) and are what make a migration a first-class, measurable deploy. They are the raw material for the service-level objectives that let you say, with a number rather than a feeling, whether a migration met its budget.

The unifying idea is a budget per signal: a maximum lock-wait duration, a maximum replication lag, and a maximum backfill runtime, each agreed before the migration runs. Observability is the machinery that measures the live value against the budget and reacts — surface it on a dashboard, and page when it is breached.

Prerequisites & Decision Criteria

Live migration monitoring earns its wiring when a migration can run long enough to be watched and can hurt production while it does. A metadata-only column add that returns in milliseconds needs a metric but not a live dashboard; a CREATE INDEX CONCURRENTLY on a 400-million-row table, or a backfill that runs for six hours, needs all three signal families instrumented before it starts. The checklist and table below decide how much to instrument.

• A read-only monitoring role exists that can read pg_stat_activity, pg_locks, and pg_stat_replication (or the MySQL performance_schema equivalents) without any write or DDL grant.
• The migration runner can emit metrics to your telemetry backend (Prometheus pushgateway, StatsD, or an OpenTelemetry collector) from the environment where migrations execute.
• A lock-wait budget, a replication-lag budget, and a maximum-runtime budget are agreed and written down, so each alert rule has a threshold to test against.
• Replicas expose their lag to the monitoring role; on managed databases confirm the provider surfaces replay_lag or Seconds_Behind_Source rather than only a coarse dashboard metric.
• An alert route exists that can page the on-call engineer during a deploy, not just file a ticket, because a lock-wait breach is minutes-urgent.
• The migration runs outside a maintenance window against live traffic — if it runs during a full lockout, live monitoring matters less and post-hoc metrics suffice.

If you instrument only one signal, instrument replication lag — an unthrottled backfill is the most common way a migration that passed every gate still degrades production. If you instrument two, add lock waits, because a blocked-query pileup turns a slow DDL step into a full outage in seconds.

Step-by-Step Procedure

This procedure stands up live monitoring for a single high-risk migration: a concurrent index build followed by a batched backfill. Wire the read-only role and the metric emitter once, then reuse them for every migration.

1. Provision a read-only monitoring role. The dashboards and alert queries must read live stats without any capacity to write or lock.

-- PostgreSQL · run as a superuser/owner once · grants are persistent, review before production
CREATE ROLE mig_monitor LOGIN PASSWORD 'rotate-me';
GRANT pg_monitor TO mig_monitor;   -- exposes pg_stat_activity, pg_stat_replication, pg_locks fully
-- No INSERT/UPDATE/DELETE/DDL — this role observes, it never changes state.

Verify before proceeding: connect as mig_monitor and confirm SELECT * FROM pg_stat_replication returns rows but CREATE TABLE t(x int) is denied.

2. Start the migration with a bounded lock wait and a step timer. A migration you intend to watch must fail fast if it cannot acquire its lock, so a stuck step surfaces as an error instead of an invisible queue.

-- PostgreSQL · run as the migration role · CREATE INDEX CONCURRENTLY must run OUTSIDE a transaction
-- Context: production primary, live traffic; lock_timeout makes a contended DDL step fail fast.
SET lock_timeout = '3s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_region ON orders (region_code);

Verify before proceeding: the statement either completes or returns ERROR: canceling statement due to lock timeout within seconds — it never hangs silently.

3. Watch lock waits live in a second session. While the DDL runs, poll for anything waiting behind the migration. This is the query that tells you whether a slow step is merely slow or is actively blocking traffic.

-- PostgreSQL · run as mig_monitor in a separate session while the migration runs
-- Context: read-only diagnostics; safe anywhere; shows blocked PIDs and the blocker.
SELECT w.pid AS waiting_pid, w.query AS waiting_query,
       b.pid AS blocking_pid, b.query AS blocking_query,
       now() - w.query_start AS waited_for
FROM pg_stat_activity w
JOIN pg_locks wl ON wl.pid = w.pid AND NOT wl.granted
JOIN pg_locks bl ON bl.relation = wl.relation AND bl.granted
JOIN pg_stat_activity b ON b.pid = bl.pid AND b.pid <> w.pid
WHERE w.wait_event_type = 'Lock'
ORDER BY waited_for DESC;

Verify before proceeding: during a healthy online build this returns zero rows; a growing result set with rising waited_for is the signal to abort the DDL step.

4. Sample replication lag on a fixed interval. Before the backfill starts writing, begin recording lag so the throttle and the alert both have a live number.

# bash · run from a host that can reach the primary as mig_monitor
# Context: read-only; loop pauses the backfill's caller if lag exceeds the budget.
while true; do
  lag=$(psql "$PRIMARY_URL" -tAc \
    "SELECT COALESCE(MAX(EXTRACT(epoch FROM replay_lag)),0) FROM pg_stat_replication;")
  echo "replica_replay_lag_seconds $lag" | curl --data-binary @- \
    "$PUSHGATEWAY/metrics/job/migration_backfill"
  awk "BEGIN{exit !($lag > 2.0)}" && echo "LAG BREACH: ${lag}s" >&2
  sleep 5
done

Verify before proceeding: the pushgateway shows a fresh replica_replay_lag_seconds sample every five seconds, and a deliberate write burst makes the value climb.

5. Emit a metric for every migration phase. Wrap each phase so the runner records its start, duration, row count, and outcome. These metrics are what an SLO later judges.

# bash · migration runner wrapper · emits to StatsD-compatible collector
# Context: runs in the migration job; label every metric with the migration version.
emit() { echo "migration.$1:$2|$3|#version:${MIG_VERSION}" | nc -u -w0 "$STATSD_HOST" 8125; }

emit "phase_start" 1 "c"                          # counter: phase entered
start=$(date +%s)
./bin/backfill --table orders --column region_code --batch 1000 --max-lag-seconds 2
emit "backfill_duration_seconds" "$(( $(date +%s) - start ))" "g"   # gauge
emit "phase_success" 1 "c"                        # counter: phase completed cleanly

Verify before proceeding: after a test run, the collector shows migration.phase_start, migration.backfill_duration_seconds, and migration.phase_success all tagged with the migration version.

6. Wire an alert on each budget. Turn the three live signals into alert rules that page during the deploy, not a report you read afterward.

# Prometheus alerting rules — evaluated while the migration runs
# Context: configuration; the routes must reach on-call during a deploy window.
groups:
  - name: migration-observability
    rules:
      - alert: MigrationReplicationLagHigh
        expr: replica_replay_lag_seconds > 5
        for: 30s
        labels: { severity: page }
        annotations: { summary: "Backfill outrunning replica by s" }
      - alert: MigrationLockWaitPileup
        expr: pg_blocked_backends > 5
        for: 15s
        labels: { severity: page }
        annotations: { summary: " queries blocked behind a migration lock" }

Verify before proceeding: force a lag spike on staging and confirm the MigrationReplicationLagHigh alert routes to the on-call pager within its for window.

Verification & Observability

The point of this section is recursive: you must observe that your observability actually works before you trust it during an incident. Confirm each signal produces a live, correct reading against the running migration.

On PostgreSQL, the single most useful live query counts blocked backends — the number that turns “a lock is held” into “the lock is hurting us”:

-- PostgreSQL · run as mig_monitor during the migration
-- Context: read-only; a non-zero, rising count is the abort signal for a DDL step.
SELECT count(*) AS blocked_backends
FROM pg_stat_activity
WHERE wait_event_type = 'Lock' AND state = 'active';

On MySQL 8.0, confirm the backfill is not accumulating lock waits and read the replica lag directly:

-- MySQL 8.0 · run as the monitoring user during the migration
-- Context: read-only; data_lock_waits requires performance_schema enabled.
SELECT COUNT(*) AS waiting_trx FROM performance_schema.data_lock_waits;
SHOW REPLICA STATUS\G   -- read Seconds_Behind_Source for the live lag

Treat every migration as an observable deploy and confirm the following hold before you rely on the setup:

• The blocked-backends query returns zero during a healthy online index build and climbs during a deliberately contended one.
• replica_replay_lag_seconds appears in the metrics backend within one scrape interval of the backfill starting.
• Every phase emits a phase_start and a matching phase_success or phase_abort, tagged with the migration version.
• A forced lag spike on staging fires MigrationReplicationLagHigh and pages the correct on-call route.
• The lock-wait dashboard distinguishes a lock the migration holds from queries waiting behind it — only the second is an alert.
• Backfill throughput (rows/sec) is visible live, so a stalled backfill is distinguishable from a slow one.

The deep version of the lock-wait watch — reading pg_stat_activity correctly, interpreting wait_event values, and deciding when to cancel a step — is covered in Monitoring Long-Running Migrations in Production, and the lag-specific alerting during a backfill has its own treatment in Alerting on Replication Lag During Backfills.

Rollback Path

Observability’s “rollback” has two faces: aborting the migration step the monitoring caught going wrong, and backing out an alert rule that turned out to be noisy. Both must be safe to do live.

Aborting a migration step is the urgent one. When the blocked-backends count is climbing and the DDL step is the blocker, cancel that step, not the database session at random. On PostgreSQL, cancel the migration’s backend cleanly:

-- PostgreSQL · run as mig_monitor (needs pg_signal_backend) or the migration role
-- Context: production; pg_cancel_backend stops the statement without killing the connection.
SELECT pg_cancel_backend(pid)          -- gentle: cancels the running statement
FROM pg_stat_activity
WHERE query LIKE 'CREATE INDEX%idx_orders_region%' AND state = 'active';
-- Escalate to pg_terminate_backend(pid) only if cancel does not release the lock.

A canceled CREATE INDEX CONCURRENTLY leaves an invalid index behind rather than a partial one; the safe follow-up is DROP INDEX CONCURRENTLY IF EXISTS idx_orders_region;, which itself takes no exclusive lock. A canceled backfill is safe to stop at any batch precisely because it follows Idempotent Script Design — every batch is re-runnable, so stopping mid-run loses no correctness, only progress. Rollback is safe under one condition: the aborted step was additive and idempotent. If the monitoring caught a destructive step going wrong, cancellation is not enough and the incident escalates to the data-preserving reversal in Rollback Automation.

Backing out a noisy alert is the calmer face. Demote the rule from severity: page to severity: ticket rather than deleting it, so the signal stays visible while you retune the threshold. Never delete a migration alert during an active deploy — a quiet dashboard during a migration is indistinguishable from a healthy one, and that ambiguity is exactly what observability exists to remove.

Common Errors & Fixes

ERROR: canceling statement due to lock timeout. The DDL step could not acquire its lock within lock_timeout because an open transaction held the table. Root cause: a long-running query or an idle-in-transaction session blocked the ALTER, exactly the pileup the monitoring is meant to surface. Fix by finding the blocker with the lock-wait query, ending the idle transaction, and re-running the step — this is a feature, not a failure, because failing fast beat hanging silently.

replay_lag is NULL in pg_stat_replication. The lag column is null when no WAL has been replayed since the last checkpoint, or when the standby is not streaming. Root cause: the monitoring role read the primary during an idle moment, or a replica is disconnected. Fix by falling back to the LSN byte delta, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn), which is defined even when the interval is null, and alert separately on a replica dropping out of pg_stat_replication entirely.

Seconds_Behind_Source on MySQL sticks at 0 during a heavy backfill, then jumps. The value reports lag only relative to events the replica has received, so a replica still fetching a large transaction reports 0 until it starts applying. Root cause: Seconds_Behind_Source measures apply lag, not fetch lag, and a big batched write hides inside one transaction. Fix by shrinking the backfill batch size so each transaction is small, and reading performance_schema.replication_applier_status_by_worker for per-worker apply progress.

Prometheus shows a flat replica_replay_lag_seconds line that never updates. The pushgateway kept the last sample after the backfill loop exited, so a dead monitor looks like zero lag. Root cause: pushed metrics are sticky and outlive the job that pushed them. Fix by adding a push_time_seconds freshness check to the alert (time() - push_time_seconds{job="migration_backfill"} > 30) so a stale sample pages instead of reassuring.

The lock-wait alert never fires even though queries were visibly blocked. The alert keyed on the migration holding a lock rather than on backends waiting for one. Root cause: holding an ACCESS EXCLUSIVE lock is normal for a fraction of a second; the harmful state is a queue behind it. Fix by alerting on pg_blocked_backends (waiting count) rather than on lock presence, so the rule matches the outage condition, not the routine one.

Child Page Index

Three focused areas build on this foundation, each taking one signal family from dashboard to production runbook. Monitoring Long-Running Migrations in Production goes deep on the live lock-wait watch — reading pg_stat_activity and pg_locks under real traffic, telling a lock held from a queue formed behind it, and deciding the exact moment to cancel a stalling DDL step before the pileup becomes an outage. Alerting on Replication Lag During Backfills covers the backfill phase in detail — sampling replay_lag and Seconds_Behind_Source correctly, wiring a lag-aware throttle that pauses the job before the alert fires, and routing the page so an on-call engineer catches a lag spike while it is still recoverable. Tracking Schema Migration Metrics and SLOs turns the runner’s emitted events into service-level objectives — defining a migration’s duration and success-rate budgets, computing an error budget across releases, and reporting whether your migration process meets its own numbers.

To place this monitoring back in the pipeline it protects, return to CI/CD & Migration Automation for the end-to-end deploy contract, where gating, testing, observability, and rollback compose into a single safe path from pull request to production.

Frequently Asked Questions

When should I watch a migration live versus just record its metrics afterward? Watch live whenever a step can run long enough to be aborted and can hurt production while it runs — a concurrent index build on a large table, or any backfill against live traffic. For those, a human or an alert must be able to intervene mid-run. A metadata-only change that returns in milliseconds cannot be meaningfully watched; record its metric for the SLO and move on. The deciding question is whether there is a window between “going wrong” and “too late” long enough to act in.

What is the single most important signal to alert on? Blocked backends for DDL, replication lag for backfills. Both measure the collateral damage a migration is causing to everything else, which is what an outage actually is. Alerting on “the migration is running” or “the migration holds a lock” produces noise, because both are normal; alerting on “queries are queuing behind the migration” or “replicas are falling behind” matches the real failure condition and fires only when it matters.

Why alert on queries waiting behind a lock rather than on the lock itself? Because holding an ACCESS EXCLUSIVE lock is routine and brief — a fast ALTER takes and releases it in milliseconds and harms nothing. The outage begins only when the lock is held long enough that new queries queue behind it. An alert on lock presence fires constantly and gets muted; an alert on pg_blocked_backends fires exactly when the queue forms, which is the moment you need to decide whether to cancel the step.

Can I reuse my application’s existing dashboards for migration monitoring? Partly. Application dashboards already show error rate and latency, and a migration going wrong will move those — but by then the collateral damage is live traffic degrading. Migration-specific instrumentation reads the cause (lock waits, replication lag, backfill throughput) rather than the symptom (rising latency), giving you minutes of lead time. Keep the application dashboards as the outcome check and add the database-signal dashboards as the early warning.