Node Postgres storing multiple database connections ...

[Mark S]

I've got a few virtual hosts, and each one has its own database. I
want to keep persistent connections to each db if possible, and am a
little confused about how node-postgres handles this sort of thing.

I'm using cluster to create a couple of child processes, and am aiming
for each process to have this :

var conns = {
hostname1: {
db: theDbConn,
redis: 1
}
hostname2: {
db: theDbConn,
redis: 2
}
}

So depending on the request host, I can basically
var conn = conns[req.headers('Host')]
and I then know which redis db to 'SELECT' "conn.redis", and want
"conn.db" to give a guaranteed handle to the correct database.

Is this do-able? All the examples I've come across connect to and
disconnect from the database per request. Is the built in client pool
going to work against me if I try this?

Thanks,

Mark.

[brianc]

Good question.

The built in client pool using 'pg.connect' is merely a convenient
shortcut for creating your own client via the client constructor or
instead passing you an idle client if one exists. It's helpful for the
"common scenario" but can be safely and completely bypassed by using
the client constructor directly. It would likely be wise to use a
pool of some sort to avoid opening and closing database connections
frequently (a quasi-costly operation when compared to a single query
dispatch). One other thing to note is using 'pg.connect' will create a
unique pool for each unique connection string so in your described
situation you could pass conns[host].db to the pg.connect function and
it would maintain separate pools for each host.

Apologies for typos. I'm on iPhone keyboard.

[Mark S]

Thanks for the quick response Brian.

Is there an easy way to limit the size of the pools? Or should I just
let it "do its stuff?"

I'm asking because the production environment is 16 core, and I'm
thinking of initially allocating something like 8 to nodes, 4 to Nginx
and 4 to Postgres (on the basis that Nginx and Postgres will pretty
much be IO bound) and then tuning further from there (as much as
possible will be cached and gzipped so Nginx will be serving most
requests directly.)

If I have 50 virtual hosts and 8 nodes, that's 400 pools, so 400 *
connections per pool to Postgres. Hmmm, maybe I'm answering my own
question and 8 nodes is a silly idea?

Does anyone have any thoughts on this?

Thanks,

Mark.

[brianc]

require ('pg').defaults.poolSize = 10; you can set this to a different
number before creating any connections. If you need more granular
control you'll have to make your own pools unfortunately. lib/index.js
contains the pooling logic if you want a reference.

https://github.com/brianc/node-postgres/blob/master/lib/index.js

[Mark S]

Perfect. Thanks for your help :-)