Configuring Connection Pool
PostgreSQL clusters use PgBouncer for managing connection pools. PgBouncer minimizes overhead when configuring new connections to PostgreSQL. You can configure the connection pool on the Settings page when creating a cluster or during its operation.
To configure it, specify the values for the following parameters:
- pooling mode – strategy for connecting the application server to the database;
- pool size – how many concurrent connections are allowed from the pool to postgresql.
In order not to compromise transaction semantics for connection pooling, pgbouncer supports several types of pooling:
Transaction — connection is assigned to a client for the duration of a transaction. This mode allows each client to use the pool for the duration of a single transaction at a time.
If there are not enough available connections in the pool to handle the transactions submitted, the additional transactions are queued to run once connections are available.
Transaction mode is useful when you have a large number of clients that maintain connections.
This mode is enabled by default.
Session — connection is assigned when a client connects. This mode allows clients to continue issuing requests until they disconnect from the database, which maintains the client’s connection the entire time.
If there are not enough available connections in the pool to handle all the connecting clients, the additional clients are queued up to connect once an existing client disconnects.
Session mode is useful when your application uses prepared statements, advisory locks, listen/notify, or other features which operate on a session level rather than on a transaction level.
Statement — multi-statement transactions are not allowed.
This mode is the most restrictive one and only allows a single statement at a time before moving on to the next in the queue. This means queries with multiple statements are not allowed and will fail.
Statement mode is useful when you force autocommit for each client, meaning each transaction is limited to a single statement.
The pool size determines the minimum number of concurrent connections allowed from the pool to PostgreSQL.
Default pool size is 30. You can increase this value up to 50.