Deploy an AP architecture

This topic describes how to plan and deploy the AP capabilities of OceanBase Database. It covers the following topics: row-column hybrid storage, single-node and distributed topologies, readable replica types, routing and disaster recovery strategies, and resource control in batch processing scenarios. This topic also provides information on how to use AP columnar replicas and routing strategies in a write-intensive TP environment.

Version and access path decision

The deployment method of columnstore replicas (C replicas) and the route of read requests to C replicas are closely related to the OceanBase Database version. Please confirm the version before selecting the access path:

For hybrid row-columnstore tables (without independent C replicas), there are no ODP requirements as described above. The table-level storage form and ob_table_access_policy still apply.

Architecture overview

The typical AP architecture of OceanBase Database can be understood from three dimensions:

• Hybrid row-columnstore architecture: TP writes are the main focus, and AP read/write separation can be achieved with columnstore replicas. Routing supports automatic routing and forced specification (the specific methods vary by version, see the version table above).
• Single-node topology: In a single zone or simple multi-zone setup, F replicas (all-purpose) support both TP and AP; C replicas (pure columnstore) are only suitable for pure AP.
• Distributed topology: In multi-zone/multi-datacenter setups, columnstore replicas work with routing strategies, disaster recovery degradation, and load balancing. For heavy workloads like batch processing, you can use Resource Manager for resource control.

Hybrid row-columnstore architecture

Read/write separation: TP writes as the main focus, AP columnstore replicas

OceanBase Database supports TP and AP workloads within the same cluster, typically in two forms:

• Hybrid row-columnstore tables: A single table can have both rowstore and columnstore forms. TP writes go to the rowstore, and AP queries can be automatically routed to the rowstore or columnstore by the optimizer, suitable for mixed TP/AP scenarios with strong consistency reads. For more information, see OceanBase table types and storage forms.
• Columnstore replicas (C replicas): Deploy read-only columnstore replicas in a separate zone. TP writes and strong reads go to the primary/standby replicas (F replicas), while AP analysis goes to C replicas for physical isolation of TP/AP resources and storage savings.
  • Before V4.6.0: AP access to C replicas typically relies on independent ODP and routing variables on the ODP, often in weak read scenarios.
  • V4.6.0 and later: You can directly connect to or use regular connections to access C replicas with columnstore replica auto-selection, without needing to deploy a separate ODP for C replicas. Read consistency can still be configured using ob_read_consistency as described by version. General information about replicas and locality is available in Deploy and use columnstore replicas.

Routing strategy: automatic routing and forced specification

• V4.6.0 and later (with columnstore replica auto-selection): If a tenant has configured C replicas, the execution on columnstore replicas is determined by variables like ap_query_route_policy, ap_query_cost_threshold, and ap_query_replica_fallback, along with optimizer behavior. You can also use hints to control single SQL statements. For more information, see Deploy and use columnstore replicas. Session-level ob_route_policy.
• Before V4.6.0 (accessing C replicas through ODP): If not explicitly specified, the default ODP strategy and ob_route_policy determine the access. AP traffic is often routed to columnstore replicas through ODP's route_target_replica_type, proxy_route_policy, and init_sql with ob_route_policy = 'COLUMN_STORE_ONLY'.

For detailed information about readable replica types, ODP routing, disaster recovery degradation, and load balancing, see the Columnstore replicas and routing strategy (detailed) section of this topic and ODP read/write separation and routing strategy.

How to deploy the hybrid row-columnstore architecture

• If you adopt the hybrid row-columnstore table without C replicas (TP and AP in the same database, strong consistency reads), follow these steps:

  1. Choose hybrid row-columnstore or columnstore when creating a table: Specify the storage form using table properties, and the optimizer will automatically select the rowstore or columnstore path based on the query. For more information, see Columnstore.
  2. (Optional) Configure access preference: Use the system variable ob_table_access_policy (such as COLUMN_STORE, ROW_STORE, or AUTO) to specify the preferred access type.
  3. (Optional) Run batch jobs and isolate resources: For heavy workloads like materialized view refreshes or large-scale imports, configure resource groups or limits using Resource Manager to avoid affecting online TP/AP. For more information, see DBA_OB_RSRC_DIRECTIVES.

  Verification: Perform point queries and range scans on hybrid row-columnstore tables to confirm the storage format in the execution plan matches expectations.

