🐥 Troubleshooting blocked queries in PostgreSQL

database postgresql sql

Today, a colleague wanted to do a truncate of a table in his database. However, that query never seemed to complete.

To fix it, we first checked which queries were blocking the truncate one. This can be done using this query:

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

We found that there was a select query which was blocking the truncate.

The next step was to kill that select query by issueing:

SELECT pg_terminate_backend(PID);

Once killed, the truncated finished as well.

source

If this post was enjoyable or useful for you, please share it! If you have comments, questions, or feedback, you can email my personal email. To get new posts, subscribe use the RSS feed.