PostgreSQL slow and locked queries

Recently I've found myself needing to check long-running and locked queries in PostgreSQL. Despite a quick search that yields lots of examples, I couldn't find a query that shows both long-running queries and blocked statements. Hence;

WITH activity_with_age AS (
	SELECT
		pid,
		age(now(), query_start) AS age,
		pg_blocking_pids(pid) AS blocking_pids,
		datname,
		state,
		wait_event_type,
		wait_event,
		query
	FROM pg_stat_activity
	WHERE state != 'idle'
)
SELECT
	pid,
	CONCAT(
		CASE WHEN EXTRACT(hour FROM age) > 0
			 THEN EXTRACT(hour FROM age)::int || 'h ' ELSE '' END,
		CASE WHEN EXTRACT(minute FROM age) > 0
			 THEN EXTRACT(minute FROM age)::int || 'm ' ELSE '' END,
		ROUND(EXTRACT(second FROM age), 2) || 's'
	) duration,
	CASE
		WHEN blocking_pids = '{}'::int[] THEN NULL
		ELSE blocking_pids
		END AS blocked_by_pids,
	datname AS database,
	state,
	wait_event_type,
	wait_event,
	query
FROM activity_with_age
ORDER BY age DESC NULLS LAST;

Example output:

← Home