Deploy and use columnstore replicas

To support hybrid workloads such as TP and AP, OceanBase Database supports columnstore replicas (COLUMNSTORE, abbreviated as C replicas). C replicas can be deployed in independent zones. User tables (including replicated tables, excluding index tables, internal tables, and system tables) are stored in these replicas in columnar format.

This topic describes how to deploy C replicas in the Locality tenant and how to use C replicas.

Limitations and considerations

• Limitations

  • C replicas cannot be converted to F or R replicas, and vice versa.
  • Physical restore is not supported for C replicas. If a C replica is included in the locality of a tenant, the restore will fail.

• Deployment recommendations

  • Starting from V4.3.5 BP1, you can deploy multiple C replicas. We recommend that you deploy up to 3 C replicas.
  • For OceanBase Database versions earlier than V4.6.0, we recommend that you deploy only C replicas in the zone where the C replica is located. The independent ODP must be ODP V4.3.2 or later.
  • If the primary database does not have a C replica, we recommend that you do not deploy a C replica in the standby database.

• Considerations

  • When you perform DDL operations in a cluster that has C replicas, the system resource consumption typically increases. This is because the system needs to process both rowstore and columnstore data simultaneously, and the commit logs (clogs) must be synchronized to the corresponding replicas. As a result, the CPU, memory, disk, and I/O resources face higher pressure.
  • The major compaction of a C replica is generally slower than that of an F or R replica. A new tenant-level major compaction cannot be initiated until the previous one is completed. Before manually initiating a major compaction, we recommend that you check CDB_OB_ZONE_MAJOR_COMPACTION and DBA_OB_ZONE_MAJOR_COMPACTION. For more information, see View major compaction information.

Deploy a tenant with a C replica

In the Locality of a tenant, you can specify the C replica. The following two scenarios are common:

• Scenario A: Create a tenant with a C replica.
• Scenario B: Add a C replica to an existing tenant (add a new zone and adjust the Locality).

The following sample SQL statements use sample values for CPU, memory, and disk. You must adjust the values based on your actual workload and resource planning.

Scenario A: Create a tenant with a C replica

Assume that you have deployed a cluster in the 2F1A deployment mode (two replicas and one arbitration service). The cluster has three zones: zone1, zone2, and zone3. The arbitration service is deployed in zone3. You need to create a tenant with a C replica in the cluster. The steps are as follows:

1. Find a server that can communicate with the cluster and deploy ODP on the server.

   To prevent resource contention, we recommend that you deploy ODP on a separate server. ODP V4.3.2 or later must be deployed.

2. Create a resource unit named unit1.

   CREATE RESOURCE UNIT unit1,
       MAX_CPU=5,
       MIN_CPU=5,
       MEMORY_SIZE= '32G',
       MAX_IOPS=10000,
       MIN_IOPS=5000,
       LOG_DISK_SIZE=5301023539200;
   

3. Create a resource pool named pool1 and specify unit1 as the resource unit.

   CREATE RESOURCE POOL pool1
       UNIT='unit1',
       UNIT_NUM = 1,
       ZONE_LIST = ('zone1','zone2','zone3');
   

4. Create a tenant named tenant_c and specify its Locality as F@zone1,F@zone2,C@zone3.

   CREATE TENANT tenant_c
       LOCALITY = 'F@zone1,F@zone2,C@zone3',
       PRIMARY_ZONE='zone1;zone2,zone3',
       RESOURCE_POOL_LIST=('pool1') SET ob_tcp_invited_nodes = '%';
   

   In this example, the tenant is created in MySQL compatible mode by default. To create a tenant in Oracle compatible mode, you must explicitly specify ob_compatibility_mode='oracle' by using the system variable ob_compatibility_mode.

Scenario B: Add a C replica to an existing tenant

Assume that the cluster has a tenant named tenant_c with a Locality of F@zone1,F@zone2,F@zone3. The tenant uses a resource pool named pool1, whose ZONE_LIST is 'zone1','zone2','zone3'. You need to add a C replica to the tenant. You can add zone4 and modify the Locality of the tenant to F@zone1,F@zone2,F@zone3,C@zone4. The steps are as follows:

