PolarDB-X routes online transaction processing (OLTP) and online analytical processing (OLAP) traffic to different instances based on query type and workload. This document explains how routing works across the three supported instance topologies and how to configure each one.
How routing works
PolarDB-X provides three instance types, each with a defined storage access scope:
| Instance type | Workload | Default storage | Common uses |
|---|---|---|---|
| Primary instance | TP | Row store | Read/write splitting for TP; forwards AP traffic to column store and read-only instances |
| Read-only instance | TP reads | Row store | Application-level read/write splitting, ETL |
| Column store read-only instance | AP reads | Column store only | Data analytics, complex queries |
Data synchronization from the row store to the column store has a latency of several seconds. To prevent data inconsistency, primary and read-only instances access only row store data by default.
PolarDB-X offers three ways to control how queries are routed:
| Method | Scope | How it works |
|---|---|---|
| Automatic routing (cost-based) | Connection or cluster level | Connect to the cluster endpoint. The system estimates scan cost and routes queries automatically — AP traffic to the column store read-only instance, TP traffic to the primary instance. |
| Manual HINT | Statement level | Add a SQL HINT prefix to force a specific statement to the column store read-only instance, regardless of cost estimation. |
| Separated endpoints | Connection level | Connect OLTP applications to the cluster endpoint and OLAP applications directly to the column store read-only instance endpoint. |
Primary instance + column store read-only instance
Hybrid workload scenario
When OLAP and OLTP queries share the same database connection, connect to the cluster endpoint of the primary instance. The system automatically routes traffic based on the estimated scan cost of each SQL statement.
Automatic routing rules:
Read traffic with an estimated scan cost exceeding the threshold is identified as AP traffic and routed to the column store read-only instance.
Read traffic with scan cost below the threshold, read traffic inside transactions, and all write traffic are identified as TP traffic and routed to the primary instance.
If the primary instance has multiple column store read-only instances, the system automatically uses Massively Parallel Processing (MPP) for scheduling and execution.
Force a statement to the column store: Add the following HINT prefix:
/*+TDDL: workload_type=ap enable_columnar_optimizer=true*/
SELECT ...To bind this HINT to a specific SQL statement without changing application code, use the execution plan management feature. The database then routes that statement type to the column store read-only instance automatically.
Enable automatic routing:
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.
Click the ID of the target primary instance to open the Basic Information page.
In the Connection Information section, click Configuration Management.
In the Configuration Management dialog box:
In the Resource Configuration section, select the target column store read-only instance from the Available Instances list and move it to the Selected Instances list.
In the Read-only settings for column-oriented data section, click Enable, and then click OK.
Data synchronization between the column store read-only instance and the primary instance has a latency of several seconds. If your application requires strong data consistency between the two, do not use automatic routing.
Separated workload scenario
When OLAP and OLTP services use separate applications, connect each application to a dedicated endpoint: the OLTP application to the cluster endpoint, and the OLAP application directly to the column store read-only instance endpoint. This separates traffic between the primary instance and the column store read-only instance at the connection level.
Primary instance + read-only instance
Transparent read/write splitting
Connect to the cluster endpoint of the primary instance. No application code changes are needed. The system routes a configurable percentage of read traffic to the read-only instance automatically.
Configure transparent read/write splitting:
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.
Click the ID of the target primary instance to open the Basic Information page.
In the Connection Information section, click Configuration Management.
In the Configuration Management dialog box:
In the Resource Configuration section, select the target row store read-only instance from the Available Instances list and move it to the Selected Instances list.
In the Row Store Read-only Settings section, configure the items as needed.
Click OK.
Configuration items:
| Configuration item | Options | Description |
|---|---|---|
| Strongly consistent read | Strong consistency | Requests routed to a read-only data node (DN) can read the latest data. If the latency of a read-only DN exceeds the threshold, the system routes requests to another DN with normal latency (if available) or to the primary instance. Routing to the primary instance requires Fail back read-only traffic to primary instance to be enabled. |
| Weak consistency | Requests routed to a read-only DN can only read the latest data available on that DN. | |
| Enable MPP query acceleration | Enable / Disable | Estimates cost and routes complex AP queries to the read-only instance (compute node (CN) + DN) for MPP acceleration. |
| Read/Write splitting | Percentage of traffic routed to read-only instances | Routes a percentage of pushdown queries — originally sent to the primary instance DNs — to the read-only DNs. |
| Read-only instance latency threshold (s) | If the latency of a read-only DN exceeds this threshold, the system routes requests to another DN with normal latency (if available) or to the primary instance. | |
| Fail back read-only traffic to primary instance | Enable / Disable | When enabled, read traffic falls back to the primary instance when a read-only DN's latency exceeds the threshold. |
Manual read/write splitting
Use different endpoints for write and read operations: point write operations to the primary instance endpoint and read operations to the read-only instance endpoint. Read and write traffic are isolated at the application level.
Single primary instance
With only a primary instance, the application accesses row store data only.
FAQ
How do I use a column store read-only instance to accelerate queries?
Two approaches are available:
If you know which queries should run on the column store, connect directly to the column store read-only instance endpoint. See Separated workload scenario.
If you want the database to decide automatically, connect to the cluster endpoint of the primary instance. Complex queries are routed to the column store based on query cost. See Hybrid workload scenario.
Statistics-based cost estimates can sometimes be inaccurate. What happens then?
Statistics are collected periodically and can become outdated, leading to inaccurate scan cost calculations. For critical complex queries, bind a column store HINT to the SQL statement. This ensures that these SQL statements are always routed to the column store read-only instance for execution. To bind a HINT without modifying application code, use the execution plan management feature.
How do I bind a column store HINT to a SQL statement without changing application code?
Use the execution plan management feature to bind a routing rule to a specific SQL statement. The database then routes that statement type to the column store read-only instance automatically.
Query performance on the column store read-only instance is not significantly better. How do I improve it?
Column store instances are optimized for complex queries, which are CPU-intensive. For better performance, use a column store read-only instance with at least 32 CPU cores. These instances are highly cost-effective.