Как мы научились строить деревья блокировок PostgreSQL в фоне и без влияния на производительность

Но вторая сессия действительно является и заблокированной (ожидает освобождения строки, захваченной в сессии 1), и блокирующей (захватила блокировку в режиме RowExclusiveLock и ее транзакция не завершена, так как мы ожидаем освобождения строки). Можно проверить также с помощью вызова системной функции pg_blocking_pids:

postgres=# SELECT pid,
       pg_blocking_pids(pid),
       wait_event_type,
       state,
       query
FROM pg_stat_activity 
WHERE query LIKE '%lock_test%';

   pid   | pg_blocking_pids | wait_event_type |        state        |                      query                       
---------+------------------+-----------------+---------------------+-------------------------------------------------
 2563897 | {}               | Client          | idle in transaction | update lock_test set name = '2222' where ID = 1;
 2563987 | {2563897}        | Lock            | active              | update lock_test set name = '3333' where ID = 1;
 2563992 | {2563987}        | Lock            | active              | update lock_test set name = '4444' where ID = 1;

Получаем цепочку 2563897<--2563987<--2563992. Вторая сессия, 2563987, является промежуточным звеном: ожидает 2563897 и одновременно является блокиратором для 2563992.

Возможно, Вы имели в виду подсчет только корневых блокираторов. Можете показать запрос.

Как мы научились строить деревья блокировок PostgreSQL в фоне и без влияния на производительность

Добрый день! На наблюдаемой базе выполняется только один запрос, он был разобран в статье - сбор сырых данных через джойн pg_locks и pg_stat_activity. Запрос выполняется ежесекундно. А метрики мы уже вычисляем в нашем хранилище с сырыми данными. По умолчанию агрегация данных происходит каждые 5 секунд. Если адаптировать запрос под живой pg_locks, то получается такой вариант (для синей линии blocked, для красной - blocking):

SELECT count(distinct blocking_pid) blocking,
	   count(distinct blocked_pid) blocked
FROM (
	SELECT kl.pid blocking_pid,
		   bl.pid blocked_pid
	FROM pg_locks bl
	JOIN pg_locks kl ON bl.locktype = kl.locktype 
                  	 AND NOT bl.database IS distinct FROM kl.database
                  	 AND NOT bl.relation IS distinct FROM kl.relation
                  	 AND NOT bl.page IS distinct FROM kl.page
                  	 AND NOT bl.tuple IS distinct FROM kl.tuple
                  	 AND NOT bl.virtualxid IS distinct FROM kl.virtualxid
                  	 AND NOT bl.transactionid IS distinct FROM kl.transactionid
                  	 AND NOT bl.classid IS distinct FROM kl.classid
                  	 AND NOT bl.objid IS distinct FROM kl.objid
                  	 AND NOT bl.objsubid IS distinct FROM kl.objsubid
                  	 AND bl.pid <> kl.pid
    WHERE kl.granted
    AND NOT bl.granted
) tb;

Как мы научились строить деревья блокировок PostgreSQL в фоне и без влияния на производительность

Добрый день! Инструмент нужен не для слежки, а для реконструкции цепочки событий. Это помощь в ретроспективном анализе. Запущенный кем-то нерегламентированный REINDEX - один из простейших примеров. На практике могут быть проблемы серьезнее. Например, блокировки могут использовать для реализации бизнес-логики и разработчикам важно видеть, что цепочка блокировок идет в правильном порядке, а в случае проблем иметь перед глазами иерархию. Если возвращаться к тому же REINDEX, то описанный пример был взят из жизни, но упрощен для статьи. На практике встречала случай, когда DBA, занимаясь оптимизацией, создали регламентное задание для перестройки некоторых индексов, но по времени задание пересеклось с тяжелой ночной аналитикой, запущенной группой разработки. Да, есть вопросы к организации рабочего процесса, но подобные инструменты для ретроспективного анализа в том числе и такие расследования делают быстрее и удобнее.
Что касается других причин зависания - конечно же, блокировки не всегда корень зла. Если говорить о платформе Kintsugi, то в родительском сервисе Performance Insights тоже представлен исторический анализ, но не блокировок, а активных сессий. Есть статистика по типам ожиданий, БД, пользователям и в том числе топы по потреблению CPU и IO. В пользовательской документации есть описание панели.
От ответа на третий вопрос воздержусь. Статья все-таки посвящена анализу блокировок, а не организации работы.