This topic describes the transaction splitting feature of PolarDB for PostgreSQL clusters, including how it works and how to enable it.
Background information
When you use the cluster endpoint of a PolarDB for PostgreSQL cluster in read/write mode, PolarProxy routes read and write requests to the primary node and read-only nodes. To ensure read/write consistency within a session, PolarProxy sends all requests that belong to a transaction in the session to the primary node.
For example, some database client drivers, such as Java Database Connectivity (JDBC), wrap requests in transactions by default. As a result, all requests from the application are sent to the primary node, which causes heavy load on the primary node while read-only nodes remain almost idle, as shown in the following figure.
To resolve this issue, PolarDB provides the transaction splitting feature. This feature routes read requests in a transaction to read-only nodes while ensuring read/write consistency, which reduces the load on the primary node.
Features
Basic transaction splitting service
PolarProxy routes the read requests before the first write request in a transaction to read-only nodes, which reduces the load on the primary node. Because uncommitted data in a transaction is invisible on read-only nodes, all read and write requests after the first write request are still routed to the primary node to ensure read/write consistency. For more information about how to enable the basic transaction splitting service, see Enable transaction splitting.
Benefits
You do not need to modify the application code or configurations to offload the read load within transactions from the primary node to read-only nodes, which improves the stability of the primary node.
Precautions
-
Transaction splitting is supported only for the Read Committed transaction isolation level.
-
For the basic transaction splitting service, if the consistency level is not set to Eventual Consistency (Weak), the read requests before the first write request in a transaction are routed to read-only nodes only after the data on read-only nodes has been synchronized with the primary node. Otherwise, these requests are still sent to the primary node. For more information about the consistency levels, see Consistency levels.
Enable transaction splitting
-
Log in to the PolarDB console，In the navigation pane on the left, click Clusters. Select the Region where the cluster is deployed, and then click the cluster ID to go to the cluster details page。
-
In the Database Connections section, click Configure next to the target connection endpoint.
-
Click Transaction Splitting and then click On on the right side.
NoteAfter transaction splitting is enabled, it takes effect only on new connections. Existing connections must be re-established for the change to take effect.
-
Click OK.
Related operations
|
API operation |
Description |
|
Modifies the attributes of a PolarDB cluster endpoint, including the read/write mode, whether new nodes are automatically added to the endpoint, the consistency level, transaction splitting, and whether the primary node accepts read requests. |