agent's migration is waiting on an AccessExclusiveLock held by a long-running analytics query - how to find and kill...

TL;DR: Find what is blocking you with pgblockingpids, look at the blocking query to confirm it is safe to stop, terminate it with pgterminatebackend, then re-run the migration. DDL needs an AccessExclusiveLock, which conflicts with every other lock, so even a plain SELECT can hold it hostage.

agent's migration is waiting on an AccessExclusiveLock held by a long-running analytics query - how to find and kill the blocker

1. Confirm your migration is actually waiting on a lock and find the blocker.

SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE pid = pg_backend_pid();
SELECT pid, usename, query, state, now() - query_start AS running_for
FROM pg_stat_activity WHERE pid = ANY (SELECT pg_blocking_pids(pg_backend_pid()));

Expected: your session shows a Lock wait event, and the second query names the blocking pid, its query text, and how long it has been running.

1. Inspect the blocker before touching it. Confirm it is the analytics query and not another migration, a replication process, or autovacuum.

Expected: you can say what the query is, who ran it, and that stopping it is acceptable. If it is another migration, coordinate instead of killing.

1. Terminate the blocker.

SELECT pg_terminate_backend(BLOCKER_PID);

Expected: the call returns true, the blocker row disappears from pgstatactivity, and your migration's wait event clears.

1. Re-run the migration and watch it acquire the lock promptly.

Expected: the DDL completes instead of hanging, and the schema change is visible afterwards.

1. Prevent the recurrence: schedule DDL in a maintenance window or low-traffic period, and consider a lock_timeout on migration sessions so a stuck migration fails fast instead of queueing forever.

SET lock_timeout = '30s';

Expected: future migrations error quickly on lock contention instead of hanging silently.

Use this when

• A migration hangs with a Lock wait event in pgstatactivity
• pgblockingpids points at a long-running SELECT or analytics query
• DDL will not start on a busy production database
• You need to find and stop whatever is holding the table lock

Not for this skill when

• The blocker is another migration or a deploy process - coordinate with its owner instead of killing it
• The blocker is autovacuum or a replication connection - killing those causes bigger problems
• The migration is slow for its own reasons (rewriting a huge table) rather than waiting on a lock - check waiteventtype first
• You are on a managed database where pgterminatebackend is restricted - use the provider's session-kill tooling instead

Variant phrasings

• migration waiting on AccessExclusiveLock, find blocking query
• postgres DDL blocked by long running select, how to kill blocker
• alter table hangs, analytics query holding lock
• pgblockingpids migration stuck

Why it happens

Postgres DDL takes an AccessExclusiveLock, the strongest lock there is - it conflicts with every other lock mode, including the AccessShareLock that even a simple SELECT holds. A long analytics query therefore blocks all DDL on the table for its whole runtime, and every new query queues behind the waiting DDL, which can look like a full outage. The lock queue is first-come first-served, so the migration waits its turn no matter how important it is.

Edge cases

• Terminating a query rolls back its work. For a multi-minute analytics query that is usually fine, but confirm with the query owner on a shared warehouse.
• Killing the blocker does not help if ten more analytics queries are queued behind it. Pause the analytics workload or run DDL in a quiet window.
• Setting lock_timeout too low makes migrations fail during normal traffic. Tune it to your quietest period, not to zero.
• On read replicas, DDL replay can conflict with long replica queries too - the same find-and-terminate logic applies on the primary's behalf.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_bGtEThkAJpb6e-WwfOR8xQ