Aiven PostgreSQL connection pooling

Aiven PostgreSQL connection pooling allows you to maintain very large numbers of connections to a database while keeping the server resource usage low.

Aiven PostgreSQL connection pooling utilizes PGBouncer for managing the database connection and each pool can handle up to 5000 database client connections. Unlike when connecting directly to the PostgreSQL server, each client connection does not require a separate backend process on the server. PGBouncer automatically interleaves the client queries and only uses a limited number of actual backend connections, leading to lower resource usage on the server and better total performance.

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 [1]), 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.

[1] PostgreSQL 9.6 introduced limited parallelization support for running queries in parallel on multiple CPU cores.

Without a connection pooler the database connections are handled directly by PostgreSQL backend processes, one process per connection:

Adding a PGBouncer pooler that utilizes fewer backend connections frees up server resources for more important uses, such as disk caching:

Many frameworks and libraries (ORMs, Django, Rails, etc.) support client-side pooling, which solves much the same problem. However, when there are many distributed applications or devices accessing the same database, a client-side solution is not enough.

Connection pooling modes

Aiven PostgreSQL supports three different operational pool modes: “session”, “transaction” and “statement”.

How to get started with Aiven PostgreSQL connection pooling

First you need an Aiven PostgreSQL service, for the purposes of this tutorial we assume you already have created one. A quick Getting Started guide is available that walks you through the service creation part.

This the overview page for our PostgreSQL service in the Aiven web console. You can connect directly to the PostgreSQL server using the settings described next to “Connection parameters” and “Service URL”, but note that these connections will not utilize PGBouncer pooling.

Clicking the “Pools” tab opens a list of PGBouncer connection pools defined for the service. Since this service was launched, there are no pools defined yet:

To add a new pool click on the “Add pool” button:

The pool settings are:

For the purposes of this tutorial we’ll name the pool as “mypool” and set the pool size as 1 and the pool mode as “statement”. Confirming the settings by clicking “Add pool” will immediately create the pool and the pool list is updated:

Clicking the “Info” button next to the pool information shows you the database connection settings for this pool. Note that PGBouncer pools are available under a different port number from the regular unpooled PostgreSQL server port. Both pooled and unpooled connections can be used at the same time.

Verifying the connection pool

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

From terminal #1:

 $ psql <pool-uri>

From terminal #2 we open another connection:

 $ psql <pool-uri>

Now we have two open client connections to the PGBouncer pooler. Let’s verify that each connection is able access the database:

Terminal #1:

mypool=> SELECT 1;
?column?
──────────
1
(1 row)

Terminal #2:

mypool=> 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:

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

And as we can see from the pg_stat_activity output the two psql sessions use the same PostgreSQL server database connection.

Summary

The more client connections you have to your database, the more useful connection pooling becomes. Aiven PostgreSQL makes using connection pooling an easy task and migrating from non-pooled connections to pooled connections is just a matter of gradually changing your client-side connection database name and port number!

Try PostgreSQL 9.6 for free in Aiven

Remember that trying Aiven is free: you will receive US$10 worth of free credits at sign-up which you can use to try any of our service plans. The offer works for all of our services: PostgreSQL, Redis, InfluxDB, Grafana, Elasticsearch and Kafka!

Go to https://aiven.io/ to get started!

Cheers,
Team Aiven

Your database in the cloud, www.aiven.io

Love podcasts or audiobooks? Learn on the go with our new app.