The database proxy feature of ApsaraDB RDS for PostgreSQL provides advanced capabilities such as automatic read/write splitting. A database proxy resides between your database system and your application and receives all requests from your application. A database proxy is easy to use and maintain, and provides high availability and high performance. This topic describes the database proxy feature of ApsaraDB RDS for PostgreSQL.

Scenarios

  • The primary RDS instance is heavily loaded due to a large number of requests that are encapsulated in transactions.
  • The primary RDS instance is heavily loaded due to an excessively large number of connections.
  • You need to process read-only workloads and workloads that need to be isolated from multiple applications.
    Note For example, your database system consists of one primary RDS instance and four read-only RDS instances, and you have two applications, Application A and Application B. Application A initiates only read requests, and Application B initiates both read and write requests. In this case, you can bind two read-only RDS instances to Proxy Terminal A that has the Read-only attribute and bind the other two read-only RDS instances to Proxy Terminal B that has the Read/Write attribute. This way, Application A and Application B are physically isolated from each other in your database system.

Terms

  • proxy terminal

    Proxy terminals are the core of database proxies. You can create custom endpoints for proxy terminals. Each ApsaraDB RDS for PostgreSQL instance supports up to seven proxy terminals. You can modify the read and write attributes of each proxy terminal based on your business requirements.

  • read/write splitting

    Read/write splitting indicates that proxy terminals are used to automatically forward read and write requests.

    If your database system receives a large number of read requests and a small number of write requests, the primary RDS instance may fail to efficiently process read requests and your workloads may be interrupted. The read/write splitting feature allows ApsaraDB RDS for PostgreSQL to forward write requests to the primary RDS instance and read requests to the read-only RDS instances. This reduces the loads on the primary RDS instance.

  • transaction splitting
    The transaction splitting feature is automatically enabled for a database proxy. This feature allows ApsaraDB RDS for PostgreSQL to forward the read requests prior to write operations in a transaction to the read-only RDS instances of your database system. This reduces the loads on the primary RDS instance.
    Note
    • Explicit transactions cannot be split. These explicit transactions include the transactions that are started by executing the BEGIN or START statement.
    • If the transaction splitting feature is enabled, global consistency cannot be ensured. Before you use this feature, we recommend that you evaluate whether this feature is suitable for your workloads.
    • The transaction splitting feature cannot be disabled. If you want to disable this feature, submit a ticket. If the transaction splitting feature is disabled, transaction requests are forwarded to the primary RDS instance.

Benefits of read/write splitting

  • Unified endpoint to facilitate maintenance

    If you do not enable the read/write splitting feature, you can perform read/write splitting only after you add the endpoints of the primary RDS instance and read-only RDS instances to your application.

    If you enable the read/write splitting feature, you can use a database proxy endpoint to implement read/write splitting. You need to only add the database proxy endpoint to your application. After your application is connected to the database proxy endpoint, your database system can forward read and write requests to the primary RDS instance and read-only RDS instances based on the read weights of the instances. This reduces maintenance costs.

    You can also create read-only RDS instances to increase the read capability of your database system without the need to modify the configuration data on your application.

  • Native links to improve performance and reduce maintenance costs

    If you build your own proxy layer in the cloud to implement read/write splitting, data must be parsed and forwarded by multiple components before the data reaches your database system. As a result, response latencies increase. The read/write splitting feature is embedded in the ApsaraDB RDS ecosystem to reduce response latencies, increase processing speeds, and reduce maintenance costs.

  • Instance-level health checks to ensure high availability

    If the read/write splitting feature is enabled, ApsaraDB RDS for PostgreSQL automatically checks the health status of the primary RDS instance and read-only RDS instances. If a read-only RDS instance unexpectedly exits or the data replication latency of the read-only RDS instance exceeds the specified threshold, ApsaraDB RDS for PostgreSQL stops forwarding read requests to the instance. ApsaraDB RDS for PostgreSQL forwards the read requests that are destined for the faulty RDS instance to other healthy RDS instances in your database system. This ensures service availability even if a read-only RDS instance fails. After the faulty read-only RDS instance recovers, ApsaraDB RDS for PostgreSQL resumes forwarding read requests to the instance.

    Note To mitigate the impacts of single points of failure (SPOFs), we recommend that you create at least two read-only RDS instances.
  • Configurable read weights and thresholds to ensure suitability in various scenarios

    You can specify the read weights of the primary RDS instance and read-only RDS instances. You can also specify the latency threshold for data replication to the read-only RDS instances.

