Rule-based row and column routing - PolarDB - Alibaba Cloud Documentation Center

In mixed workload scenarios that handle both high-concurrency online transactional processing (OLTP) and complex online analytical processing (OLAP), distributing different types of queries to the appropriate storage engine is key to optimizing performance. PolarDB for X provides a rule-based row and column routing feature. This feature lets you precisely route queries to row store nodes or column store read-only instances by configuring rules based on accounts, SQL templates, or keywords. This helps divert traffic and improve overall cluster performance without changing your application code.

Feature overview

PolarDB for X uses column store read-only instances to divert traffic in the following two ways:

Direct connection to a column store read-only instance: Your application connects directly to the endpoint of a column store read-only instance and explicitly sends analytical queries to that instance. This method is direct, but it requires you to manually classify OLTP and OLAP workloads and modify your application code accordingly.
Connection to the primary instance: Your application connects to the primary instance endpoint. The database optimizer then automatically routes complex queries to a column store read-only instance based on cost estimation. This method is transparent to your application but may result in incorrect routing due to inaccurate cost estimation.

The rule-based row and column routing feature combines the advantages of both methods. It lets you define precise routing policies on the primary instance's endpoint based on the source account, a specific template, or keywords in an SQL statement. This approach avoids application code modifications while ensuring routing accuracy, making it an ideal choice for diverting traffic in mixed workload scenarios.

Scope

The compute node (CN) version of your instance must be 5.4.20 or later.

Note

For information about the instance version naming rules, see Release notes.
For information about how to view the version of an instance, see View and update the version of an instance.

Notes

Performance and resources: Accessing columnstore indexes is resource-intensive and has limited concurrency. You can add a dedicated column store read-only instance for column store queries to avoid affecting the OLTP workloads on the primary instance. This feature is suitable only for low-concurrency, heavy-scan, or heavy-aggregation OLAP queries. Do not route queries with high queries per second (QPS) to column store read-only instances.
Data synchronization latency: The latency between column store data and row store data is in seconds. Do not route queries to column store read-only instances for applications that require real-time data.
Feature limitations: Rule-based routing works only for SELECT queries. Queries within a transaction cannot be routed to a column store read-only instance using rules.
Syntax: All fields defined in a routing rule, such as usernames and keywords, are case-insensitive.

Procedure

Step 1: Prepare the environment

Add a column store read-only instance and create columnstore indexes for the relevant tables.
Configure the endpoint of the primary instance.
Note
This operation associates the column store read-only instance with the primary instance's endpoint. This allows queries sent to the primary instance endpoint to be routed to the column store read-only instance.
1. Go to the PolarDB for X console. On the Instance List page, click the PolarDB-X 2.0 tab. Click the ID of the target primary instance to open the Basic Information page.
2. In the Connection Information section, click Configuration Management.
3. In the Configuration Management dialog box, in the Resource Configuration section, move the target column store read-only instance from the Available Instances list to the Selected Instances list.

Step 2: Create a routing rule

Connect to the primary instance using a privileged account and run the CREATE ROUTING_RULE command to create a routing rule.

CREATE ROUTING_RULE {IF NOT EXISTS} 'rule_name' TO 'user_name'
{FILTER BY TEMPLATE('template_id') | FILTER BY KEYWORD('kw1','kw2')}
WITH TYPE=[ROW | COLUMNAR];

Parameters

Parameter	Description
`rule_name`	The name of the rule. The name must be globally unique.
`user_name`	The database account name. Use `%` to represent all users.
`FILTER BY`	Optional. The filter condition. Use this to specify template-based or keyword-based routing. If you omit this parameter, the rule applies account-based routing. `TEMPLATE('template_id')`: Matches queries based on the SQL template ID. This is suitable for scenarios where the query structure is fixed and only the parameters change. `KEYWORD('kw1','kw2')`: Matches queries based on a sequence of keywords. The rule is hit if a query contains all specified keywords in order. This is suitable for scenarios where query conditions are not fixed. Note You cannot configure both template and keyword filters in the same routing rule. Keyword filtering: Keywords do not support regular expressions. You can configure multiple keywords, but the total length cannot exceed 500 characters.
`TYPE`	The routing destination. `ROW`: Routes queries to a row store node. `COLUMNAR`: Routes queries to a column store read-only instance.

Route priority

If an SQL statement matches multiple rules, the system follows this priority order:

