How to detect query which holds the lock in Postgres?

Deep-diving into lock detection

The fast answer above is your go-to method for a quick assessment. However, to unravel the mystery behind persistent and complex locking scenarios, dig a little deeper.

Hunting down the culprits

Direct your flashlight towards the pg_blocking_pids() function that PostgreSQL kindly provides to bring the locking culprits into the limelight:

SELECT pid, usename, 
       pg_blocking_pids(pid) as blocked_by,   -- Sherlock Holmes in query form  
       query as blocked_query 
FROM pg_stat_activity 
WHERE cardinality(pg_blocking_pids(pid)) > 0;


This query not only shows who's waiting for a lock, but also who's being the naughty kid holding the lock.

Your tool for repeated checks

If you find yourself regularly embroiled in the lock investigation game, it helps to create a view that simplifies the job:

CREATE VIEW lock_monitor AS  -- Your CSI lab in one line
SELECT ...


Now, with your shiny new view in your toolbox, a single statement will serve up the lock-related insights you need.

Proceed, but with caution!

When it comes to killing off blocking sessions with pg_cancel_backend() or pg_terminate_backend(), remember what Spiderman's uncle said: "With great power comes great responsibility." Kill locks with care, folks! Dig deeper to find the root cause to eliminate repeated issues.

Unravel lock durations, types and more

To know what exactly Q456 has been doing—how long it's been sitting, the lock type, and more—broaden your horizon by including pg_catalog:

SELECT *, age(current_timestamp, query_start) AS duration -- When did Q456 park?! 
FROM ...
JOIN pg_catalog.pg_locks ON ...


Knowing the duration and the nature of the lock, you're now equipped to make things flow smoother in the future.

GOAT of lock investigation

Let's extract even more information using the unnest() function with pg_blocking_pids(). This gives you a super-detailed trait-profile of your lockers, bringing you closer to being the G.O.A.T of lock investigation!

SELECT unnest(pg_blocking_pids(pid)) as blocking_pid -- Now who's the G.O.A.T?! 
...