Request forwarding types

Forwarding destination Request type
Primary RDS instance
  • Requests that are used to execute INSERT, UPDATE, DELETE, and SELECT FOR UPDATE statements
  • All requests that are used to execute DDL statements, such as the DDL statements that are used to create databases or tables, delete databases or tables, and change schemas or permissions.
  • All requests that are encapsulated in transactions
  • Requests that are used to call user-defined functions
  • Requests that are used to run stored procedures.
  • Requests for multi-statements
    Note If you execute multi-statements or call stored procedures, all subsequent requests over the current connection are forwarded to the primary RDS instance. To perform read/write splitting again, you must close the current connection and establish a new connection.
  • Requests that involve temporary tables
  • Read and write requests for system tables
  • Write operations that are stored in PreparedStatement objects
Primary RDS instance or read-only RDS instances
  • Requests that are used to execute SELECT statements that are not encapsulated in transactions
  • Requests prior to the first write operation in the transaction after the transaction splitting feature is enabled
  • Read operations that are stored in PreparedStatement objects
  • System functions, such as pg_sleep, that can be safely called on read-only RDS instances
Primary RDS instance and read-only RDS instances
  • All requests that are used to reconfigure system variables
  • Parsing operations that are stored in PreparedStatement objects
  • BEGIN, START, END, ROLLBACK, and COMMIT
  • CANCEL

Usage notes

  • Database proxy instances, read-only RDS instances, and primary RDS instances are separately billed. For more information about the billing rules, see Billing rules for the database proxy of an ApsaraDB RDS for PostgreSQL instance, Pricing, and Billable items, billing methods, and pricing.
  • When you change the specifications of the primary RDS instance or a read-only RDS instance, a transient connection may occur.
  • If you create or restart a read-only RDS instance after you enable the database proxy feature, only the requests that are sent over new connections are forwarded to the new or restarted read-only RDS instance.
  • If a database proxy endpoint is used to perform read/write splitting, the read consistency of the requests in a session cannot be ensured. If you want to ensure the read consistency of these requests, you must update your AliPG version to 20220228 or later and then submit a ticket. For more information about how to update your AliPG version, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
  • If you use a database proxy endpoint, you must add /*force_master*/ /*force_slave*/ when you view session variables to check the configurations on the primary RDS instance and read-only RDS instances.
  • The database proxy feature uses the 1:N connection model. After your application initiates a connection request, the database proxy replicates the established connection to the primary RDS instance and all the read-only RDS instances. The maximum number of connections that are allowed for the database proxy is not limited. The maximum number of connections varies based on the specifications of the primary RDS instance and read-only RDS instances. After the database proxy receives a request from your application, the database proxy establishes a connection to each of the primary RDS instance and read-only RDS instances. After you enable the database proxy feature, we recommend that you specify the same maximum number of connections for the primary RDS instance and read-only RDS instances. If the maximum numbers for the primary RDS instance and read-only RDS instances are different, the maximum number of connections that are allowed for the database proxy is subject to the minimum number of connections among these instances.
  • If the primary RDS instance is locked, the enabled proxy instances are not released but can process only read requests.
  • If the primary RDS instance is released, the enabled proxy instances are automatically released. You are no longer charged for the database proxy feature.

Enable the database proxy feature

For more information, see Enable and configure the database proxy feature for an ApsaraDB RDS for PostgreSQL instance