nodejs pg-pool doesn't seem to be pooling

Question

I've been trying to get nodejs to pool postgresql connections in my app unsuccessfully. This is an independent test I did:

const config = require('../config/project_config.json');
const Pool = require('pg-pool');

var pool = new Pool({
    user: config.DB_USER,
    host: config.DB_HOST,
    database: config.DB_DB,
    password: config.DB_PW,
    port: 5432,
    max: 500,
    min: 200,
    idleTimeoutMillis: 0,
    connectionTimeoutMillis: 10000
});

for (var i=0; i<100; i++){
    pool.query(
        "SELECT id, email FROM players WHERE email ~ 'ltUser'",
        [],
        (err, res) => {
            if (err !== null && err != undefined){
                console.log(`err: ${err}`);
            }
            else{
                console.log(`num rows: ${res.rows.length}`);
            }
        });
}

And the result I get is:

num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400

As you can see, it's throwing connection timeouts, which means that it didn't create the connections when I created the pool. I've tried various combinations of parameters when creating the pool, including having a keepalive: true, and none of it seems to make pg-pool actually pool connections. I've also tried pg instead of pg-pool. but got exactly the same results, though I've since found out that it's basically the same code.

If I run it with a longer running query, I can connect to the database in psql and run

SELECT datname,usename, ssl, client_addr, count(*
FROM pg_stat_ssl
  JOIN pg_stat_activity
    ON pg_stat_ssl.pid = pg_stat_activity.pid
where usename != 'azure_superuser'
group by datname,usename, ssl, client_addr;

And watch the connection count for my IP address go up and then back down again.

Am I doing something wrong or is pg-pool broken?

I'm using nodejs v10.22.1 on an ubuntu xenial server.

Answer 1

It turns out that pg-pool is working, just not in the way I expected based on my experience in other programming languages like Java and Erlang. Nodejs doesn't create the connections ahead of time, but when a connection is checked out of the pool.

Based on this the main advantage of pooling in Nodejs is that the programmer doesn't have to handle opening and closing connections, and that connections can be re-used.