Monitoring Long-Running Migrations in Production

The migration passed every gate, the deploy went green, and then it just… kept going. Ten minutes in, the ALTER TABLE is still running, the on-call dashboard shows p95 latency climbing, and the Slack channel is filling with “is anyone touching the database?” You have no idea whether the statement is making progress, stuck behind another session’s lock, or holding a lock that is quietly strangling every query behind it. The decision in front of you is brutal and time-boxed: let it finish, or kill it — and killing the wrong thing can be worse than waiting. This page is the live-migration watch: how to read pg_stat_activity and MySQL’s SHOW PROCESSLIST under real traffic, tell a lock the migration holds from a queue forming behind it, read what progress views exist, and abort the step cleanly before the pileup becomes an outage.

Symptom / Error Signatures

You are on the right page if a migration step has been running longer than you budgeted and one or more of these signals is present. In PostgreSQL, a monitoring query against pg_stat_activity shows your migration backend with state = 'active' and a non-empty wait_event_type of Lock — the statement is not working, it is waiting. Worse is when other backends show wait_event_type = 'Lock' with a query_start that keeps aging: those are ordinary application queries queued behind whatever your migration is holding. On a heavily contended ALTER TABLE you will eventually see the client abort with:

ERROR:  canceling statement due to lock timeout

or, if no lock_timeout was set and a deadlock formed, ERROR: deadlock detected.

On MySQL 8.0 the same story reads out of SHOW PROCESSLIST (or information_schema.PROCESSLIST): the migration thread sits in State: Waiting for table metadata lock while a swarm of application threads pile up in the same state behind it. The InnoDB engine surfaces the mirror image in SHOW ENGINE INNODB STATUS under TRANSACTIONS, and a step that exceeds lock_wait_timeout returns:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The signature that must trigger action is not “the migration is slow” — a CREATE INDEX CONCURRENTLY on a large table is supposed to be slow. It is “queries are queuing behind the migration,” a rising count of blocked backends. A slow step that blocks nothing is fine to let run; a slow step with a growing queue behind it is an outage in progress.

Reading the lock queue in pg_stat_activity The blocking session holds a lock; the migration and application backends queue behind it in Lock wait state, their waited_for ages shrinking toward the newest arrival. One holds, everything else waits pid query wait_event waited_for 7710 idle in transaction — holds the lock (granted) 12m 40s blocker 8842 ALTER TABLE orders … Lock 9m 30s migration 9001 SELECT … FROM orders Lock 6m 12s 9014 UPDATE orders SET … Lock 4m 03s 9020 SELECT … FROM orders Lock 1m 40s Four backends waiting on one lock — the growing queue, not the slow ALTER, is the outage.
The readout that decides the incident: one session holds the lock, the migration waits on it, and every application query arriving after lines up behind — the shrinking waited_for ages show the queue still growing.

Root Cause Analysis

A long-running migration hurts production through one of two mechanisms, and telling them apart decides what you do next. The first is that the migration is blocked: it wants a lock another session already holds, so it waits, and — critically — on PostgreSQL an ALTER TABLE requesting ACCESS EXCLUSIVE queues behind the current lock holder while itself blocking every new query that arrives, so one idle-in-transaction session upstream freezes the whole table. The second is that the migration is the blocker: it acquired its exclusive lock and is holding it for the full duration of a table rewrite, and everything else waits on it. The remedy differs — for the first you clear the upstream holder, for the second you must decide whether to abort the migration itself.

The signals live in different places on each engine, and the columns you read to make the call diverge:

Question PostgreSQL MySQL 8.0
Is my step waiting or working? pg_stat_activity.wait_event_type / wait_event PROCESSLIST.State (e.g. Waiting for table metadata lock)
Who blocks whom? pg_locks (granted vs ungranted) + pg_blocking_pids(pid) performance_schema.data_lock_waits + data_locks
How many queries are queued? count(*) where wait_event_type = 'Lock' count of threads in a waiting State
How much work is left? pg_stat_progress_create_index, pg_stat_progress_copy SHOW ENGINE INNODB STATUS (rough), performance_schema stage events
How do I stop it? pg_cancel_backend(pid) → pg_terminate_backend(pid) KILL QUERY <id> → KILL <id>

