Skip to main content

PgBouncer connection pooler in a PostgreSQL TimescaleDB cluster

Last update:

PostgreSQL TimescaleDB creates a separate process to handle each client connection. The more connections there are, the more processes are used, which consumes RAM. The maximum number of connections to a PostgreSQL TimescaleDB process is determined by the parameter max_connections.

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

PostgreSQL TimescaleDB managed database clusters use the PgBouncer connection pooler. Three modes of PgBouncer pooling are available. Read more about the connection pooler in the PgBouncer documentation.

Maximum number of connections to a PostgreSQL process (max_connections)

The PostgreSQL TimescaleDB DBMS max_connections parameter defines the maximum number of simultaneous connections to a PostgreSQL TimescaleDB process. The default value is 100 connections.

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

Changing the max_connections value causes the PostgreSQL TimescaleDB cluster nodes to restart.

Pool size (pool_size)

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

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

Pooling modes

Pooling mode is a strategy for connecting a client to PostgreSQL TimescaleDB. PostgreSQL TimescaleDB managed database clusters use the PgBouncer connection pooler. Read more about the connection pooler in the PgBouncer documentation.

PgBouncer supports three modes:

By default, cloud databases use the transaction mode (transaction).

You can select 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 terminates, the pooler returns the connection to the pool. This connection can later 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 determines 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. Each connection between the connection pooler and the PostgreSQL TimescaleDB server can execute transactions from different clients during its lifecycle.

Transaction mode reduces the load on DBMS resources if there are a large number of low-load client connections.

Limitations of transaction mode

Transaction mode interrupts the operation of certain PostgreSQL TimescaleDB mechanisms. Select another mode if clients use these options. Some connection flags can be shared between different clients, which can lead to unpredictable behavior and incorrect results.

The following do not work in transaction mode:

Read 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 continues — 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. For each client connection, a connection between the pooler and the PostgreSQL TimescaleDB server is used. The connection is returned to the pool and can be reused only after terminating the previous client's connection to the database.

Unlike the transaction mode (transaction), this mode is secure, replicates a direct connection to PostgreSQL TimescaleDB, supports all mechanisms, and is suitable for all PostgreSQL TimescaleDB clients. Resource load is not reduced when using this mode.

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

Statement mode (statement)

The pooler returns the connection to the pool as soon as the first request is processed — transactions with multiple statements will abort, and the pooler will return an error.

This mode allows for more client connections than transaction mode. This mode is suitable if it is known that each transaction is limited to a single request (AUTOCOMMIT enabled).

Change pooling mode

For your information

If you select the transaction mode (transaction), read the limitations of this mode.

  1. In the control panel, click Products in the top menu 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 the pooling mode.
  5. Click Save.

Change pool size

  1. In the control panel, click Products in the top menu 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.