As you might know, in the last pgCenter release
the new tool has been added - wait events profiler. In this post, I'd like to explore this tool and propose some use-cases for it.
First of all, what are "wait events"? PostgreSQL official documentation doesn't give an explanation for wait events (it just provides a list of all wait events). In short, wait events are points in time where backends have to wait until a particular event occurs. This may be waiting for obtaining locks, IO, inter-process communication, interacting with client or something else. Stats about wait events are provided by pg_stat_activity
view in wait_event_type
we always can understand what query does. But EXPLAIN is aimed to work with query planner and doesn't show time when query got stuck in waitings. For this, you can use pgCenter's wait event profiler.
How does it work? First, you need to know PID of profiled backend. It can be found using pg_stat_activity
, or, if you're connected to Postgres directly, with pg_backend_pid()
. Next, in the second terminal, run 'pgcenter profile
' and pass backend PID as an argument. That's it. pgCenter connects to Postgres and using wait events stats from pg_stat_activity
will start collecting data. When query finishes, pgCenter shows you distribution of wait events, like this: