If your business scenario involves many short-lived connections or requires you to frequently create and destroy connections, you can enable the connection pool feature for ApsaraDB RDS for PostgreSQL. This feature reuses database connections to significantly reduce the performance overhead of such connections on your instance. It also helps you control the load and resource consumption of the database server.
Features
The connection pool feature of ApsaraDB RDS for PostgreSQL is based on the open source component PgBouncer. It acts as middleware between your application and the database to manage and reuse database connections.
Connection reuse: The connection pool prioritizes the reuse of existing connections. This avoids the performance overhead of frequently creating and destroying connections. It significantly reduces the database server load, improves application response time, and increases concurrency.
Resource control: The connection pool lets you set limits on parameters, such as the maximum number of client connections and the maximum idle time. This effectively prevents an excessive number of connections from consuming system resources.
Prerequisites
Your ApsaraDB RDS for PostgreSQL instance must meet the following requirements:
The major engine version of the instance is PostgreSQL 11 or later.
The product series is Basic Edition or High-availability Edition.
The billing method is subscription or pay-as-you-go.
The minor engine version of the instance is 20240830 or later, and the version number does not have the
babelfishsuffix.NoteFor more information about how to view or upgrade the minor engine version, see Upgrade the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
Billing
This feature is free of charge.
Notes
When you enable PgBouncer, a new PgBouncer port (default: 6432) is allocated. Direct access through the original database port (default: 5432) is not affected. After you disable PgBouncer, you can no longer access the instance through the PgBouncer port. Make sure that your application is configured to use the original database port.
After you enable PgBouncer, the system automatically installs the pgbouncer_fdw and dblink plugins in the postgres database to support viewing connection pool metrics. You cannot uninstall these plugins.
When you enable SSL encryption for an instance, PgBouncer also enables SSL. However, it does not support connection authentication with the Access Control List (ACL) set to `verify-ca` or `verify-full`, or the use of a client certificate revocation file.
For applications that involve complex transactions, require tracking of database connection status, or use specific PostgreSQL features, connect directly to the database instead of using PgBouncer.
The maximum idle time for a connection in the pool is 10 minutes by default. Idle connections that exceed this duration are closed. You cannot change this value.
Procedure
Enable or disable the connection pool
Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, click Database Connection.
Click Enable PgBouncer or Disable PgBouncer.
In the dialog box that appears, click OK.
After PgBouncer is enabled, you can view the PgBouncer port (default: 6432) on the Database Connection page.
You can click Modify Endpoint. In the Modify Endpoint window, select an endpoint type and then modify the PgBouncer Port.
Connect to an RDS for PostgreSQL instance through the connection pool
After you enable PgBouncer, replace the connection port with the PgBouncer port (default: 6432) in your application or client. Other connection parameters, such as the endpoint, username, and password, remain unchanged. For more information about how to connect to an instance, see Connect to an ApsaraDB RDS for PostgreSQL instance.
Modify connection pool parameters
After you enable PgBouncer, you can use the Parameter Settings feature to modify PgBouncer parameters and adjust the connection pool configuration, such as controlling the maximum number of client connections.
You can view and modify PgBouncer parameters using the Parameter Settings feature only after you enable PgBouncer.
If you want to use a parameter template to modify parameters in batches, you must first enable PgBouncer and then apply the parameter template to your RDS for PostgreSQL instance.
The default parameter template is PostgreSQL_PgBouncer_Default Parameter Template (rpg-sys-pgsql-pgbouncer).
The modifiable parameters are described as follows:
Parameter | Type | Default value | Description |
pgbouncer.pool_mode | string | transaction | The connection pool mode.
|
pgbouncer.default_pool_size | int | 20 | The default number of connections allowed in the connection pool. |
pgbouncer.max_client_conn | int | 100 | The maximum number of client connections allowed by the connection pool. |
pgbouncer.min_pool_size | int | 0 | The minimum number of client connections allowed by the connection pool. |
pgbouncer.query_wait_timeout | int | 120 | The maximum time in seconds that a query can wait in the queue for execution. A value of 0 indicates an indefinite wait. If the query times out, the client is automatically disconnected. |
pgbouncer.ignore_startup_parameters | string | "extra_float_digits" | A comma-separated list of extra parameters that the connection pool ignores. By default, the connection pool processes only core parameters (client_encoding, datestyle, timezone, standard_conforming_strings). Other parameters cause the connection to be denied. To support extra parameters, list them here. The connection pool automatically ignores these parameters to ensure a successful connection. Important When you add other parameters, retain extra_float_digits to ensure compatibility with PostgreSQL Java Database Connectivity (JDBC) connections. |
pgbouncer.stats_users | string | "" | A comma-separated list of users who are allowed to connect to the virtual database of the connection pool and execute read-only queries. |
For more information about the parameters, see the official PgBouncer documentation.
View connection pool metrics
You can use the Enhanced Monitoring feature to view connection pool metrics. The related metrics are available in Enhanced Monitoring only after you enable the connection pool feature.
The metrics are as follows:
Metric | Description |
db.pgbouncer.client_connections.active | The number of active client connections. |
db.pgbouncer.client_connections.waiting | The number of waiting client connections. |
db.pgbouncer.server_connections.active | The number of active server connections. |
db.pgbouncer.server_connections.idle | The number of idle server connections. |
db.pgbouncer.total_pooled_connections | The total number of connections in the connection pool. |
db.pgbouncer.num_pools | The number of connection pools. |
Related API operations
You can call the ModifyDBInstanceConfig operation to enable or disable the PgBouncer feature for an RDS for PostgreSQL instance. The parameters that you must configure are as follows:
Parameter | Description | Example |
DBInstanceId | The ID of the RDS for PostgreSQL instance for which you want to enable or disable the PgBouncer feature. | pgm-**** |
ConfigName | The name of the configuration item. | pgbouncer |
ConfigValue | The value of the configuration item.
| true |