• If you adopt the TP writes as the main focus, AP through columnstore replicas, it falls under the "Single-node/Distributed + C read" scheme. For more information, see C read deployment in single-node topology or Columnstore replica deployment in distributed topology below.

Single-node architecture

In a single-node or single-zone deployment, or in a scenario with a small number of zones, the relationship between readable replicas and AP is as follows.

F-read (Follow read): A comprehensive replica supporting both TP and AP

• Full replicas (F replicas) contain both the leader and followers in Paxos and participate in log synchronization. They can handle both strong-consistency reads and writes and weak reads.
• In a single-node or multi-zone topology, if you do not deploy independent C replicas, AP queries can be routed to F replicas (the columnar part of row-column hybrid tables), with the optimizer and ob_table_access_policy determining whether to use row or column storage.
• Characteristics: No need for separate columnar zones; simple deployment; TP and AP share the same set of F replicas, requiring resource isolation (e.g., through Resource Manager or user resource groups).

How to deploy F-read: No additional deployment is needed for AP. After deploying the cluster normally, when creating tables, choose row-column hybrid or columnar storage (see How to deploy a row-column hybrid architecture). TP and AP can share the same set of F replicas. For heavy workloads like batch processing, it is recommended to configure Resource Manager for resource isolation.

C-read: Pure columnar replicas, suitable only for AP

• ColumnStore replicas (C replicas) are read-only and pure columnar, not participating in Paxos elections. They are dedicated to AP workloads.
• In a single-node topology, if you add a C replica for a tenant (e.g., F replicas in a single zone plus an independent C replica in another zone), the way AP traffic reaches the C replica varies by version:
  • Before V4.6.0: You must configure an independent ODP to route traffic only to columnar replicas (e.g., route_target_replica_type = 'ColumnStore' and proxy_route_policy = 'TARGET_REPLICA_TYPE_FOLLOWER_ONLY') to achieve read isolation from TP.
  • V4.6.0 and later: After deploying C replicas in the tenant's locality, configure columnar replica auto-selection variables without needing to deploy ODP separately for C replicas.
• Use cases: When the workload is primarily analytical and you want to physically isolate AP reads from TP; whether to use weak or strong reads depends on the business requirements and the ob_read_consistency parameter and version.

How to deploy C-read (C replicas in a single-node or multi-zone topology):

1. Specify C replicas for the tenant: When creating a tenant, specify C in the LOCALITY parameter (e.g., F@zone1,F@zone2,C@zone3). For existing tenants, add a zone or node and then use ALTER TENANT ... LOCALITY to add C@zone4. For complete SQL, see Deploy and use columnar replicas.

2. Configure access paths based on the version

   • OceanBase Database ≥ V4.6.0: Set parameters like ap_query_route_policy as described in Deploy and use columnar replicas. Applications can connect to OBServer or existing proxies without needing to deploy ODP separately for C replicas or configure route_target_replica_type, proxy_route_policy, or init_sql for ODP.
   • OceanBase Database < V4.6.0: Deploy ODP (V4.3.2+) on a separate machine. Log in as root@proxysys and set: obproxy_read_consistency = 1, route_target_replica_type = 'ColumnStore', proxy_route_policy='TARGET_REPLICA_TYPE_FOLLOWER_ONLY', and init_sql='set @@ob_route_policy = COLUMN_STORE_ONLY'. Verify with SHOW PROXYCONFIG.

3. Verify: Execute a typical AP query and confirm that the execution plan and monitoring indicate data is sourced from the C replica (e.g., columnar scan operators). You can check the progress of row-to-column conversion using DBA_OB_CS_REPLICA_STATS or CDB_OB_CS_REPLICA_STATS.

Deploy in a distributed topology

When you deploy the system across multiple zones, IDCs, or regions, you need to consider disaster recovery, downgrade, and load balancing in addition to replica types and routing.

Resource control by Resource Manager in batch processing scenarios

• Batch processing, large-scale data import, and materialized view refresh are background AP tasks that consume a large amount of CPU and I/O resources, which may affect TP or online AP queries.
• OceanBase Database provides the Resource Manager feature, which allows you to isolate and set limits for different types of tasks such as materialized view refresh, MLOG purge, and batch processing tasks, to prevent resource exhaustion during batch processing.
• We recommend that you configure a resource group or Resource Manager strategy for batch processing tasks in a distributed AP architecture to ensure the stability of online TP/AP queries. For more information about the related views and configurations, see DBA_OB_RSRC_DIRECTIVES.

