PolarDB for PostgreSQL clusters support the read/write splitting feature. If you use cluster endpoints whose read/write mode is Read and Write (Automatic Read-write Splitting), the cluster endpoints automatically forward read and write requests to the relevant nodes.

Background information

If a large number of read requests but a few write requests are sent to databases, a single node may not be able to handle the workloads. This may affect services. If you use cluster endpoints whose read/write mode is Read and Write (Automatic Read-write Splitting), the cluster endpoints automatically forward write requests to the primary node, and forward read requests to read-only nodes. In this way, the read capability can be elastically scaled to handle a large number of read requests that are sent to databases.

Benefits

  • Easy maintenance based on a unified endpoint

    If you do not use cluster endpoints whose read/write mode is Read and Write (Automatic Read-write Splitting), you must configure the endpoints of the primary node and each read-only node in your application. Otherwise, you cannot send write requests to the primary node and read requests to read-only nodes. If you connect your application to cluster endpoints whose read/write mode is Read and Write (Automatic Read-write Splitting), the cluster endpoints can automatically forward read and write requests to the relevant nodes. This reduces maintenance costs. You need only to add read-only nodes to improve the processing capabilities of clusters, and do not need to modify your applications.

  • Session-level read consistency

    When a client connects to the backend by using the cluster endpoint, the built-in proxy for read/write splitting automatically establishes a connection to the primary node and each read-only node. In the same session, the built-in proxy first selects an appropriate node based on the data synchronization progress of each database node. Then, the proxy forwards read and write requests to the nodes whose data is up-to-date and correct. This balances read and write requests among the nodes.

  • Even distribution of the PREPARE statements

    The PREPARE statements that contain write operations and the related EXECUTE statements are sent to only the primary node. The PREPARE statements that contain read-only operations are broadcast to all the nodes, and the related EXECUTE statements are routed based on the loads on these nodes. This achieves load balancing for query requests.

  • Support for native high security links, and improved performance

    You can build your own proxy on the cloud to achieve read/write splitting. However, an excessive latency may occur because data is parsed and forwarded by multiple components before the data arrives at a database. However, PolarDB uses a built-in proxy that works as a cluster component for read/write splitting. The built-in proxy provides a lower latency and higher data processing speed than external components.

  • Node health checks to enhance database availability

    The read/write splitting module of PolarDB performs health checks on the primary node and read-only nodes of a cluster. If a node fails or its latency exceeds a specified threshold, PolarDB stops distributing read requests to this node, and distributes write and read requests to other healthy nodes. This ensures that applications can access the cluster even if a single read-only node fails. After the node recovers, PolarDB automatically adds the node into the list of nodes that are available to receive requests.

Limits

  • PolarDB does not support the following statements or features:
    • Connect to a cluster through the replication-mode method. If you need to set up dual-node clusters based on a primary/secondary replication architecture, use the endpoint of the primary node.
    • Use the name of the temporary table to declare the %ROWTYPE attribute.
      create temp table fullname (first text, last text);
      select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;
    • Create temporary resources by using functions.
      • If you create a temporary table by using functions and execute an SQL statement to query the temporary table, an error message may be returned. The error message indicates that the table does not exist.
      • If your function contains the PREPARE statement, an error message may be returned when you execute the EXECUTE statement. The error message indicates that the PREPARE statement name does not exist.
  • Routing-related restrictions:
    • Requests in the transaction are routed to the primary node, and load balancing is resumed after the transaction terminates.
    • All statements that use functions except aggregate functions such as COUNT() and SUM() are routed to the primary node.

Create or modify a cluster endpoint

Configure transaction splitting

If the default READ COMMITTED isolation level is used, PolarDB does not start a transaction immediately after PolarDB receives a statement that is used to start the transaction, for example, the BEGIN statement or the SET autocommit=0 statement. PolarDB starts the transaction when a write operation occurs.

By default, PolarDB sends all the requests in a transaction to the primary node. This ensures the correctness of the transaction. However, some frameworks encapsulate all the requests in one transaction. This results in heavy loads on the primary node. To resolve this issue, you can enable the transaction splitting feature. After this feature is enabled, PolarDB can identify the current transaction status, and distribute read requests to read-only nodes by using the load balancing module before transactions are started.

Transaction splitting
Note The transaction splitting feature is not suitable for workloads that require global consistency. Therefore, before you enable transaction splitting, make sure that you are fully aware of the impacts of transaction splitting on your workloads.

To enable transaction splitting, perform the following steps:

  1. Log on to the PolarDB console.
  2. On the top of the page, select the region where the target cluster is located.
  3. Find the target cluster and click the cluster ID to go to the Overview page.
  4. In the Endpoints section, find the cluster endpoint that you want to modify, and on the right of the cluster endpoint, choose Modify > Modify.
    Modify a cluster endpoint
  5. In the Configure Nodes dialog box, enable Transaction Splitting.
    Transaction splitting
    Note The transaction splitting feature takes effect on only new connections. To enable the feature to take effect on existing connections, close the existing connections and create connections.
  6. Click OK.

Specify a consistency level

For more information, see Consistency levels.

Add hints to specify routing directions of SQL statements

Add the /*FORCE_MASTER*/ or /*FORCE_SLAVE*/ hint before an SQL statement to specify the direction that you want to route the SQL statement. By default, the SELECT * FROM test statement is routed to a read-only node. If you change the SQL statement to /*FORCE_MASTER*/ SELECT * FROM test, the SQL statement is routed to the primary node.

Connect to a PolarDB for PostgreSQL cluster

Connect to a PolarDB for PostgreSQL cluster

Note After the cluster endpoint is created, you need only to configure the cluster endpoint in your application to achieve automatic read/write splitting.

Related operations

API Description
CreateDBEndpointAddress Creates a public-facing endpoint for a PolarDB cluster.
DescribeDBClusterEndpoints Queries endpoint information about a PolarDB cluster.
ModifyDBClusterEndpoint Modifies the configurations of a cluster endpoint for a PolarDB cluster.
ModifyDBEndpointAddress Modifies an endpoint for a PolarDB cluster, for example, a custom cluster endpoint.
DeleteDBEndpointAddress Releases the public-facing endpoint of the primary endpoint, the default cluster endpoint, or a custom cluster endpoint for a PolarDB cluster.