Quick 📸 on 🐘 wait events from pg_stat_activity

For quick performance troubleshooting, here is a simple script that reads 150 samples from pg_stat_activity, every 100 milliseconds during 15 seconds, to sum the Active Session History per wait event and time buckets, like this:

I create the temporary tables to store the samples:

-- create temporary views and table to store samples
drop table if exists temp_ash;
drop view if exists  temp_psa;
create temporary view  temp_psa
 as select now() as ts, * from pg_stat_activity;
create temporary table temp_ash
 as select * from psa where false;

I gather the 150 samples using \watch in psql:

-- take samples every 100ms during 15s
insert into temp_ash
 select * from temp_psa
\watch interval=0.1 count=150
--              ^^^       ^^^
--     frequency(s)   samples 

I query the samples, using \crosstabview in psql:

-- query and group into 5 buckets ( = every 3 seconds for 15s gathered)
select ev as "state                              wait_event_type : wait_event"
 , bucket, to_char(count(*)/count(distinct ts),'99999D9') from (
 select ev, ts, to_char(max(ts)over(partition by bucket),'HH24:MI:SS') bucket from (
 select ts
 , format('%14s %-30s %10s:%-15s',backend_type, state, wait_event_type,wait_event) ev
 , ntile(5) over(order by ts) bucket from ash
--       ^
--       time buckets columns
 ) x ) x group by ev, bucket order by 3 desc,ev, bucket
\crosstabview

This gives the following result:

     state                              wait_event_type : wait_event      | 19:24:16 | 19:24:19 | 19:24:07 | 19:24:13 | 19:24:10
--------------------------------------------------------------------------+----------+----------+----------+----------+----------
 client backend idle                               Client:ClientRead      |     83.0 |     83.0 |     82.0 |     80.0 |     79.0
                                                         :                |      4.0 |      4.0 |      4.0 |      4.0 |      4.0
 client backend active                                 IO:DataFileRead    |      2.0 |      3.0 |      2.0 |      3.0 |      2.0
 client backend active                             LWLock:buffer_mapping  |      1.0 |      2.0 |          |          |
 client backend active                                   :                |      1.0 |      1.0 |      1.0 |      1.0 |      1.0
 client backend active                             LWLock:buffer_io       |      1.0 |      1.0 |      1.0 |      1.0 |      1.0
 client backend idle in transaction                      :                |          |      1.0 |          |          |
 client backend idle in transaction (aborted)      Client:ClientRead      |          |          |      1.0 |      1.0 |      1.0
 client backend idle in transaction                Client:ClientRead      |      1.0 |      1.0 |      1.0 |      1.0 |      1.0
(9 rows)

The Amazon Aurora documentation has a great description of PostgreSQL wait events. In the example above Client:ClientRead is the idle backend waiting from something to do (Oracle DBAs will recognize SQL*Net message from client) so I have mostly idle users here and a few ones are within a transaction. The numbers are the average number of session in this state during the time bucket.

Of course, you can gather samples multiple times, with different frequency, and change the number of columns (in ntile())