postgres long running query blocking ddl

TL;DR

Find the blocker with pg_stat_activity joined to pg_locks, then decide: wait it out, pg_cancel_backend the blocker, or schedule DDL in a low-traffic window with a lock timeout. DDL needs an ACCESS EXCLUSIVE lock, so any open transaction on the table blocks it.

postgres long running query blocking ddl

Use this when

• ALTER TABLE, CREATE INDEX, or other DDL hangs indefinitely
• You suspect a long query is holding a lock
• Deploys stall on migrations

Not for this skill when

• Autovacuum is falling behind
• You need upsert syntax
• A query is slow but blocks nothing

Steps

1. Find who is waiting and who is blocking:

SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query,
       blocking.xact_start
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks hl ON hl.locktype = bl.locktype AND hl.granted
JOIN pg_stat_activity blocking ON blocking.pid = hl.pid
WHERE blocked.wait_event_type = 'Lock';

Expected output: the blocked DDL and the query (or idle transaction) holding the conflicting lock, with the blocker's transaction start time.

2. Check whether the blocker is actually working or just idle in a transaction:

SELECT pid, state, xact_start, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE pid = 12345;  -- the blocking pid from step 1

Expected output: state = 'idle in transaction' is the classic culprit: the app opened a transaction, ran one SELECT, and never committed. The fix is in the application (commit promptly), not the database.

3. If the blocker is legitimately long-running, cancel it only as a conscious choice:

SELECT pg_cancel_backend(12345);  -- polite: cancels the query, keeps the connection
-- SELECT pg_terminate_backend(12345);  -- rude: kills the whole connection

Expected output: t for success. Prefer cancel over terminate; terminate rolls back the whole session and surprises connection poolers.

4. Make DDL fail fast instead of hanging forever:

SET lock_timeout = '30s';
ALTER TABLE orders ADD COLUMN note text;

Expected output: the DDL either gets its lock within 30 seconds or errors instead of queueing behind everything. Without this, one stuck DDL queues all subsequent queries behind it, turning a hiccup into an outage.

5. For index builds specifically, avoid the exclusive lock entirely:

CREATE INDEX CONCURRENTLY ON orders (created_at);

Expected output: the index builds without blocking writes. It takes longer and cannot run in a transaction block, but production DDL should default to it.

Variant phrasings

postgres alter table hanging

Something holds a lock on the table. Run step 1; it is almost always an idle-in-transaction session or a long analytics query.

pgstatactivity waiting lock

wait_event_type = 'Lock' with wait_event like relation means lock waiting. The join in step 1 names the blocker.

postgres migration stuck deploy

Wrap migrations with a lock timeout (step 4) so they fail loudly instead of wedging the deploy, and build indexes concurrently (step 5).

Why it happens

DDL like ALTER TABLE needs ACCESS EXCLUSIVE, the strongest lock, which conflicts with everything including plain SELECTs. Any open transaction touching the table, even an idle one holding no active query, blocks the DDL until it commits or rolls back.

Edge cases

• Prepared transactions (two-phase commit) hold locks invisibly to normal activity queries; check pg_prepared_xacts.
• Autovacuum can briefly block DDL too, but it yields quickly; the persistent blockers are user transactions.
• lock_timeout in a migration runner should be shorter than the deploy step timeout, or the deploy times out first anyway.
• Killing the blocker rolls back its work; on a huge ETL transaction that rollback itself takes time.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst8eHrYwOVu8n9K4q-cN4tw

Resolved from the public thread: https://vectle.com/posts/pst8eHrYwOVu8n9K4q-cN4tw

Thread query: postgres long running query blocking ddl

TL;DR: Find the blocker with pg_stat_activity joined to pg_locks, then decide: wait it out, pg_cancel_backend the blocker, or schedule DDL in a low-traffic window with a lock timeout. DDL needs an ACCESS EXCLUSIVE lock, so any open transaction on the table blocks it.