For AI agents: the complete documentation index is available at https://docs.ovhcloud.com/pl/llms.txt, the full documentation bundle is available at https://docs.ovhcloud.com/pl/llms-full.txt, and this page is available as Markdown at https://docs.ovhcloud.com/pl/guides/public-cloud/databases/postgresql-pool.md.

Create and use connection pools in Public Cloud Databases for PostgreSQL

Pokaż jako Markdown

Create and use connection pools in your Public Cloud Databases for PostgreSQL

Objective

Public Cloud Databases allow you to focus on building and deploying cloud applications while OVHcloud takes care of the database infrastructure and maintenance in operational conditions.

This guide explains how to create and use connection pools.

Requirements

Concept

Connection pooling allows you to maintain very large numbers of connections to a database while minimizing the consumption of server resources.

Why connection pooling?

Eventually a high number of backend connections becomes a problem with PostgreSQL as the resource cost per connection is quite high due to the way PostgreSQL manages client connections. PostgreSQL creates a separate backend process for each connection and the unnecessary memory usage caused by the processes will start hurting the total throughput of the system at some point. Also, if each connection is very active, the performance can be affected by the high number of parallel executing tasks.

It makes sense to have enough connections so that each CPU core on the server has something to do (each connection can only utilize a single CPU core), but a hundred connections per CPU core may be too much. All this is workload specific, but often a good number of connections to have is in the ballpark of 3-5 times the CPU core count.

Connection pooling modes

  • Session pooling: A server connection is assigned to the client application for the life of the client connection. PgBouncer releases the server connection back into the pool once the client application disconnects.

  • Transaction pooling: A server connection is assigned to the client application for the duration of a transaction. When PgBouncer detects the completion of the transaction, it releases the server connection back into the pool.

Warning

Several PostgreSQL features, described in the official PgBouncer features page, are known to be broken by transaction pooling and must not be used by the application when in this mode.

The most frequently encountered cases are:

  • Named prepared statements: since the server connection changes from one transaction to the next, a statement prepared on one connection is not available on the next one. Several drivers and ORMs use them by default. The pgbouncer.max_prepared_statements advanced parameter lets PgBouncer track these statements; a value of 0 disables them.
  • Session-level objects: temporary tables, WITH HOLD cursors, LISTEN/NOTIFY, session-level advisory locks and SET outside a transaction do not survive the connection being returned to the pool.

You must carefully consider the design of the client applications connecting to PgBouncer, otherwise the application may not work as expected. If in doubt, use session pooling.

  • Statement pooling: A server connection is assigned to the client application for each statement. When the statement completes, the server connection is returned into the pool. Multi-statement transactions are not permitted for this mode.

OVHcloud Control Panel Access

  • Direct link:
  • Navigation path: Public Cloud > Select your project > Databases

Instructions

Create a connection pool

Select your PostgreSQL instance, then select the Pools tab. Click on Add a pool, and fill the form.

The settings available are:

  • Connection pool name: Enter a name for your connection pool here. This will also become the "database" or "dbname" connection parameter for your pooled client connections.
  • Database: Choose the database that you want to connect to. Each pool can only connect to a single database.
  • Mode: Select the pooling mode.
  • Size: Select how many PostgreSQL server connections this pool can use at a time.
  • Username: Select the database username that you want to use when connecting to the backend database.

Then click on Create a connection pool.

Add a pool
Info

PgBouncer behaviour can be fine-tuned with the service advanced parameters prefixed with pgbouncer.: server connection lifetime and idle timeout, minimum pool size, number of tracked prepared statements. See the advanced parameters reference for the full list.

Info

You can edit, delete and access information about the pool by clicking on ... to the right of the pool.

Connect to a connection pool

A pool is reached on its own port, which differs from the service port: always use the port shown in the pool information. The database name to use in your connection string is the pool name, not the name of the underlying database.

To establish a connection, get information about the pool:

Pools

Click on ... to the right of the pool, then on Information, and collect the required information.

Info

Connections to a pool are encrypted like direct connections to the service and rely on the same CA certificate. See the Secure the TLS connection guide to verify the server identity with sslmode=verify-full.

Connection pool information

Checking

We can use the psql command-line client to verify that the pooling works as supposed:

From terminal 1:

$ psql "postgres://avnadmin:xxxxxxxxxxxxxxxxxxxxx@postgresql-ab123456-cd7891011.database.cloud.ovh.net:20185/pgpool?sslmode=require"

From terminal 2:

$ psql "postgres://avnadmin:xxxxxxxxxxxxxxxxxxxxx@postgresql-ab123456-cd7891011.database.cloud.ovh.net:20185/pgpool?sslmode=require"

We have two open client connections to the pool. Let's verify that each connection is able to access the database:

Terminal 1:

pgpool=> SELECT 1;
?column?
-------
        1
(1 row)

Terminal 2:

pgpool=> SELECT 1;
?column?
-------
        1
(1 row)

Both connections respond as they should. Now let's check how many connections there are to the PostgreSQL backend database:

Terminal 1:

pgpool=> SELECT COUNT(*) FROM pg_stat_activity WHERE usename = 'avnadmin';
 count
-------
     1
(1 row)

pgstatactivity outputs the two psql sessions, which uses the same PostgreSQL server database connection.

Go further

Advanced parameters for PostgreSQL

Secure the TLS connection

Connect using the CLI

Visit our dedicated Discord channel: https://discord.gg/ovhcloud. Ask questions, provide feedback and interact directly with the team that builds our databases services.

For training or technical assistance implementing our solutions, contact your sales representative or visit our Professional Services page to request a quote and have your project analysed by our experts.

Join our community of users.

Czy ta strona była pomocna?