Tracking Schema Migration Metrics and SLOs

Someone asks in the incident review why last Tuesday’s deploy felt slow, and nobody can answer with a number. The migration ran, the deploy went green, the graphs of application latency show a bump but no one can tie it to the schema change — because the migration emitted nothing. It held an ACCESS EXCLUSIVE lock for eleven seconds, rewrote four million rows, and ran ninety seconds longer than the release before it, and none of that is recorded anywhere you can query. The next migration might be twice as slow and you would only find out when it pages someone. The fix is to treat every migration as a measured event: emit its duration, its lock-hold time, and its rows-affected count as first-class metrics, then define a service-level objective against those metrics so a regression shows up as a budget breach on a dashboard instead of a hunch in a retro. This page shows the exact instrumentation and the SLO math that turn “the migration felt slow” into “migration V231 breached the 30-second duration objective and burned 4% of this quarter’s error budget.”

Symptom / Error Signatures

The signature of an un-instrumented migration process is an absence, not an error. You notice it when you try to answer a question and cannot. “Is our migration duration trending up?” — no series exists to plot. “Which migration held the longest lock this quarter?” — the number was never captured, so the honest answer is a shrug.

When instrumentation exists but the SLO is missing or misconfigured, the signatures are concrete. A Prometheus query returns nothing because the metric name was never registered:

# PromQL · run in the metrics explorer · read-only
# Symptom: this returns "no data" — the migration runner never emitted the series.
histogram_quantile(0.95, rate(migration_duration_seconds_bucket[30d]))

A recording rule that computes the SLO fails to load with error parsing metric selector because the label set on the emitted metric does not match what the rule expects — usually the migration version tag is missing. A Grafana panel shows a flat line at the last pushed value because a pushgateway kept a stale sample after the job exited, the same stickiness that bites live lag monitoring. And the most expensive signature of all is a post-incident finding that reads “migration duration had regressed 3x over six releases and no alert fired,” which means the metrics were emitted but no objective was ever defined to judge them against.

Root Cause Analysis

There are two distinct failures that produce the same “we can’t see it” outcome, and they need different fixes. The first is a measurement gap: the migration runner completes work but emits no telemetry, so there is nothing to judge. The second is a specification gap: the runner emits rich metrics but no one has written down what “good” means, so the numbers accumulate without ever tripping a threshold. An SLO program needs both halves — a signal to measure and an objective to measure it against.

The three metrics worth emitting per migration are chosen because each maps to a distinct failure mode. Duration captures total wall-clock time and is the headline number an SLO defends; a rising duration trend is the earliest sign a migration is outgrowing its safe envelope. Lock-hold time — how long the step actually held a blocking lock — matters more than duration for outage risk, because a ten-minute CREATE INDEX CONCURRENTLY that never blocks a query is safe while a two-second ACCESS EXCLUSIVE rewrite on a hot table is not; this is the same distinction between a lock held and a queue formed behind it that monitoring long-running migrations in production reads live from pg_stat_activity. Rows-affected captures the write volume a backfill pushed, which is the driver of the replication lag that alerting on replication lag during backfills watches; a migration whose rows-affected jumps between releases will pressure replicas even if its duration looks stable.

Where the two databases diverge is in how you read lock-hold time after the fact, so an emitter that hardcodes one engine’s source silently records nothing on the other:

The deeper root cause behind most missing-metric incidents is that migrations are treated as a side effect of a deploy rather than a deploy in their own right. The application’s request metrics are instrumented to death; the schema change that can take the whole application down emits nothing. Closing that gap is the entire job.

Immediate Mitigation

If you are mid-incident and need to reconstruct what a migration did — because it just ran and you have no recorded metric — pull the numbers straight from the engine before the evidence ages out.

1. Capture the duration and rows-affected of the step that just ran from the runner’s own log or the client return. If your runner did not record it, the migration tool’s history table usually did.

-- PostgreSQL · run as the migration or monitoring role · read-only
-- Context: Flyway/Liquibase history retains execution_time; read it now, before rows churn.
SELECT version, description, execution_time AS duration_ms, installed_on
FROM flyway_schema_history
ORDER BY installed_rank DESC
LIMIT 5;

2. Recover the lock-hold time if the migration is still running or just finished, by reading the age of any lock its backend still holds.

-- PostgreSQL · run as a role with pg_monitor · read-only, safe during an incident
-- Context: shows how long the migration backend has held each lock right now.
SELECT l.pid, l.mode, l.relation::regclass AS table,
       now() - a.xact_start AS held_for
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.granted AND a.query ILIKE 'ALTER TABLE%' OR a.query ILIKE 'CREATE INDEX%'
ORDER BY held_for DESC;

3. Backfill a single metric sample by hand so the incident timeline has a data point, pushing it with the same job label your dashboards already expect.

# bash · run from a host that can reach the pushgateway · one-shot
# Context: reconstruct one sample so the SLO calculation is not silently missing a run.
cat <<EOF | curl --data-binary @- "$PUSHGATEWAY/metrics/job/migration/instance/V231"
migration_duration_seconds{version="V231"} 101.4
migration_lock_hold_seconds{version="V231"} 11.2
migration_rows_affected{version="V231"} 4013220
EOF

4. Write down the observed numbers as the provisional SLO baseline if none existed — even a single migration’s duration and lock-hold time is a better objective than none, and the next section makes it permanent.

Permanent Fix / Long-Term Pattern

