Re: Pgbouncer

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(dot)dba09(at)gmail(dot)com>
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.
>