Postgres, including blocking sessions last command in Active Sessions List
Aug 24, 2023
1 min read
I love pg_stats_activity. It'sthe heart of all my postgres performance monitoring. It's about the only place I go. But one thing that had been super frustrating, especially when the house is burning down, is that when I saw blockers mucking up the system, I didn't know what they were doing and/or if I could kill them. So newest modification is to include them in the output even if they are "idle".
Output looks like:

script
select
case when b.bpids::integer = a.pid
then a.pid
else null end as blocker,
COALESCE(wait_event,'cpu'),
COALESCE(round(EXTRACT(EPOCH FROM (clock_timestamp() -
query_start))::numeric, 5), -1) as query_age ,
CASE when lower(substr(query , 1, 5)) = 'alter'
then 999999
else
COALESCE(round(EXTRACT(EPOCH FROM (clock_timestamp() -
xact_start ))::numeric, 5), -1) end as xact_age,
pid::text,
pg_blocking_pids(PID)::text as blocker,
state,
substr(query , 1, 80)
-- , query
-- , *
from pg_stat_activity a,
(SELECT COALESCE(
(SELECT unnest(string_to_array(replace(replace(pg_blocking_pids(PID)::text, '{', ''), '}', ''), ','))::int
FROM pg_stat_activity ), -1 ) bpids
) b
where
( state='active'
or pid=3379309
or PID = b.bpids::integer
)
--and
-- pid != pg_backend_pid()
-- pid=3379309
-- pid in (3533517,3651820,3651819)
order by 3 desc, 5 , 2 desc
-- \watch 1
;
I live the comments in because these I things I often modify on the fly.
Like restricting it to one PID or making sure to include a specific PID



This article hits on a crucial aspect of Postgres monitoring that can save a lot of headaches during high-stress situations. It's always useful to have visibility into what blockers are doing, as mentioned in the article. For more in-depth insights and strategies on managing Postgres performance, you might find the scritchy scratchy game helpful, as it covers a range of topics including this.
Rather than making large movements Snow Rider, try using gentle taps to adjust your position. Small corrections are much safer than sudden swerves, especially once the sled reaches higher speeds.