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