Question 253076 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Usage scenario for Pgbouncer in a multitenant application with multiple DBs

So far, in my experience, I have seen pgbouncer used in a multi-client, single DBMS, single DB scenario: i.e. many hosts, running the same application, connect to the same db on a single DBMS host, and pgbouncer helps to reduce the number of connections open on the DBMS; without pgbouncer we would have P x no-of-client-hosts connections (where P is the number of connections opened by the single application on a generic client host).

Now I am facing a different scenario and I would like to know, before diving into the documentation, if pgbouncer can help me also here.

I have a single host A where a "multitenant" application is running. This application connects to a single DBMS host (could be the same host A or a different one, that’s not the point, i think). Every tenant connects to its own database (configured application side with a pgsql uri) inside the same DBMS via a connections pool (let’s say P).

So here i have a single client, multiple tenant, single DBMS, multiple DB scenario.

Since the number of tenants can be hundreds, even with a P of 5 connections, the total number of connections to the DBMS can easily reach thousands.

Please note that query throughput is minimal.

Can pgbouncer be configured to minimize open connections on the DBMS?

I tried to figured out a configuration but, probably i am wrong, every database on the dbms needs to be "declared" in pgbouncer.ini… that would not allow me to add tenant on-the-fly without a pgbouncer reconfiguration.
Am I correct?

Tags:

2

Answers


  1. 0

    If these hundreds of databases are all in the same cluster, that is a broken design. You need one connection pool per database, so even if that pool is small, you will end up with hundreds or thousands of connections, which will be bad for performance.

    This is not a limitation of pgBouncer, but lies in the nature of PostgreSQL: a database connection is tied to a certain database.

    You should redesign the application. Perhaps several tenants could share a database.

    Login or Signup to reply.
  2. 0

    Please note that query throughput is minimal.

    Do you need a pool at all? Just close every connection when you are done with it. Establishing a connection is expensive if you do it hundreds of times a second, but not if you do it a minimal number of times.

    I tried to figured out a configuration but, probably i am wrong, every database on the dbms needs to be "declared" in pgbouncer.ini… that would not allow me to add tenant on-the-fly without a pgbouncer reconfiguration. Am I correct?

    You seem to be incorrect. See the docs, near the line * = host=foo. (That still doesn’t make this a good idea)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search