If a large number of short-lived connections are established or connections need to be frequently established or closed for your ApsaraDB RDS for PostgreSQL instance, we recommend that you enable the connection pooling feature for the instance. This feature reduces the impacts caused by frequent connection establishment and closing on instance performance. This feature also helps you manage the load and resource usage of the database server.
Background information
PgBouncer is an open source database connection pooler for PostgreSQL databases. PgBouncer sits between the application and the database to manage database connections and provide connection pooling-related features.
PgBouncer is used to optimize the performance and resource usage of database connections. When an application needs to connect to a PostgreSQL database, PgBouncer can reuse an existing database connection in a connection pool. This prevents performance overhead caused by frequent connection establishment and closing. The connection reuse mechanism significantly reduces the load on the database server and improves the response speed and concurrency performance of the application.
Scenarios
This feature is suitable for scenarios in which a large number of short-lived connections exist and connections are frequently established and closed.
PgBouncer can limit the maximum number of client connections and the maximum idle time for connections. This prevents malicious or unreasonable connections from consuming an excessively large number of system resources and helps manage the load and resource usage of the database server.
Prerequisites
The RDS instance meets the following requirements:
The RDS instance runs PostgreSQL 11 or later.
The RDS instance runs a minor engine version of 20230530 or later.
NoteFor more information about how to view or update the minor engine version, see Upgrade the minor engine version.
You cannot enable PgBouncer for RDS instances for which Babelfish is enabled.
You cannot enable PgBouncer for serverless RDS instances.
Impacts
If PgBouncer is enabled for your RDS instance, the system automatically assigns port 6432 to the RDS instance. Port 6432 does not conflict with port 5432 and does not affect the connections to the RDS instance that are established over port 5432.
If you enable server-side or client-side SSL encryption before or after you enable PgBouncer, server-side or client-side SSL encryption is also enabled for PgBouncer. PgBouncer does not verify connections for which the access control list (ACL) is set to verify-ca or verify-full. It also does not support a client certificate revocation list (CRL) file before the client certificate expires. For more information, see Configure a client CA certificate on an ApsaraDB RDS for PostgreSQL instance.
Usage notes
After PgBouncer is disabled, you can no longer use the PgBouncer port to connect to the RDS instance. In this case, you need to change the connection port to the original connection port of the RDS instance in your application configuration. The default connection port of the RDS instance is 5432.
PgBouncer is not suitable for specific scenarios. For example, if complex transactions exist, the database connection status needs to be traced, or specific PostgreSQL features are required, you can directly connect to the database.
Billing rules
The connection pooling feature is provided free of charge.
Procedure
Enable or disable the connection pooling feature for an RDS instance
- 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 left-side navigation pane, click Database Connection.
Click Enable PgBouncer or Disable PgBouncer.
In the dialog box that appears, click OK.
Optional. View the PgBouncer port on the Database Connection page. The default port is 6432. You can click Modify Endpoint. In the Modify Endpoint dialog box, select an endpoint type and change the PgBouncer port.
Use a connection pool to connect to an RDS instance
If you want to use a connection pool to connect to your RDS instance after you enable PgBouncer, change the connection port to the PgBouncer port. The default PgBouncer port is 6432. For more information about how to connect to an RDS instance, see Connect to an ApsaraDB RDS for PostgreSQL instance.
Modify connection pool-related parameters
After you enable PgBouncer, you can go to the Parameters page to modify PgBouncer-related parameters. For example, you can adjust the maximum number of client connections. For more information about how to modify the parameters of an RDS instance, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.
You can also view the PostgreSQL_PgBouncer_Default Parameter Template (rpg-sys-pgsql-pgbouncer) template on the System Parameter Templates tab to learn more about PgBouncer-related parameters. For more information, see Use a parameter template to configure the parameters of ApsaraDB RDS for PostgreSQL instances.
After you enable PgBouncer, you can go to the Parameters page to view and modify PgBouncer-related parameters. After you disable PgBouncer, the PgBouncer-related parameters are no longer displayed on the Parameters page.
If you want to use a parameter template to modify multiple parameters at a time, you must enable PgBouncer and then apply the parameter template to the RDS instance.
By default, the maximum idle time for connections is 10 minutes. You cannot change the value.
The following table describes the parameters that can be modified.
Parameter | Type | Default | Description |
pgbouncer.pool_mode | string | transaction | The connection pool mode that specifies when to reuse connections.
|
pgbouncer.default_pool_size | int | 20 | The default number of connections that are allowed in the connection pool. |
pgbouncer.max_client_conn | int | 100 | The maximum number of client connections that are allowed in the connection pool. |
pgbouncer.min_pool_size | int | 0 | The minimum number of client connections that are allowed in the connection pool. |
pgbouncer.query_wait_timeout | int | 120 | The maximum period of time that a client is allowed to wait before a query is assigned to the server. Unit: seconds. If the query is not assigned to the server during this period of time, the client closes the connection. If you set the value to 0, this parameter is disabled, and the client waits indefinitely. |
pgbouncer.ignore_startup_parameters | string | "extra_float_digits" | The PgBouncer startup parameters that you want to trace. Separate multiple parameters with commas (,). Important The default value of this parameter is "extra_float_digits". When you modify the parameter, we recommend that you do not delete this default value. The "extra_float_digits" value is required for this parameter when you use the PostgreSQL JDBC driver to connect to the RDS instance. |
pgbouncer.stats_users | string | "" | The database users that are allowed to connect to the PgBouncer virtual database to run read-only queries. Separate multiple database users with commas (,). |
For more information about the preceding parameters, see PgBouncer documentation.
View connection pool-related metrics
ApsaraDB RDS for PostgreSQL allows you to use the enhanced monitoring feature to view the PgBouncer-related metrics. For more information about enhanced monitoring, see View the Enhanced Monitoring metrics of an ApsaraDB RDS for PostgreSQL instance.
To obtain PgBouncer-related metrics with ease, ApsaraDB RDS for PostgreSQL automatically installs the pgbouncer_fdw and dblink extensions on the postgres database after PgBouncer is enabled. You cannot uninstall the extensions.
You can view the connection pool-related metrics on the Enhanced Monitoring tab only after you enable the connection pool feature.
The following table describes PgBouncer-related metrics.
Item | Definition |
db.pgbouncer.client_connections.active | The number of active connections on the client. |
db.pgbouncer.client_connections.waiting | The number of waiting connections on the client. |
db.pgbouncer.server_connections.active | The number of active connections on the server. |
db.pgbouncer.server_connections.idle | The number of idle connections on the server. |
db.pgbouncer.total_pooled_connections | The total number of connections in a connection pool. |
db.pgbouncer.num_pools | The number of connection pools. |
Related operations
You can call the ModifyDBInstanceConfig operation to enable or disable PgBouncer for an RDS instance. The following table describes the required parameters.
Parameter | Description | Example |
DBInstanceId | The ID of the RDS instance for which you want to enable or disable PgBouncer. | pgm-**** |
ConfigName | The name of the configuration item. | pgbouncer |
ConfigValue | Specifies whether the configuration item is enabled.
| true |