Node-postgres: How I can have two different connection pools for two different databases?

[alekbarszczewski]

It is possible to set pool size in pg.defaults. However let's say I have two databases and two separate connection strings for them:

postgres://localhost/db1
postgres://localhost/db2

And I want to have two different pool sizes for each of them. For example max 20 connections for db1 and max 100 connections for db2. How I can do that? Changing pool size in pg.defaults will set it globally...

[rpedela]

I don't think it is supported. I recommend using pgbouncer for connection pooling. It is more flexible, faster, mature than the node-postgres connection pooling. There are other options too from the Postgres community. If you use pgbouncer, then you can open/close single connections in your JS code without any significant performance penalty.

[vitaly-t]

@rpedela and how does this work in Node.js?

Or more accurately, with this library?

[rpedela]

You configure pgbouncer, have it run as a daemon, and then you make Postgres queries to pgbouncer (default port 6543) in Node.js like normal without using the pooling code like the example in the README:

var pg = require('pg');

var conString = "postgres://username:password@localhost:6453/database";

var client = new pg.Client(conString);
client.connect(function(err) {
  if(err) {
    return console.error('could not connect to postgres', err);
  }
  client.query('SELECT NOW() AS "theTime"', function(err, result) {
    if(err) {
      return console.error('error running query', err);
    }
    console.log(result.rows[0].theTime);
    //output: Tue Jan 15 2013 19:12:47 GMT-600 (CST)
    client.end();
  });
});

[vitaly-t]

Does this mean that we can use it through the pooling also? I understand it would be more like double-pooling, but it would make this thing compatible with how most people use this library - through the connection pool.

[rpedela]

Yes you could double pool or set the Node.js pool size to 1.

[vitaly-t]

@rpedela Now with version 4.5.3 we have isolated Native Bindings from JavaScript Bindings, which includes the pool instances.

So, if you only have 2 databases, then you can have one database use Native Bindings, and another one - JavaScript bindings. This will give you two separate pools.

It is a little nasty, but quite functional work-around for you ;)

I have added full support for this within the latest pg-promise, it works just fine ;)

[alekbarszczewski]

Hey I think I found solution - instead of passing connection string to pg.connect it is possible to pass connection config as object (described in docs). However inside config it is possible to pass all pool options (which is not described in docs).

For example:

var pg = require('pg')

pg.connect({
  host: 'localhost',
  database: 'db1',
  poolSize: 10
}, function (err, client, done) {
  done()
  pg.connect({
    host: 'localhost',
    database: 'db2',
    poolSize: 20
  }, function (err, client, done) {
    done()
    console.log(Object.keys(pg.pools.all)) // Two pools with different sizes
  })
})

// output:
// [ '{"host":"localhost","database":"db1","poolSize":10}',
//  '{"host":"localhost","database":"db2","poolSize":20}' ]

In code above first pg.connect() will use one pool and second one another one.
This means it is possible to get two pools of connections to different databases, but also two different pools of connections to same database.

[vitaly-t]

In code above first pg.connect() will use one pool and second one another one.

Are you sure about that? I'm not. I believe they use one shared pool.

[alekbarszczewski]

I did not test it, but pg.connect calls pools.getOrCreate() internally, and this method (pools.getOrCreate()) returns two different instances of Pool. I am pretty sure it means that they are separate pools with completely different configs.

---

Look at the output - two separate pools were created. Pool is identified by JSON.stringify(config) so when one config differs from another (database or poolSize or port etc) then new, independent pool is created for it.

[vitaly-t]

I have looked at the code, and I can see it creates a new pool for every new connection detail, regardless of whether it is a configuration object or a connection string.

module.exports = function(Client) {
  var pools = {
    //dictionary of all key:pool pairs
    all: {},
    //reference to the client constructor - can override in tests or for require('pg').native
    getOrCreate: function(clientConfig) {
      clientConfig = clientConfig || {};
      var name = JSON.stringify(clientConfig);
      var pool = pools.all[name];
      if(pool) {
        return pool;
      }
      pool = genericPool.Pool({
        name: name,
        max: clientConfig.poolSize || defaults.poolSize,
        idleTimeoutMillis: clientConfig.poolIdleTimeout || defaults.poolIdleTimeout,
        reapIntervalMillis: clientConfig.reapIntervalMillis || defaults.reapIntervalMillis,
        log: clientConfig.poolLog || defaults.poolLog,

But in this case, why is there a problem to begin with? You should have been given a new pool for each of your connection strings (one per database).

Did you check that somehow it wasn't working?

[brianc]

I've deprecated the singleton opaque pool factory in favor of manually creating and managing your own pool instances in your app: https://github.com/brianc/node-pg-pool

[PaulKushch]

@brianc @vitaly-t @rpedela Hi, any updates on this issue? I use serverless and pgbouncer. What is the correct way now days to use node-postgres library with such a set up? Also is it possible to execute multiple queries in the same statement?
Great library btw!
Best Regards

[brianc]

if you want two pools it's pretty straight forward:

const pool1 = new pg.Pool({ database: 'database1' })
const pool2 = new pg.Pool({ database: database2' })

Up to you in your app to decide which pool to use.

As far as serverless + pgbouncer...it should work though I don't run pgbouncer myself in production.

Also is it possible to execute multiple queries in the same statement?

You can only run multiple queries in a single statement if you do __not__ use parameters. This is a postgres protocol restriction & there's no working around it. 99% of the time just do multiple statements w/ async/await

const res1 = await pool.query('SELECT 1')
const res2 = await pool.query('SELECT 2')

If you absolutely need to execute multiple statements in the same query, don't use parameters:

const res = await pool.query('SELECT 1; SELECT 2;).