1. Add a new zone named zone4 to the cluster. For more information, see Add a zone.

2. Add nodes to zone4. For more information, see Add a node.

3. Create a resource unit named unit2.

   CREATE RESOURCE UNIT unit2,
       MAX_CPU=5,
       MIN_CPU=5,
       MEMORY_SIZE= '32G',
       MAX_IOPS=10000,
       MIN_IOPS=5000,
       LOG_DISK_SIZE=5301023539200;
   

4. Create a resource pool named pool2 and specify unit2 as the resource unit.

   CREATE RESOURCE POOL pool2
       UNIT = 'unit2',
       UNIT_NUM = 1,
       ZONE_LIST = ('zone4');
   

5. Add pool2 to the tenant.

   ALTER TENANT tenant_c RESOURCE_POOL_LIST = ('pool1','pool2');
   

6. Modify the Locality of the tenant to F@zone1,F@zone2,F@zone3,C@zone4.

   ALTER TENANT tenant_c LOCALITY = 'F@zone1,F@zone2,F@zone3,C@zone4';
   

Use columnar replicas

Use of columnar replicas

• Before V4.6.0, you must configure an independent ODP to send business requests to the columnar replica.

  That is, configure a separate ODP and set weak consistency, routing, and init_sql (commonly ob_route_policy = COLUMN_STORE_ONLY) on the ODP to specify the request routing strategy.

• From V4.6.0 onward, you do not need to deploy an independent ODP for the columnar replica. The columnar replica shares the ODP with the F replica. The database automatically selects the routing strategy, which is referred to as automatic routing of columnar replicas.

For more information about the automatic routing rules of columnar replicas, see Routing rules for AP queries and columnar replicas.

Before V4.6.0: Deploy an independent ODP to send business requests to the C replica

This applies to V4.6.0 and earlier or V4.6.0 and later, if you still need to use an independent ODP to route AP weak-consistency reads to the C replica, and isolate the TP link.

Deploy an ODP

Deploy an ODP dedicated to accessing the C replica on a server that can communicate with the cluster. We recommend that you deploy it on a single server. The ODP version must be no earlier than V4.3.2.

Configure the routing forwarding strategy and weak-consistency reads

Log in to the ODP as root@proxysys, for example:

obclient -uroot@proxysys -h10.10.10.1 -P2883 -p

Select Option 1 or Option 2. We recommend that you select:

• Option 1 (proxy_primary_zone_name) if you know the zone where the C replica resides and want to converge weak-consistency read traffic by zone.
• Option 2 (route_target_replica_type) if you prefer to route traffic by replica type (ColumnStore) instead of binding to a fixed zone name.

• Option 1

  • Read-only and weak-consistency read parameters:

    obclient> ALTER PROXYCONFIG SET obproxy_read_only = 0;
    obclient> ALTER PROXYCONFIG SET obproxy_read_consistency = 1;
    

  • Configure the ODP to route all requests to the zone where the C replica resides and generate query plans for the C replica. For example, the zone where the C replica resides is zone4.

    obclient> ALTER PROXYCONFIG SET proxy_primary_zone_name='zone4';
    obclient> ALTER PROXYCONFIG SET init_sql='set @@ob_route_policy = COLUMN_STORE_ONLY';
    

• Option 2

  • Read-only and weak-consistency read parameters:

    obclient> ALTER PROXYCONFIG SET obproxy_read_only = 0;
    obclient> ALTER PROXYCONFIG SET obproxy_read_consistency = 1;
    

  • Configure the ODP to route all requests to the C replica and generate query plans for the C replica:

    obclient> ALTER PROXYCONFIG SET route_target_replica_type = 'ColumnStore';
    obclient> ALTER PROXYCONFIG SET init_sql='set @@ob_route_policy = COLUMN_STORE_ONLY';
    

  • Configure the ODP to select only from replicas for all requests. If all replicas are unavailable, the ODP disconnects from the client:

    obclient> ALTER PROXYCONFIG SET proxy_route_policy='TARGET_REPLICA_TYPE_FOLLOWER_ONLY';
    

