Skip to main content

PgBouncer connection pooler in a PostgreSQL TimescaleDB cluster

In PostgreSQL TimescaleDB, a separate process is created to handle each client connection. The greater the number of connections, the more processes consume RAM. The maximum number of connections to the PostgreSQL TimescaleDB process is determined by the max_connections parameter.

To optimize resource consumption, you can use a connection pooler. Clients do not connect directly to PostgreSQL TimescaleDB, but to the connection pooler. At the same time, a small number of connections is maintained between the pooler and the PostgreSQL TimescaleDB server — the pooler creates a new connection or reuses an existing one. The number of connections between the pooler and the database on each cluster node is determined by the pool size (the pool_size parameter).

In PostgreSQL TimescaleDB Managed Database clusters, the PgBouncer connection pooler is used. Three PgBouncer pooling modes are available. Learn more about the connection pooler in the PgBouncer documentation.

Maximum number of connections to the PostgreSQL process (max_connections)

The PostgreSQL TimescaleDB max_connections DBMS parameter determines the maximum number of concurrent connections to the PostgreSQL TimescaleDB process. The default value is set to 100 connections.

You can change the parameter value in the DBMS settings in the control panel to the maximum number of connections under load. Keep in mind that each connection consumes RAM resources.

Changing the max_connections value entails restarting the cluster nodes PostgreSQL TimescaleDB.

Pool size (pool_size)

The pool size (the pool_size parameter) is the maximum number of connections between the connection pooler and each PostgreSQL TimescaleDB database on each cluster node.

You can choose the pool size when creating a cluster and change the size in an existing cluster. Available values are from 1 to 500.

Pooling modes

The pooling mode is a client connection strategy for PostgreSQL TimescaleDB. In PostgreSQL TimescaleDB Managed Database clusters, the PgBouncer connection pooler is used. Learn more about the connection pooler in the PgBouncer documentation.

PgBouncer supports three modes:

By default, Cloud Databases uses the transaction mode.

You can choose the pooling mode when creating a cluster and change the mode in an existing cluster.

Transaction mode (transaction)

The connection to PostgreSQL TimescaleDB is maintained until the transaction completes. When the transaction completes, the pooler returns the connection to the pool. Later, this connection can be reused by the same client for other connections or by another client.

The total number of client connections to PgBouncer can reach 10,000, but the number of active transactions is determined by the pool size. For example, if the pool size is 30, there will be 30 active transactions.

The number of connections between the connection pooler and each PostgreSQL TimescaleDB database on each cluster node is also determined by the pool size.

A client can simultaneously execute multiple transactions on different connections. In this case, each connection between the connection pooler and the PostgreSQL TimescaleDB server can execute transactions of different clients during its lifecycle.

Transaction mode reduces the load on DBMS resources when there are many low-load client connections.

Limitations of transaction mode

Transaction mode disrupts some PostgreSQL TimescaleDB features. Choose a different mode if clients use these options. Some connection flags may be distributed across different clients, which may lead to unpredictable behavior and incorrect results.

The following do not work in transaction mode:

Learn more about incompatible options in the PgBouncer documentation.

Session mode (session)

In session mode, a client can continue sending queries as long as the session lasts — the connection between the connection pooler and the PostgreSQL TimescaleDB server will be maintained until the client disconnects from the database.

The number of connections between the connection pooler and the PostgreSQL TimescaleDB server is determined by the pool size. Each client connection uses a connection between the pooler and the PostgreSQL TimescaleDB server. The connection is returned to the pool and can only be reused after the previous client disconnects from the database.

Unlike transaction mode, this mode is safe, replicates a direct connection to PostgreSQL TimescaleDB, supports all features, and is suitable for all PostgreSQL TimescaleDB clients. When using this mode, resource load is not reduced.

This connection mode is useful for clients with many short-lived database connections because it increases the connection speed to the DBMS.

Statement mode (statement)

The pooler will return the connection to the pool as soon as the first query is processed — multi-statement transactions will be aborted, and the pooler will return an error.

This mode allows using more client connections than transaction mode. It is suitable if you know that each transaction is limited to a single query (AUTOCOMMIT is enabled).

Change pooling mode

For your information

If you choose transaction mode, see limitations of this mode.

  1. In the Control panel, in the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the Connection pooler block, click Edit and select a pooling mode.
  5. Click Save.

Change pool size

  1. In the Control panel, in the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the Connection pooler block, click Edit and change the pool size.
  5. Click Save.