Kill Blocking DB Queries

​

Prerequisites

• Access to the production database. See Connecting to Production DB.

​

1. Find the blocking query

SELECT
  a.pid,
  a.state,
  age(clock_timestamp(), a.query_start) AS waiting,
  a.query AS blocked_query,
  bl.pid AS blocking_pid,
  bl.query AS blocking_query
FROM pg_stat_activity a
JOIN pg_stat_activity bl ON bl.pid = ANY(pg_blocking_pids(a.pid))
ORDER BY waiting DESC;

• pid / blocking_pid — process IDs
• waiting — how long the blocked query has been waiting
• blocked_query — the query that can’t proceed
• blocking_query — the last query run by the blocking session

​

2. Terminate the blocking session

SELECT pg_terminate_backend(<blocking_pid>);

​

3. After termination

• Verify the blocked operation completes successfully
• Check application logs / Sentry for errors caused by the terminated session
• Document in the on-call log
• Notify the team

​

Root causes to investigate

• Transaction open across external I/O — e.g. a worker that queries the DB and then makes a slow HTTP call (Expo push, Stripe, etc.) while still inside the same database transaction. The transaction never commits until the HTTP call returns.
• Missing lock_timeout on DDL migrations — without a timeout, a DROP TABLE or ALTER TABLE will queue indefinitely behind hot-table readers, and while queued it also blocks new readers.
• No idle_in_transaction_session_timeout — connections left idle inside a transaction are never auto-killed by the DB.