Blocking on idle connections on ClientRead for parametrized queries (bindings) during high traffic

Question

I am looking for good solution for my problem which occurs during high traffic peaks. I use postgres on AWS with nodejs (knex for queries buliding) - details below.

When I am looking on Performance Insights in my RDS console I see that some of queries stuck on "ClientRead". My RDS instances are rather huge and my CPU utilization is on very low level (1%-10%). So I confirmed it by connect to db and run query for pg_stats and in result I saw that a lot of queries is idle on ClientRead event.

What connect these queries? Bindings. I assume that these parametrized queries wait to get values from my EC2 instances. I thought that my services are too slow, so I scaled up to more instances, but the results on RDS were worse and more connections were blocked.

For testing solution I converted few of queries from parametrized into raw sql queries without bindings (with values directly in query). And these queries exactly were run immediately without any problems. But it seems that not perfect solution even if security reasons.

At the moment I have no idea where the problem is? Should I reduce traffic by add throttling on api gw? Create inner queues in my service? Is it a communication problem or settings of my RDS/postgre?

If anyone has more experience with similar cases or is possible to point at probable solution, link to documents which could help me or detect where the problem is it would be great.

AWS RDS (Aurora) Postgres 9.6.9 nodejs 10.12.0 knex 0.17.3 node-postgres 7.4.1

Answer 1

I was facing the same problem and upgrading my pg Node.js package from 7.14.0 to 8.5.1 solved this exact problem.

Before upgrading, ClientRead wait event was accounting for something like 80 % of the RDS performance chart, now it's down to less than 10 %.

Answer 2

If your database backends are blocked waiting for ClientRead, that means that the database is waiting for requests from the client.

The queries you are seeing are not running queries. If the state is not active, query contains the last SQL statement that was run on this database connection.

If you are experiencing performance problems, the cause seems to be outside the database.