title: How to find cause of ClientRead wait_event in Postgresql pg_stat_activity?
body: <p>I've got an endpoint which receives a lot of data. It inserts it into a table called ingress. I've also written a couple parsers which loop over the messages in the ingress table and parse it into various other tables.</p>
  <p>I've been having a lot of performance problems on our postgres cluster lately and I can't really find the cause. So I first started looking into the <code>pg_stat_activity</code> table to see which queries are taking a long time. In there I found this:</p>
  <pre><code>postgres=&gt; select pid, query_start, age(clock_timestamp(), query_start) as age, state, wait_event_type, wait_event, LEFT(query, 40) 
  from pg_stat_activity where state like '%idle%' and datname = 'mydatabase' 
  order by query_start asc limit 5;
  
   pid  |          query_start          |       age       | state | wait_event_type | wait_event |                   left                   
  ------+-------------------------------+-----------------+-------+-----------------+------------+------------------------------------------
   9429 | 2021-08-04 12:20:55.790618+02 | 00:05:29.874102 | idle  | Client          | ClientRead | INSERT INTO &quot;ingress_message&quot; (&quot;created_
   9551 | 2021-08-04 12:21:42.384146+02 | 00:04:43.280586 | idle  | Client          | ClientRead | INSERT INTO &quot;ingress_message&quot; (&quot;created_
   9776 | 2021-08-04 12:23:37.849208+02 | 00:02:47.815526 | idle  | Client          | ClientRead | select 1
   9922 | 2021-08-04 12:25:02.207894+02 | 00:01:23.456841 | idle  | Client          | ClientRead | INSERT INTO &quot;ingress_message&quot; (&quot;created_
   9891 | 2021-08-04 12:25:02.378745+02 | 00:01:23.285992 | idle  | Client          | ClientRead | INSERT INTO &quot;ingress_message&quot; (&quot;created_
  (5 rows)
  </code></pre>
  <p>As you can see the longest running query has been running for more than 5 minutes (!!) and it's caused by a &quot;ClientRead&quot;. That seems odd to me. Why would a read prevent a write? Or does it mean some process is locking the full table to do a read?</p>
  <p>I checked the pg_locks table, which shows me the result below. I've been reading up on pg_locks, but I can't really grasp what this information is telling me.</p>
  <p>What I mainly wonder; can I find out which query is causing the ClientRead locks which cause the inserts to take so long?</p>
  <pre><code>postgres=&gt; select * from pg_locks;
  
     locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted | fas
  tpath 
  ---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----
  ------
   relation      |    82586 |    11645 |      |       |            |               |         |       |          | 93/129             |  3764 | AccessShareLock  | t       | t
   virtualxid    |          |          |      |       | 93/129     |               |         |       |          | 93/129             |  3764 | ExclusiveLock    | t       | t
   relation      |    82586 | 22442205 |      |       |            |               |         |       |          | 42/6323            |  3141 | RowExclusiveLock | t       | t
   relation      |    82586 |  9898413 |      |       |            |               |         |       |          | 42/6323            |  3141 | RowExclusiveLock | t       | t
   relation      |    82586 |  9898449 |      |       |            |               |         |       |          | 42/6323            |  3141 | RowExclusiveLock | t       | t
   relation      |    82586 | 12134578 |      |       |            |               |         |       |          | 42/6323            |  3141 | AccessShareLock  | t       | t
   relation      |    82586 | 12134578 |      |       |            |               |         |       |          | 42/6323            |  3141 | RowExclusiveLock | t       | t
   relation      |    82586 | 12103296 |      |       |            |               |         |       |          | 42/6323            |  3141 | AccessShareLock  | t       | t
   relation      |    82586 | 12103296 |      |       |            |               |         |       |          | 42/6323            |  3141 | RowExclusiveLock | t       | t
   relation      |    82586 | 12103295 |      |       |            |               |         |       |          | 42/6323            |  3141 | AccessShareLock  | t       | t
   relation      |    82586 | 12103295 |      |       |            |               |         |       |          | 42/6323            |  3141 | RowExclusiveLock | t       | t
   relation      |    82586 | 12102372 |      |       |            |               |         |       |          | 42/6323            |  3141 | AccessShareLock  | t       | t
   relation      |    82586 | 12102372 |      |       |            |               |         |       |          | 42/6323            |  3141 | RowExclusiveLock | t       | t
   relation      |    82586 | 12102338 |      |       |            |               |         |       |          | 42/6323            |  3141 | AccessShareLock  | t       | t
   relation      |    82586 | 12102338 |      |       |            |               |         |       |          | 42/6323            |  3141 | RowExclusiveLock | t       | t
   relation      |    82586 | 12102331 |      |       |            |               |         |       |          | 42/6323            |  3141 | AccessShareLock  | t       | t
   relation      |    82586 | 12102331 |      |       |            |               |         |       |          | 42/6323            |  3141 | RowShareLock     | t       | t
   relation      |    82586 | 12102331 |      |       |            |               |         |       |          | 42/6323            |  3141 | RowExclusiveLock | t       | t
   virtualxid    |          |          |      |       | 42/6323    |               |         |       |          | 42/6323            |  3141 | ExclusiveLock    | t       | t
   relation      |    19825 | 12429831 |      |       |            |               |         |       |          | 31/7218            |  3128 | AccessShareLock  | t       | t
   relation      |    19825 | 12429819 |      |       |            |               |         |       |          | 31/7218            |  3128 | AccessShareLock  | t       | t
   relation      |    19825 | 12429818 |      |       |            |               |         |       |          | 31/7218            |  3128 | AccessShareLock  | t       | t
   relation      |    19825 | 12429770 |      |       |            |               |         |       |          | 31/7218            |  3128 | AccessShareLock  | t       | t
   relation      |    19825 | 12429751 |      |       |            |               |         |       |          | 31/7218            |  3128 | AccessShareLock  | t       | t
   relation      |    19825 | 12429744 |      |       |            |               |         |       |          | 31/7218            |  3128 | AccessShareLock  | t       | t
   virtualxid    |          |          |      |       | 31/7218    |               |         |       |          | 31/7218            |  3128 | ExclusiveLock    | t       | t
   relation      |    19825 | 12429831 |      |       |            |               |         |       |          | 30/6218            |  3127 | AccessShareLock  | t       | t
   relation      |    19825 | 12429819 |      |       |            |               |         |       |          | 30/6218            |  3127 | AccessShareLock  | t       | t
   relation      |    19825 | 12429818 |      |       |            |               |         |       |          | 30/6218            |  3127 | AccessShareLock  | t       | t
   relation      |    19825 | 12429770 |      |       |            |               |         |       |          | 30/6218            |  3127 | AccessShareLock  | t       | t
   relation      |    19825 | 12429751 |      |       |            |               |         |       |          | 30/6218            |  3127 | AccessShareLock  | t       | t
   relation      |    19825 | 12429744 |      |       |            |               |         |       |          | 30/6218            |  3127 | AccessShareLock  | t       | t
   virtualxid    |          |          |      |       | 30/6218    |               |         |       |          | 30/6218            |  3127 | ExclusiveLock    | t       | t
   relation      |    19825 | 12429831 |      |       |            |               |         |       |          | 29/5284            |  3126 | AccessShareLock  | t       | t
   relation      |    19825 | 12429819 |      |       |            |               |         |       |          | 29/5284            |  3126 | AccessShareLock  | t       | t
   relation      |    19825 | 12429818 |      |       |            |               |         |       |          | 29/5284            |  3126 | AccessShareLock  | t       | t
   relation      |    19825 | 12429770 |      |       |            |               |         |       |          | 29/5284            |  3126 | AccessShareLock  | t       | t
   relation      |    19825 | 12429751 |      |       |            |               |         |       |          | 29/5284            |  3126 | AccessShareLock  | t       | t
   relation      |    19825 | 12429744 |      |       |            |               |         |       |          | 29/5284            |  3126 | AccessShareLock  | t       | t
   virtualxid    |          |          |      |       | 29/5284    |               |         |       |          | 29/5284            |  3126 | ExclusiveLock    | t       | t
   relation      |    19825 | 12429831 |      |       |            |               |         |       |          | 28/5964            |  3123 | AccessShareLock  | t       | t
   relation      |    19825 | 12429819 |      |       |            |               |         |       |          | 28/5964            |  3123 | AccessShareLock  | t       | t
   relation      |    19825 | 12429818 |      |       |            |               |         |       |          | 28/5964            |  3123 | AccessShareLock  | t       | t
   relation      |    19825 | 12429770 |      |       |            |               |         |       |          | 28/5964            |  3123 | AccessShareLock  | t       | t
   relation      |    19825 | 12429751 |      |       |            |               |         |       |          | 28/5964            |  3123 | AccessShareLock  | t       | t
   relation      |    19825 | 12429744 |      |       |            |               |         |       |          | 28/5964            |  3123 | AccessShareLock  | t       | t
   virtualxid    |          |          |      |       | 28/5964    |               |         |       |          | 28/5964            |  3123 | ExclusiveLock    | t       | t
   relation      |    29251 |    31001 |      |       |            |               |         |       |          | 71/415             | 10820 | AccessShareLock  | t       | t
   relation      |    29251 |    30988 |      |       |            |               |         |       |          | 71/415             | 10820 | AccessShareLock  | t       | t
   relation      |    29251 |    30981 |      |       |            |               |         |       |          | 71/415             | 10820 | AccessShareLock  | t       | t
   virtualxid    |          |          |      |       | 71/415     |               |         |       |          | 71/415             | 10820 | ExclusiveLock    | t       | t
   relation      |    29251 |    30981 |      |       |            |               |         |       |          | 72/645             | 10821 | AccessShareLock  | t       | t
   virtualxid    |          |          |      |       | 72/645     |               |         |       |          | 72/645             | 10821 | ExclusiveLock    | t       | t
   transactionid |          |          |      |       |            |    1762434479 |         |       |          | 42/6323            |  3141 | ExclusiveLock    | t       | f
  (54 rows)
  
  </code></pre>