PolarDB:Read/write splitting

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

• For more information about how to create a custom cluster endpoint, see Create a custom cluster endpoint.

• For more information about how to modify a cluster endpoint, see Create a custom cluster endpoint.

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

Related operations