We recently switched from mysql to postgresql, and we are using node-posgres to send queries. Everything runs fine for a few minutes, and then queries start slowing down until our service is no longer usable.
Here is an example of output:
fetchRows: Pool has 56 clients, 1 idle clients, 0 queued waiting requests.
Query SELECT * FROM mytable executed in **232775 ms**
At the beginning of the day, that query takes 2ms, 3 minutes later it takes 11513ms and it keeps getting worse.
It is not a problem with the db itself, I can run this query with psql and this table is empty (12 columns, no rows). The db doesn't show any active query, so apparently they are stuck somewhere upstream.
Has anyone seen this problem before? We are sending a lot of queries, maybe we are flooding the system?
We are using "pg" version 8.11.1, on node 16. pg pool is configured with 100 max clients, and idleTimeoutMillis = 30000. Most of our queries use the callback version of pool.query(), only a few use pool.connect() to handle transactions. The db and node server are on the same host.
Any pointer is appreciated. We are looking into potential network issues as well. Thanks