How to deploy resource isolation for batch processing

In a distributed topology, if you have background tasks such as rerunning batch processing tasks or refreshing materialized views, you can configure a resource group or set CPU/IO limits for these tasks through Resource Manager without modifying the replicas or ODP. For more information about the configuration method and views, see DBA_OB_RSRC_DIRECTIVES and the documentation on resource isolation.

Columnstore replicas and routing strategy (distributed)

In a distributed topology, the planning of columnstore replicas + routing can be understood in two parts:

How to deploy columnstore replicas in a distributed topology

1. Specify C replicas in the tenant's locality. You can specify multiple zones.

2. If OceanBase Database is V4.6.0 or later: Configure session or global variables as described in Deploy and use columnstore replicas. You do not need to deploy ODP separately for C replicas.

3. If OceanBase Database is earlier than V4.6.0: Deploy an independent ODP (V4.3.2 or later) to access C replicas. Configure weak reads, set route_target_replica_type='ColumnStore', proxy_route_policy='TARGET_REPLICA_TYPE_FOLLOWER_ONLY', and init_sql='set @@ob_route_policy = COLUMN_STORE_ONLY'.

4. Verify the request and row-to-column conversion progress. In a multi-IDC scenario, you can continue to configure IDC/REGION for zones and proxy_idc_name for ODP to achieve LDC affinity. For a complete locality example, see Deploy and use columnstore replicas.

Columnstore replicas and routing strategy

Readable replica type (route_target_replica_type) when accessing C replicas through ODP

Applicable to versions before V4.6.0.

• Full: A full replica (leader + follower) that participates in log synchronization and supports both TP and AP.
• Readonly: A read-only replica for read scaling.
• ColumnStore: A columnstore replica dedicated to AP; cannot be combined with Full or Readonly. When configured to use only columnstore replicas, the disaster recovery strategy is fixed to disallow rollback to the primary replica, and proxy_route_policy must be set to TARGET_REPLICA_TYPE_FOLLOWER_ONLY.

Routing and disaster recovery downgrade (proxy_route_policy)

Applicable to versions before V4.6.0.

• TARGET_REPLICA_TYPE_WITH_LEADER: Routes to both the primary and specified replicas without disaster recovery or downgrade logic.
• TARGET_REPLICA_TYPE_FOLLOWER_FIRST: Prioritizes specified-type replicas; if all are unavailable, falls back to read from the primary.
• TARGET_REPLICA_TYPE_FOLLOWER_ONLY: Routes only to specified-type replicas. If unavailable, disconnects without rolling back to the primary; this strategy is fixed when directing traffic to columnstore replicas.

Columnstore replica selection on the server side

Applicable to versions V4.6.0 and later.

• Based on ap_query_route_policy, ap_query_cost_threshold, and ap_query_replica_fallback. For more information, see Deploy and use columnstore replicas.
• ob_route_policy specifies the replica type for read paths on OBServer.

Load balancing (LDC/IDC)

• When traffic passes through ODP, ODP uses LDC to sort available replicas by IDC → Region → Cross-Region, achieving IDC affinity and load balancing.
• Configure IDC and REGION for zones on OBServer and proxy_idc_name for ODP. For more information, see ODP read/write splitting and routing strategy and SQL routing.

Deployment prerequisites and selection decisions

Prerequisites

Before deployment, ensure the following:

• Version and path: Based on the "Version and Access Path Decision" section of this document, determine whether to use ODP Dedicated Routing (for versions below V4.6.0) or Auto-Selected Columnstore Replica (for V4.6.0 and above).
• ODP (for versions below V4.6.0): The ODP accessing the C replica must be V4.3.2 or later and is recommended to be deployed separately from the ODP accessing the F replica.
• Cluster and tenant: The cluster has been deployed according to the plan (e.g., 2F1A, 3F, etc.); if using the C replica, at least one zone is dedicated to the C replica, and it is recommended to deploy only the C replica in this zone.
• Network and permissions: The application and OBServer/ODP must be network-interoperable; the account executing Locality, tenant, and ODP configurations must have the corresponding permissions.

Deployment recommendations

For more details, see Deploy and use columnstore replicas.

References

• Deploy and use columnstore replicas
• AP query and columnstore replica routing rules