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.

Answer 2

If you want to open a certain number of connections to the back end, say 200 ( this is too large a number, likely you want about 64 )

Then you can do this by creating the pool, and then immediately issuing 200 queries, without releasing the clients

And then release all 200 clients.

From that point on, if idleTimeoutMillis === 0, the pool will forever hold 200 open connections to the DB, which you can use as an initialised pool

var pool = new Pool({
    user: config.DB_USER,
    host: config.DB_HOST,
    database: config.DB_DB,
    password: config.DB_PW,
    port: 5432,
    max: 200,   // set max to 200 connections to the db
    //min: 200, // min is not a configuration option
    idleTimeoutMillis: 0,
    connectionTimeoutMillis: 10000
});

// create a list to hold all the "done" functions 
//   that release clients
let releaseClientDoneList = [];

// open 200 clients, without releasing them
for (var i=0; i<200; i++){
  // callback - checkout a client
  pool.connect((err, client, done) => {
      // asyncronously store the "done" function once the client 
      //   has connected to the db
      connectedNewClient(done);
    }
  )
}

let connectedNewClient = function (doneFunction) {
  // store the "done" function in the list
  releaseClientDoneList.push(doneFunction)

  // if the list has 200 "done" functions inside, 
  //   then we know we have initialised all the clients we want
  if (releaseClientDoneList.length >= 200) {
    
    // call all the "done" functions to release all the clients
    for (let doneFunctionReleasesClient of releaseClientDoneList) {
      doneFunctionReleasesClient();
    }

    // now we know there are 200 available, 
    //   initialised db connections in the pool
    databasePoolInitialised();
  }
}

let databasePoolInitialised = function () {
  // ... rest of your application code goes here
}