PostgreSQL’s advantage is the pg_stat_progress_* family: since v12, pg_stat_progress_create_index reports the current phase and blocks_done / blocks_total, and pg_stat_progress_copy reports tuples_processed for a COPY-backed load. MySQL has no first-class DDL progress view; the honest answer to “how much is left” is estimated from the table’s size against InnoDB’s rows-per-second rate, or read from performance_schema stage events if instrumentation is enabled. This is why online-DDL tools like gh-ost and pt-online-schema-change emit their own progress line — they exist partly to fill that observability gap. Understanding which lock class a step takes is the same classification you rehearse in migration-test lock regression checks; production is where you confirm the test was right.

Blocked versus blocker: one query, two remedies pg_blocking_pids on the migration's PID distinguishes the two mechanisms; an empty result means the migration is the blocker, a non-empty result names the upstream holder to clear. Is the migration the victim or the cause? SELECT pg_blocking_pids(<migration_pid>); — what is this backend waiting on? non-empty → ← empty Migration is BLOCKED waiting on someone else's lock Migration is the BLOCKER holds the exclusive lock; queue behind Clear the upstream holder terminate the idle-in-transaction session; the migration proceeds Decide whether to abort it read the progress view first, then cancel-then-terminate the migration Same slow step, opposite remedy — the blocking-PID answer decides which one.
One query settles the fork: if pg_blocking_pids names an upstream session, the migration is a victim — clear the holder; if it returns empty, the migration itself holds the lock and you decide whether to abort.

Immediate Mitigation

You have a live, long-running step and a growing queue. Work these in order; each command notes the session it runs in and the permission it needs.

  1. See who is waiting and who is blocking. Run this from a separate read-only session so you never add to the contention. It names the blocked query, the blocker, and how long the wait has aged.
-- PostgreSQL · run as a pg_monitor role in a SEPARATE session · read-only, safe anywhere
SELECT w.pid                        AS waiting_pid,
       now() - w.query_start        AS waited_for,
       left(w.query, 60)            AS waiting_query,
       b.pid                        AS blocking_pid,
       left(b.query, 60)            AS blocking_query
FROM pg_stat_activity w
JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid) ON true
JOIN pg_stat_activity b ON b.pid = bp.pid
WHERE w.wait_event_type = 'Lock'
ORDER BY waited_for DESC;
  1. Read the progress view before you decide. If the migration is the blocker but is genuinely almost done, waiting may beat aborting. PostgreSQL tells you concretely for an index build:
-- PostgreSQL · run as pg_monitor · read-only · only populated during CREATE INDEX
SELECT phase,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct_done,
       tuples_done, tuples_total
FROM pg_stat_progress_create_index;
  1. On MySQL, list the metadata-lock pileup. There is no progress view, so the decision leans on the queue depth and how long the DDL thread has held its lock.
-- MySQL 8.0 · run as a user with PROCESS + performance_schema access · read-only
SELECT p.id, p.time AS secs_in_state, p.state, left(p.info, 60) AS query
FROM information_schema.PROCESSLIST p
WHERE p.command <> 'Sleep'
ORDER BY p.time DESC;
-- Cross-check the actual lock graph:
SELECT * FROM performance_schema.data_lock_waits\G
  1. Abort the step cleanly if the queue is growing and progress is not. Cancel the statement first (gentle); escalate to terminating the connection only if the cancel does not release the lock. Target the migration’s own PID — never kill at random.
-- PostgreSQL · run as pg_monitor (needs pg_signal_backend) or the migration role · production
SELECT pg_cancel_backend(<migration_pid>);      -- gentle: cancels the running statement
-- Only if the lock is still held after a few seconds:
SELECT pg_terminate_backend(<migration_pid>);   -- forceful: drops the whole connection
-- MySQL 8.0 · run as a user with CONNECTION_ADMIN · production
KILL QUERY <thread_id>;   -- gentle: aborts the statement, keeps the session
KILL <thread_id>;         -- forceful: drops the connection
  1. Clean up what the abort left behind. A cancelled CREATE INDEX CONCURRENTLY leaves an invalid index, not a partial one. Remove it before retrying — the drop itself takes no exclusive lock:
