Skip to main content

PostgreSQL - Create and use connection pools

Stephanie R.
Updated

Learn how to create and use connection pools.

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.

 

Requirements

 

Concept

Connection pooling allows you to maintain 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.

    NOTE: Several PostgreSQL features, described in the official PgBouncer features page, are known to be broken by the default transaction-based pooling and must not be used by the application when in this mode. You must carefully consider the design of the client applications connecting to PgBouncer, otherwise, the application may not work as expected.

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

 

Instructions

Create a connection pool

To create a new connection pool, log in to your OVHcloud Control Panel and open your database instance via the Public Cloud and Databases menus.

Select the Pools tab, click + Add a pool, and fill in the form.

The settings available are:

  • 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.
  • Pool Mode: Select the pooling mode.
  • Pool 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.

manager_database_addPool_postgresql.png

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

 

Connect to a connection pool

To establish a connection, get information about the pool:

manager_database_pool_moreOptions.png

Click on Information, then collect the required information.

manager_database_pool_information.png

 

Checking

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

  • Terminal 1:
$ psql "postgres://avnadmin:xxxxxxxxxxxxxxxxxxxxx@postgresql-b412100d-o2626ab53.database.cloud.ovh.us:20185/pgpool?sslmode=require"
  • Terminal 2:
$ psql "postgres://avnadmin:xxxxxxxxxxxxxxxxxxxxx@postgresql-b412100d-o2626ab53.database.cloud.ovh.us:20185/pgpool?sslmode=require"

We have two open client connections to the pool. Let's verify that each connection can 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 username = 'avnadmin';
 count
-------
     1
(1 row)

Pgstatactivity outputs the two psql sessions, which use the same PostgreSQL server database connection.

 

Go further

For more information and tutorials, please see our other Managed Databases & Analytics or Platform as a Service guides. You can also explore the guides for other OVHcloud products and services.

Related articles