For different rule types, the priority is determined by the rule type: Template routing > Keyword routing > Account routing.
For the same rule type, the priority is determined by the account scope: Rules for a specific account > Rules for all accounts (%).
For the same rule type and account scope, the priority is determined by the routing type: Row store (ROW) rules > Column store (COLUMNAR) rules.

Examples

Create a sample orders table that includes the columnstore index orders_col_index.

CREATE TABLE `orders` (
    `o_orderkey` int(11) NOT NULL,
    `o_custkey` int(11) NOT NULL,
    `o_orderstatus` varchar(1) NOT NULL,
    `o_totalprice` decimal(15, 2) NOT NULL,
    `o_orderdate` date NOT NULL,
    `o_orderpriority` varchar(15) NOT NULL,
    `o_clerk` varchar(15) NOT NULL,
    `o_shippriority` int(11) NOT NULL,
    `o_comment` varchar(79) NOT NULL,
    PRIMARY KEY (`o_orderkey`),
    CLUSTERED COLUMNAR INDEX `orders_col_index` (`o_orderdate`, `o_orderkey`) PARTITION BY HASH(`o_orderkey`) PARTITIONS 64
) ENGINE = InnoDB DEFAULT CHARSET = latin1 PARTITION BY KEY(`o_orderkey`) PARTITIONS 16

Configure routing rules.

Account routing

Route all queries from the test0 account to the column store by default.

CREATE ROUTING_RULE 'test0_col_rule' TO 'test0' WITH TYPE=COLUMNAR;

Test the result

When the test0 account runs any SELECT query, you can run EXPLAIN to view the query plan. The plan shows that the columnstore index, such as orders_col_index, is accessed.

EXPLAIN SELECT COUNT(*) FROM orders;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg(COUNT(*)="SUM(COUNT(*))")                                                                                                                                                            |
|   Exchange(distribution=single, collation=[])                                                                                                                                                |
|     PartialHashAgg(COUNT(*)="COUNT()")                                                                                                                                                       |
|       OSSTableScan(tables="orders_col_index_$9554[p1,p2,p3,...p64]", shardCount=64, partition=[remote], sql="SELECT `o_orderkey` FROM `orders_col_index_$9554` AS `orders_col_index_$9554`") |
| HitCache:false                                                                                                                                                                               |
| Source:PLAN_CACHE                                                                                                                                                                            |
| TemplateId: bc92bbe3                                                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.04 sec)

If another account runs a SELECT query, the columnstore index is not accessed.

EXPLAIN SELECT COUNT(*) FROM orders;

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg(count(*)="SUM(count(*))")                                                                                                                                                                                                                                  |
|   Gather(concurrent=true)                                                                                                                                                                                                                                          |
|     LogicalView(tables="orders[p1,p2,p3,...p16]", shardCount=16, sql="SELECT COUNT(*) AS `count(*)` FROM `orders` AS `orders`", physicalPlan="[{table:orders,selectType:SIMPLE,type:index,key:PRIMARY,rows:1,filtered:100.0,extra:Scan rows(1, 1); Using index}]") |
| HitCache:false                                                                                                                                                                                                                                                     |
| Source:PLAN_CACHE                                                                                                                                                                                                                                                  |
| TemplateId: b856efe3                                                                                                                                                                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.09 sec)

Template routing

Force a specific type of query from the test0 account to be routed to the row store.

Retrieve the SQL template ID.

Assume that the target SQL statement is SELECT COUNT(*) FROM orders WHERE o_orderkey > ?;. You can run the EXPLAIN command to view the TemplateId in the query plan.

EXPLAIN SELECT COUNT(*) FROM orders WHERE o_orderkey > 10;

The output is as follows. The TemplateId is 24dd9dde.

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg(COUNT(*)="SUM(COUNT(*))")                                                                                                                                                                                     |
|   Exchange(distribution=single, collation=[])                                                                                                                                                                         |
|     PartialHashAgg(COUNT(*)="COUNT()")                                                                                                                                                                                |
|       OSSTableScan(tables="orders_col_index_$9554[p1,p2,p3,...p64]", shardCount=64, partition=[remote], sql="SELECT `o_orderkey` FROM `orders_col_index_$9554` AS `orders_col_index_$9554` WHERE (`o_orderkey` > ?)") |
| HitCache:true                                                                                                                                                                                                         |
| Source:PLAN_CACHE                                                                                                                                                                                                     |
| TemplateId: 24dd9dde                                                                                                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.01 sec)

Create a template-based routing rule.

CREATE ROUTING_RULE 'test0_row_rule' TO 'test0' FILTER BY TEMPLATE('24dd9dde') WITH TYPE=ROW;

