How do I find what is blocking a query?

Ask the server directly: there is a function that takes a process id and returns the sessions blocking it, which is far more reliable than joining the lock view to itself. Then repeat it on each answer until you reach a session that is blocked by nobody. That session is the cause, and the queries everybody is staring at are its victims.

Follow the chain, do not stop at the first link

A blocked query names the session in front of it, and that session is very often blocked as well. Acting on the first name you find kills a victim and changes nothing, because the queue re-forms behind the same root a second later.

The root has a characteristic appearance that trips people up: it is usually not running a query at all. It holds a lock from a statement that finished, inside a transaction nobody committed, so its state reads as idle and its query text is whatever it ran last. Judging it by what it appears to be doing is how the real cause gets skipped twice.

There is a second shape worth recognising. A request for a strong lock does not jump the queue; it waits, and everything arriving afterwards waits behind it, including trivial reads that conflict with nothing. So one blocked schema change can make an entire table appear unavailable while the actual holder is a single harmless query that started earlier.

Knowing how long it has been waiting

Before acting, establish duration. A wait of a few hundred milliseconds during a deployment is normal; the same wait sustained for minutes is an incident. The lock view records when each wait started from PostgreSQL 14 onward, which is the first version where the question can be answered from the server rather than inferred from when the session’s state last changed.

Watching the tree for a few seconds is also worth more than a single reading. A tree that dissolves and reforms is contention; a tree that is identical thirty seconds later is one stuck transaction.

Once you have the root, ending it is a decision with a wrong answer available, and is it safe to kill a Postgres query covers which of the two functions to use. For reading the whole tree at once rather than following it link by link, and for what to alert on, see lock contention and blocking trees.