Hi Rajesh,
Without taking into account PgBouncer, what is your actual requirement? What are you trying to achieve?
I believe there is a misunderstanding with the actual purpose and role of PgBouncer and the concept of connection pooler.
Using session mode.
My requirement is simply handle idle connections but there should always have one connection open per client.
Do you want to have or not to have idle connections on the database?
Just to give a brief background information; PostgreSQL is process based. While a new database connection is starting, postgres process forks. And, this is an expensive operation and takes time.
PgBouncer is a connection pooler. It keeps database connections open and idle. Whenever a client wants to establish a new database connection, PgBouncer assigns an idle database connection from its appropriate connection pool to the client. Thanks to having open and idle connections on the database at PgBouncer's disposal, starting a new connection to the database becomes less expensive, more efficient, and faster. In this picture, clients start connections to PgBouncer instead of directly to the database.
Shortly, the expensive connection initialisation cost is paid once at the beginning. And the same connections are used by multiple clients multiple times.
If you have PgBouncer in your setup, you will have some idle database connections, which is sort of the desired result, unless you have too many idle database connections. Definition of "too many" is context dependent.
I also recommend you to review the reason why you use session mode for connection pool. Does the application depend on some session level features of PostgreSQL?
Best regards.
Samed YILDIRIM
On Tue, 23 Jan 2024 at 22:58, Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx> wrote:
Hi all,I have been facing difficulty to configure pgbouncer for past 2 months. I get lot of errors such as query_wait_timeout error, server_idle_timeout error etc.Using session mode. Plz help me with the pgbouncer configuration (I have tried enough from internet answers).My requirement is simply handle idle connections but there should always have one connection open per client.