The durable pattern has two moving parts wired once and reused by every migration: an emitter that wraps each step, and a set of recording rules plus an alert that turn the raw metrics into an SLO judgment. Both belong to the broader migration observability discipline, which composes gating, live monitoring, and these after-the-fact metrics into one contract.

Wrap every migration step in an emitter that records all three metrics plus the outcome, tagged with the migration version so each release is a distinct series:

# bash · migration runner wrapper · emits to a Prometheus pushgateway
# Context: runs inside the migration job; MIG_VERSION is the release tag for every series.
run_step() {
  local start rows dur
  start=$(date +%s.%N)
  rows=$("$@")                                   # step prints its rows-affected on stdout
  dur=$(echo "$(date +%s.%N) - $start" | bc)
  cat <<EOF | curl --silent --data-binary @- "$PUSHGATEWAY/metrics/job/migration/instance/${MIG_VERSION}"
migration_duration_seconds{version="${MIG_VERSION}"} ${dur}
migration_rows_affected{version="${MIG_VERSION}"} ${rows}
migration_outcome{version="${MIG_VERSION}",result="success"} 1
EOF
}

Capture lock-hold time from the engine rather than guessing it, because wall-clock duration and lock-hold time are different numbers and the SLO defends the second:

-- PostgreSQL · run as the migration role immediately after a DDL step · read-only
-- Context: log_lock_waits + a granted-lock age sample give the true blocking duration.
SET log_lock_waits = on;   -- session scope; logs any wait longer than deadlock_timeout
-- After the step, the runner reads the max granted-lock age it recorded and emits it as
-- migration_lock_hold_seconds{version="..."}.

With the metrics flowing, define the SLO as a recording rule and an error-budget alert. An SLO here is a target — “95% of migrations complete within 30 seconds and hold no blocking lock longer than 5 seconds over a rolling quarter” — and the error budget is the allowed 5% that may miss it:

# Prometheus rules — recording rule computes the SLO, alert fires on budget burn
# Context: configuration; the alert must reach the platform team, not page on-call at 03:00.
groups:
  - name: migration-slo
    rules:
      - record: migration:duration_slo:ratio_30d
        expr: |
          sum(rate(migration_duration_seconds_bucket{le="30"}[30d]))
          / sum(rate(migration_duration_seconds_count[30d]))
      - alert: MigrationDurationSLOBurn
        expr: migration:duration_slo:ratio_30d < 0.95
        for: 1h
        labels: { severity: ticket }
        annotations: { summary: "Migration duration SLO at {{ $value | humanizePercentage }} — below 95% target" }

The point of the objective is not to page someone in the night — a slow migration is rarely a wake-you-up emergency — but to make a trend visible before it becomes an incident. When migration:duration_slo:ratio_30d slips from 0.99 to 0.94 across a quarter, that is a table outgrowing its migration strategy, and it is a scheduling conversation, not a fire. Pair the duration SLO with a lock-hold objective, because the two protect different things: duration protects deploy speed, lock-hold protects the availability of everything querying the table while the migration runs.

Verification Checklist

• Every migration emits migration_duration_seconds, migration_lock_hold_seconds, and migration_rows_affected, each tagged with the migration version.
• Lock-hold time is read from the engine (pg_locks / performance_schema.metadata_locks), not approximated from wall-clock duration.
• A written SLO exists with an explicit target and window (e.g. 95% of migrations under 30s over a rolling 30 days) and a named owner.
• A recording rule computes the SLO ratio and a dashboard plots it as a trend, not just a current value.
• A pushed-metric freshness check (time() - push_time_seconds) guards against a stale sample reading as a passing migration.
• The SLO-burn alert routes to the team that owns migration strategy as a ticket, distinct from the minutes-urgent live alerts.

For the live counterpart to these after-the-fact metrics — watching lock waits and replication lag while the migration runs so you can abort it — return to the parent topic, Migration Observability & Monitoring, which frames metrics, live monitoring, and alerting as one system.

Frequently Asked Questions

Should an SLO breach page the on-call engineer? Almost never. A duration or lock-hold SLO measures a slow trend across many releases, not a live outage, so its breach belongs in a ticket routed to whoever owns migration strategy — a scheduling and re-architecture signal, not a 03:00 page. The minutes-urgent conditions (a query pileup behind a lock, a replica falling behind a backfill) are handled by the live alerts described in the parent topic and should page; keep the two routes distinct so a slow-trend ticket never competes with a real incident for attention.

What is the difference between migration duration and lock-hold time, and why track both? Duration is total wall-clock time for the step; lock-hold time is how long it actually held a blocking lock. They diverge sharply for online operations: a CREATE INDEX CONCURRENTLY can run for ten minutes of duration while holding no blocking lock, so it is safe despite being long. A two-second ALTER TABLE rewrite has tiny duration but holds ACCESS EXCLUSIVE the whole time, blocking every query. Duration defends deploy speed; lock-hold defends availability. An SLO on duration alone would pass the dangerous migration and flag the safe one.

How do I set the initial SLO target when I have no history? Start descriptive, not aspirational. Instrument first, let ten to twenty migrations flow, then read the p95 of the observed duration and lock-hold distributions and set the target a little above the current p95 so today’s normal migrations pass. An SLO that fails on every run is noise that gets muted; one set just above real behavior catches genuine regressions while tolerating normal variance. Tighten it deliberately over quarters as you improve the migration strategy, treating each tightening as an explicit decision rather than a number picked on day one.