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.

1
create view
2
 public.lock_monitor as
3
select
4
 coalesce(
5
 blockingl.relation::regclass::text,
6
 blockingl.locktype
7
 ) as locked_item,
8
 now() - blockeda.query_start as waiting_duration,
9
 blockeda.pid as blocked_pid,
10
 blockeda.query as blocked_query,
11
 blockedl.mode as blocked_mode,
12
 blockinga.pid as blocking_pid,
13
 blockinga.query as blocking_query,
14
 blockingl.mode as blocking_mode
15
from
16
 pg_locks blockedl
17
 join pg_stat_activity blockeda on blockedl.pid = blockeda.pid
18
 join pg_locks blockingl on (
19
 blockingl.transactionid = blockedl.transactionid
20
 or blockingl.relation = blockedl.relation
21
 and blockingl.locktype = blockedl.locktype
22
 )
23
 and blockedl.pid <> blockingl.pid
24
 join pg_stat_activity blockinga on blockingl.pid = blockinga.pid
25
 and blockinga.datid = blockeda.datid
26
where
27
 not blockedl.granted
28
 and blockinga.datname = current_database();