Skip to main content

Troubleshoot connection pooling issues in Aiven for PostgreSQL®

Discover the PgBouncer connection pooler and learn how to cope with some specific connection pooling issues.

About connection pooling with PgBouncer​

PgBouncer is a lightweight connection pooler for PostgreSQL® with low memory requirements (2 kB per connection by default).

PgBouncer offers several methods when rotating connections:

  • Session pooling: This is the most permissive method. When a client connects, it gets assigned with a server connection that is maintained as long as the client stays connected. When the client disconnects, the server connection is put back into the pool. This mode supports all PostgreSQL features.

  • Transaction pooling: A server connection is assigned to a client only during a transaction. When PgBouncer notices that the transaction is over, the server connection is put back into the pool.

    warning

    This mode breaks a few session-based features of PostgreSQL. Use it only when the application cooperates without using the features that break. For incompatible features, see PostgreSQL feature map for pooling modes.

  • Statement pooling: This is the most restrictive method, which disallows multi-statement transactions. This is meant to enforce the autocommit mode on the client and is mostly targeted at PL/Proxy.

Handling connection pooling issues​

A high CPU utilization while using the PgBouncer pooling may indicate a usage anti-pattern with a suboptimal pooling method selection or frequent reconnect operations.

SSL handshakes are expensive resource-wise with asymmetric cryptography adding overhead. After a negotiation, relatively efficient symmetric ciphers are used.

If clients in the application pool frequently disconnect between queries, this negates part of the benefit of the pooler and adds additional overhead.

For most applications with a large pool of clients, the transaction pooling allows the application pool to maintain their connections, which helps avoid the overhead of new connection requests.

For the setup and configurations of PgBouncer, refer to Connection pooling.

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

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