Rule-based row and column routing lets you define explicit routing policies on the primary instance endpoint to direct SELECT queries to the row store or a column store read-only instance — based on the source database account, a SQL template ID, or a sequence of keywords. Queries that match no rule fall back to the optimizer's cost-based routing.
How it works
PolarDB for X supports two built-in approaches for directing mixed online transactional processing (OLTP) and online analytical processing (OLAP) traffic to the appropriate storage engine:
Direct connection: Connect directly to a column store read-only instance endpoint. Analytical queries go to that instance explicitly. This requires classifying workloads in your application code.
Cost-based routing: Connect to the primary instance. The optimizer automatically routes complex queries to a column store read-only instance based on cost estimation. No code changes are needed, but routing accuracy depends on the optimizer's estimates. This is the default behavior when no routing rules are configured.
Rule-based routing combines the advantages of both. Rules are defined on the primary instance endpoint, so no application changes are needed. Because rules are explicit, routing accuracy doesn't depend on cost estimation. Each rule targets one of three matching criteria — account, SQL template, or keyword — and routes matched queries to either the row store (ROW) or a column store read-only instance (COLUMNAR).
All three rule types coexist and interact by priority. See Rule priority.
Prerequisites
Before you begin, make sure that:
The compute node (CN) version of your instance is 5.4.20 or later. To check the version, see View and update the version of an instance. For version naming conventions, see Release notes.
A column store read-only instance is added to your cluster. See Add a column store read-only instance.
Columnstore indexes exist on the relevant tables. See Guide to creating columnstore indexes.
Usage notes
Supported queries: Only
SELECTqueries can be routed by rules. Queries inside a transaction cannot be routed to a column store read-only instance.Data latency: Column store data lags behind row store data by seconds. Don't use column store routing for applications that require real-time data.
Concurrency limits: Column store access is resource-intensive with limited concurrency. Use a dedicated column store read-only instance for OLAP queries to avoid impacting OLTP workloads on the primary instance. Don't route high-QPS queries to column store read-only instances — this feature is designed for low-concurrency, heavy-scan, or heavy-aggregation workloads.
Case sensitivity: All fields in routing rules, including usernames and keywords, are case-insensitive.
Set up the environment
Associate the column store read-only instance with the primary instance endpoint so that queries sent to the primary endpoint can be routed to the column store.
Go to the PolarDB for X console. On the Instances
In the Connection Information section, click Configuration Management.
In the Configuration Management dialog box, under Resource Configuration, move the target column store read-only instance from the Available Instances list to the Selected Instances list.
Create a routing rule
Connect to the primary instance using a privileged account and run CREATE 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. Must be globally unique. |
user_name | The database account to match. Use % to match all accounts. |
FILTER BY | Optional. Omit this clause for account-based routing. Include it to match by SQL template (TEMPLATE) or keyword sequence (KEYWORD). You cannot combine both filters in a single rule. |
TEMPLATE('template_id') | Matches queries by their SQL template ID. Use this when the query structure is fixed and only parameter values change. |
KEYWORD('kw1','kw2') | Matches queries that contain all specified keywords in order. Use this when query conditions vary. Keywords do not support regular expressions. The total length of all keywords cannot exceed 500 characters. |
TYPE | The routing destination: ROW (row store node) or COLUMNAR (column store read-only instance). |
Rule priority
When a query matches multiple rules, the system selects the highest-priority rule:
By rule type: Template routing > Keyword routing > Account routing
By account scope (same rule type): Specific account > All accounts (
%)By routing destination (same rule type and scope):
ROW>COLUMNAR
Examples
The following examples use the orders table defined with 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 16InEXPLAINoutput,OSSTableScanon a columnstore index indicates column store routing.LogicalViewon the base table indicates row store routing.
Account routing
Route all queries from the test0 account to the column store:
CREATE ROUTING_RULE 'test0_col_rule' TO 'test0' WITH TYPE=COLUMNAR;To verify, run EXPLAIN on any SELECT as the test0 account. The plan shows an OSSTableScan on the columnstore index, confirming column store routing:
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)When another account runs the same query, the plan shows a LogicalView on the orders table instead, confirming row store routing:
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
Route a specific query pattern from the test0 account to the row store, while the account-based rule still routes all other queries to the column store.
Get the template ID. Run
EXPLAINon the target query and note theTemplateIdfield:EXPLAIN SELECT COUNT(*) FROM orders WHERE o_orderkey > 10;+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)The template ID is
24dd9dde. This ID represents the query structureSELECT COUNT(*) FROM orders WHERE o_orderkey > ?— the same template applies to all queries with different values foro_orderkey.Create the rule:
CREATE ROUTING_RULE 'test0_row_rule' TO 'test0' FILTER BY TEMPLATE('24dd9dde') WITH TYPE=ROW;
To verify, run EXPLAIN on a query matching this template. The plan shows a LogicalView on orders, confirming row store routing:
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)Other SELECT queries from test0 that don't match the template still hit the account routing rule and go to the column store. Template routing takes precedence over account routing per the rule priority order.
Keyword routing
Route queries containing a specific keyword sequence from any account to the column store.
Get the keyword list for the target query. Run
EXPLAIN KEYWORDand note theKEYWORDS_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 subset of keywords that uniquely identifies this query type and create the rule:
CREATE ROUTING_RULE 'keyword_columnar' TO '%' FILTER BY KEYWORD('select', 'count', 'as', 'cnt', 'from', 'orders', 'where', 'o_clerk', 'like') WITH TYPE="columnar";
To verify, run EXPLAIN on a matching query. The plan shows an OSSTableScan on the columnstore index, confirming column store routing:
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)View routing rules
View all rules
Use SHOW ROUTING_RULES or query the INFORMATION_SCHEMA.ROUTING_RULES view:
SHOW ROUTING_RULES [WHERE expr] [ORDER BY expr] [LIMIT expr]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)The HIT_COUNT field shows how many times each rule has been matched since it was created, which is useful for identifying unused or frequently triggered rules.
Debug routing for a specific query
Use EXPLAIN ROUTING to see the full routing decision for a query, including which rule triggered it and why:
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)The output columns explain how each routing decision was made:
| Column | Description |
|---|---|
ROUTING TYPE | The routing destination determined by rules (COLUMNAR or ROW) |
CANDIDATE OPTIMIZER TYPES | The optimizer types considered (NULL when determined by a rule) |
WORKLOAD TYPE | The detected workload type (AP for analytical) |
OPTIMIZER TYPE | The optimizer selected |
PLAN TYPE | The final plan type used |
DETAIL TRACE | Step-by-step explanation of how each field was determined |
Delete routing rules
Use DROP ROUTING_RULE to delete one or more rules. A privileged account is required.
DROP ROUTING_RULE {IF EXISTS} ruleName1{,...,ruleNameN};Example:
DROP ROUTING_RULE test0_col_rule, test0_row_rule, keyword_columnar;FAQ
If a query matches multiple rules, which one applies?
The rule with the highest priority applies. Priority is determined in this order: (1) template routing beats keyword routing, which beats account routing; (2) within the same rule type, a specific-account rule beats an all-accounts (%) rule; (3) within the same rule type and account scope, a ROW rule beats a COLUMNAR rule. See Rule priority.
How do I find the template ID or keywords for a query?
Run EXPLAIN <SQL> to get the template ID — look for the TemplateId field in the output. Run EXPLAIN KEYWORD <SQL> to get the keyword list — the KEYWORDS_LIST field shows all tokens for that query. Select a subset that uniquely identifies the query type when creating a keyword rule.
What happens if no routing rules are configured?
Without routing rules, PolarDB for X falls back to cost-based routing: the optimizer automatically decides whether to send a query to the row store or a column store read-only instance based on cost estimation. Rule-based routing overrides cost-based routing for matched queries only.