pool.connect() suddenly takes 20 seconds to execute!?

[Garfield2013]

console.time('timer1');
const client = await pool.connect();
console.timeEnd('timer1');

Normally the code above runs fast - but suddenly it takes 20 seconds!? How is this even possible? I can see other people report the same at StackOverflow- but what is the reason and most of all the solution to this issue?

According to this 3 years old post: https://stackoverflow.com/questions/43346895/node-js-pg-pool-slow-on-second-execute

these guys moved to the other NPM module: https://www.npmjs.com/package/pg-promise
But how would it fix anything - when it is based on node-postgres as well?

[vitaly-t]

But how would it fix anything - when it is based on node-postgres as well?

Oftentimes things go wrong because of some non-obvious connection pool misuse. pg-promise makes sure that it is not possible, by hiding the pool from you, and guaranteeing that all work with the pool is correct, doing it automatically in the background, so you never have to touch it.

[Garfield2013]

Thank you so much for you rapid response my friend! Highly appreciated! :-)
But now I'm just curious - can you elaborate a bit about: "Oftentimes things go wrong because of some non-obvious connection pool misuse."

It is me / the user of your module which is not using it correct? Again most of the time it works - but suddenly without any code changes... it suddenly starts to lag a lot...

And it other words - you would always recommend to use pg-promise instead?

[Garfield2013]

Last question: if I for example have 1000 different queries I would like to execute simultaneously and as lightning fast as possible. How would I do this in the best way via pg-promise? :)

[vitaly-t]

can you elaborate a bit about: "Oftentimes things go wrong because of some non-obvious connection pool misuse

Connection pool is the most valuable resource when working with the database, as it is also the most limited. Its misuse in one place would always yield some grave consequence. Typically, those manifest by depleting the pool, so your app ends up queuing for a connection for a long time, which in turn brings your app scalability down to nothing.

And it other words - you would always recommend to use pg-promise instead?

I'm not gonna do it here, being the author of pg-promise, it will be deemed self-promotion.

if I for example have 1000 different queries I would like to execute simultaneously and as lightning fast as possible. How would I do this in the best way via pg-promise?

I cannot go into that here, not the right place. Best advise - use StackOverflow to research it and ask question, if necessary, or use pg-promise issues, as the last resort.

[Garfield2013]

Okay - thank you so much! I will give pg-promise a try asap... :) cheers you just saved my week! :)

[charmander]

Normally the code above runs fast - but suddenly it takes 20 seconds!?

Suddenly when? After you made a code or dependency change? After some number of requests while your application is running? One day for no reason?

Are you connecting to a local or remote database? What are the connection options? Which versions of pg and Node are you using? Which operating system?

If you don’t use the pool, does it still take as long? How about if you use the native client instead of the JavaScript one, or vice versa? If you connect using something else, like psql?

[Garfield2013]

Without ANY changes - at least what we know of...we are 2 developers here...and we both experience it ...from time to time... right now it happened on my Windows 10 laptop - but it has happen on Ubuntu 18.04 as well...

It happen before on my laptop which has PostgreSql 11.7 server install - so everything running local (host).
We are running version 8.0.3.

It is difficult to check and test - when we can't reproduce it when we like...it just happens from time to time...so I can't tell you right now - if it would work better without the pool...

We have tried the native client some time ago to check if it improved performance in general - but we in our tests it didn't change anything.

[Garfield2013]

Hey guys! I have now created this issue at stackoverflow please check it out: https://stackoverflow.com/questions/62149200/pool-connect-pg-and-db-query-pg-promise-suddenly-takes-20-seconds-to-execute