When a PostgreSQL system wedges, or when it becomes dreadfully slow for some reason, I often find myself relying on tools like strace, gdb, or perf to figure out what is happening. This doesn't tend to instill customers with confidence; they would like (quite understandably) a process that doesn't require installing developer tools on their production systems, and doesn't require a developer to interpret the results, and perhaps even something that they could connect up to PEM or Nagios or whatever alerting system they are using.
There are obviously many ways that we might think about improving things here, but what I'd like to do is try to put some better information in pg_stat_activity, so that when a process is not running, users can get some better information about *why* it's not running. The basic idea is that pg_stat_activity.waiting would be replaced by a new column pg_stat_activity.wait_event, which would display the reason why that backend is waiting. This wouldn't be a free-form text field, because that would be too expensive to populate.
I've not looked into the feasibility of it, but if it were also possible to have a "waiting_for" column which would store the process ID of the process that's holding a lock that this process is waiting on, then it would be possible for some smart guy to write some code which draws beautiful graphs, perhaps in Pg Admin 4 of which processes are blocking other processes. I imagine this as a chart with an icon for each process. Processes waiting on locks being released would have an arrow pointing to their blocking process, if we clicked on that blocking process we could see the query that it's running and various other properties that are existing columns in pg_stat_activity.
Obviously this is blue-skies stuff, but if we had a few to provide that information it would be a great step forward towards that.
