Read/write splitting routes read queries to read-only instances, offloading read traffic from the primary instance and scaling query throughput for hybrid transactional and analytical processing (HTAP) workloads.
Read/write splitting is available only for PolarDB-X instances of Enterprise Edition running version 5.4.13-16415631 or later.
Endpoint types
PolarDB-X provides two endpoint types for read/write splitting:
| Endpoint | Description | Use when |
|---|---|---|
| Cluster endpoint | The default endpoint for a PolarDB-X instance. Routes write traffic to the primary instance and, when configured, distributes read traffic across read-only instances. | Standard online business and HTAP workloads with both TP and AP traffic |
| Read-only routing endpoint | A dedicated endpoint for a specific read-only instance. All connections through this endpoint go to that instance only. | Resource-isolated workloads: batch processing, offline data export, or scenarios requiring exclusive access to a single read-only instance |
How traffic is routed
When you add a read-only instance to the resource pool of the primary instance, the cluster endpoint can distribute read traffic across both instances. Two traffic types are routed independently:
| Traffic type | Routing behavior | Configuration required |
|---|---|---|
| AP traffic (analytical processing) | Automatically routed to read-only instances via Intelligent Read/write Splitting, which uses massively parallel processing (MPP) to accelerate queries. Enabled by default after a read-only instance is purchased. | None |
| TP traffic (transaction processing) | Routed to the primary instance by default. Routed to read-only instances only after you set Read-Only Traffic (%) to a value greater than 0. | Set Read-Only Traffic (%) in the cluster endpoint configuration |
This means AP queries are offloaded automatically after you purchase a read-only instance, but TP read queries require explicit configuration.
Configure a cluster endpoint for read/write splitting
Prerequisites
Before you begin, make sure you have:
-
A PolarDB-X Enterprise Edition instance running version 5.4.13-16415631 or later
-
At least one read-only instance purchased (two or more recommended for high availability)
Configure the cluster endpoint
-
Log on to the PolarDB-X console.
-
In the top navigation bar, select the region where the instance is deployed.
-
On the Instances page, click the PolarDB-X 2.0 tab.
-
Find the instance and click the instance ID.
-
In the Connection Information section, click Configuration Management.
-
Configure the parameters in the following table, then save your changes.
| Parameter | Description |
|---|---|
| Resource Configuration | Add read-only instances to the resource pool of the primary instance. Read-only instances not in the resource pool cannot receive offloaded read traffic. |
| Read-only instances are automatically added | When enabled (default), newly purchased read-only instances are automatically added to the resource pool. |
| Intelligent Read/write Splitting | When enabled (default), AP traffic is automatically routed to read-only instances for MPP-accelerated query processing. To stop routing AP traffic, disable this setting. For details, see Read/write splitting. |
| Read-Only Traffic (%) | The percentage of TP read queries routed to read-only instances in the resource pool. Set this to a value greater than 0 to offload TP read traffic. |
| Data read consistency |
Controls how read-only instances handle data not yet replicated from the primary instance:
|
| Latency Threshold for Read-Only Instances (s) | If the read latency on a read-only instance exceeds this threshold, requests are redirected to other read-only instances within the threshold. If all instances exceed the threshold, the system evaluates whether to route requests to the primary instance based on availability. |
| Read-only Traffic Switched Back to the Primary Instance | When all read-only instances are unavailable or exceed the latency threshold, determines whether TP read traffic falls back to the primary instance. Use two or more read-only instances to reduce the likelihood of this fallback. |
Strong consistency adds up to 3 seconds of latency per read and may cause query timeouts when DDL statements are executing on the primary instance or when replication latency is high. If your workload does not require strong consistency, select Weak consistency for better query performance.
Configure a read-only routing endpoint
The read-only routing endpoint provides an exclusive connection to a single read-only instance, with no traffic sharing or load balancing across other instances. Use it for workloads that require resource isolation, such as batch jobs or offline analytics.
-
Log on to the PolarDB-X console.
-
In the top navigation bar, select the region where the instance is deployed.
-
On the Instances page, click the PolarDB-X 2.0 tab.
-
Find the read-only instance and click the instance ID.
-
In the Connection Information section, click Configuration Management to configure the read-only endpoint parameters.
The Data read consistency options for the read-only routing endpoint work as follows:
-
Strong consistency: Queries return the most recent data committed on the primary instance. If there is a significant replication delay, queries wait for synchronization before returning results.
-
Weak consistency: Queries return the most recent data available on the read-only instance immediately, without waiting for synchronization.
FAQ
I purchased a read-only instance, but no traffic is being routed to it. What do I do?
Check which traffic type you expect to be routed. AP traffic (analytical queries) is automatically routed after purchase if Intelligent Read/write Splitting is enabled. TP traffic (transactional reads) is not routed until you set Read-Only Traffic (%) to a value greater than 0 in the cluster endpoint configuration. Also verify that the read-only instance has been added to the resource pool of the primary instance.
I set Read-Only Traffic (%) to 0, but traffic is still going to the read-only instance. Why?
The traffic is AP traffic routed by Intelligent Read/write Splitting, which is enabled by default and operates independently of the Read-Only Traffic (%) setting. To stop all routing to the read-only instance, disable Intelligent Read/write Splitting.
I configured Read-Only Traffic (%), but TP traffic is still not reaching the read-only instance. Why?
Read-Only Traffic (%) routes traffic only to read-only instances in the resource pool. Your read-only instance may have been removed from the resource pool. In the cluster endpoint configuration, check the Resource Configuration setting and add the instance back to the pool.
Can I implement read/write splitting using separate connection strings instead of the PolarDB-X built-in feature?
Yes. Remove the read-only instance from the resource pool of the primary instance, then connect your application directly to the read-only routing endpoint for read queries and the cluster endpoint for write queries.
Do I have to enable strong consistency?
No. If your workload tolerates reading slightly stale data, select Weak consistency for better query performance. Enable strong consistency only when your application requires that read results always reflect the latest writes on the primary instance. Note that strong consistency may cause query timeouts when DDL statements are executing or when read-only instance specifications are significantly smaller than those of the primary instance.
How do I disable strong consistency?
In the cluster endpoint configuration, set Data read consistency to Weak consistency. If your application connects through the read-only routing endpoint, set Data read consistency to Weak consistency in the read-only endpoint configuration as well.
Queries are timing out after I enabled read/write splitting. What do I do?
This is typically caused by large replication latency between the primary and read-only instances when strong consistency is enabled. Common causes include:
-
A DDL statement is executing on the primary instance, or a large volume of data is being imported.
-
The read-only instance specifications are significantly smaller than those of the primary instance.
Switch to Weak consistency to eliminate the replication wait, or increase the read-only instance specifications to reduce replication latency.