-- PostgreSQL · run as the table owner · CONCURRENTLY variant is safe under live traffic
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_region;
The abort-decision timeline Along a time axis the step starts and a queue forms; at the budget-breached line the operator reads the progress view and either waits for a near-done step or cancels a stalled one, then drains the queue and drops the invalid index. Read the progress view before you kill time budget breached 1 step starts 2 queue forms 3 read progress view near-done (95%) let it finish — wait stalled + queue rising 4 pg_cancel_backend 5 queue drains 6 drop invalid index Cancel only when progress is flat and the queue is still growing — a near-done step is worth the wait.
The abort is a branch, not a reflex: past the budget line you read the progress view — a near-done step is left to finish, a stalled one with a rising queue is cancelled, the queue drains, and the invalid index left behind is dropped.

Permanent Fix / Long-Term Pattern

Watching by hand is the incident tool; the durable fix is to make every high-risk step self-limiting and self-reporting so it can never form a silent queue in the first place. Bound the lock wait so a contended step fails fast instead of hanging: set lock_timeout (PostgreSQL) or lock_wait_timeout (MySQL) at the top of the migration, so a step that cannot acquire its lock in a few seconds returns an error you can retry rather than an invisible pileup.

-- PostgreSQL · run as the migration role · CREATE INDEX CONCURRENTLY must run OUTSIDE a transaction
SET lock_timeout = '3s';   -- a blocked DDL step fails fast instead of queuing traffic behind it
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_region ON orders (region_code);

For any step that rewrites or scans a large table, prefer the online path — CREATE INDEX CONCURRENTLY, ADD COLUMN without a volatile default, or an online-DDL tool that streams changes rather than holding one long lock — and drive large data changes through a batched, restartable backfill instead of a single statement, the approach detailed in Backfill Optimization. Because each batch follows idempotent script design, stopping mid-run costs progress but never correctness, so aborting is always cheap. Finally, do not rely on a human staring at a query window: promote the blocked-backends count and the progress view into a live dashboard and an alert that pages during the deploy window, the standing setup described in the parent topic, Migration Observability & Monitoring. Watching live is what you do when the alert has already fired; the goal is to need it rarely.

Verification Checklist

Return to the parent topic — Migration Observability & Monitoring — for the standing dashboards, budgets, and alert routing that turn this manual watch into an automated one.

Frequently Asked Questions

How do I know whether to let a migration finish or kill it? Read the progress view and the queue depth together. On PostgreSQL, pg_stat_progress_create_index tells you the percentage of blocks done — if a step is at 95% and the queue behind it is small and stable, waiting out the last few seconds beats a cancel-and-retry that starts over. Abort when progress is flat and the blocked-backends count is rising, because that combination means the step is stalled and actively strangling traffic. MySQL has no DDL progress view, so lean on how long the DDL thread has held its lock versus the table’s size and how fast the queue is growing.

What is the difference between pg_cancel_backend and pg_terminate_backend? pg_cancel_backend(pid) sends a gentle cancel that aborts the currently running statement while leaving the database connection open — always try this first. pg_terminate_backend(pid) drops the entire connection, which is forceful and can leave the client confused. Escalate to terminate only when a cancel does not release the lock within a few seconds, for example a backend stuck in an uninterruptible state. On MySQL the equivalents are KILL QUERY <id> (statement) and KILL <id> (connection).

Why do application queries pile up behind a slow ALTER even though the ALTER is just one statement? Because on PostgreSQL an ALTER TABLE that needs ACCESS EXCLUSIVE conflicts with every other lock mode, and the lock request queues. Once it is queued and waiting, new queries requesting even a shared lock on that table line up behind it, so a single blocked DDL step freezes the whole table until the upstream holder releases. MySQL behaves the same way through metadata locks — the DDL waits for a metadata lock and every subsequent query waits behind it. That queue, not the migration’s own runtime, is the actual outage, which is why you alert on blocked backends rather than on the migration merely running.

Related Articles