After the configuration is modified, run the following statement to verify the modification:

obclient> SHOW PROXYCONFIG ALL LIKE 'obproxy_read_only';
obclient> SHOW PROXYCONFIG ALL LIKE 'obproxy_read_consistency';
obclient> SHOW PROXYCONFIG ALL LIKE 'proxy_primary_zone_name';
obclient> SHOW PROXYCONFIG ALL LIKE 'init_sql';
obclient> SHOW PROXYCONFIG ALL LIKE 'route_target_replica_type';
obclient> SHOW PROXYCONFIG ALL LIKE 'proxy_route_policy';

After the configuration is successful, OLAP services access the system through the independent ODP and direct queries to the C replica.

V4.6.0 and later: C replicas and F replicas use the same ODP for automatic routing

This feature is applicable when C replica deployment is completed, and all business requests need to be uniformly routed to the database. Please use the same connection method as for accessing F/R replicas: through the same business ODP.

Quick usage example (please replace the table name and conditions based on your environment):

-- 1. Confirm the existence and default values of automatic routing-related variables
SHOW VARIABLES LIKE 'ap_query%';

-- 2. Keep the default AUTO or explicitly specify (optional)
SET SESSION ap_query_route_policy = 'AUTO';

-- 3. Check if the plan involves columnar replicas (actual operators apply)
EXPLAIN SELECT COUNT(*) FROM your_table;

Whether to plan for read-only operations on the C replica is jointly determined by ap_query_route_policy, ap_query_cost_threshold, ap_query_replica_fallback, routing rules, and read consistency. For more details, see Automatic routing for columnar replicas.

Columnstore replica auto-routing

Starting from OceanBase Database V4.6.0, you can control whether read-only queries attempt to execute on C replicas using system variables.

The ap_query_route_policy variable is the columnstore replica auto-routing control switch that determines whether the system attempts to generate execution plans for qualifying analytical queries targeting C replicas. For more details on the determination rules, see AP query and columnstore replica routing determination rules.

Prerequisites

• Tenant: The target tenant has C replicas configured in Locality, and the replicas can provide read services.
• Variables: Execute SHOW VARIABLES LIKE 'ap_query%'; to confirm the existence of variables such as ap_query_route_policy, ap_query_cost_threshold, and ap_query_replica_fallback.

System variable descriptions

The following variables are used for columnstore replica auto-selection. The meanings are based on the cluster SHOW VARIABLES and the current version's documentation.

ap_query_route_policy values

obclient> SHOW VARIABLES LIKE 'ap_query_route_policy';
obclient> SET ap_query_route_policy = 'AUTO';
obclient> SET ap_query_route_policy = 'FORCE';

ap_query_cost_threshold: In AUTO mode, the cost of a single parallelism plan exceeding this threshold triggers consideration for columnstore plans. The default value is determined by SHOW VARIABLES (commonly 200000).

obclient> SHOW VARIABLES LIKE 'ap_query_cost_threshold';
obclient> SET SESSION ap_query_cost_threshold = 300000;

ap_query_replica_fallback: If the target is a C replica but it is unavailable or planning fails, whether to fallback to rowstore.

obclient> SHOW VARIABLES LIKE 'ap_query_replica_fallback';
obclient> SET SESSION ap_query_replica_fallback = OFF;

Relationship with ob_route_policy

• ap_query_route_policy: Controls whether the optimizer attempts to generate columnstore plans (Auto/Force/Off).
• ob_route_policy: Specifies the types of replicas available for read paths within an OBServer (e.g., COLUMN_STORE_ONLY).

