Aiven for PostgreSQL® connection pooling with PgBouncer

Connection pooling in Aiven for PostgreSQL® services allows you to maintain very large numbers of connections to a database while minimizing the consumption of server resources.

About connection pooling

Aiven for PostgreSQL connection pooling uses PgBouncer to manage the database connection.

Unlike when you connect directly to the PostgreSQL® server, each client connection does not require a separate backend process on the server. PgBouncer automatically inserts 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.

Maximum number of client connections

How many client connections your service can handle depends on the RAM size that your service plan supports.

• Each gigabyte of RAM allows 500 connections.
• Minimum number of client connections per service is 5000.
• Maximum number of client connections per service is 50000.

Calculate max_client_connections

Use the following formula to calculate how many client connections your service can handle:

$min(max(n * 500, 5000), 50000)$

Where:

• n is the number of RAM GB that a service plan supports.

• $n * 500$ is intermedia_max_connections.

• $5000 \leq intermedia\_max\_connections \leq 50000$

  • If intermedia_max_connections is less than 5000, lower bound 5000 applies.
  • If intermedia_max_connections is greater than 50000, upper bound 50000 applies.

Examples

• Startup-4 service plan (4 GB RAM)

  $n = 4$

  $n * 500 = 2000$

  $min(max(2000, 5000), 50000) = 5000$

  For a startup-4 machine, pgbouncer_max_client_connections is 5000.

• Business-16 service plan (16 GB RAM)

  $n = 16$

  $n * 500 = 8000$

  $min(max(8000, 5000), 50000) = 8000$

  For a business-16 machine, pgbouncer_max_client_connections is 8000.

• Business-120 service plan (120 GB RAM)

  $n = 120$

  $n * 500 = 60000$

  $min(max(60000, 5000), 50000) = 50000$

  For a business-120 machine, pgbouncer_max_client_connections is 50000.

Connection pooling benefits

A high number of backend connections can become a problem with PostgreSQL, as the resource cost per connection is quite high due to how PostgreSQL manages client connections. PostgreSQL creates a separate backend process for each connection, and the unnecessary memory usage caused by the processes will start affecting the total throughput of the system at some point. Moreover, 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 utilise 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 roughly 3-5 times the CPU core count. Aiven enforces connection limits to avoid overloading the PostgreSQL database.

note

Since 9.6, PostgreSQL offers parallelization support enabling to run queries in parallel on multiple CPU cores.

Without a connection pooler, the database connections are handled directly by PostgreSQL backend processes, with 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:

Instead of having dedicated connections per client, now PgBouncer manages the connections assignment optimising them based on client request and settings like the pooling modes.

tip

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 server-side solution is a better approach.

Connection pooling modes

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

• The default and recommended setting option is transaction pooling mode allows each client connection to take their turn in using a backend connection for the duration of a single transaction. After the transaction is committed, the backend connection is returned back into the pool and the next waiting client connection gets to reuse the same connection immediately. In practice, this provides quick response times for queries as long as the typical execution times for transactions are not excessively long. This is the most commonly used PgBouncer mode and also the default pooling mode in Aiven for PostgreSQL.

warning

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.

• The session pooling mode means that once a client connection is granted access to a PostgreSQL server-side connection, it can hold it until the client disconnects from the pooler. After this, the server connection is added back onto the connection pooler's free connection list to wait for its next client connection. Client connections are accepted (at TCP level), but their queries only proceed once another client disconnects and frees up its backend connection back into the pool. This mode can be helpful in some cases for providing a wait queue for incoming connections while keeping the server memory usage low, but is of limited use under most common scenarios due to the slow recycling of the backend connections.
• The statement operational pooling mode, similar to the transaction pool mode, except that instead of allowing a full transaction to run, it cycles the server-side connections after each and every database statement (SELECT, INSERT, UPDATE, DELETE statements, etc.). Transactions containing multiple SQL statements are not allowed in this mode. This mode is sometimes used, for example when running specialised sharding frontend proxies.

Password encryption migration to SCRAM and compatibility with PGBouncer

Aiven for PostgreSQL now defaults to scram-sha-256 password encryption for enhanced security. MD5 password encryption will be deprecated in future PostgreSQL versions.

Database users managed by Aiven can be upgraded from MD5 with a single button.

Organizations with existing PGBouncer pools may need to take action to ensure compatibility. If you have PGBouncer connection pools configured and are experiencing authentication issues, this may be related to password encryption methods.

Organization with database users that aren't managed by Aiven can follow the guidance below to re-hash their passwords.

Who is affected

• Organizations with PGBouncer connection pools that are tied to specific database users
• Services that have created additional database users

Recommended actions

Update your user config to enforce scram-sha-256 for your service

Update the password encryption value in your service's user_config:

{
  "pg": {
    "password_encryption": "scram-sha-256"
  }
}

This maintains MD5 compatibility: you may re-hash the password (shown below) at a later point. New managed users' password will be hashed and authenticated using scram-sha-256.

Re-hash database user password to upgrade to scram-sha-256

Re-hash user passwords: Existing passwords supported by MD5 need to be re-hashed to use the new encryption.

This can be done using the following SQL statement:

ALTER ROLE <rolename> PASSWORD 'new_password';

Here is example Python code to list all database users and upgrade them to SCRAM:

# Use avn-client to fetch the avnadmin service user connection details
# Then provide a script that can be run using uv to pack all dependencies

Update your pgBouncer connection pool configuration

When connection pools are configured with specific user names, attempting to connect using another role will fail with a permission denied error.

This is due to the challenge-response flow initiated by the PG client, that ensures

Troubleshooting connection issues

If you experience authentication failures:

1. Check client library support: Ensure your PostgreSQL client supports scram-sha-256
2. Verify PGBouncer pool configuration: Check
3. Review connection logs: Look for authentication method mismatches

Need help?

Contact Aiven support if you need assistance with:

• PGBouncer configuration updates
• Client library compatibility
• Migration planning for large deployments