Test the result

When the test0 account runs a query that matches this template, such as ... WHERE o_orderkey > 1000, the query is routed to the row store (orders table).

EXPLAIN SELECT COUNT(*) FROM orders WHERE o_orderkey > 1000;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg(COUNT(*)="SUM(COUNT(*))")                                                                                                                                                                                                                                                                        |
|   Gather(concurrent=true)                                                                                                                                                                                                                                                                                |
|     LogicalView(tables="orders[p1,p2,p3,...p16]", shardCount=16, sql="SELECT COUNT(*) AS `COUNT(*)` FROM `orders` AS `orders` WHERE (`o_orderkey` > ?)", physicalPlan="[{table:orders,selectType:SIMPLE,type:index,key:PRIMARY,rows:1,filtered:100.0,extra:Scan rows(1, 1); Using where; Using index}]") |
| HitCache:true                                                                                                                                                                                                                                                                                            |
| Source:PLAN_CACHE                                                                                                                                                                                                                                                                                        |
| TemplateId: 24dd9dde                                                                                                                                                                                                                                                                                     |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

If you set all queries from the test0 account to be routed to the columnstore index by default using account-based routing, other SELECT queries will access the columnstore index instead of the row store.

Note

According to the route priority, template routing takes precedence over account routing. Therefore, queries that do not match the template will access the columnstore index based on the account routing rule.

EXPLAIN SELECT COUNT(*) FROM orders;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg(COUNT(*)="SUM(COUNT(*))")                                                                                                                                                            |
|   Exchange(distribution=single, collation=[])                                                                                                                                                |
|     PartialHashAgg(COUNT(*)="COUNT()")                                                                                                                                                       |
|       OSSTableScan(tables="orders_col_index_$9554[p1,p2,p3,...p64]", shardCount=64, partition=[remote], sql="SELECT `o_orderkey` FROM `orders_col_index_$9554` AS `orders_col_index_$9554`") |
| HitCache:true                                                                                                                                                                                |
| Source:PLAN_CACHE                                                                                                                                                                            |
| TemplateId: bc92bbe3                                                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

Keyword routing

Route queries from all accounts that contain a specific sequence of keywords to the column store.

Retrieve the keyword list for the SQL statement. You can run EXPLAIN KEYWORD to retrieve the keywords for the query and select a suitable list.

EXPLAIN KEYWORD SELECT COUNT(1) AS cnt FROM orders WHERE o_clerk LIKE '%xx%';
+--------------------------------------------------------------------------------------------------+
| KEYWORDS_LIST                                                                                    |
+--------------------------------------------------------------------------------------------------+
| 'select', 'count', '(', '1', ')', 'as', 'cnt', 'from', 'orders', 'where', 'o_clerk', 'like', '?' |
+--------------------------------------------------------------------------------------------------+

Select a set of keywords that uniquely identifies this type of query and create a rule.
```
CREATE ROUTING_RULE 'keyword_columnar' TO '%' FILTER BY KEYWORD('select', 'count', 'as', 'cnt', 'from', 'orders', 'where', 'o_clerk', 'like') WITH TYPE="columnar";
```
When any user runs a query that matches this keyword sequence, it is routed to the columnstore index.

Test the result

You can run EXPLAIN on a matching SQL statement. The query plan shows that the columnstore index, such as orders_col_index, is accessed.

EXPLAIN SELECT COUNT(1) AS cnt FROM orders WHERE o_clerk LIKE '%xx%';

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg(cnt="SUM(cnt)")              |
|   Exchange(distribution=single, collation=[])              |
|     PartialHashAgg(cnt="COUNT()")              |
|       OSSTableScan(tables="orders_col_index_$6ef7[p1,p2,p3,...p64]", shardCount=64, partition=[remote], sql="SELECT `o_orderkey` FROM `orders_col_index_$6ef7` AS `orders_col_index_$6ef7` WHERE (`o_clerk` LIKE ?)") |
| HitCache:false              |
| Source:PLAN_CACHE              |
| TemplateId: e1b2617d              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.03 sec)

Step 3: View routing rules

View all rules

You can use the SHOW ROUTING_RULES command or query the INFORMATION_SCHEMA.ROUTING_RULES view.

SHOW ROUTING_RULES [WHERE expr] [ORDER BY expr] [LIMIT expr]

Example

