Pg-promise: Connection pool with pgBouncer

[msjoshi]

I have a question regarding pg-promise connection pool while using pgBouncer. We have a micro-services architecture where each micro-service will create its own pool of connections using pg-promise and it worked well so far.

As we are scaling out using multi-RDS Postgres cluster in AWS, we are using pgBouncer as an external connection pooler with multiple Postgres databases.

In this configuration does it make sense to create a pool in pg-promise (which essentially is creating a pool with pgBouncer) or should I disable connection pooling in pg-promise? If yes, how can I disable connection pooling in pg-promise? If I set pool size to 1 and idle timeout to 0, will it disable connection pooling so every request creates a connection to pgBouncer and disconnects.

[vitaly-t]

In this configuration does it make sense to create a pool in pg-promise

The connection pool is at the very core of each [Database] object, you cannot not create a pool.

should I disable connection pooling in pg-promise?

You can't.

If I set pool size to 1 and idle timeout to 0, will it disable connection pooling so every request creates a connection to pgBouncer and disconnects.

It is possible, but I won't speculate, as I don't know how this all will work in combination with pgBouncer. You might want to address a wider audience for this by asking on StackOverflow.

And if it is at all possible, then you would be closer to the truth by investigating it against the driver directly, or its connection pool, because those are the ones used within pg-promise.

[vitaly-t]

I have done a little more investigation into this. Here's one of the related issues: https://github.com/brianc/node-postgres/issues/975

From what I've seen, you can create a pool of size 1 within this library, and use it that way, but I'm not sure how this going to affect the scalability of the app. You see, the connection pool is what provides the two key features here:

• scalability, through dynamically allocated physical connections, according to the maximum number of parallel requests, and capped by the maximum pool size
• automatic restoration of lost connections

If the double-pooling compromises any of those two key functions, the end result will be bad. You cannot use an API that doesn't scale its connectivity, and you cannot rely on something that cannot auto-fix broken connections.

---

Other than that, there are lots of issues pertaining to pgBouncer logged against the driver: https://github.com/brianc/node-postgres/issues?utf8=%E2%9C%93&q=pgBouncer

[msjoshi]

Thanks @vitaly-t for the investigation. The post in node-postures was very helpful.

[vitaly-t]

I trust you will continue the search against the links provided above. So I'm closing it here ;)