They operate at different levels and are not simple substitutes. When accessing a C replica via an independent ODP, you often set ob_route_policy = COLUMN_STORE_ONLY in init_sql, alongside ODP routing, to direct traffic to columnstore. Columnstore replica auto-routing, on the other hand, relies on the OBServer-side ap_query_route_policy variable.

Strategy selection by scenario

Brief steps

After understanding the configuration methods and strategy selection for different scenarios, you can follow these steps to complete the setup.

• The default value of ap_query_route_policy is AUTO. You can manually change it to FORCE or OFF if needed.
• The system will only attempt to generate columnstore plans if the tenant has available C replicas and the query meets the relevant criteria.
• You can adjust the sensitivity to heavy queries in AUTO mode using ap_query_cost_threshold, and control fallback to rowstore when C replicas are unavailable or planning fails using ap_query_replica_fallback.

Recommended operation sequence:

1. Confirm that C replicas have been deployed as described in the Deploy Columnstore Replica section.
2. Check variables: SHOW VARIABLES LIKE 'ap_query%';
3. Adjust as needed for the current session, e.g.: SET SESSION ap_query_route_policy = 'FORCE'; or modify ap_query_cost_threshold and ap_query_replica_fallback.
4. Execute EXPLAIN on the target SQL to check for columnstore-related operators; also use DBA_OB_CS_REPLICA_STATS / CDB_OB_CS_REPLICA_STATS to check the status of C replicas.

Query-level control

For temporary verification, single SQL optimization, or when not changing the session's global default, use hints in the statement:

• Attempt to follow columnstore strategies for this statement (if allowed by policy and replicas):

  obclient> EXPLAIN SELECT /*+opt_param('ap_query_route_policy', 'FORCE')*/ *
     FROM t1, t2
     WHERE t1.c1 = t2.c1;
  

• Disable auto-selection of columnstore for this statement:

  obclient> EXPLAIN SELECT /*+opt_param('ap_query_route_policy', 'OFF')*/ *
      FROM t1, t2
      WHERE t1.c1 = t2.c1;
  

Read consistency on columnstore replicas

Before V4.6.0, accessing C replicas only supported weak read consistency (ob_read_consistency = 'weak'). Starting from V4.6.0, strong consistency reads are supported on C replicas. For more information on the ob_read_consistency variable, see ob_read_consistency.

Strong read consistency on columnstore replicas

In strong read scenarios, the transaction layer checks whether the C replica (Follower) has replayed logs to a readable point based on the global consistent snapshot. If necessary, it waits for the replay before executing the query. The following are simplified steps for better understanding, not implementation details:

1. Obtain the global consistent snapshot version (e.g., via GTS).
2. Check if the C replica has replayed logs to this version.
3. If not ready, wait; otherwise, execute the read.

Comparison between strong read and weak read

Recommendation: Use strong read (default) when high consistency is required; use weak read when latency can be tolerated, as it often performs better.

When ap_query_route_policy = AUTO, the system evaluates conditions such as weak read, parallelism, and cost threshold to determine whether to attempt a columnar plan. Weak read is one of the conditions that may trigger this attempt, but it is not the only condition. For more information, see AP query and columnar replica routing rules.

Verification and troubleshooting recommendations

We recommend that you troubleshoot in the following order:

• C replica: Check whether the tenant's locality includes C, whether the replica is available, and whether row-to-column conversion is complete (see Row-based storage and CDB_OB_CS_REPLICA_STATS / DBA_OB_CS_REPLICA_STATS).
• Variables: Run SHOW VARIABLES LIKE 'ap_query%'; to verify whether ap_query_route_policy and other variables are set as expected.
• Execution plan: Run EXPLAIN on the target SQL statement (or view the actual execution plan) to check whether columnar operators are involved.
• Still not using columnar storage: Compare the basic conditions and AUTO-triggered conditions in AP query and columnar replica routing rules.

For tenants without C replicas, enabling the above variables typically does not change the basic planning behavior when columnar storage is not available.

References

• Columnstore replicas
• AP query routing rules for columnstore replicas
• AP architecture deployment guide
• Columnstore FAQ