How to check if my queries are being blocked by other queries?

Last edited: 1/17/2025

You can set a lock monitor view to help investigate these.

Once you run the query that takes a long time to complete, you can go in the dashboard (or select from this view below) to check what are the blocks.


_28
create view
_28
public.lock_monitor as
_28
select
_28
coalesce(
_28
blockingl.relation::regclass::text,
_28
blockingl.locktype
_28
) as locked_item,
_28
now() - blockeda.query_start as waiting_duration,
_28
blockeda.pid as blocked_pid,
_28
blockeda.query as blocked_query,
_28
blockedl.mode as blocked_mode,
_28
blockinga.pid as blocking_pid,
_28
blockinga.query as blocking_query,
_28
blockingl.mode as blocking_mode
_28
from
_28
pg_locks blockedl
_28
join pg_stat_activity blockeda on blockedl.pid = blockeda.pid
_28
join pg_locks blockingl on (
_28
blockingl.transactionid = blockedl.transactionid
_28
or blockingl.relation = blockedl.relation
_28
and blockingl.locktype = blockedl.locktype
_28
)
_28
and blockedl.pid <> blockingl.pid
_28
join pg_stat_activity blockinga on blockingl.pid = blockinga.pid
_28
and blockinga.datid = blockeda.datid
_28
where
_28
not blockedl.granted
_28
and blockinga.datname = current_database();