This topic describes the transaction splitting feature of PolarDB for PostgreSQL clusters and shows you how to enable transaction splitting.
If you use a PolarDB for PostgreSQL cluster endpoint in read/write mode, the proxy forwards read and write requests to the primary node and read-only nodes. To ensure read/write consistency of transactions in a session, the proxy sends all the transaction requests in the session to the primary node.
For example, some database client drivers, such as the Java Database Connectivity (JDBC) driver, encapsulate all requests in transactions by default. In this case, all the requests from applications are sent to the primary node. This results in heavy loads on the primary node and low loads on read-only nodes, as shown in the following figure.
To fix the preceding issue, PolarDB enables the transaction splitting feature that aims to ensure read/write consistency. This feature reduces the loads on the primary node by sending read requests in transactions to read-only nodes.
Basic transaction splitting
The proxy sends read requests that are received before the first write request in a transaction to read-only nodes. This reduces the loads on the primary node. The uncommitted data in transactions is invisible to read-only nodes. Therefore, to ensure read/write consistency in transactions, all the read and write requests that are received after the first write request are still routed to the primary node. To enable basic transaction splitting, see Enable transaction splitting.
This feature allows you to shift the read loads from the primary node to read-only nodes without modifying application code or configurations. This improves the stability of the primary node.
- Only transactions that use the Read Committed isolation level can be split.
- Assume that basic transaction splitting is enabled, and the consistency level is not eventual consistency. The proxy sends the read requests that are received before the first write request in a transaction to the read-only nodes only after data synchronization between the primary node and read-only nodes is complete. If data synchronization is not complete, the proxy still sends the read requests to the primary node. For more information about consistency levels, see Consistency levels.
Enable transaction splitting
- Log on to the PolarDB console.
- In the upper-left corner of the console, select the region where the cluster resides.
- Find the cluster, and then click the cluster ID.
- In the Endpoints section, find the cluster endpoint and click Modify on the right side of the cluster endpoint.
- Click On next to 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 establish the connections again.
- Click OK.
Related API operations
|ModifyDBClusterEndpoint||Modifies the attributes of a PolarDB cluster endpoint. For example, you can modify the following attributes: read/write mode, consistency level, transaction splitting, and offload read requests from the primary node. You can also specify whether to associate the specified cluster endpoint with newly added nodes.|