SHOW ROUTING_RULES ORDER BY HIT_COUNT DESC;
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
| ID   | RULE_NAME        | USER_NAME | TEMPLATE_ID | KEYWORDS                                                     | ROUTING_TYPE | CREATE_TIME         | HIT_COUNT |
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
|   19 | keyword_columnar | %         | NULL        | [select, count, as, cnt, from, orders, where, o_clerk, like] | columnar     | 2025-09-12 11:46:49 |         1 |
|   18 | test0_row_rule   | test0     | 24dd9dde    | []                                                           | row          | 2025-09-12 11:46:12 |         0 |
|   17 | test0_col_rule   | test0     | NULL        | []                                                           | columnar     | 2025-09-12 11:45:50 |         0 |
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
3 rows in set (0.02 sec)

SHOW * FROM information_schema.routing_rules ORDER BY HIT_COUNT DESC;
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
| ID   | RULE_NAME        | USER_NAME | TEMPLATE_ID | KEYWORDS                                                     | ROUTING_TYPE | CREATE_TIME         | HIT_COUNT |
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
|   19 | keyword_columnar | %         | NULL        | [select, count, as, cnt, from, orders, where, o_clerk, like] | columnar     | 2025-09-12 11:46:49 |         1 |
|   18 | test0_row_rule   | test0     | 24dd9dde    | []                                                           | row          | 2025-09-12 11:46:12 |         0 |
|   17 | test0_col_rule   | test0     | NULL        | []                                                           | columnar     | 2025-09-12 11:45:50 |         0 |
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
3 rows in set (0.01 sec)

View the routing process for a single SQL statement

You can use the EXPLAIN ROUTING command to view the detailed routing decision process for a specific SQL statement.

EXPLAIN ROUTING <SQL>

Example

EXPLAIN ROUTING SELECT COUNT(*) FROM orders;

+--------------+---------------------------+---------------+----------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ROUTING TYPE | CANDIDATE OPTIMIZER TYPES | WORKLOAD TYPE | OPTIMIZER TYPE | PLAN TYPE | DETAIL TRACE                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+--------------+---------------------------+---------------+----------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| COLUMNAR     | NULL                      | AP            | COLUMNAR       | COLUMNAR  | 
determine routing type:
current user: test0
routing type: COLUMNAR, caused by determined by user:test0

cached plan from plan cache:
routing type: COLUMNAR, caused by plan cache
candidate optimizer types: {null}, caused by plan cache doesn't record
workload type: AP, caused by plan cache
optimizer type: COLUMNAR, caused by plan cache
plan type: COLUMNAR, caused by plan cache
plan type: COLUMNAR, caused by setting from planner context
 |
+--------------+---------------------------+---------------+----------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Routing type (ROUTING TYPE): COLUMNAR.
Candidate optimizer types (CANDIDATE OPTIMIZER TYPES): NULL.
Workload type (WORKLOAD TYPE): AP.
Optimizer type (OPTIMIZER TYPE): COLUMNAR.
Plan type (PLAN TYPE): COLUMNAR.
Details (DETAIL TRACE): This section explains how the preceding types are determined.

Step 4: Delete routing rules

You can use the DROP ROUTING_RULE command to delete one or more rules.

DROP ROUTING_RULE {IF EXISTS} ruleName1{,...,ruleNameN};

Note

You must use a privileged account to delete routing rules.

Example

DROP ROUTING_RULE test0_col_rule,test0_row_rule,keyword_columnar;

FAQ

If an SQL statement matches multiple routing rules, which rule is applied?

The distributed version of PolarDB applies rules in the following order of priority:

For different rule types, the priority is determined by the rule type: Template routing > Keyword routing > Account routing.
For the same rule type, the priority is determined by the account scope: Rules for a specific account > Rules for all accounts (%).
For the same rule type and account scope, the priority is determined by the routing type: Row store (ROW) rules > Column store (COLUMNAR) rules.

The system selects the rule with the highest priority to execute.

How do I retrieve the template ID or keywords for an SQL statement?

Retrieve the template ID: You can add the EXPLAIN keyword before the target SQL statement and run it. Then, find the value of the TemplateId field in the returned result.
Retrieve the keywords: You can add the EXPLAIN KEYWORD keyword before the target SQL statement and run it. From the KEYWORDS_LIST in the returned result, you can select a sequence of keywords that uniquely identifies this type of query.

PolarDB:Rule-based row and column routing

Feature overview

Scope

Notes

Procedure

Step 1: Prepare the environment

Step 2: Create a routing rule

Parameters

Route priority

Examples

Account routing

Template routing

Keyword routing

Step 3: View routing rules

View all rules

View the routing process for a single SQL statement

Step 4: Delete routing rules

FAQ

Related topics