Menu
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.
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 pg_stat_activity table to see which queries are taking a long time. In there I found this:
postgres=> 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 "ingress_message" ("created_
9551 | 2021-08-04 12:21:42.384146+02 | 00:04:43.280586 | idle | Client | ClientRead | INSERT INTO "ingress_message" ("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 "ingress_message" ("created_
9891 | 2021-08-04 12:25:02.378745+02 | 00:01:23.285992 | idle | Client | ClientRead | INSERT INTO "ingress_message" ("created_
(5 rows)
As you can see the longest running query has been running for more than 5 minutes (!!) and it's caused by a "ClientRead". 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?
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.
What I mainly wonder; can I find out which query is causing the ClientRead locks which cause the inserts to take so long?
postgres=> 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)
788
I don't think you have locking or server side problems. From the documentation:
ClientRead: Waiting to read data from the client.
It seems to me, the server is waiting for the client to deliver the data for the INSERT query. Maybe networking problems?
99
answered Oct 31 '25 02:10
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
