Quick 📸 on 🐘 wait events from pg_stat_activity
Tags: postgres, performance, sql, database
Published: 2023-12-07 20:23:46

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:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4w7khahue5ae525fck2i.png)

I create the temporary tables to store the samples:
```sql

-- 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`:
```sql

-- 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`:
```sql

-- 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:
```sql
     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](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Tuning.html) 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()`)