DEV Community: OceanBase User Group

Online Horizontal Scaling: How OceanBase Adds Nodes Transparently

Charles Wu — Wed, 05 Aug 2026 14:59:00 +0000

How OceanBase supports online horizontal scaling by adding nodes and automatically rebalancing data and workloads at the database kernel layer

When businesses grow rapidly, database scaling often becomes one of the biggest challenges for operations teams. For many relational databases, horizontal scaling is never as simple as “adding one more machine.” It usually comes with service interruption risk and requires teams to manually redesign sharding strategies, migrate data, and carefully validate the results.

Mainstream cloud-native databases offer natural advantages in scaling reads, but writes are often constrained by the architectural bottleneck of a single primary node. When write traffic surges, teams often have to upgrade to a larger instance type, which still involves a database restart or a primary-standby switchover.

As a native distributed database, OceanBase makes adding nodes much simpler. Users only need to adjust a parameter, and the new nodes can automatically start taking over traffic. The change is largely transparent to applications, and services remain available throughout the process. This turns what was once a high-risk scaling operation into a routine task that can be performed whenever needed.

How OceanBase Horizontal Scaling Works

Adjust One Parameter to Scale Out and Rebalance Data Automatically

In “OceanBase Tenant-Level Scaling: Adjust Resources in Seconds,” we compared an OceanBase cluster to a large business park, where each physical machine is like an office building. When a company, or tenant, moves into the park, it leases a package of offices distributed across different buildings. These offices correspond to the resource units allocated to the tenant.

In that article, we explained that the fastest way to handle sudden traffic spikes is to add more desks and chairs inside the existing offices — that is, to increase the resource quota within each machine. But when a single machine is already close to its physical limit, the system needs to scale out by adding nodes.

In OceanBase, horizontal scaling is mainly achieved in two ways:

Increase the number of resource units.
This applies to capacity expansion within the same data center. In the business park analogy, this is like adding new office buildings to the same park and leasing additional offices in those buildings. In technical terms, new nodes are added to the cluster, and the tenant receives more resource units across those nodes. Heavy workloads that were previously concentrated in a small number of offices are redistributed across more offices and processed in parallel, breaking through the compute and storage limits of a single machine.
Increase the number of primary zones.
This applies to service expansion across data centers, allowing nodes in more regions to serve traffic together. It is like a company with offices in cities A, B, and C. Originally, only city A handles customer reception and order processing, while cities B and C mainly handle accounting synchronization, or data backup. When city A reaches its service capacity limit, the branch office in city B can also be promoted into a business center that handles orders. In this way, nodes in multiple regions can directly process read and write requests at the same time. Without adding new physical machines, the system can make better use of previously idle resources.

No matter which parameter is adjusted, adding nodes and redistributing workloads, or data rebalancing, are handled automatically by the database kernel. After new nodes join the cluster, OceanBase automatically and smoothly migrates some data to the new nodes based on data volume, storage usage, and hotspot distribution.

For example, if three nodes originally manage 100 data segments, after two new nodes are added, the system may redistribute roughly 20 segments to each new node. Eventually, all five nodes share the workload together, with no service interruption or manual intervention.

How Does OceanBase Maintain Consistency During Data Migration?

During horizontal scaling, OceanBase does not take the entire database offline or move all data to a new set of nodes at once. Instead, it migrates data gradually at the partition level.

In a multi-replica architecture, read and write traffic for each partition always follows the corresponding leader replica. During scaling, the system first creates new follower replicas for selected partitions on the new nodes and starts synchronizing data. This synchronization is based on OceanBase’s internal physical logs, known as clog, and the Multi-Paxos protocol.

Compared with the binlog and relay log processing chain used in MySQL primary-standby replication, this mechanism is more lightweight. It reduces synchronization overhead in high-concurrency scenarios and helps new replicas catch up with the source nodes more quickly.

After the replicas on the new nodes catch up, the system initiates a smooth leader switchover based on the Multi-Paxos protocol. The current leader does not step down until the necessary log commits are complete. Through majority election and lease mechanisms, the system ensures that the replica on the new node can safely take over as the new leader.

This mechanism ensures that the same partition never has two valid leaders at the same time, preventing data inconsistency caused by split-brain scenarios. After the switchover is complete, the frontend proxy component automatically detects the change, refreshes routing information, and forwards subsequent requests to the node where the new leader resides.

The entire handoff is completed at the kernel and routing layers, while business applications continue running steadily.

Note: OceanBase’s Multi-Paxos protocol and related mechanisms play a decisive role in balancing data reliability and availability. For details, see the article on how OceanBase uses Multi-Paxos for consensus and disaster recovery.

Why Are Applications Unaware of the Change?

After data migration is complete, how does the application know where to find the data?

The answer is that it does not need to know.

As mentioned above, OceanBase provides a transparent routing proxy layer, OceanBase Database Proxy (ODP, also known as OBProxy). No matter how the underlying nodes change or where data is migrated, the application continues to connect through a single stable database endpoint.

Developers do not need to modify database connection strings or change SQL syntax. They can use a continuously expanding cluster just as they would use a conventional single-node database.

Performance Scales Near-Linearly as Nodes Are Added

The real value of scaling lies in whether performance actually improves as more nodes are added.

In the publicly available TPC-C benchmark, when an OceanBase cluster expanded from 3 nodes to 1,500 nodes, its transaction throughput per minute, or tpmC, achieved near-linear growth. The cluster maintained this near-linear scaling even when 10% of the workload consisted of distributed transactions.

Many distributed architectures maintain consensus and synchronization independently for replicas of each data partition. This means that as the number of partitions increases, the total overhead required to maintain data consistency also rises. OceanBase’s design ties this overhead to the number of nodes rather than the number of partitions. As a result, when the cluster scales horizontally, overhead grows smoothly with the number of nodes instead of spiraling out of control as data partitions multiply.

By contrast, mainstream cloud-native architectures are still essentially based on a primary-standby model. The cluster has only one write node, and simply adding read-only nodes cannot break through the physical ceiling of write capacity.

Scaling Duration and Business Impact

A practical question for engineers is: how long does the operation take, and how much will it affect the business?

The time required for scaling is mainly determined by the amount of business data and the available cluster resources at the time, such as network bandwidth and disk I/O. The upper limit of migration speed can be roughly estimated as follows:

Note: Available bandwidth equals the hardware’s physical limit minus the resources currently consumed by the business. The default migration quota ratio is 60%.

Put simply, migration can only run as fast as the more constrained resource allows, whether that constraint is disk throughput or network bandwidth. If scaling is performed during peak business hours, business traffic may already be consuming a large amount of these resources. With fewer resources left for migration tasks, scaling naturally takes longer.

During scaling, background data migration tasks share node resources with frontend business traffic. To avoid resource contention, OceanBase provides resource isolation mechanisms. Operations teams can centrally configure the network bandwidth quota, disk IOPS limit, and CPU weight available to migration tasks. During off-peak hours, they can loosen these limits to accelerate migration. During peak hours, they can tighten them to protect normal business traffic.

The actual impact on the business depends on cluster load at the time of scaling.

When the cluster is lightly loaded, such as when CPU and I/O utilization are below 60%, the business is almost unaffected.
When the cluster is heavily loaded, resource isolation can proportionally limit the bandwidth and CPU consumed by migration tasks, keeping QPS fluctuations within 15% and restoring stability within a few minutes.

When Should You Use Horizontal Scaling?

OceanBase provides multiple elastic scaling options, and enterprises can choose based on their needs. As the final topic in this series, this article summarizes the recommended approaches for different scenarios:

Note: In the pay-as-you-go mode of OceanBase Cloud, billing based on the new specification starts as soon as the scaling operation is initiated, and compute costs increase as nodes are added. After scale-in, node charges also stop immediately, so you do not need to continue paying for idle capacity.

Summary

Database scaling should not be a disruptive, high-risk engineering project. It should be a routine resource adjustment.

OceanBase achieves this through a combination of kernel-level mechanisms: consensus protocols protect data during migration, the routing layer keeps applications unaware of topology changes, and the database kernel handles load rebalancing automatically. This design allows the database to scale flexibly with business demand, while helping enterprises avoid long-term over-provisioning for uncertain growth.

OceanBase Tenant-Level Scaling: Adjust Resources in Seconds

Charles Wu — Tue, 04 Aug 2026 14:59:00 +0000

How OceanBase tenant-level scaling adjusts CPU, memory, and IOPS in seconds without data migration, failover, or application changes

Traffic fluctuations are inevitable for online services. For many platform engineers and DBAs, the real challenge is not necessarily a consistently high volume of traffic, but traffic that is highly volatile and unpredictable. Promotional events, product launches, flash sales, and new game releases can all create sudden traffic spikes. Estimating database capacity requirements for these events in advance is difficult, and traditional database scaling is often slow, tedious, and involves operational risk.

Many operations teams are familiar with this challenge. To ensure system stability during a major event — such as a highly anticipated game launch or a limited-edition product release — engineers are often forced to perform database scaling during off-peak hours, often late at night.

Scaling traditional single-node databases like MySQL requires upgrading the server specification, which involves provisioning a new machine, syncing data, and performing a primary-standby switchover. Even with modern cloud-native databases like Amazon Aurora, which eliminate some data migration overhead, upgrading compute instances still requires a restart or a failover that can cause brief connection interruptions. The entire process is time-consuming, and because the switchover can cause service interruptions, it usually has to be scheduled in a maintenance window.

Worse still, to play it safe, engineers often over-provision resources based on the most extreme scenarios. Once the event ends, they must go through the same cumbersome downscaling process all over again.

OceanBase’s tenant-level elastic scaling is designed specifically to address this pain point. It transforms database resource adjustment into a routine, daytime operation that takes effect in seconds and remains completely transparent to applications.

How OceanBase Manages Tenant Resources

To understand how tenant-level elastic scaling works, we first need to look at how OceanBase allocates and manages tenant resources.

As a native distributed database, OceanBase organizes multiple physical servers into a unified cluster. On top of this cluster, OceanBase introduces the concept of tenants. A tenant can be understood as an independent database instance running within the cluster. Each tenant has its own data and resource quotas, and is fully isolated from other tenants. To an application, connecting to a tenant is identical to connecting to a standalone MySQL or Oracle database instance.

This multi-tenant architecture is powered by a fine-grained resource management system:

Unit config: Defines the allocation of physical resources, including CPU, memory, IOPS, and transaction log disk space.
Resource unit: The actual allocation unit for these physical resources, distributed across the nodes of the cluster.
Resource pool: A collection of resource units. A tenant obtains its required compute and storage resources by being associated with a resource pool.

To use an analogy, think of an OceanBase cluster as a large business park, where the physical servers are the office buildings. When a company (a tenant) moves into the park, it leases a package of offices (a resource pool) spread across different buildings. Each individual office is a resource unit. The layout of the office — whether it is configured for 10 or 20 workstations — is defined by the unit config.

Scaling by Adjusting Unit Configs

In OceanBase, tenant-level scaling can be achieved through two primary paths:

Adjusting the unit config to increase resource quotas within the existing nodes.
Adding nodes or additional zones to scale out horizontally.

This article focuses on the first method — adjusting the unit config — which is a convenient way to address capacity bottlenecks.

Adjusting the unit config is a vertical scaling mechanism applied at the tenant level. By modifying a tenant’s unit config, you can quickly increase or decrease the CPU, memory, and IOPS allocated to that tenant on each node.

Returning to our office analogy: this is equivalent to keeping the number of offices the same but adding more desks and chairs to each existing office to accommodate more employees. Because this process does not require moving any data, the configuration change takes effect in seconds. When a sudden traffic spike hits, administrators can adjust the tenant’s resource specifications at any time — even during peak business hours — quickly increasing the tenant’s processing capacity.

But where do the extra CPU and memory come from?

This scaling mechanism assumes that the physical nodes have unallocated, schedulable resources (which administrators can monitor in real time via system views). The cluster does not create additional resources on its own. Instead, it reallocates available physical resources within the existing cluster to the target tenant. This spare capacity typically comes from two sources:

Reserved resources on each node: When a cluster is deployed, not all physical resources on each machine are assigned to tenants. Some CPU and memory are usually left unallocated for emergency scaling.
Idle resources from tenants under lighter load: In OceanBase’s multi-tenant architecture, workloads from different business lines often reach their busiest periods at different times. For example, consumer-facing services may see heavier traffic in the evening, while internal enterprise systems are usually busier during working hours. Administrators can temporarily allocate idle resources from tenants that are currently under lighter load to a tenant experiencing a sudden traffic surge. This time-based resource reuse improves overall resource utilization.

Does this online resource adjustment affect other tenants running in the same cluster? It does not, as long as the cluster has sufficient overall resources. Increasing the quota for Tenant A simply assigns previously unallocated resources to Tenant A’s resource pool. The existing quotas for Tenant B remain unchanged, and its services continue running without disruption. (We will explore OceanBase’s tenant resource isolation mechanisms in an upcoming article in our flexibility series.)

Note: Tenant-level elastic scaling is still limited by the physical resources available on a single node. When an “office” has no room for additional workstations and the node has no schedulable resources left, resources must be added at the infrastructure layer. This can be done in two ways. The first is cluster-level vertical scaling, such as upgrading to a larger cloud cluster specification or allocating more machine resources to the OceanBase process in an on-premises deployment. The second is horizontal scaling, such as adding physical nodes. We will cover cluster-level vertical and horizontal scaling in a later post.

Why Scaling Is Fast and Transparent

Traditional database scaling is slow and risky because it is essentially a physical-layer operation. In MySQL, for example, scaling requires provisioning a higher-specification server as a standby node, waiting hours for data replication to complete, and then performing a primary-standby switchover, which can briefly interrupt active client connections.

In contrast, OceanBase’s tenant-level scaling is essentially a logical change in resource scheduling. When adjusting a unit config, the system only needs to perform basic resource availability checks and update the quota online.

This process involves no machine replacement, no data migration, and no primary-standby switchover, so the entire operation completes in seconds. Existing database connections remain active, and applications require no code modifications or schema changes.

Because OceanBase avoids time-consuming physical operations, operations teams can scale resources up or down at any time of day without waiting for a late-night maintenance window.

Case Study: How POP MART Handles Launch-Day Traffic Spikes

How does scaling that takes effect in seconds hold up under extreme, real-world workloads? POP MART’s online blind box business, where users purchase randomized collectible figures, provides a clear example.

In the past, whenever POP MART released a popular new product, the platform would see nearly one million concurrent users. To handle these sudden traffic spikes, the operations team had to pre-provision expensive, oversized database instances late the night before the launch, and then stay up late again after the rush to scale them back down. This approach not only wasted resources but also placed heavy pressure on the operations team, especially because of the frequent late-night changes.

After migrating to OceanBase and consolidating dozens of isolated database instances into three multi-tenant clusters, their operational model changed completely:

Now, on product launch days, administrators simply scale up tenant resource quotas online during the day and scale them back down with a single click once the peak passes. According to public data, their database scaling time was cut by 90%. Without any application-level modifications, the system achieved 99.999% service continuity during high-concurrency product launches. For most medium-scale traffic fluctuations, the team can now easily handle the load simply by “adding workstations” to the existing tenants.

Summary

OceanBase’s tenant-level elastic scaling shifts database capacity management from “late-night maintenance with service risk” to “anytime online adjustment.” With no hardware replacement, no failover, and no application changes, OceanBase enables enterprises to adjust capacity safely during business hours and respond to traffic volatility with greater confidence. This allows engineering teams to focus on business innovation rather than exhausting infrastructure operations.

As workloads continue to grow, tenant-level scaling may eventually reach the physical limits of a single machine. At that point, higher-level scaling capabilities become necessary, such as cluster-level horizontal scaling and machine specification upgrades. We will explore these topics in future articles.

No Manual Sharding: How OceanBase Builds Scalability into Its Database Kernel

Charles Wu — Thu, 30 Jul 2026 14:59:00 +0000

How to Eliminate Manual Database Sharding and Scale Writes Without Application Complexity

As data volumes and workloads grow, engineers almost inevitably face the same difficult question: what happens when a single-node database can no longer keep up?

For many years, the industry’s standard answer has been horizontal scaling through database sharding. A large table is split into multiple pieces and distributed across independent database instances. A middleware layer is then introduced to process SQL requests from the application, route each request to the target shard based on predefined sharding rules, aggregate results from multiple shards when necessary, and return the final result to the application.

This approach works. But as systems continue to grow, the development and operational effort involved in sharding can gradually become a heavy burden.

The Pros and Cons of Database Sharding

The idea behind sharding is straightforward: it allows a system to grow beyond the storage and throughput limits of a single-node database. However, because this approach moves scalability logic outside the database rather than building it into the database itself, its costs grow over time. It pushes distributed-system complexity onto the application layer, middleware, and DBAs.

On the development side, SQL design becomes tightly coupled with how data is distributed. If a query does not include the sharding key, the middleware has no choice but to scan all database shards, which leads to poor performance. When updates span multiple shards, data consistency can also become harder to guarantee in complex scenarios.

The operational side is no easier. Whenever data volume grows rapidly and the system needs to scale out, the process becomes a complex engineering project: redesigning the sharding strategy, building custom dual-write migration tools, and carefully validating migrated data. Even more challenging, as the business keeps growing, the number of shards continues to expand. Once the number of shards reaches the hundreds, even small architectural changes can bring extremely high management costs.

Can Cloud-Native Databases Put an End to Sharding?

When looking for alternatives, many companies turn to cloud-native databases such as Amazon Aurora.

Aurora adopts an architecture that separates compute from storage, relying on distributed shared storage underneath. This removes the storage capacity ceiling of a single machine. Read requests can also be scaled horizontally by adding read replicas.

However, many shared-storage cloud-native relational databases still face a fundamental architectural constraint: writes are typically handled by a single primary node, or Writer. As a result, the Writer node can easily come under heavy load. For example, when executing a mixed SQL statement such as INSERT INTO ... SELECT ..., which includes both complex reads and writes, the entire operation must be executed on the Writer node to ensure data consistency. This can make the primary node a performance bottleneck.

When workloads grow rapidly and the write capacity of a single node reaches its limit, users usually have to upgrade to a larger instance type, often with a restart or a brief service interruption. This problem is even more pronounced in write-intensive scenarios such as social interactions, gaming, and live-streaming e-commerce. If the largest single Writer instance still cannot handle write spikes, users have little choice but to split data across independent clusters and reintroduce database sharding.

In short, cloud-native architectures based on shared storage solve the problem of storage capacity, but they do not fundamentally solve the problem of write scalability. In this sense, they are closer to an enhanced primary-standby architecture than to a fully distributed write-scalable database.

OceanBase: Hiding Distributed Complexity in the Database Kernel

OceanBase takes a different path: a shared-nothing, native distributed architecture. Each node in the cluster has its own CPU, memory, and local storage resources, rather than relying on a central shared-storage layer. This architecture eliminates the single-writer bottleneck found in shared-storage systems, so manual sharding is no longer required.

For developers and DBAs, OceanBase presents itself as a single logical database. So how does it distribute data internally while preserving the experience of a single-node database?

1. Storing One Large Table Across Multiple Machines

In OceanBase, even if a large table contains tens of billions of rows, the system can split the data into multiple partitions based on partitioning rules and place those partitions across multiple commodity servers. At the logical layer, however, it remains a single complete table. Application developers generally no longer need to manually split tables or maintain complex sharding logic.

2. After Data Is Distributed, How Does a Query Find the Right Node?

Once data is distributed, where should a request be sent? No application-layer code needs to be modified. OceanBase Database Proxy (ODP, also known as OBProxy) handles this automatically. It parses incoming SQL predicates, locates the target partition and node based on the partitioning key, and forwards the request accordingly.

From the application’s perspective, connecting to OceanBase is no different from connecting to a single-node MySQL database. There is no need to rewrite routing logic.

3. What If the SQL Query Does Not Include the Partitioning Key?

One of the biggest concerns in sharded architectures is queries that do not include the sharding key. These queries usually cause the middleware to scan all shards, significantly degrading performance.

This is a common challenge for any distributed partitioning architecture. OceanBase addresses it through built-in global indexes. A global index does not have to follow the primary table’s partitioning scheme, so indexes can be created on columns other than the partitioning key. Even if a SQL query does not include the partitioning key, the system can use a global index to narrow the query down to a small number of target nodes, avoiding inefficient full-shard scans.

4. Can ACID Still Be Guaranteed for Cross-Node Reads and Writes?

In traditional sharded architectures, if a business operation such as a transfer or an order update involves data distributed across different nodes, the application layer often needs to introduce distributed transaction middleware as a safeguard. This not only increases architectural complexity but also makes data inconsistency more likely in exceptional scenarios.

OceanBase builds consistency guarantees for cross-node transactions directly into the database kernel. Regardless of how many nodes the data spans, each transaction either commits completely or rolls back completely. Full atomicity, consistency, isolation, and durability (ACID) semantics are handled by the database itself, without requiring additional processing at the application layer. OceanBase also uses the Multi-Paxos protocol to ensure strong consistency across replicas.

OceanBase’s cross-node transaction guarantees can be understood in two layers.

The first layer is transaction-level coordination, which guarantees atomicity. When a transfer or order operation involves multiple partitions, the system first asks the relevant nodes to enter a “prepare to commit” state. The transaction is committed only after all participants confirm success. If any participant fails, the entire transaction is rolled back. By using an optimized two-phase commit protocol, OceanBase prevents partial commits, where one part of the operation succeeds while another fails.
The second layer is replica-level consistency, based on a majority mechanism. Each piece of data usually has multiple replicas stored across different nodes. OceanBase uses the Multi-Paxos majority consensus mechanism: a data change does not need confirmation from every replica; it can be considered committed once a majority of replicas have persisted it. Mathematically, any two majorities must overlap in at least one node — just as two groups that each include more than half of all members must share at least one person. This avoids split-brain behavior, where two isolated groups of nodes both believe they can commit changes independently. As a result, even when a minority of nodes fail or become disconnected, the system can continue serving requests as long as a majority remains available, while preserving strong consistency and durability.

One real-world example comes from transaction reconciliation at a large live-streaming platform that uses OceanBase. In the past, the platform used a traditional database sharding solution. The middleware had limited support for cross-database consistency and transaction atomicity. As a result, the system often encountered data inconsistencies in complex or abnormal situations — for example, refunds that were not issued or inaccurate debit amounts — which in turn caused financial losses. These are precisely the kinds of consistency issues that OceanBase’s native distributed transactions are designed to address at the kernel level.

5. Will Data Distribution Become Unbalanced as Data Grows or Nodes Are Added and Removed?

Automatic rebalancing is one of the features that significantly reduces the operational burden on database administrators (DBAs). When the cluster is nearing capacity and new nodes are added, OceanBase performs online rebalancing: it smoothly migrates data partitions to the new nodes and automatically rebalances read and write traffic across the cluster.

The entire process requires no manual intervention, no routing-rule changes, and no downtime for data migration. As a result, horizontal scaling becomes transparent to applications.

Together, these five mechanisms — transparent partitioning, automatic routing, global indexes, native distributed transactions, and automatic rebalancing — form the foundation that allows OceanBase to eliminate the need for manual sharding. These capabilities are built into the database kernel and remain transparent to applications.

Architectural Comparison of Three Approaches

OceanBase in Production: Scaling a Large-Scale Social and Live-Streaming Platform

What do these architectural differences mean in real-world business scenarios? A good example is Inkeverse, an OceanBase customer that runs a large-scale social and live-streaming platform.

Like many internet companies, Inkeverse built most of its systems on cloud infrastructure and relied on a managed relational database service with a primary-standby architecture. As the company grew and expanded into overseas markets, the team began to run into two major limitations.

The first was the rising cost of scaling reads. Social applications generate massive amounts of data and place heavy pressure on the database with highly concurrent queries. To keep the primary database from being overwhelmed, the team had to provision additional database instances as read replicas. These read replicas became a major line item on the company’s monthly cloud bill.

The second was delayed service response during scaling operations. Live-streaming traffic can change rapidly, requiring frequent scale-out and scale-in operations. In overseas scenarios where Inkeverse used traditional cloud databases, each scaling operation often introduced more than 10 seconds of response delay — an unacceptable hit to user experience for real-time services such as live streaming.

To overcome these limitations, the team migrated its core systems to OceanBase. This architectural shift directly addressed both problems.

Read/Write-Capable Nodes and High Compression Cut Database Costs by 40% to 50%

Thanks to OceanBase’s distributed architecture, data replicas are spread across nodes for high availability, and nodes can process both reads and writes for the data they serve. When business volume increases, the team can add nodes to improve overall read and write throughput, without provisioning dedicated read replicas.

Together with OceanBase’s data compression technology, this combination of architectural redundancy reduction and storage optimization reduced overall database costs by 40% to 50%.

True Horizontal Scaling with Smooth, Application-Transparent Expansion

When facing sudden traffic spikes, the team can now simply add nodes horizontally. OceanBase uses its distributed mechanisms to smoothly migrate data partitions and rebalance request traffic across the expanded cluster.

Because there is no longer a need to restart or upgrade a single primary database as in the past, scaling delays of more than 10 seconds have become a thing of the past. The entire scaling process is smooth, and the live-streaming application remains unaware of the underlying changes.

Summary

Database sharding “outsources” distributed complexity. In the short term, it gives the system room to grow, but in the long run, it increases operational burden and architectural complexity. Cloud-native architectures greatly ease storage constraints, but many remain limited by the single-writer bottleneck and the high cost of scaling.

OceanBase takes a different path: it handles distributed complexity inside the database kernel while presenting a simple logical database to applications.

A native distributed architecture without manual sharding is the foundation of OceanBase’s elastic scaling capabilities. Application developers can focus on business logic, while DBAs no longer need to worry about data migration or read/write bottlenecks. Built on this foundation, OceanBase’s tenant-level dynamic resource isolation and smooth node scale-out and scale-in can remain transparent to applications and help achieve zero downtime for the business. Future articles in this series will explore these elastic capabilities in more detail.

Start Small, Scale Big: Inside OceanBase’s Distributed Architecture

Charles Wu — Tue, 28 Jul 2026 14:59:00 +0000

How to Scale From Standalone to Distributed Database Without Rebuilding Your Architecture

For many enterprises, database scaling is not just a performance problem. It is an architectural continuity problem.

OceanBase addresses this with an integrated standalone-distributed architecture. Businesses can start with a small deployment that behaves much like a standalone database, then scale out to a distributed system as they grow. This helps reduce repeated migrations, sharding complexity, and the operational burden of maintaining multiple database systems.

Key Takeaways

Lower early architecture risk. Teams can start with a small deployment and scale out later without switching database architectures.
More controllable distributed overhead. With dynamic log stream, the number of log streams scales mainly with the number of nodes, rather than simply with data shards.
A smoother long-term growth path. Teams can move from small-scale deployment to distributed scale-out while reducing migration, sharding, and multi-system maintenance work.

Database Scaling Often Arrives Earlier Than Expected

Many companies start with MySQL. It is simple, open source, mature, and familiar to most engineering teams. For early-stage businesses, a standalone MySQL database is often enough.

As the business grows, database pressure builds quickly. Order volume increases, tables grow, and queries slow down. Teams often add read-write splitting, caching, and eventually sharding. Each step solves an immediate problem, but also adds application changes, migration risk, and operational complexity.

That is why many CTOs eventually ask: Why should every new stage of business growth require another painful database rebuild?

The challenge is not only how to scale, but how to scale without repeatedly changing the database architecture.

Existing Scaling Paths Still Involve Trade-offs

Traditional database scaling usually follows one of two paths:

Open-source path: MySQL standalone → MySQL sharding → NewSQL distributed database
Commercial path: Oracle standalone → Oracle RAC → NewSQL distributed database

Both paths can solve problems at certain stages, but they also introduce long-term challenges.

Standalone databases and shared-storage clusters have scaling limits. Hardware upgrades can relieve pressure, but they do not truly provide linear scale-out for massive data volumes and high concurrency.

Sharding breaks through the capacity limit of a single machine, but it pushes complexity to applications and operations. Cross-shard queries, distributed transactions, data migration, scaling, and failure handling all become more difficult.

Cloud-native databases improve elasticity and high availability. However, in many primary-replica architectures, write throughput may still be limited by the primary node.

NewSQL databases provide horizontal scalability, but they may also involve trade-offs in small-scale cost, standalone performance, operational complexity, and ecosystem maturity.

Enterprises do not need another architectural bet. They need a database path that can handle current workloads without limiting future scale-out.

OceanBase’s Approach: One Architecture for Standalone and Distributed Deployment

OceanBase’s answer is an integrated standalone-distributed approach — one architecture that supports both standalone and distributed deployment. This capability was introduced in OceanBase 4.0 and has since become the foundation of the product’s scaling model.

The idea is simple: Start small, scale big.

In the early stage, OceanBase can run as a small standalone deployment, supporting business workloads at a lower cost while keeping performance and operations close to those of a traditional standalone database.

As the business grows, the same OceanBase system can scale out into a distributed cluster to support larger data volumes, higher concurrency, and stronger high availability.

The key point is that the deployment model can change without requiring a different database architecture. Teams can start small and scale out as needed within the same system.

One of the core technologies behind this capability is dynamic log stream.

Dynamic Log Stream: Combining Standalone Performance with Distributed Capability

A key part of this architecture is the dynamic log stream mechanism.

In many distributed database designs, data is divided into shards. As the number of shards increases, the system may need to manage more log streams. This can make distributed overhead grow with the number of shards, even when the actual deployment size remains small.

The scaling path may look like this:

More data
  → More shards
  → More log streams
  → Higher distributed overhead

OceanBase takes a different approach: log streams scale mainly with the number of nodes, rather than simply with the number of shards. As a result, distributed overhead is more closely related to deployment scale, instead of growing directly with the number of data shards.

In a single-tenant scenario, this can be illustrated as follows:

Note: The table above is based on a single-tenant scenario. In multi-tenant scenarios, the total number of log streams is also affected by the number of tenants and resource distribution.

This design brings three key benefits:

Flexible partition placement: Data partitions can be dynamically bound to different log streams, supporting flexible migration.
Lightweight standalone deployment: In standalone deployment, there is only one log stream, and the transaction processing path is similar to that of a classic standalone database.
More controllable scale-out: When the system scales horizontally, log streams increase mainly with the number of nodes, keeping distributed overhead more controllable.

This is what allows OceanBase to stay lightweight in small deployments while preserving a path to large-scale distributed expansion.

Optimizing for the Common Path

OceanBase is also designed around a practical observation: in many business systems, most database operations are local to a single user, account, order, or tenant. Only a smaller portion requires cross-node coordination, such as cross-account transactions or global analytics.

Based on this observation, OceanBase optimizes for the common path:

For common single-node operations, OceanBase minimizes unnecessary distributed overhead so that performance remains close to standalone databases.
For cross-node operations, OceanBase uses mechanisms such as two-phase commit, parallel execution, and asynchronous processing to improve execution efficiency.

This helps OceanBase keep common operations efficient while still supporting distributed execution when needed.

Business Value and Best-Fit Scenarios

For enterprises, OceanBase’s integrated standalone-distributed architecture delivers value beyond raw performance — it lowers long-term architecture cost and reduces evolution risk.

In practice, this means:

Lower total cost of ownership through compression, automatic load balancing, automatic failover, and multi-tenant isolation.
A smoother scale-out path from small deployments to large distributed clusters.
High availability for critical workloads, supported by Paxos-based three-replica strong synchronization, RPO = 0, and fast failover.
Fewer systems to maintain by supporting OLTP, real-time OLAP, Key-Value, JSON, and GIS use cases in one database.

This makes OceanBase especially relevant for startups, fast-growing companies, large enterprises, and financial institutions that need to scale without repeated migrations, manual sharding, or deep cloud lock-in.

Adoption Path: Start with a Small Validation

Enterprises do not need to start with a large distributed deployment. A gradual adoption path is often more practical:

Standalone validation: Start with a non-critical workload and test SQL compatibility, performance, and operations.
Small distributed deployment: Deploy a three-node cluster and validate high availability, failover, backup and recovery, and monitoring.
Scale on demand: Expand gradually as workloads grow and promote best practices across more systems.

This lets teams validate, deploy, and scale within the same database system.

Database selection should not force enterprises to rebuild their architecture at every stage. It should provide a foundation that evolves with the business.

That is the goal of OceanBase’s standalone-distributed architecture:

Start small, scale big — without rebuilding the database architecture at every stage.

OceanBase Real-Time Analytics: Ecosystem Compatibility and Supporting Capabilities

Charles Wu — Fri, 24 Jul 2026 14:59:00 +0000

How to Unify Your Analytics Ecosystem Without Rebuilding Your Data Pipeline From Scratch

1. Introduction

When it comes to real-time analytics, people tend to focus first on how fast the engine runs. But in an enterprise setting, raw speed is only one piece of the puzzle. For an analytical database to truly land in production, it must integrate smoothly with the data ecosystem the enterprise has already built. Data is typically scattered across file systems, message queues, and existing operational databases, while analytical results need to flow back into reports, dashboards, and downstream applications. If every integration point requires custom adapter code to develop and maintain, the adoption cost quickly becomes prohibitive.

The traditional approach is to stitch together multiple specialized components into an analytics pipeline. Data might first go into Kafka for buffering, then through Flink for stream processing, land in Hive for historical storage, and finally be loaded into ClickHouse for querying. Each layer means another round of synchronization and transformation. The longer the pipeline, the harder it is to control end-to-end latency, and the greater the consistency risk between systems. More practically, development teams spend enormous effort on data movement and pipeline maintenance, leaving less time for actual business logic.

Therefore, real-time analytics capabilities require a complete set of supporting features, centered on three core concerns: how data comes in, how results get out after processing, and how the entire process is managed. These three concerns span the full pipeline from data ingestion through processing to consumption. OceanBase’s approach is to use a unified engine to interface with the existing ecosystem, minimizing intermediate layers rather than asking enterprises to add even more components. At the same time, OceanBase supports in-database data warehouse layering, allowing data to be progressively refined from ODS (Operational Data Store) to DWS (Data Warehouse Summary) to ADS (Application Data Store) within a single database, with Flink handling inter-layer data flow to further reduce dependence on external systems. The following sections walk through these capabilities in order of data flow.

2. Data Integration: Ingestion and Flow of Multi-Source Data

Getting data into the database is the first step of analytics. Data from different sources, at different scales, and with different freshness requirements calls for different ingestion methods. OceanBase provides capabilities across three dimensions: batch import, external tables, and real-time synchronization.

2.1 Batch Import

For scenarios that require loading large volumes of data in one shot, OceanBase provides Direct Load. Data bypasses the SQL parsing and transaction processing layers and directly generates the underlying storage files (SSTables), skipping the overhead of per-row transaction commits and delivering significantly higher efficiency for bulk writes.

This approach is well suited for initial database seeding, historical data migration, monthly archiving, and ETL batch loading. These scenarios share common characteristics: large data volumes and low requirements for per-row transaction semantics, which play directly to Direct Load’s strengths. Compared to row-by-row inserts, Direct Load processes a batch of data as a whole, reducing repeated transaction commits and log writes. The larger the data volume, the more pronounced the time savings.

Complementing Direct Load, the OBLOADER tool handles batch import and export of structured data, managing the mapping between data file formats and database table structures to make bulk loading operations simpler and more controllable.

2.2 External Tables and Data Lake Integration

Much analytical data does not reside inside the database but is stored as files in object storage or distributed file systems. The core idea behind OceanBase’s external table capability is to avoid moving data altogether — instead, it defines mappings to external files directly within the database.

In terms of file formats, external tables support the universal CSV text format as well as Parquet and ORC columnar storage formats. Columnar formats organize data by column, achieving high compression ratios and reading only the columns needed during scans, making them ideal for analytical scenarios. For storage systems, external tables are compatible with S3, OSS, and other object storage services, as well as HDFS and ODPS. For metadata management, external tables can connect to Hive Metastore (HMS) and ODPS Catalog to form a unified metadata view, and support table formats such as Hive and Iceberg.

Building on this, External Catalog can register database and table information from multiple external data sources into OceanBase in a unified manner, allowing external data to be queried directly with standard SQL without the need to manually create external tables one by one. Data already sitting in a data lake does not need to be copied into the database again, reducing both storage redundancy and the latency introduced by data movement.

2.3 Real-Time Synchronization and Data Collection

Analytics scenarios often demand data that is as fresh as possible, requiring continuous synchronization of changes from operational databases and timely ingestion of various log and event data. OceanBase supports integration with a wide range of synchronization and collection tools, covering everything from log collection to database synchronization.

For log collection, Flume and Logstash collect log data from application servers such as Nginx and Apache Tomcat, performing simple parsing and formatting before pushing data into OceanBase. This type of log data is high-volume and high-frequency, typically used for behavioral analysis, operations monitoring, and similar scenarios.

For database synchronization, OMS (OceanBase Migration Service) is OceanBase’s built-in migration and synchronization tool, supporting both full migration and incremental sync across migrations from MySQL, Oracle, PostgreSQL, PolarDB, and other databases into OceanBase. DataX is Alibaba’s open-source heterogeneous data source synchronization tool, supporting batch data synchronization across multiple database types, suitable for periodic full or incremental extraction tasks.

On the data processing side, dbt handles SQL-level ETL transformation logic, while DataWorks provides data integration and development platform capabilities on Alibaba Cloud. Kafka, as a message queue, handles buffering and distribution of streaming data and is often used in conjunction with the synchronization tools mentioned above.

The fact that these tools can connect directly means that most existing enterprise data pipelines can be reused — there is no need to build an entirely new synchronization pipeline just to integrate with OceanBase.

2.4 Flink Connector Ecosystem

In real-time data integration scenarios, Apache Flink is one of the most widely used stream processing engines today. OceanBase provides multiple official Flink Connectors covering the full pipeline from data writing to change data capture, adapting to different performance requirements and use cases.

The JDBC Connector reads and writes OceanBase through the standard JDBC protocol, supporting both MySQL and Oracle compatibility modes. It includes built-in Druid connection pool management and batch write optimization (buffering plus batch commit), along with retry mechanisms. It is suited for common scenarios such as real-time data synchronization, CDC incremental writes, and writing stream processing results to the database, making it the most versatile choice.

The DirectLoad Connector is based on the Direct Load API. Data bypasses the SQL parsing layer and writes directly to the underlying storage files. Write throughput is far higher than the JDBC approach, making it ideal for high-throughput scenarios such as large-scale data import, ETL batch processing, and historical data migration.

The OBKV HBase Connector writes to OceanBase via the OBKV HBase API, suited for HBase-compatible scenarios and wide-table storage requirements.

Flink CDC (Source) reads change data from OceanBase, supporting both MySQL and Oracle compatibility modes, and synchronizes incremental data to downstream systems. It is suited for scenarios such as syncing data out of OceanBase and data lake ingestion. At the same time, Flink CDC can also listen to change logs from external databases (MySQL, Oracle, PostgreSQL, etc.) and write incremental data into OceanBase in real time, making it one of the core channels for data ingestion.

In addition, the Flink Connector JDBC (community edition) and Apache SeaTunnel can also be used to process OceanBase data through Flink, providing more options for existing technology stacks.

In an in-database data warehouse layering architecture, Flink also takes on the role of inter-layer processing. Data flows from ODS through cleansing into DWS, then aggregates into ADS, with real-time flow between each layer handled by Flink. This way, OceanBase provides unified storage while Flink handles inter-layer computation, and together they form a lightweight real-time data warehouse solution that reduces dependence on multiple external systems.

A quick selection guide: use the JDBC Connector for regular writes; choose DirectLoad Connector for bulk data loading; use OBKV HBase Connector for HBase compatibility needs; use Flink CDC when you need to read changes from OceanBase.

2.5 Spark Connector Ecosystem

If Flink’s strength lies in real-time stream processing, Spark excels at large-scale batch processing and interactive analytics. The two are complementary: Flink handles continuous incremental computation, while Spark handles periodic full scans, complex ETL, and ad-hoc queries. OceanBase provides connectivity for both, allowing users to flexibly choose the compute engine based on the scenario.

Starting from v1.1, the OceanBase Spark Connector is deeply integrated with Spark Catalog, which is the currently recommended usage approach. Its key features include:

Zero-code access with full SQL interaction. Spark-SQL can directly read, write, and manage databases and tables in OceanBase, supporting common operations such as SHOW DATABASES, SHOW TABLES, and CTAS without writing additional code.
Adaptive partitioning and parallel reads. The Connector automatically recognizes OceanBase partition table structures and splits read tasks based on partition information, enabling multi-partition parallel scanning that fully leverages the distributed computing power of the Spark cluster.
Predicate pushdown. Filter conditions are pushed down to OceanBase for execution, reducing the amount of data transferred over the network and improving query efficiency.
Automatic schema inference. No need to manually define table structures — the Connector automatically discovers column information and type mappings for OceanBase tables.
Flexible write modes. Supports both JDBC writes and Direct Load writes. JDBC is suited for regular write scenarios, while Direct Load is designed for bulk data loading, bypassing the SQL layer and writing directly to storage for higher throughput.
Upsert support for primary key tables. In MySQL mode, this is based on the INSERT … ON DUPLICATE KEY UPDATE syntax; in Oracle mode, it is based on the MERGE syntax.
Supports both MySQL and Oracle compatibility modes simultaneously.

Typical use cases: leveraging Spark’s distributed computing power for batch analytics and ETL processing on massive data in OceanBase; syncing data from Hive or data lakes into OceanBase via Spark; completing heavy computation tasks in the Spark cluster and efficiently writing results back to OceanBase via Direct Load.

In addition to the Catalog Connector, OceanBase also provides the OBKV HBase Connector for Spark for data access in HBase-compatible scenarios. The legacy Spark Connector (for Spark 2.x) is being gradually retired; new projects are recommended to use the Catalog approach.

3. SQL Compatibility: Lowering the Barrier to Entry

Once data is in the database, how it gets queried is another critical concern. If the query syntax differs significantly from the enterprise’s existing systems, application modification and staff training both incur costs. OceanBase has invested heavily in SQL compatibility, with the goal of enabling existing applications and tools to connect with minimal changes.

3.1 MySQL Compatibility

OceanBase’s MySQL compatibility operates on two levels. At the protocol level, OceanBase communicates using the MySQL protocol, so existing MySQL clients and drivers — such as JDBC and Python connectors — can connect directly without replacement. At the syntax level, DDL, DML, and common functions are largely consistent.

The implications for upstream and downstream systems are straightforward: BI tools, ORM frameworks, and applications originally built against MySQL typically do not need code changes when switching to OceanBase. For teams already built on the MySQL ecosystem, this significantly reduces migration costs.

3.2 Oracle Compatibility

For systems that have traditionally used Oracle, OceanBase provides Oracle compatibility. Perhaps the most practically significant aspect is compatibility with PL/SQL stored procedures. Many traditional enterprises have encapsulated core business logic in Oracle stored procedures. If the database is not compatible, it means rewriting all that business logic with a new technology stack — high cost, long cycle, and significant risk.

OceanBase’s Oracle compatibility allows these stored procedures to be migrated almost as-is. SQL compatibility reaches 99.9%, application modification costs are very low, and the majority of existing stored procedures and business logic can be migrated smoothly.

3.3 Standard SQL Analytical Capabilities

Analytical queries are often more complex than online business queries, commonly involving large table joins, multi-level nested subqueries, window functions (such as ranking and sliding averages), and recursive CTE queries. OceanBase covers these commonly used standard SQL syntax features for analytics, enabling analysts to express complex statistical logic in familiar ways.

Building on this, OceanBase also provides materialized view capabilities. Materialized views pre-compute and store the results of complex queries, so subsequent queries can read directly from the materialized view instead of recomputing every time. OceanBase supports near-real-time and real-time materialized view refresh, as well as automatic SQL rewriting — when a query can match a materialized view, it is automatically routed to it. For high-frequency, repeated analytical queries, this can dramatically reduce response times.

By contrast, some big data platforms have limited support for standard SQL. When encountering complex joins or window functions, developers are often forced to use low-level APIs or hand-written UDFs, which raises the barrier and increases ongoing maintenance costs.

4. Database Management and Operations Ecosystem

Once data can flow in and be queried, the next requirement is ensuring the entire system can be managed stably. Management and operations capabilities determine the system’s long-term maintainability. OceanBase integrates with the common operations ecosystem across three dimensions: scheduling and orchestration, observability, and resource management.

4.1 Scheduling and Orchestration

A complete analytics task is rarely accomplished in a single step. It typically involves multiple stages — data extraction, cleansing and transformation, aggregation computation, and result writing — with dependencies between steps that need to be triggered in order or based on conditions. The role of a scheduling platform is to define these steps as a DAG (Directed Acyclic Graph) and execute them automatically according to a schedule.

OceanBase supports integration with mainstream scheduling and data flow management tools such as Airflow, DolphinScheduler, and NiFi. Airflow and DolphinScheduler excel at defining complex task dependencies and timed scheduling policies. Apache NiFi is a data flow management tool that specializes in visually defining data collection, routing, and transformation processes, suited for scenarios that require flexible orchestration of data flows. Once integrated with these platforms, analytics task orchestration can directly reuse the enterprise’s existing scheduling infrastructure without developing a separate scheduling logic layer.

4.2 Observability

Operations teams need to understand the system’s running state at all times, especially in multi-tenant environments where different business lines share a cluster and need to independently monitor their resource usage and query performance. OceanBase integrates with the Prometheus and Grafana monitoring stack, with Prometheus collecting metrics and Grafana handling visualization and alert configuration.

In addition to the open-source approach, OceanBase also supports integration with commercial APM platforms such as Datadog, meeting the needs of enterprises already using Datadog for unified monitoring and avoiding the need to maintain a separate monitoring system.

Monitoring coverage includes node CPU, memory, and disk I/O; query latency distributions; slow query statistics; and tenant-level resource consumption. With these metrics, issues can be quickly pinpointed when they arise, and the data also supports capacity planning and performance tuning on an ongoing basis.

4.3 Multi-Tenant Resource Management

When online transactions and analytics tasks run on the same cluster, resource contention is a common problem. If analytics queries consume too much CPU or I/O, online business may be affected. OceanBase provides multiple levels of resource isolation to handle this.

Tenants are logically independent database instances with independent resource quotas that do not interfere with each other. Foreground and background tasks are also isolated — background operations such as compaction and merging do not disrupt foreground user queries. At a finer granularity, OceanBase supports user-level and SQL-level resource groups, allowing specific users or specific SQL statements to be assigned dedicated resource groups for fine-grained control. For mixed TP/AP workloads, OceanBase supports strong isolation between TP/AP replicas — analytics queries can be routed to read-only replicas, physically separated from the primary replicas handling online transactions.

This way, multiple workload types can safely coexist on the same physical cluster without interfering with each other.

5. BI and Data Visualization

Analytical results ultimately need to be seen and used by business users, so the ability to connect seamlessly with BI tools directly determines the practical value of analytics capabilities. OceanBase is compatible with mainstream platforms including Superset, Grafana, Tableau, Power BI, QuickBI, and GuanData, allowing analytical results to be displayed directly in these tools. Superset and Grafana are open-source options suited for flexible customization; Tableau and Power BI are enterprise-grade business intelligence tools with rich capabilities in data modeling and interactive exploration.

The significance of self-service analytics is that business users can drag and drop fields, set filter conditions, and build reports on their own, without submitting every request to the data development team and waiting for scheduling. This shortens the cycle from asking a question to seeing the data, enabling people who understand the business to validate ideas directly, and extending analytics capabilities from the development team to the business team.

In terms of real-world results, one international retail brand previously used a combination of ADB, Deltalake, and ClickHouse, with different technology stacks for ETL and serving, resulting in lengthy integration cycles and difficult maintenance. After migrating to OceanBase, a single cluster provides both ETL and serving capabilities with MySQL compatibility, reducing overall database costs by more than 40%. Another example is a provincial mobile carrier that used external tables to read CSV, ORC, and other files directly from HDFS, then used Direct Load for real-time import, achieving a 69% overall performance improvement over the existing system.

6. AI/ML Integration

With the proliferation of large language models and AI applications, databases serve not only as the foundation for traditional analytics but are increasingly taking on data storage and retrieval needs for AI applications. Through standard interfaces and vector search capabilities, OceanBase can integrate with mainstream AI/ML ecosystem tools.

Python is the most widely used programming language in the AI/ML space. Because OceanBase is compatible with the MySQL protocol, developers can connect directly to OceanBase using common drivers such as pymysql and SQLAlchemy, performing data reads, writes, and analysis in Python environments without an additional adaptation layer.

In LLM application development, LangChain and LlamaIndex are the currently dominant frameworks. They support using OceanBase as a vector storage backend for storing text embeddings and providing similarity search capabilities. Combined with model services such as OpenAI, enterprises can build RAG (Retrieval-Augmented Generation) applications on OceanBase, enabling large models to retrieve relevant context from the database when answering questions, improving both accuracy and timeliness. OceanBase’s vector search capabilities allow structured data queries and semantic retrieval to be completed within the same database, eliminating the need to deploy a separate vector database for AI applications.

7. Data Governance

Data quality is the prerequisite for trustworthy analytical results. If source data has missing values, duplicates, or format anomalies, downstream analysis and decision-making will be affected. OceanBase is compatible with mainstream data quality governance tools, enabling enterprises to perform quality control at every stage of data ingestion, processing, and output.

Soda is a data quality monitoring tool that supports defining data quality rules (such as field non-null rates, numerical ranges, uniqueness constraints, etc.) and periodically checking data against these rules, alerting when anomalies are detected. Great Expectations is a data validation framework in the Python ecosystem, used to define and verify expected data characteristics, well suited for integration into ETL pipelines for automated validation. Data Profiler focuses on data profiling, helping understand data distributions, outliers, and statistical features, which is especially useful during initial data onboarding. These tools connect to OceanBase through standard database connections, allowing enterprises to embed data quality checks into existing data pipelines without building a separate governance system.

8. Summary

The core of ecosystem support is making data flow more smoothly, rather than forcing enterprises to adapt to the database. OceanBase’s approach is to use a unified engine to interface with the existing data ecosystem — from data ingestion, layered processing, query analytics, and visualization through to AI applications and data governance — minimizing the glue code and synchronization pipelines between systems, so that data can come in, be processed quickly, be seen clearly, and be managed effectively.

Compared to the traditional approach of stitching together multiple components, this reduces pipeline complexity and lowers the latency and consistency risks that come with overly long pipelines, making it easier for real-time analytics capabilities to truly land in enterprise production environments.

A Guide to AP Query Diagnosis and Tuning

Charles Wu — Wed, 22 Jul 2026 14:59:00 +0000

How to Diagnose Analytical Queries Systematically in Distributed AP Tuning

1. Why AP Queries Need a Dedicated Tuning Approach

In an HTAP architecture, TP queries and AP queries have fundamentally different execution profiles. TP queries typically touch a small amount of data, relying on primary keys or indexes for precise lookups and targeting millisecond-level response times. AP queries, by contrast, must scan large volumes of data, join multiple tables, and perform complex aggregations and subqueries. Their execution plans run deep, involve many operators, and depend heavily on parallel execution and columnar scans.

This difference dictates entirely different tuning strategies. When a TP query is slow, adding an index usually does the trick. AP query slowness rarely has such a simple fix — the problem may lie in join order, unexpanded subqueries, statistics skew, data skew, or a combination of these factors.

In a distributed database, AP query complexity escalates further. Data is spread across multiple nodes, and query execution requires multi-node coordination — a HASH JOIN may need to shuffle the driving table’s data across the network to all participating nodes, making network latency and bandwidth new bottleneck dimensions. Even more troublesome is uneven data distribution: if the join key values are heavily skewed, large amounts of data get routed to the same thread, leaving other threads idle while overall elapsed time is determined by the slowest thread. On top of that, EXCHANGE operators in distributed execution plans introduce additional overhead for serialization, transmission, and deserialization.

Traditional single-node database tuning experience does not fully apply to distributed AP scenarios. In a single-node environment, bottlenecks usually concentrate on CPU and disk I/O, with relatively clear optimization targets. In a distributed environment, network transfer, data redistribution strategies, degree-of-parallelism allocation, and load balancing across nodes all become additional considerations. An execution plan that performs well on a single node may degrade significantly in a distributed setting due to a poor shuffle strategy.

Therefore, AP query tuning requires a systematic approach: first understand the execution plan, locate the real bottleneck, filter out harmless noise, and then choose the right optimization technique for the root cause.

2. Common AP Query Problem Types

In practice, the vast majority of slow AP queries can be classified into the following categories:

Excessive scan volume: Full table scans lack effective skip mechanisms, or the wrong index is chosen. Fact tables in AP queries often contain hundreds of millions of rows; a single unnecessary full table scan may read tens of GB of data, with only a tiny fraction of rows being relevant. Columnar storage reduces read width, but the waste at the row level is still costly.
Suboptimal join algorithm or order: NLJ, HASH, or MERGE join algorithms are poorly chosen, or the join order causes intermediate result bloat. A typical star schema in AP scenarios involves joins of five to ten tables. The number of possible join orderings is enormous — once the optimizer picks wrong, intermediate results can balloon from millions to hundreds of millions of rows, dragging down every subsequent operator.
Unexpanded subqueries: Correlated subqueries degrade into SUBPLAN FILTER, re-executing once for every outer row. Nested subqueries are common in AP reports; once they cannot be expanded into joins, the subquery must execute as many times as there are rows in the outer table, turning linear complexity into quadratic.
Excessive aggregation and sort overhead: A sort-based aggregation implementation is chosen, which is expensive at large data volumes. AP query aggregation inputs typically range from millions to hundreds of millions of rows. A sort-based implementation must sort all input data, with a time complexity of O(N log N), far higher than the O(N) of hash aggregation.
Statistics skew: The optimizer’s row estimates deviate significantly from actuals, triggering a cascade of poor decisions. Multi-table joins in AP queries amplify estimation errors — a 3× error at the base table level can become 30× after two joins, ultimately warping the entire plan shape.
Parameter/type mismatch: Type inconsistency on both sides of a predicate causes implicit CAST, which invalidates indexes. AP queries involve many tables and columns; when SQL is assembled across systems, type alignment is easily overlooked. A single implicit conversion can turn an index scan into a full table scan.
Insufficient parallelism or PDML limitations: Too low a degree of parallelism fails to leverage multi-core advantages, or writes have not enabled Parallel DML. AP queries process large data volumes; setting parallelism too low artificially creates a bottleneck. Without PDML enabled, large batch writes can only proceed in a single thread.
Data skew and poor shuffle strategy: In distributed scenarios, uneven data distribution causes long-tail threads. When join keys contain hot values, HASH shuffle on those keys funnels large amounts of data to a few threads. Other threads finish and wait; overall elapsed time is determined by the slowest thread.
SQL Work Area memory spill: Sort or hash areas run out of memory, causing data to spill to disk. AP queries handle large data volumes; when memory cannot hold the data, spills are triggered, and performance can drop by one to two orders of magnitude.
Plan regression and stale SPM baselines: After data changes, a previously fixed baseline may no longer represent the optimal plan. AP scenarios see rapid data growth; a baseline bound a month ago may no longer suit the current data distribution.

These problems frequently appear in combination. The key to diagnosis is identifying the primary contributor from the execution plan.

3. Diagnostic Framework

Diagnostic Toolchain

OceanBase provides a three-tier diagnostic toolchain, ranging from quick triage to operator-level deep analysis:

SQL Audit is the lightest-weight entry point for investigation. The GV$OB_SQL_AUDIT view records execution summary information for every SQL statement. By sequentially checking RETRY_CNT, QUEUE_TIME, GET_PLAN_TIME, and EXECUTE_TIME, you can quickly determine whether the problem lies in the execution environment or the plan itself. The logic behind this check order is: first, check whether RETRY_CNT is greater than zero — if the statement was retried, it encountered environmental issues such as lock conflicts or leader switches, and the execution time includes retry overhead, so you cannot directly assess plan quality; next, check QUEUE_TIME — if queuing time dominates, the issue is resource contention rather than the plan; then check GET_PLAN_TIME — abnormally high values may indicate hard parse overhead or low Plan Cache hit rates; finally, check EXECUTE_TIME — only after confirming it is purely slow execution is it worth analyzing the execution plan in depth.

SELECT svr_ip, trace_id, query_sql, retry_cnt, queue_time, get_plan_time, execute_time
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE tenant_id = <tenant_id>
  AND request_time > time_to_usec(now() - INTERVAL 10 MINUTE)
ORDER BY execute_time DESC
LIMIT 20;

DBMS_XPLAN / Execution Plan Inspection is the critical bridge between identifying a problem and performing operator-level analysis. It reveals the join order, join algorithms, access paths, and distribution strategies chosen by the optimizer. The core purpose of this step is to determine whether the problem is at the plan level or the execution level: if the plan shape itself has obvious flaws (e.g., wrong join algorithm, full table scan instead of index scan), prioritize adjusting the plan; only if the plan is sound but execution is still slow should you drill down into per-thread operator-level execution data.

SQL_PLAN_MONITOR provides operator-level real-time monitoring. The GV$SQL_PLAN_MONITOR view records thread count, output rows, scanned rows, and execution time for each operator, enabling deep analysis of per-thread execution details. This view answers key questions: Which operator is the bottleneck — determined by comparing each operator's CPU TIME share; is parallel execution balanced — determined by comparing processed rows and elapsed time for the same operator across different threads to detect load skew; what is the I/O pattern — by examining each operator's I/O data, including read volume and spill volume, you can directly determine whether disk spill is occurring and which operator is spilling.

The typical usage sequence for diagnostic tools follows a three-step progression: first, use SQL Audit to confirm the problem and rule out environmental factors such as retries and queuing; second, use DBMS_XPLAN to examine the execution plan and determine whether the issue is at the plan level or the execution level; third, if the plan is sound but execution is still slow, use SQL_PLAN_MONITOR to drill into per-thread operator execution details and pinpoint bottleneck operators and skew issues.

Core Analysis Dimensions for Each Tool

Now that the toolchain and usage sequence are clear, the next question is which metrics to focus on when using these tools. Below is an overview of several core analysis dimensions; specific execution plan interpretation details are covered in subsequent sections:

CPU TIME for hotspot identification: Normalize each operator’s CPU TIME against the total time — operators with a high share are hotspots. Operators accounting for less than 10% can generally be ignored. However, high CPU TIME does not necessarily mean there is a problem; you must also consider the operator type to determine the root cause — if a TABLE SCAN operator has high CPU time and scans a large volume, it may indicate a missing index; if a HASH JOIN operator has high CPU time but reasonable input row counts, the data volume may simply be large, and there may not be much to optimize.

IO TIME for detecting memory spills: If a sort or hash operator’s IO TIME is abnormally high, it can almost certainly be attributed to insufficient SQL Work Area memory causing data to spill to disk. The rule of thumb is: when an operator’s IO TIME exceeds 50% of its total elapsed time, and the operator type is SORT or HASH JOIN, you should consider the possibility of a memory spill.

Scan-to-output ratio for detecting ineffective scans: For scan operators, if the scanned row count divided by the output row count exceeds 100, it means large amounts of data are being scanned and then filtered out — a strong signal for index optimization. A ratio below 10 is generally acceptable; between 10 and 100 is worth noting but not necessarily urgent; above 100 should be treated as a high-priority optimization target.

EST vs REAL deviation for diagnosing statistics issues: Compare the optimizer’s estimated row counts against actual row counts. Base table scan estimation deviations exceeding 5× require close attention; join result deviations of 2–10× are within the normal range. The reason base table deviations are more serious than join deviations is that the base table estimate is the input assumption for the entire plan — if the base table row count is estimated wrong, all downstream join cost estimates will be wrong as well, causing the optimizer to choose incorrect join orders and algorithms. Join result deviations, on the other hand, are typically the cumulative effect of multiple estimation errors; correcting any single one may not improve the overall plan.

Filtering false positives: The following signals can be safely skipped — high CPU on EXCHANGE/PX COORD operators is normal, as these operators coordinate data transfer between parallel threads; row count deviations on the right side of NLJ are cumulative results rather than statistics issues, since the right side is probed multiple times in the loop, and the displayed total row count is the sum across all probes; deviations with row counts below 100 have limited impact; deviations downstream of Runtime Filters are expected behavior, because Runtime Filters are dynamic filters that take effect at runtime, and the optimizer cannot predict their filtering effect. Attempting to optimize these signals without understanding the underlying mechanisms may introduce incorrect hints that interfere with plan selection.

4. Optimization Capabilities Overview

Once the diagnostic tools above have pinpointed the specific problem, different optimization techniques can be applied depending on the scenario. The following sections cover common problem scenarios.

Poor Join Order or Algorithm Selection

When diagnosis reveals join-related issues — such as HASH JOIN operator time share being too high, intermediate result row counts far exceeding expectations, or NLJ driving-side row counts being too large — optimization revolves around two levers: join order and join algorithm. The reason join order has such a massive impact is the bloat effect of intermediate results: if joining two tables produces an intermediate result much larger than expected, that bloat propagates to all subsequent join steps. For example, joining two large tables first and then filtering with a small dimension table can produce intermediate results reaching hundreds of millions of rows; filtering with the dimension table first and then joining may yield only millions. The LEADING hint controls join order, ensuring that tables with strong filtering power and small result sets participate in joins early, controlling intermediate result size at the source. Regarding join algorithms, HASH JOIN is suitable for equi-joins where both sides have large data volumes, with cost proportional to the build-side row count; NLJ is suitable for scenarios where the driving-side result set is small (typically under 10,000 rows) and the probed side has efficient indexes, with total cost equal to the driving row count multiplied by the per-probe cost. If the driving-side row count is misjudged, NLJ performance degrades sharply. Hints such as use_hash and use_nl can guide the selection. Additionally, Semi JOINs (IN/EXISTS) can be converted to inner joins with filter pushdown for speedup.

Excessive Scan Volume or Ineffective Indexes

When diagnosis shows that a scan operator’s scan-to-output row ratio is excessively high, or the execution plan uses a full table scan instead of the expected index scan, an ineffective scanning problem exists.

OceanBase supports composite index design to reduce ineffective scans and table access-by-index costs. The column order of a composite index follows a core logic: equality condition columns come first, enabling precise positioning within a specific index segment; range condition columns follow, leveraging ordering for range scans; columns needed for output but not for filtering are placed in STORING to form a covering index. The value of a covering index lies in avoiding table access — AP queries involve scanning large numbers of rows, and each table access is a random I/O. When scan volumes reach millions, table access overhead can be several times higher than the index scan itself. For plan misselection caused by statistics skew, dynamic sampling is available to correct estimates without affecting global statistics. For index misuse in ORDER BY + LIMIT scenarios, the Row Goal optimization can be disabled to correct the behavior.

Excessive Aggregation and Sort Overhead

When diagnosis shows that a SORT or MERGE GROUP BY operator’s CPU TIME share is excessively high, or a sort operator exhibits significant IO TIME, the aggregation implementation is likely poorly chosen.

Sort-based MERGE GROUP BY and MERGE DISTINCT are expensive at large data volumes and can be switched to hash implementations via hints. The reason sort-based implementations are suboptimal is that they must sort all input data, with a time complexity of O(N log N), and sorting is a blocking operation — no output can begin until all data is sorted. By contrast, hash implementation has a time complexity of O(N) and, with sufficient memory, can process and output simultaneously, achieving higher pipeline efficiency. The advantage of hash implementation is especially pronounced when the aggregation cardinality is small relative to the input row count. Multi-dimensional aggregations (ROLLUP, CUBE, COUNT DISTINCT) benefit from enabling parallelism to distribute computation across multiple threads.

Unexpanded Subqueries

When the execution plan contains a SUBPLAN FILTER operator with an abnormally high time share, it indicates that correlated subqueries have not been expanded.

The SUBPLAN FILTER operator’s execution model is: for every row in the outer table, the subquery must be fully executed once. Its cost equals the outer row count multiplied by the subquery’s per-execution cost — if the outer table has one million rows and the subquery scans one thousand rows each time, the total processing volume reaches the billions. OceanBase supports unnesting subqueries into joins via UNNEST, avoiding per-row repeated execution. After expansion, the optimizer can choose efficient algorithms such as HASH JOIN to complete matching in one pass, reducing cost from O(M×N) to O(M+N). Regarding the tradeoff between automatic and manual rewriting: the optimizer’s automatic expansion guarantees semantic equivalence, correctly handling edge cases with NULL values and duplicates; manually rewriting IN subqueries as JOINs may introduce semantic differences when NULL values or duplicates are present, so automatic rewriting is recommended as the first choice. For full table scans caused by OR conditions, OR_EXPANSION can split them into multiple branches, each using its own index.

Distributed Parallelism

Most AP queries execute in distributed parallel mode. OceanBase supports multiple shuffle strategies (LOCAL, PARTITION, BROADCAST, HASH), and the choice depends on the data volume comparison and distribution characteristics on both sides of the join. BROADCAST is suitable for scenarios where one side has a small data volume, broadcasting the small table to all nodes to avoid moving the large table; HASH shuffle is suitable for equi-joins where both sides are large tables, redistributing data by join key so that matching key values land on the same node; PARTITION shuffle leverages existing data distribution when the join key happens to be the partition key, avoiding redistribution overhead. The PQ_DISTRIBUTE hint can guide data redistribution. For load skew in parallel execution, the cause is typically hot values in join keys or GROUP BY keys, funneling large amounts of data to the same thread. The diagnostic method is to compare processed row counts by thread using SQL_PLAN_MONITOR or ODC Query Profile; if the maximum thread's processing volume exceeds 3× the average, skew is confirmed. Mitigation includes adjusting the shuffle strategy or performing pre-aggregation before the shuffle to reduce data transfer. Parallelism is set via query-level PARALLEL hints; PDML can be enabled for large-volume writes.

Resource Management and Plan Stability

When sort/hash operators frequently spill to disk, adjusting the SQL Work Area memory allocation can help. The performance impact of spills goes beyond disk I/O latency — the spill process involves serializing and writing data out, then merging it back in during read, and a single spill can increase operator elapsed time by more than 10×. When multiple operators spill simultaneously, I/O contention further amplifies the impact. For plan regression caused by stale SPM baselines, NO_USE_SPM can temporarily bypass baseline verification; after confirmation, the baseline management process should be followed to update it. A typical scenario for baseline expiration is: after significant data growth, a plan that originally used NLJ is no longer suitable because the driving-side row count has increased, but the baseline still forces the old plan. In this case, use hints first to verify that the new plan is indeed better, then replace the baseline through the SPM process.

5. Tuning Recommendation Priority

When facing the same problem, select techniques from top to bottom according to the following priority, escalating to the next level only when the current level cannot resolve the issue:

The logic behind this priority is a tradeoff between speed of effect and magnitude of side effects. It is important to emphasize: for inaccurate row estimates, dynamic sampling should be preferred over gathering statistics. Dynamic sampling performs small-scale sampling on relevant tables only when the current SQL is compiled, without consuming large amounts of cluster resources or affecting other queries’ plan choices like full statistics collection does. Dynamic sampling results affect only the current compilation, whereas gathering statistics changes global statistics and may cause plan changes for other SQL statements. Therefore, unless statistics are genuinely missing or clearly stale, dynamic sampling is the safer choice. Hints must directly target the root cause — do not push irrelevant hints just for the sake of following priority.

6. Closed-Loop Process from Diagnosis to Optimization

A complete AP query tuning cycle is a closed loop:

SQL Audit identifies the problematic SQL (ruling out retry / queue / plan cache interference)
DBMS_XPLAN examines the execution plan to determine whether the issue is at the plan level or the execution level
SQL_PLAN_MONITOR / ODC Query Profile drills into operator-level, per-thread execution details
Locate hotspots (CPU TIME / IO TIME / scan-to-output ratio / EST vs REAL)
Filter out false positives
Match problem scenarios to optimization techniques
Formulate a plan by priority (Hints → Indexes → Rewrites → Statistics)
Verify results (compare plans and elapsed times before and after optimization)

Tuning is not a one-shot deal — after making changes, you must re-examine the execution plan to confirm improvement and ensure no new issues were introduced. This closed-loop process provides a repeatable methodology: follow fixed steps to progressively narrow down the problem scope, avoiding guesswork and repeated trial-and-error. There is no one-size-fits-all solution for AP query tuning, but there is a stable diagnostic methodology — read the primary contradiction from the execution plan, then select the appropriate technique according to priority. With this approach, most slow queries can be resolved systematically.

Governing DOP Under Mixed Workloads

Charles Wu — Tue, 21 Jul 2026 14:59:00 +0000

When to Increase Parallelism for Analytics Without Breaking Your Transaction Latency Under Mixed HTAP Workloads

Introduction: Why Parallel Execution Deserves Attention

In HTAP scenarios, transactions and analytics often share the same tenant resources. When an analytical query slows down, many teams’ first instinct is to add a parallel hint or raise the session DOP. Inside a batch window, this sometimes does help; but under a mixed workload, the same tactic can bring side effects — the response time of a single report SQL comes down, yet the transaction tail latency starts to jitter, and other analytical jobs and background tasks get dragged down along with it.

Parallel execution is one of the capabilities in OceanBase that tends to pay off relatively easily for analytical queries. The basic idea is to split work that would otherwise be done by a single execution thread across multiple workers that cooperate, thereby shortening the critical path of a large query through its scan, join, and aggregation stages. Given a reasonably large data volume, a query that genuinely has room to parallelize, and enough resource headroom in the current window, setting a sensible degree of parallelism (DOP, Degree of Parallelism) generally makes better use of multi-core CPUs and distributed resources.

The catch is that parallelism does not affect only this one SQL. It directly changes how parallel threads, CPU, I/O, and network resources are allocated within the tenant. A single report getting faster does not mean the system as a whole is more stable; and Auto DOP being on does not mean resource control can be handed over entirely to the optimizer. The optimizer can pick a degree of parallelism for a single query, but it will not judge for you whether the tenant as a whole is still in a state where it makes sense to keep scaling parallelism up.

So when we discuss DOP under a mixed workload, the focus is not “how to tune a given SQL to run faster,” but three questions: which scenarios are worth tuning, how to tell whether the tuning actually took effect, and when to rein it back in.

1. What DOP Actually Optimizes in OceanBase

In OceanBase’s parallel execution framework, DOP determines the number of worker threads that a single DFO (Data Flow Operation — one sub-plan segment within a parallel plan) can use during execution. In other words, DOP expresses “how many parallel resources this query is willing to request during its parallel stage,” not a fixed “speedup multiplier.”

Within a SQL execution plan, not every stage can run in parallel. Final result aggregation, the handoff between parallel stages, and some output-organizing steps still often have to be done serially. What DOP can accelerate is mainly the stages that are naturally suited to being split: wide-range table scans, Hash Join, aggregation, cross-node data redistribution (EXCHANGE), and the like. The appearance of PX (Parallel Execution) operators in a plan only means the optimizer believes certain stages can be parallelized; it does not guarantee that the bottleneck of the whole SQL lies in those stages.

1.1 Which Execution Stages DOP Mainly Accelerates

In practice, the following execution characteristics tend to be a better match for DOP:

Wide-range scans, especially scans on partitioned tables that need to read large amounts of data;
Heavy Hash Join and heavy aggregation;
Data redistribution across OBServers or across partitions;
AP-leaning tasks such as reporting, batch jobs, and ad hoc analytics, where the data processed in a single run is large enough.

For this kind of SQL, if the slowest operator already falls on a parallelizable stage such as scan, join, or aggregation, raising DOP appropriately or enabling Auto DOP is usually worth considering.

1.2 The Boundaries of DOP

DOP needs to be kept separate from “scanning less data.” Partition pruning, index selection, and predicate pushdown address the problem of scan scope; DOP addresses “given that this data must be processed, how to process it faster.” If a SQL genuinely needs to scan and process a large amount of data, then parallel execution is a reasonable way to speed it up; but if the actual scan volume is clearly larger than the business expects, you should first investigate whether there is an execution path that can be optimized, rather than relying on higher DOP alone to paper over the problem.

Likewise, lock waits, serial hot spots, and wrong plans caused by badly skewed statistics are not things that adjusting DOP can fundamentally fix. When you run into these, treat DOP first as a boundary for judgment: confirm whether the problem really lies in the parallel stage, and only then decide whether to tune or to stop.

1.3 Where Auto DOP Fits

The value of Auto DOP lies in reducing the maintenance cost of adding hints one SQL at a time, but it relies on the optimizer’s estimates, and whether those estimates are trustworthy is inseparable from the quality of the statistics. More importantly, Auto DOP optimizes the parallel choice for a single query, which is not the same as tenant-level optimality under a mixed workload, and cannot replace resource isolation, time-window policies, and parallel budget control.

2. When DOP Tuning Fits, and When It Doesn’t

2.1 Scenarios Where DOP Is Worth Considering First

The execution plan is dominated by wide-range scans, heavy joins, and heavy aggregation, and the amount of data actually processed is large enough; the query involves a fair amount of cross-node or cross-partition data redistribution; the task type leans toward reporting, batch jobs, or periodic analytics; and the current period is off-peak, or the tenant still has headroom in parallel threads and CPU.

Analytical queries on partitioned tables, given a reasonable execution path, are often better candidates for a DOP discussion than point lookups on small tables.

2.2 Scenarios Where “Keep Adding DOP” Should Not Be the Main Lever

Point lookups that return a single row or a small number of rows; queries where a highly selective index has already squeezed the scan scope down to something small; high-frequency short queries on typical OLTP paths; and cases where the bottleneck clearly lies in lock waits or serial stages, or where there is no PX in the plan at all.

When you force all queries in a session to use the same DOP, short queries that did not need parallelism in the first place also get dragged down by the parallel framework.

Having PX in the Explain output does not mean “parallelism is already in place,” either. If the slowest operator is not in a parallel stage, adding more DOP usually just amplifies coordination cost and EXCHANGE overhead.

If the scan scope is clearly on the large side, you also need to distinguish further: is this large-scale data that the business genuinely needs to process, or something you can first bring down through partition pruning, index selection, predicate pushdown, and similar techniques?

2.3 A Simple Test: Is This SQL Worth Tuning DOP For

The judgment can be narrowed down to four questions:

First, are the data volume and scan scope large enough, and does the slow part fall on a large scan, a heavy join, or a heavy aggregation?

Second, if the scan scope is large, first determine whether this is data the business genuinely needs to process, or whether it is actually scanning more than expected. In the former case you can keep discussing DOP; in the latter case you should first investigate path issues such as partition pruning, index selection, and predicate pushdown, rather than adding DOP first.

Third, does the query type and the current period allow more parallel resources to be allocated to it?

Fourth, if most of the above conditions are not met, should you instead turn toward statistics, the execution path, or other non-parallel directions?

Only tune DOP once “the data volume is large enough, the hot operator is parallelizable, the task leans analytical, and the period allows it” all hold at the same time; otherwise, stop first, and do not treat DOP as an all-purpose knob.

3. How to Tell Whether DOP Actually Took Effect for a Single SQL

For a single SQL, the questions are: did parallelism get applied where it should have, and did the elapsed time improve noticeably? You can approach this in three steps.

Step one, first check whether this SQL is suitable for parallelism. Against the execution plan, confirm whether the slow part falls on stages such as large scans, heavy joins, or heavy aggregation. If the number of scanned rows or the range of partition access is seriously out of line with business expectations, you should first put the suspicion on the execution path — for example, whether partition pruning is taking effect, or whether the right index is being used — rather than defaulting to “the DOP isn’t big enough.”

Step two, next check whether parallelism is landing on the slowest operator. Having a PX operator in the plan does not mean the slowest stage is actually being accelerated effectively. A typical counterexample: most of the total time is spent in some serial join or table-lookback stage, while PX only appears on a secondary scan. In that case, even if you raise the DOP, the share of time spent by the hot operator may barely change. When judging, focus on the stage type and time share of the slowest operator, rather than only on whether the total RT of the SQL dropped a little.

Step three, verify the actual execution result. A hint, session setting, table-level PARALLEL attribute, or the DOP given by the optimizer are all only "expected values." At actual execution time, the DOP may be downgraded due to resource limits such as parallel_servers_target, or it may be delayed in getting threads because of queuing. So you cannot just look at dop= in the Explain output; you also need to bring in runtime monitoring.

You can usually use GV$SQL_PLAN_MONITOR to look at the actual execution of each operator, focusing on three things: whether the actual DOP is close to what was expected; whether the time spent by the slowest operator drops as DOP is adjusted; and, if the plan has PX but the time of the slowest stage barely changes, then parallelism has not landed on the real hot spot, and you should go back to investigating at the path and operator level rather than keep adding DOP.

If you use Auto DOP, you also need to pay extra attention to statistics quality. When statistics are badly off, you may see cases that should have parallelized but didn’t, or a DOP that is clearly unreasonable. Auto DOP is well suited as a way to reduce manual intervention, but it still needs to be verified against actual execution results, just like a manually specified DOP.

4. Why Bigger DOP Is Not Always Better

4.1 DOP Scales Resource Requests, Not Just Speed

A larger DOP usually means more parallel workers, more EXCHANGE channels, and higher scheduling and coordination cost. Given limited tenant resources, both the benefits and the side effects get amplified at the same time. Thinking of DOP as an “acceleration factor” easily misleads judgment; a more accurate way to put it is: how many extra threads and data-channel resources this query has to request in exchange for stronger parallel processing capability per unit time.

The scale of threads a tenant can use for parallel execution is constrained by variables such as parallel_servers_target. When parallel thread resources are exhausted, new parallel requests have to queue. You may have written parallel(32), but the DOP actually achieved could be far below 32 — and in that case, continuing to raise it usually makes little sense.

4.2 Parallel Gains Usually Do Not Grow Linearly

Within a parallel SQL, there are always serial stages, which cannot possibly shrink in proportion to DOP. Beyond that, there are several common kinds of limits:

If the plan has multiple DFOs running at the same time, the parallel threads have to be distributed across several stages, and will not all be piled onto one hot operator;
Data skew causes some workers to finish early while others drag out the overall progress with a long tail;
The higher the DOP, the higher the scheduling and buffering cost of EXCHANGE and data transfer tends to be;
If I/O, CPU, or network is already the system bottleneck, adding more DOP may just make more threads wait together rather than run faster together.

So raising DOP from 4 to 16 does not necessarily cut the elapsed time to a quarter; sometimes it barely changes, or even gets slightly worse due to framework overhead. These are phenomena to face squarely, not signs that “the parameter just isn’t cranked high enough.”

4.3 Under a Mixed Workload, the Side Effects of High DOP Deserve More Attention

In an HTAP same-tenant scenario, an analytical SQL running a bit faster after its DOP is raised does not mean the system is more stable. When several high-DOP queries run concurrently, you may see parallel threads queuing, CPU contention, and large scans maxing out I/O and network bandwidth, which then squeezes TP queries and the write path. A single SQL getting faster and the overall stability of the tenant need to be looked at separately — the latter is the harder constraint when governing DOP.

4.4 When You Should Not Keep Raising It

When the following signals appear, you should stop and investigate first rather than keep increasing DOP:

The time share of the hot operator barely changed;
The actual DOP is clearly below expectation;
CPU, I/O, and network pressure on the system rose, but the improvement in SQL elapsed time is limited;
PX queuing is starting to increase.

All of these indicate that the problem is more likely in the execution path, resource budget, or where parallelism lands, rather than “the DOP value still isn’t big enough.”

5. How DOP Should Be Governed Under HTAP

5.1 Define the Governance Goal First

Our goal is not to make every analytical SQL as fast as possible, but to improve the execution efficiency of analytical tasks such as reporting and batch jobs within an acceptable impact on TP. In other words: allow the analytical side to get moderately faster, but not at the cost of transaction tail latency spinning out of control, worse write jitter, or widespread degradation of background tasks.

5.2 Set Policies by Time Window and Workload Type

Usually you layer by time window and session type, rather than forcing parallelism uniformly across the whole tenant and all sessions.

Transaction peak periods and online-transaction-related sessions: keep the parallel policy conservative, prioritizing stability and tail latency. You can pair this with a higher parallel_min_scan_time_threshold so that only scans with a long enough estimated time enter parallelism, and use parallel_degree_limit to cap the upper bound of Auto DOP.

Off-peak periods and reporting/batch windows: given available resource headroom, you can relax the parallel policy somewhat and use manual hints or dedicated session configurations for the key batch tasks.

Reporting, batch, and ad hoc analytics sessions: manage them separately from OLTP sessions as much as possible. Beyond using different parallel policies, you can also combine resource groups for isolation, so as to avoid using the same aggressive configuration to directly contend for CPU and parallel thread resources during transaction peaks.

5.3 The Role of Resource Group Isolation in DOP Governance

Under a mixed workload, managing only the DOP upper bound and the Auto DOP threshold is usually not enough. They can decide whether parallelism is turned on and how large it can go at most, but they cannot stop reporting, batch, and transactional queries from fighting each other for resources.

For TP, reporting, batch, and ad hoc analytics coexisting within the same tenant, it is more appropriate to combine resource groups for isolation: let transactional sessions and analytical sessions use different resource groups, constraining CPU and other resource usage separately, so that high-DOP queries do not directly squeeze online transactions during peak periods.

The DOP-related parameters govern how large a single SQL can parallelize; resource groups govern whether, once that SQL parallelizes big, it can drag other business down with it. The former solves “how large it can go,” the latter solves “whether the impact can be contained” — neither can be left out.

5.4 Control Risk with Upper Bounds and a Parallel Budget

Effective DOP control is usually a combined set of settings, not a change to a single parameter:

Decide which SQL is more likely to enter parallelism — for example, through the Auto DOP threshold, or by using hints to enable it only for key SQL;
Decide how large parallelism can go once entered — for example, parallel_degree_limit, manual hints, and the table-level PARALLEL attribute;
Decide the scale of parallel threads the tenant as a whole can bear — for example, variables such as parallel_servers_target, and whether to pair them with resource isolation.
Decide whether different types of workload need further isolation — for example, using resource groups to separate OLTP from reporting/batch sessions, so that high-DOP queries do not directly squeeze transactional resources.

Under a mixed workload, you cannot rely solely on the optimizer to pick a DOP for each SQL individually; you have to first define the boundaries clearly from the tenant’s perspective.

5.5 Where Auto DOP Sits in Governance

For teams unfamiliar with the rules for setting the degree of parallelism, setting parallel_degree_policy to AUTO is a reasonable starting point, but you still need to adjust parallel_min_scan_time_threshold and parallel_degree_limit according to the scenario. In a "query performance first, almost no other load on the tenant" scenario, you can lower the scan parallelism threshold and relax the upper bound somewhat; in a "many concurrent queries, need to protect overall system throughput and latency" scenario, you should limit the maximum DOP and raise the parallelism trigger threshold, to avoid many queries contending for resources at high DOP all at once.

There is a priority relationship among Auto DOP, manual hints, session-level DOP, and the table-level PARALLEL attribute (hints generally take precedence over session and table attributes). When governing, be explicit about which SQL is handed over to Auto DOP, which core reports are precisely controlled by hints, and which OLTP sessions should stay under the MANUAL policy.

6. How to Assess Whether the Current DOP Policy Suits This Tenant

A single SQL running faster does not mean this parallel policy suits the tenant. To judge whether a DOP policy is appropriate, you have to look at both the improvement on the analytical side and the cost on the system side.

6.1 Check Whether the Analytical Side Shows Clear Improvement

If the overall direction of the parallel policy is right, the analytical side should show improvement along observable dimensions, for example:

Whether the overall RT of typical analytical SQL drops;
Whether the total completion time of reporting and batch tasks shortens;
Whether the time to produce results for periodic tasks becomes more stable;
Whether the queuing and execution time of analytical SQL stays within an acceptable range.

These metrics need to be looked at over a time window, comparing before and after the policy change together, not just a single execution of one SQL.

6.2 Check Whether the System Cost Is Too High

At the same time, you also need to look, from the tenant as a whole, at whether the side effects are being amplified in step:

Whether parallel thread utilization stays near the upper bound for long stretches — this usually means the policy, while speeding up some analytics, is also compressing the tenant’s parallel headroom, making queuing more likely later on.
Whether PX queuing is starting to become noticeable.
Whether CPU, I/O, and network pressure rise in step as the parallel policy is relaxed.
Whether write latency, Compaction, or other background tasks start to fluctuate.

If the improvement on the analytical side is limited while these system metrics deteriorate in the same direction, then the current DOP policy is already too costly for the mixed workload, and it should be reined in rather than pushed further.

6.3 What Counts as Well-Governed

The relatively ideal state can be summed up in three points:

The analytical side shows clear but not excessive improvement, for example the RT of typical analytical SQL drops and batch time shortens.
TP and overall stability do not degrade noticeably, for example write latency and transaction tail latency stay within the business-acceptable range.
Parallel resources are not in a state of sustained queuing or overload for long stretches.

If you end up in a situation of “the report got faster, but the order database started to jitter” — where analytical tasks speed up while TP tail latency, writes, or background tasks degrade in step — that is a clear signal that the policy needs to be rolled back or re-layered, not something you can solve by continuing to fine-tune the DOP value.

7. A Concise Way to Use DOP

In a mixed-workload scenario, DOP adjustment can be driven by two judgments first.

First, judge whether it is worth parallelizing.
If the data volume is small, the query itself is short, and the bottleneck is not in a parallelizable stage such as a scan or join, then DOP should not be your first lever. Only when the data volume is large enough, the hot operator is parallelizable, and the current period has resource headroom is parallelism worth considering.

Then judge whether to keep going or stop after tuning.
Start from a conservative policy rather than cranking DOP high from the outset. Observe whether the single SQL actually got faster, and whether TP latency, PX queuing, and CPU and I/O pressure are still within an acceptable range. If the gain is limited while the system cost starts to rise, rein it in rather than keep scaling parallelism up.

For example, for a report SQL that does a wide-range scan plus aggregation, you can start with a smaller hint rather than writing a very high degree of parallelism directly:

SELECT /*+ parallel(4) */ c_nationkey, COUNT(*)
FROM customer
GROUP BY c_nationkey;

If you would rather let the optimizer decide automatically whether to parallelize, you should still set the policy on the conservative side first — for example, enable Auto DOP while limiting the parallel upper bound, to avoid many queries scaling up at the same time:

ALTER SESSION SET parallel_degree_policy = AUTO;
ALTER SESSION SET parallel_degree_limit = 4;

Whether you use a hint or Auto DOP, the first round is better aimed at “verifying whether it is worth parallelizing and whether the system can bear it,” rather than “getting the DOP to its maximum as fast as possible.”

8. Boundaries to Remember When Using DOP

Do not treat DOP as a speedup multiplier. Total elapsed time is also constrained together by serial stages, data distribution, system bottlenecks, and mixed-workload contention.

Do not assume PX is effective just because you see it. What matters is whether parallelism landed on the hot operator and whether the actual DOP reached expectation (see Section 3).

Do not treat Auto DOP as automatic governance. It can reduce manual maintenance, but it cannot replace policy control, resource isolation, and time-window management under a mixed workload (see Sections 1.3 and 5.4).

Do not look only at whether a single SQL got faster. In HTAP scenarios, TP tail latency, PX queuing, and the stability of writes and background tasks often say more about whether a policy is appropriate than the RT of a single report (see Section 6).

The signals these point to are usually issues in the execution path, statistics, resource budget, or where parallelism lands, rather than something “a few more notches of DOP can solve.”

Conclusion

In HTAP scenarios, DOP is not a speedup switch for a single SQL, but a resource-allocation choice under a mixed workload.

The question that truly matters has never been “can we add a bit more parallelism,” but: should this parallel resource go to this kind of query, should it go to this time window, and once it is given out, can transactions and background tasks still hold steady? Only when DOP is viewed within tenant-level resource control does it become a capability that remains usable over the long term, rather than turning a local optimization into a global risk.

How Direct Load Delivers Strong Transactions and High Concurrency for Real-Time Analytics

Charles Wu — Wed, 15 Jul 2026 14:59:00 +0000

How to Load Data in Bulk — Keeping Writes Fast, Transactions Safe, and Analytics Instant

In scenarios such as e-commerce flash sales, financial risk control, and telecom network big data, businesses now expect data freshness measured not in hours or minutes but in seconds or even less. The value of data for decision-making decays quickly once it is delayed — an inventory update that arrives a minute late can lead to overselling, and a risk-control signal that lags by a few seconds may only fire after a fraudulent transaction has already gone through. Whether data can join analytical queries the moment it is written directly determines how timely and accurate business decisions can be.

This places demanding requirements on a data platform’s ingestion capabilities: it must not only write large batches of data into the system quickly, but also make that data queryable immediately after the write completes, all without disrupting the online workloads already running. Traditional approaches tend to trade one of these dimensions off against another. This article looks at OceanBase’s direct load mechanism and how it balances high-throughput writes, strong transactional guarantees, and coexistence of multiple workloads.

Ingestion Challenges in Real-Time Analytics

To serve both writes and analytics, many teams adopt multi-system architectures like Lambda or Kappa, where data has to travel through components such as Kafka, Flink, Hudi, Hive, and ClickHouse, with each stage involving ingestion, synchronization, transformation, and governance. The longer the pipeline, the more latency accumulates, and the harder it becomes to keep data consistent across systems — making write-then-analyze difficult to achieve in practice. Traditional batch-oriented ETL adds T+1 latency on top of this, so analytical results inherently lag behind the events they describe.

Turning to ingestion itself, traditional ETL and standard SQL write paths have a clear throughput bottleneck. Every write has to go through SQL-layer parsing, transaction lock contention, and the synchronization cost of Redo log. When the data volume reaches millions or tens of millions of rows, these accumulated per-row costs substantially slow down ingestion and fall short of the write-then-analyze goal.

In HTAP mixed-workload environments, the problem grows more complex. On one hand, large-batch ingestion must not block the online transactions already running — TP and AP workloads need to coexist. On the other hand, data consistency cannot be compromised: written data must satisfy ACID guarantees and become visible to analytical queries immediately after commit.

Finding the balance between high-throughput ingestion and online transactions is a question every real-time analytics platform has to answer.

The Core Mechanism of Direct Load

To understand what direct load optimizes, it helps to split a single ingestion into two stages:

Stage one: data reaches the storage layer. Data goes through SQL parsing, execution plan generation by the optimizer, and reading and scheduling by the executor before finally arriving at the storage layer.
Stage two: data is written into the storage layer. Data is actually persisted at the storage layer. The standard path writes data into the MemTable, whereas direct load builds SSTables directly.

The core optimization of direct load lives in the second stage: it bypasses the MemTable, sorts data by primary key, and builds SSTables directly to insert into the storage layer. This is the most fundamental difference from the standard write path.

The Write Path

The overall write path can be summarized as:

Raw data → type conversion → sort by primary key → build SSTable directly → insert into storage layer

Compared with the standard path of writing into the MemTable row by row, building SSTables directly offers the following advantages:

Substantially lower lock overhead: a table lock is taken only once at the start of ingestion; there is no row-lock contention during the write, and multiple threads build in parallel without interfering with one another.
More efficient conflict detection: tables without a primary key can skip conflict detection entirely, while tables with a primary key scan the existing data at most once.
Less log volume: instead of logging each row individually, the encoded macroblock data is written to the log as a whole.
No stalls from a full MemTable: because the MemTable is not involved, there is no scenario where a full MemTable must be frozen and flushed, and no risk of writes stalling because background compaction cannot keep up.
Data is persisted directly in the storage engine’s final format (SSTable), skipping the multi-round compaction chain from MemTable to Mini SSTable to Major SSTable.

Full Load and Incremental Load

Depending on where data is written, direct load comes in two forms: full direct load and incremental direct load. The difference lies in which layer of the LSM-Tree the data ultimately lands in.

Full direct load: sorts the incoming data by primary key, merges it with existing data, and writes it directly into the baseline Major SSTable. It suits empty tables or tables with a small data volume.
Incremental direct load: sorts the incoming data by primary key and writes it directly into the Mini SSTable of the incremental layer. It suits tables that already hold a large data volume, avoiding a full rewrite of the baseline.

Both approaches bypass the layered structure of the LSM-Tree — such as the complex BTree structure within the MemTable — and instead generate SSTable files directly through efficient sorting, significantly improving the speed of bulk data writes.

Integration with the LSM-Tree Storage Engine

OceanBase’s storage engine is built on an LSM-Tree architecture, in which data is naturally divided into two layers:

Incremental data: newly written user data lands quickly in the MemTable in row-store form, and is later flushed into Mini SSTable, Minor SSTable, and so on. Incremental data is maintained independently by each replica and contains all multi-version information.
Baseline data: generated as Major SSTables through periodic or adaptive Major Compaction. Under the same version number, the baseline data across all replicas is physically consistent, and in AP scenarios it can be configured in column-store mode to serve analytical queries directly.

Baseline compaction here uses a daily-compaction mechanism: a tenant periodically, or based on user action, selects a global version number, and all replicas complete a round of Major Compaction at that version number, ultimately producing baseline data at the same version. Built on distributed multi-replica foundations, the baseline data across all replicas is physically consistent under the same version number, providing a stable and reliable read foundation for analytical queries.

Both direct load paths fit naturally into this incremental-baseline two-layer structure. Full load lands directly in the column-store baseline Major SSTable, while incremental load first lands in the Mini SSTable of the incremental layer and is later incorporated into the column-store baseline through the normal compaction flow. Whichever path is taken, the entire process requires no extra data movement or format-conversion pipeline.

After Ingestion: Column Store and the Vectorized Engine Accelerate Analytics

Direct load solves the throughput problem of getting data into the database; once the data enters the column-store baseline, analytical query performance is further improved by the vectorized execution engine. In column-store mode, each column is stored as a separate SSTable, and the SSTables of all columns combine into a virtual SSTable that serves as the column-store baseline data. This layout lets analytical queries read only the columns involved, reducing scans of irrelevant data.

Building on this, the vectorized engine processes data in batches rather than iterating row by row, significantly reducing iteration overhead, using the CPU cache more efficiently, and parallelizing computation with SIMD instructions. In public benchmarks, the OceanBase vectorized engine, compared with the previous version, improves ClickBench by about 14x, TPC-H 100G by about 40x, and TPC-DS 100G by about 26.5x. Together, direct load, the column-store baseline, and vectorized queries form a coherent path from write to analysis.

Full Database Feature Support

While improving write throughput, direct load does not sacrifice existing database functionality. Indexes, constraints, partitions, transactions, and a wide range of data types are all supported during ingestion.

Indexes and constraints

Primary keys, global indexes, and local indexes are built automatically and in parallel during ingestion based on the PX framework.
Uniqueness and not-null constraints are validated in real time during ingestion.
Conflict handling supports strategies such as IGNORE and REPLACE.

Partitioned table support

Supports partitioning strategies such as Range, Hash, and List.
Compatible with multi-level partitioning.
Partition-level parallel ingestion, leveraging the strengths of the distributed architecture.
Supports specifying partitions to import, loading only specific partitions.

Transactional consistency

Data is not externally visible during ingestion.
Automatic rollback on ingestion failure.

Data type support

All standard SQL data types.
LOB large objects (CLOB/BLOB).
Extended types such as JSON and GIS (continually being improved).

Data Sources and Formats

In real-world business, the data to be ingested often comes from a variety of heterogeneous systems. Combined with its external table capability, OceanBase direct load supports efficient ingestion directly from many data sources:

File formats: common analytical data formats such as CSV, JSON, Parquet, and ORC.
Storage sources: local disk, OSS, S3, HDFS, NFS, COS, OBS, ODPS, and more.
Catalog integration: Hive Metastore, ODPS Catalog, and the Iceberg table format.
Character sets: multiple character sets such as UTF-8 and GBK.
Compression formats: gzip, snappy, lz4, zstd, and others.

Data can be read directly from external storage systems and written into OceanBase via direct load, eliminating the multi-step chain of staging, cleansing, and reloading found in traditional approaches.

Ingestion Methods

Direct load offers several methods, so DBAs, data engineers, and application developers can each choose the one they prefer.

SQL support is native and compatible with MySQL’s LOAD DATA syntax, so a single command does the job:

-- Import from a local file
LOAD DATA /* direct(true, 0) parallel(32) */ INFILE 'orders.csv' INTO TABLE orders;

-- Import from remote storage (OSS/S3 supported)
LOAD DATA /* direct(true, 0) parallel(32) */ INFILE 'oss://bucket/path/data.csv' INTO TABLE orders;

INSERT SELECT, combined with internal or external tables, can perform ETL entirely within OceanBase or import directly from external files:

-- ETL with OceanBase INSERT SELECT
INSERT /* direct(true, 0) enable_parallel_dml parallel(32) */ INTO summary_table 
SELECT * FROM detail_table 
WHERE date >= '2026-01-01';

-- Import data via an external table with OceanBase INSERT SELECT
INSERT /* direct(true, 0) enable_parallel_dml parallel(32) */ INTO detail_table 
SELECT * FROM FILES(location = 's3://bucket/path', type = 'parquet', pattern='*');

For scenarios that require loading files in bulk from the command line, you can use the OB Loader tool:

obloader -h 127.0.0.1 -P 2881 \
         -u user@tenant#cluster -p password \
         --table=logs \
         --direct-load \
         --file-path=/data/logs/

In addition, in KV scenarios, batch writes through the OceanBase Table API can also take the direct load path. These integration methods work out of the box, with no extra components or middleware required.

High-Concurrency Guarantees: How Direct Load Coexists with Online Workloads

Once transactional correctness is settled, another key question is: how can large-batch ingestion avoid affecting the online workloads already running? OceanBase addresses this challenge through multi-layer resource isolation and an elastic architecture.

A Multi-Layer Resource Isolation System

OceanBase provides multi-level resource isolation, from coarse-grained to fine-grained:

Isolation across tenants: ingestion tasks for different business lines run in different tenants, with independent resource quotas and no mutual interference.
Isolation between foreground and background tasks: direct load can run as a background task, with the system setting upper limits on its CPU and I/O priority so that the response time of foreground online transactions is not affected.
User-level and SQL-level isolation: through the Resource Group mechanism, administrators can set resource quotas for specific users or specific types of SQL, preventing a single large ingestion task from crowding out the compute resources of other workloads.

Strong Isolation Between TP and AP Replicas

In mixed-workload scenarios, OceanBase supports routing ingestion traffic and analytical queries to different replicas:

Write replicas take on the data write traffic of direct load.
Read replicas (read-only replicas) take on concurrent analytical query requests.

The two workloads are isolated at the physical level; what they share is the same strongly consistent copy of the data, not the same compute resources.

Elastic Scaling and Automatic Load Balancing

To handle ingestion peaks — such as data warm-up before a flash sale or end-of-day batch loading — OceanBase offers flexible scaling options:

Both integrated and disaggregated storage-compute forms: enterprises can choose the architecture that fits their deployment environment. In the storage-compute disaggregated mode, compute nodes can scale independently without affecting data storage.
Fast scaling in and out: within a single Zone, compute nodes can be added or removed in minutes to handle traffic spikes.
Built-in dynamic data balancing: after scaling out, the system automatically redistributes the data load across nodes, avoiding hot shards or bottleneck nodes.

Summary

Direct load, strong transactional guarantees, and resource isolation work together to form a complete capability loop for OceanBase in real-time analytics scenarios:

Direct load solves the throughput problem of loading large batches of data at high speed, and adapts to different data scales through its full and incremental paths.
Full database feature support keeps indexes, constraints, partitions, and a variety of data types usable even during high-speed ingestion.
Diverse integration methods cover native SQL syntax, OB Loader, and the Table API, letting different roles integrate conveniently.
Multi-Paxos + MVCC + WAL ensure the consistency and durability of ingested data, so transactional semantics are not sacrificed in the pursuit of speed.
Multi-layer resource isolation and replica routing ensure that ingestion does not disrupt the normal operation of online workloads.

From write to query, this set of capabilities forms a clear division of labor: direct load handles high-speed ingestion, the column-store baseline and vectorized engine accelerate queries, materialized views handle precomputation, and resource isolation ensures that multiple workloads coexist in the same cluster. Working together, they let enterprises perform high-throughput writes and real-time analytical queries within a single system, without maintaining multiple technology stacks and data-synchronization pipelines. Data becomes queryable the moment it enters the system, achieving a smooth transition from write to analysis.

The Scan Optimization Boundaries of OceanBase Skip Index in HTAP

Charles Wu — Tue, 14 Jul 2026 14:59:00 +0000

When to Enable Skip Index — Cutting Wasted I/O in Mixed Workloads Without Extra Maintenance Pain

When transactions and reporting share the same OceanBase cluster, a drop in analytical query performance is usually first investigated from the angles of SQL, B+ tree indexes, and parallelism. Even when partition pruning is already in effect in the execution plan and the scan range has converged to a reasonable interval, a query may still read large amounts of data within a partition. This drives up response times, increases CPU and I/O overhead, and — during business peaks — affects short transactions and background compaction on the same node.

These problems are often not simply a matter of a table being too large or a missing index. More commonly, the scan phase reads in a large amount of data, only for part of it to be filtered out later. To arrive at the final matching results, the system still has to read all of that data first. This cost is what we call wasted scanning.

Skip Index targets exactly this problem. It is column-level pre-aggregated data written into the intermediate micro-block index layer of the SSTable — commonly MIN_MAX and NULL_COUNT, and in some scenarios SUM as well. Once a filter condition is pushed down to the storage layer, the engine uses these statistics to decide whether a data block still needs to be read: if a block cannot possibly contain the target rows, it skips reading and decoding that block, thereby reducing wasted I/O.

That said, Skip Index mainly addresses wasted reads in scan-oriented workloads, not point-lookup positioning — and it is not a universal capability that “speeds things up whenever there’s a WHERE clause." Under mixed workloads, its more important value lies in letting analytical queries consume less shared I/O and CPU, rather than simply making a single SQL statement faster.

Why Wasted Scanning Still Happens

As described above, wasted scanning refers to the I/O and decoding cost incurred by data that is read during the scan phase but later excluded by filtering. This cost does not automatically disappear just because routine optimizations have already been applied.

HTAP queries in OceanBase typically read both the merged baseline data and the newer incremental data at the same time. The columnar path can read only the columns involved in the query, and B+ tree indexes can narrow the row-positioning range for point lookups — but the two operate at different levels: the former reduces column-level reads, while the latter avoids entering a large-range scan. Once a query has already entered the scan phase, it may still read in large amounts of data at the block granularity that ends up being filtered out. When the filter conditions themselves cannot exclude many data blocks in advance, the system may still pay the cost of reading and decoding many blocks that will ultimately be filtered away.

If the problem stems from an oversized table, a missing suitable index, or a poorly designed partition key, then the focus of optimization usually does not lie at the Skip Index layer.

Skip Index is better suited to scenarios like this: the execution plan has already landed on a large-range scan within a partition (row store or column store both work), and the real bottleneck genuinely comes from “reading a lot but keeping little.” In a cluster where transactions and analytics share resources, this kind of waste does not always first show up as one particular SQL statement being slow. More often, analytical tasks continuously occupy shared resources and, during business peaks, affect short transactions and background compaction on the same node.

Where Skip Index Fits in the Scan Optimization Chain

Wasted scanning happens at the data-block granularity. To judge whether Skip Index applies, you first need to be clear about which layer it and other scan-related capabilities each operate on.

Skip Index and B+ tree indexes do not solve the same kind of problem. B+ tree indexes are good for quickly positioning a small number of target rows; Skip Index targets a different scenario: the query has already entered the scan phase, but the system still wants to read as few of the ultimately non-matching data blocks as possible. It does not turn a scan into a point lookup, but it can reduce unnecessary disk reads during the scan.

The division of responsibilities among several common capabilities is as follows: partition pruning narrows the range of partitions to be scanned; column store decides which columns to read and which columnar scan path to use; B+ tree indexes handle row positioning for point lookups and high-selectivity access; and only if a large number of data blocks still need to be read within the scan range after the above steps does Skip Index become applicable. When the preceding optimizations have not resolved the corresponding problem, or when filters cannot be pushed down and the query is mostly point lookups, enabling Skip Index alone usually brings little noticeable improvement. Only when the execution plan has already landed on a large-range scan (row store or column store) and the bottleneck manifests as a high scan-read volume with a low post-filter retention ratio does it enter the scope of Skip Index evaluation.

In scenarios such as hybrid row-column storage or a row-store primary table paired with columnar indexes, having Skip Index configured on a table does not mean every query will use it. The filter conditions must be pushable down to the storage layer, and the query must ultimately adopt a scan method that can leverage block-level statistics. The baseline side of a column-store table usually already carries default MIN_MAX statistics, which may not necessarily be shown in DESC when created by default; a row-store table requires explicitly declaring SKIP_INDEX(...) on the column. This brings additional storage and compaction maintenance overhead, but in low-selectivity filter and aggregate-pushdown scenarios it can likewise significantly reduce wasted I/O.

Why Skip Index Can Reduce Scanning

Skip Index works by relying on the block-level statistics maintained during the write and compaction processes. For a specified column, the system records statistics such as the minimum value, maximum value, and null count in the intermediate index layer of the baseline data.

How These Statistics Are Used During Query Execution

After a filter condition is pushed down to the storage layer, the engine — before prefetching a data micro-block — first uses these statistics to judge whether the block still needs to be read. If, judging by its value range, a data block cannot possibly satisfy the condition, it can directly skip the disk read and decoding; the remaining data blocks are still read normally and confirmed again by the subsequent filter, so no target rows are missed.

In certain aggregate queries, the system can also leverage the additionally maintained aggregate values to further reduce access to data micro-blocks.

Which Scenarios Show the Effect More Readily

Column-store tables: The scan data volume is usually larger, so the room for skipping is often greater as well; the baseline side typically already carries MIN_MAX statistics by default.
Row-store tables: In large-range scans with filters, the index layer can first judge whether a data block might contain the target rows before deciding whether to read the data micro-block. After explicitly declaring SKIP_INDEX(...) on a few key columns, wasted I/O can likewise be noticeably reduced in low-selectivity filter and aggregate-pushdown scenarios.
Aggregate queries: SUM mainly provides additional optimization room in aggregation scenarios. If the business genuinely depends on SUM aggregate pushdown, you can create it explicitly and then decide whether to keep it based on actual conditions. Column-store tables do not create SUM-type statistics by default, partly because it adds to the burden of direct load and compaction tasks.

The core role of Skip Index is to read fewer of the ultimately non-matching data blocks during the scan phase, thereby reducing wasted scanning and freeing up I/O and decoding resources. In HTAP scenarios, these statistics are built primarily on the baseline data; the portion that has already been compacted can participate in query acceleration first.

How to Evaluate Whether It Is Worth Enabling in HTAP

In a pure analytical environment, the main role of Skip Index is to reduce resource consumption during scanning; in HTAP scenarios, you have to look one level deeper: are analytical queries still heavily consuming the I/O and CPU that should be left for short transactions and background compaction? Every data block not read means giving back a portion of I/O and decoding capacity to the shared resource pool. Therefore, in HTAP scenarios, whether to enable Skip Index should not be judged solely by whether the analytical query itself gets faster — you also need to consider whether the resources it saves are enough to offset the extra storage overhead, the compaction maintenance overhead, and the impact on transactions and background tasks.

First, Check Whether the Query Path Matches

Skip Index is worth considering only when the following conditions are met:

The execution plan is dominated by large-range scans, in either column store or row store; partition pruning has already converged the scan range to a reasonable interval, yet large amounts of data still need to be read within the partition.
The filter fields are stable over the long term, and the filter conditions can be pushed down to the storage layer.
The data distribution allows a substantial portion of data blocks to be excluded in advance — for example, reporting-style queries filtered by low-selectivity conditions such as time or status.
For column-store tables, first confirm whether the default MIN_MAX covers the commonly used filter columns; for row-store tables, it is more appropriate to explicitly declare SKIP_INDEX only on a few key columns, confirm that there is genuinely significant room for skipping, and then consider whether SUM is needed—avoiding redundant configuration across too many columns.

If the query is mostly point lookups, or the filter conditions cover almost all data blocks, you should generally prioritize investigating partition design, SQL phrasing, or B+ tree indexes first. If these problems that should be solved first have not yet been resolved, Skip Index usually cannot make up for that layer.

Which Scenarios Are Usually Suitable for Enabling It

Skip Index is better suited to analytical queries that have already entered the scan phase but where a substantial portion of data blocks still does not need to be read — not to every query with a WHERE clause. Common situations include:

The execution plan has already landed on a large-range scan within a partition, with the bottleneck manifesting as a high scan-read volume and a low post-filter data retention ratio.
The filter conditions themselves cannot precisely position a small number of rows, but they can let a portion of data blocks be excluded in advance via block-level statistics.
The filter fields are stable and appear over the long term in analytical workloads such as reporting and batch jobs.
A few key columns have already been identified on a row-store table, and these columns have significant room for skipping.

Which Scenarios Usually Show Limited Effect

In the following scenarios, Skip Index often struggles to bring noticeable improvement:

The table itself is small, or analytical queries are rare and the workload is mostly point lookups.
The filter conditions require scanning almost all data blocks. In this case, even with Skip Index, most blocks cannot be excluded in advance, so the disk reads and decoding that can be saved are limited.
The filter fields used by queries change frequently, making it hard to consistently hit the columns maintained by Skip Index.
The data is dominated by random updates. When data within a certain range is frequently updated at random, the reference value of the statistics declines and fewer data blocks can be skipped, yet the maintenance overhead does not disappear accordingly.
When preceding optimizations such as partition pruning, B+ tree indexes, or scan paths have not been done well, enabling Skip Index alone often yields little.

Applicability Boundaries and Additional Costs

When deciding whether to enable it, you also need to factor in the additional costs and the pace at which it takes effect:

The system must pay extra storage for these statistics, and they also need ongoing maintenance during compaction.
Skip Index does not take effect across the entire table immediately upon configuration. After an attribute change, it requires progressive compaction to gradually rewrite the intermediate index layer and build statistics on the baseline side step by step; during this process, the portion that has already taken effect can participate in queries first. When judging whether it is effective, you should not rely solely on results from the launch day or before compaction is complete.
It cannot replace partition pruning, B+ tree indexes, or appropriate scan path selection.
Before adding Skip Index to a column, first confirm that it genuinely corresponds to a stable query scenario; an oversized Skip Index on a single table can also raise errors.
If the business strongly depends on the latest data that has not yet been compacted into the baseline, it is not appropriate to judge whether Skip Index is effective based on this data alone.

How to Tell Whether It Is Working After Enabling It

A common approach is to first identify stable workloads, configure Skip Index on only a few columns, wait for compaction to progress for a while, and then decide whether to keep it based on query performance and system metrics. Focus on the following points:

After Skip Index is specified for a column, statistics are gradually built during compaction; you should compare the scan volume and response time of typical analytical SQL after compaction has progressed.
The Skip Index-related storage statistics in execution plan monitoring — for example, the number of data blocks skipped in advance by Skip Index — can be used to confirm whether skipping actually happens.
During business peaks, observe whether short transactions are still noticeably dragged down by analytical queries.

Therefore, judging whether Skip Index is effective is better done after statistics have gradually been built on the baseline side, combined with actual query performance.

Don’t Look Only at the Response Time of a Single SQL

In HTAP scenarios, a drop in the response time of a single SQL statement does not mean the tenant as a whole is more stable. When deciding whether Skip Index is worth keeping, you also need to consider the compaction burden, storage footprint, and peak-time resource contention together:

If analytical queries improve slightly but compaction slows down, or online transaction latency shows noticeable fluctuations, it may not be worth it overall.
If a single SQL statement gets faster but the cluster still shows noticeable fluctuations during peaks, it often means the assessment still lacks observation of resource usage and contention.
Beyond the response time of a single SQL statement, you usually also need to look at I/O, CPU, and background task performance at the same time.

Summary

Under the LSM-Tree architecture, OceanBase unifies row store, column store, and transactional capabilities: column store mainly answers “which columns to read,” while Skip Index goes a step further on both row and column stores to answer “which data blocks can be skipped.” Discussing it separately in HTAP scenarios is warranted because the extra cost of wasted scanning spills over to short transactions and compaction tasks; and given that the execution plan is already reasonable, further reducing wasted reads in analytical queries at the storage layer remains an optimization worth considering.

Skip Index is not a universal acceleration capability, but within clearly defined applicability boundaries, it remains an option in mixed-workload governance — one that can be rolled back through configuration and is genuinely worth carefully evaluating before adoption.

When Agent Skills Start Accessing Memory: A Technical Path with MoonBit and Wasm

Charles Wu — Thu, 09 Jul 2026 13:01:43 +0000

Agent memory doesn’t fail on storage. It fails when Skills can read and write without boundaries.

For a while now, we’ve been circling the same question inside seekdb (OceanBase’s AI-oriented database) and PowerMem (OceanBase’s memory management layer for agents):

How do you give an AI agent memory that actually works over the long run?

An agent that starts from zero every conversation is a poor long-term worker. It needs to remember user preferences, project context, past decisions, and lessons from tool use — including what worked and what didn’t.

Early agent memory setups were usually simple: local files, Markdown logs, a workspace folder, or whatever still fit in the current context window.

That’s fine for experiments. But once an agent is used day after day, the cracks show up fast. How do you sync memory when you switch devices? How do you carry experience across workspaces? How do teams share what their agents learned? And how do you separate user-private memory, project memory, and team memory?

There’s a deeper issue, too: memory isn’t solved by “store everything.”

If an agent just piles up chats, logs, and task traces, it doesn’t automatically get smarter. Often the opposite happens — you end up with more memory and less ability to find what matters when it matters.

So the hard part isn’t persistence. It’s ongoing organization, precise retrieval, and safe reuse.

That’s the problem seekdb and PowerMem are built around.

seekdb focuses on the data and retrieval layer underneath memory — combining structured data, full-text search, and vector search so an agent can pull back what’s relevant to the current task instead of shoveling history back into context.

PowerMem sits closer to a Memory Management Layer for agents. It isn’t only about storage. It’s about the full lifecycle: what to extract, what to keep, what’s stale, what to merge, and what’s worth reusing in future tasks.

User preferences, project decisions, bug-hunt notes, team workflows, tool habits — these shouldn’t die in a single chat. They should graduate from ephemeral context into durable memory the agent can use later.

At larger scale, in production environments that demand higher reliability, OceanBase (a distributed SQL database) can also carry long-term memory with stronger operational guarantees.

In short: seekdb, PowerMem, and OceanBase address the data plane, retrieval, and lifecycle of agent memory.

But once memory is real inside an agent runtime, a new class of problems appears.

After memory is a service, Skills become the new boundary

In an agent runtime such as OpenClaw — where agents load Skills, call tools, and run tasks — memory isn’t just an internal history blob. Third-party Skills may want access to it too.

One Skill might need project history. Another might summarize user preferences. Another might write new lessons back into memory. Others might combine memory with external tools.

That raises very practical questions:

Can a third-party Skill read user-level memory?
Can it write workspace memory?
Can it touch team-shared memory?
Can it obtain database URLs, API keys, or direct connection details to the memory service?

Without clear answers, “memory as a service” can increase risk instead of reducing it.

Long-term memory may hold preferences, project history, team decisions, business context — and also code snippets, configs, error logs, and execution notes. Once that material becomes a durable asset, it can’t be freely read or modified by every Skill.

From an infrastructure perspective, the question isn’t only:

How do we store and retrieve memory?

It’s also:

How do we distribute, execute, and authorize third-party Skills that access memory?

The problem isn’t who can call an API

Technically, calling a memory API is easy.

JavaScript works. Python works. Go works. Rust works. MoonBit (a statically typed language with multi-backend compilation, including WebAssembly) works too.

The real issue isn’t which language can send HTTP. It’s how the runtime enforces boundaries when any third-party Skill can reach memory.

If a Skill holds a PowerMem API key and connects directly, its blast radius is hard to control. It might query memory it shouldn’t see, write where it shouldn’t write, exfiltrate results over the network, or bypass runtime audit — expanding access without the user understanding what happened.

A better model: Skills don’t connect to memory services directly. They request controlled capabilities through the runtime.

For example:

memory.search — retrieve memory within an authorized scope
memory.add — write new memory
memory.update — update existing memory
tool.call — invoke external tools on an allowlist
log.emit — emit controlled logs

In this model, a Skill doesn’t know how to reach PowerMem, seekdb, or OceanBase, and it doesn’t hold API keys. It only knows which capabilities it has been granted.

The runtime performs the actual access.

It checks user, agent, workspace, scope, and Skill permissions; allows or denies the call; and when needed, trims, redacts, or blocks results — while writing an audit trail.

Memory stops being a global resource any Skill can read or write. It becomes a set of capabilities the runtime explicitly grants.

That’s the shift from Memory API to Memory Capability.

An API is an interface.

A capability is something granted — scoped, auditable, and revocable.

Why we started looking at MoonBit + Wasm

A quick intro to MoonBit: it’s a modern, statically typed language aimed at engineering ergonomics and multi-target compilation. It can compile to WebAssembly (Wasm) — a portable binary instruction format — and is a good fit for packaging small, well-defined units of logic.

For agent Skills, we care about MoonBit less as “yet another language” and more as a way to make Skills easier to govern.

A third-party Skill shouldn’t be a one-off script. Over time it may be distributed, installed, reviewed, and granted access to memory, tools, the filesystem, or external services. It should declare: what inputs it accepts, what it returns, what errors it can surface, which permissions it needs, and which memory scopes it touches.

If those boundaries live only in dynamic JSON, prompt conventions, or developer discipline, you can ship quickly — and struggle to govern later. MoonBit’s static typing and compile-time checks can make boundaries explicit before runtime.

Compiled to Wasm, a Skill can run as a lightweight, sandbox-friendly module. It still doesn’t get API keys or direct connections to PowerMem, seekdb, or OceanBase. When it needs memory, it goes through runtime capabilities — same as before.

MoonBit → clearer, more engineering-friendly Skill definitions
Wasm → a module shape that sandboxes well
Runtime → real authorization, audit, and enforcement

Today, many Skills are written in JavaScript or Python. Mature ecosystems, fast iteration — great for proving ideas.

But an open third-party Skill marketplace pays a governance tax: runtimes to install, version skew, long dependency chains, harder supply-chain review.

Native binaries are the other pole. Go, Rust, Zig, C/C++ can produce clean deploy artifacts — excellent for high-trust infrastructure: runtimes, CLIs, daemons, plugin managers, API gateways.

If every third-party Skill shipped as a native binary, though, it behaves more like a normal host process: filesystem, network, environment variables, subprocesses, direct DB connections. Audit and trust costs go up.

We wanted something between scripts and native binaries:

Light to distribute
Clear execution boundaries
Small default privileges
External power only when the runtime grants it

That’s why MoonBit + Wasm landed on our radar.

Wasm is often introduced as “fast code in the browser.” Fair — but on the server, edge, and plugin side, the interesting property is sandboxed execution.

Without explicit host capabilities, a Wasm module doesn’t get the filesystem, network, environment, or subprocesses by default. What it can do depends on what the runtime exports.

That maps cleanly to third-party Skills.

The platform doesn’t run a full-privilege host program. It loads a module in a sandbox: inputs in, computation, results out. Memory, tools, and external services require explicit capabilities.

MoonBit + Wasm doesn’t magically solve security by itself.

Permission checks, audit, redaction, and revocation still belong to the runtime. Wasm supplies a sandbox-friendly module; MoonBit supplies clearer types and boundaries.

Together, they make it easier to fold third-party Skills into capability-based memory access.

From memory to Skills — the problem leads here naturally

Back to the starting question: how do you make agent memory truly durable?

Step one: memory can’t live only in context or local files. It must persist, be searchable, migrate, and reuse.

That’s seekdb, PowerMem, and OceanBase.

Step two: once memory lives inside a runtime like OpenClaw and third-party Skills consume it, you need answers to:

Who can access memory?
What scope — user, workspace, team?
Write access — or read-only?
Can results leave the trust boundary?
Can you audit what happened?

Memory shouldn’t be a casually callable API. It should be a runtime-granted capability.

In that picture:

seekdb — retrieval substrate (structured + full-text + vector)
PowerMem — extract, manage, update, reuse
OceanBase — production-grade persistence at scale
MoonBit + Wasm — Skills as bounded, distributable, sandboxed units that access memory only through the runtime

This isn’t “bolt seekdb onto MoonBit” for its own sake.

It’s what happens when agent memory becomes real in production runtimes: Skill execution and authorization become the next layer.

MoonBit + Wasm is one serious path on that layer.

References

PowerMem: https://github.com/oceanbase/powermem
seekdb: https://github.com/oceanbase/seekdb
MoonBit: https://www.moonbitlang.com/

Speeding Up Columnar Queries in OceanBase: Solving the Real-Time Update Bottleneck with Delete_Insert Table Mode

Charles Wu — Thu, 09 Jul 2026 12:22:13 +0000

When to trade write cost for scan stability — choosing Delete_Insert mode for update-heavy columnar tables

Columnar tables are a proven win for bulk scans and analytical aggregation. But the moment a workload involves frequent updates, query performance tends to drift downward as incremental data piles up. OceanBase’s Delete_Insert table mode tackles this by splitting every update into two complete rows at write time, one that deletes the old version and one that inserts the new. The query path no longer has to merge row by row, so scan efficiency stays steady even under continuous update load. This article starts from the root cause, then walks through the design, how to use it, measured performance, and where it fits.

Why Columnar Queries Slow Down Over Time

Many teams go through a similar arc after moving analytical queries from row store to column store. Right after the switch, bulk scans and aggregation queries get dramatically faster, and everyone is happy. But as upstream data keeps flowing in, especially once the workload involves frequent status changes, such as order states, inventory counts, or backfilled user tags, queries that used to return in seconds gradually stretch to tens of seconds. The more updates there are, the slower queries get, and the degradation is persistent. It does not recover on its own.

To understand the root cause, it helps to look at how an ordinary columnar table handles updates. The columnar storage format is naturally suited to large sequential scans, but it is not built for modifying a single row in place. To keep writes efficient, ordinary columnar tables typically use a read-time merge strategy: the baseline data already written to disk and organized by column stays untouched, while every new update and delete is first written into a separate incremental structure. When a query arrives, the storage engine has to compare and merge the baseline and incremental data row by row on the primary key, reconstruct the latest version of each row, and only then hand the result up for filtering and aggregation.

The cost of this mechanism shows up on the query path. When primary keys in the incremental data overlap with those in the baseline, a process that could otherwise scan block after block sequentially has to stop and check, row by row, whether each row was updated or deleted and which version to keep. The very strengths of columnar storage, block-level filtering, columnar computation, and vectorized execution, get repeatedly interrupted because the engine has to wait for multiple data sources to be merged before it can continue. The more incremental data there is, the more often these interruptions happen, and the more noticeable the slowdown becomes.

How the Industry Approaches the Problem

There are a few broad schools of thought for resolving the tension between columnar storage and frequent updates.

One approach sticks with read-time merge. ClickHouse’s MergeTree engine is append-oriented; updates and deletes are deduplicated through background merges or by the FINAL keyword at query time, which still means handling multi-version data while reading. TiFlash uses a similar two-layer LSM structure (Delta plus Stable), with incremental data eventually folded into the Stable layer through full merges. Both perform well when data is mostly appended, but once high-frequency updates enter the picture, the merge cost on the query path is just as unavoidable.

Another approach is Merge-on-Write. StarRocks primary key tables are a typical example: on write, the primary key index locates the position of the old row, marks it as deleted in a delete bitmap, and appends the new row to a new data file. At query time, the engine simply skips old data using the bitmap, with no row-by-row merge. Hologres does something similar, coordinating three structures: a primary key index, columnar data files, and delete bitmap files. This keeps query cost low, but it requires maintaining a global bitmap structure on the write path and implementing multi-version visibility control for it.

OceanBase takes a middle path. As an HTAP database, OceanBase sorts baseline data by primary key during merges, which means row numbers shift as merges happen. Relying on row numbers to locate a delete bitmap, as StarRocks does, would require rebuilding the mapping after every merge, which adds considerable complexity. So OceanBase chose an implementation that does not depend on a delete bitmap: at write time, each update is split into a delete plus an insert with full column data stored, and at query time each incremental data block filters independently, then skips old versions using delete markers. This avoids maintaining a global bitmap, keeps changes to the existing LSM-Tree architecture minimal, and already covers the performance needs of most real-time update scenarios.

Getting to Know the Delete_Insert Table

The core idea of the Delete_Insert table is simple: rather than stitching data together at query time, split it apart and store it ready at write time. Specifically, every UPDATE is rewritten as two actions, delete the old row and insert the new row. Both rows land in storage in full, all-column form, and a delete marker distinguishes which version is valid. As a result, every data block is self-contained at query time. Filter conditions can be pushed directly down to each block and evaluated independently, with no need to merge against baseline data first; at the end, the engine only has to skip old versions based on the delete markers. In other words, Delete_Insert turns read-time merge into write-time split, trading a bit more write cost for large, continuous scans on the query path.

Enabling a Delete_Insert table is lightweight. You specify it through the merge_engine table attribute at table creation, with no extra database-level or tenant-level switch required. It is supported only for columnar tables, and the attribute cannot be changed after the table is created, so it needs to be chosen deliberately at design time.

The most common use is to enable Delete_Insert on a pure columnar order table:

CREATE TABLE orders (
  order_id      BIGINT       NOT NULL,
  user_id       BIGINT       NOT NULL,
  status        VARCHAR(16)  NOT NULL,
  amount        DECIMAL(18, 2),
  updated_at    TIMESTAMP,
  PRIMARY KEY (order_id)
) merge_engine = delete_insert
  WITH COLUMN GROUP (each column);

If the upper layer needs both columnar storage for analysis and row storage for point lookups, you can create the table with redundant columnar and row storage, maintaining both organizations in the same table:

CREATE TABLE orders (
  order_id      BIGINT       NOT NULL,
  user_id       BIGINT       NOT NULL,
  status        VARCHAR(16)  NOT NULL,
  amount        DECIMAL(18, 2),
  updated_at    TIMESTAMP,
  PRIMARY KEY (order_id)
) merge_engine = delete_insert
  WITH COLUMN GROUP (all columns, each column);

If you do not specify merge_engine, it defaults to partial_update, which follows the traditional read-time merge mode. Only columnar tables that explicitly declare merge_engine = delete_insert will persist updates as delete the old row plus insert the new row.

How Storage and Queries Differ

The difference between Delete_Insert and an ordinary columnar table comes down to two paths: the write side and the query side. Because the cost lands in different places, the two suit different scenarios.

The write-side difference is about how much gets written. On UPDATE, an ordinary columnar table records only the modified columns plus the primary key. Unchanged columns are not rewritten, so the incremental data stays small, but each incremental record is incomplete on its own and must rely on baseline data to reconstruct the full row. A Delete_Insert table is different: every UPDATE writes two rows, an old version carrying a delete marker and a new version, both in full-column form. The incremental data is larger, but every row is self-contained and independently readable.

The query-side difference is about how much gets computed. To run a query, an ordinary columnar table must first merge baseline and incremental data row by row on the primary key to determine the latest value of each row, and only then apply the filter conditions to the result. During this process, the block-level filtering and vectorized execution that columnar storage relies on most get frequently interrupted. In a Delete_Insert table, because each incremental data block is complete in itself, filter conditions can be pushed directly down to the block and evaluated independently, with blocks not interfering with one another; at the end, the engine only needs to skip old versions of the same primary key based on delete markers.

The table below lays the key differences side by side:

In short, Delete_Insert shifts part of the cost that the query used to bear forward onto the write side. The payoff is that bulk scans are no longer frequently interrupted, and query time does not keep degrading as updates accumulate. That is precisely what makes it a good fit for real-time analytics.

Performance Gains

To show the gap between the two modes under update load directly, we ran a comparison on a columnar table of 100 million rows. The ordinary columnar table and the Delete_Insert columnar table have identical schemas, differing only in merge_engine. The benchmark query is a full-table scan with a filter:

SELECT * FROM test_column_table WHERE c3 > 99999990;

By controlling the proportion of rows touched by random UPDATE or random INSERT, we observed how the two tables behaved at different incremental scales. All timings below are in milliseconds.

The first scenario is random UPDATE. As the proportion of updated rows rose from 0% to 20%, query time on the ordinary columnar table grew rapidly, while the Delete_Insert table fluctuated only slightly.

With no updates at all, the two are essentially even, which shows that Delete_Insert does not sacrifice baseline scan performance on static data. As soon as 1% of rows are updated, the ordinary columnar table jumps to the 20-second range because of merge overhead, while Delete_Insert stays in the millisecond range. At a 20% update ratio, the ordinary columnar table degrades to the minute range, while the Delete_Insert table only rises from milliseconds to seconds. The gap keeps widening as updates grow, consistent with the expectation that read-time merge overhead grows linearly, or even super-linearly, with incremental scale.

The second scenario is random INSERT. New data does not create primary key conflicts with the baseline, but it still forms incremental data blocks that have to be scanned.

In the INSERT scenario, the Delete_Insert table is almost unaffected by the incremental ratio, because each newly inserted data block is complete in itself and filter conditions can be evaluated directly on the block. The ordinary columnar table still has to process these new data blocks along the primary key path, so its timing keeps climbing as the insert volume grows.

Putting both sets of results together, the conclusion is clear: with zero increments the two modes are even, but once there is more than 1% updated or newly inserted data, the Delete_Insert table’s query time is significantly lower than the ordinary columnar table’s. Under a high update load at the 20% level, the gap reaches two to three orders of magnitude. Put another way, Delete_Insert keeps columnar query performance from continuously degrading as write volume accumulates.

When to Use a Delete_Insert Table

Once you understand the mechanism and performance characteristics, how do you decide in practice? Three kinds of scenarios are especially well suited to Delete_Insert tables.

The first is the real-time data warehouse. The upstream continuously syncs changes from business databases through CDC or Kafka, while the downstream needs to run analytical queries at any time, with both data freshness and query response guaranteed. Ordinary columnar storage easily falls into query degradation under this write-while-read load, whereas Delete_Insert moves the cost forward to the write path, which matches this pattern well.

The second is HTAP mixed workloads. The same table has to support updates from online business while also serving BI reports and ad hoc queries. Ordinary columnar storage struggles to do both, and row storage is not suited to large-scale scans. A Delete_Insert columnar table paired with redundant row storage lets point lookups go through row storage and analytics go through columnar storage, without the two interfering.

The third is high-frequency status update workloads. Fields such as order status, device status, and user tags get repeatedly backfilled and overwritten. An ordinary columnar table faces a large amount of incremental data with overlapping primary keys, where merge overhead is heaviest; Delete_Insert avoids exactly this by writing two full-column rows and filtering by block at query time.

Conversely, if a table is essentially read-only after writing with almost no updates, or is small in scale with simple queries, then the extra storage and write cost of enabling Delete_Insert is not worth it, and an ordinary columnar table is the better choice.

There is a simple rule of thumb for deciding: if a columnar table sees continuous UPDATE or DELETE and its query performance shows observable degradation over time, it is worth trying the Delete_Insert table mode. Sometimes performance tuning does not require re-architecting anything; choosing the right storage mode is enough to solve the problem.

Multi-Model Data Types: The Foundation for AI-Driven Analytics

Charles Wu — Thu, 09 Jul 2026 11:52:50 +0000

How to eliminate AI data silos — unifying vector, text, and relational queries in one engine

When a database is no longer confined to transactions and reporting, but instead has to power AI inference, knowledge retrieval, and real-time analytics all at once, the data it must understand reaches far beyond the relational table. Vectors, full-text, JSON, arrays, and large objects are becoming the basic vocabulary of the data layer in the AI era. OceanBase takes the approach of natively supporting these multi-model data types inside a single database, so that structured and unstructured data share the same transaction engine and SQL entry point, eliminating the need to shuttle data between systems. This article walks through the technical details of these capabilities, how fused queries work, and how they play out in real AI workloads.

1. Industry Trends and Technical Challenges

The rapid adoption of GenAI is redrawing the boundaries of what a data layer must do. Training and inference on large models consume not only structured transactional data, but also text, images, audio and video, logs, and vectorized representations. A single end-to-end AI request — whether RAG retrieval, intelligent recommendation, or an Agent workflow — often has to fetch a user profile, a behavioral sequence, document fragments, and their corresponding embeddings together, all within milliseconds. This imposes three direct requirements on the data layer: native support for multi-model data types, real-time freshness so data is analyzable the moment it lands, and the ability to combine structured filtering, vector recall, and full-text matching into a single fused query.

Yet the mainstream approach today still stitches together several specialized systems: a relational database for transactional data, a vector database for embeddings, object storage for raw files, and a message queue to tie them together. Each system looks fine on its own metrics, but stacking them creates a fresh round of data fragmentation. The moment the business needs a cross-modal composite query — say, “filter a target segment by user profile, then run a vector nearest-neighbor search over their review text” — data has to be synchronized and joined across multiple systems, and freshness, consistency, and development complexity all blow up at once.

The governance cost is just as significant. With multiple metadata, permission, and lineage systems coexisting, enterprises struggle to build a unified view of their data assets. Different engines expose different query interfaces (Spark SQL, Flink SQL, dedicated vector APIs), so access control and governance standards fragment accordingly. Add the redundant storage, duplicated computation, and operational overhead of running several platforms in parallel, and a unified multi-model architecture shifts from an architectural preference to a hard TCO requirement.

2. A Panorama of OceanBase Multi-Model Data Types

Answering these challenges is not about simply wrapping several engines together; it is about letting multiple data models coexist natively within one storage, transaction, and SQL system. OceanBase is a distributed, multi-model integrated database that, in real-time analytics scenarios, handles structured, semi-structured, and unstructured data on a single SQL engine. Because all multi-model data shares one ACID transaction engine and one unified SQL entry point, cross-system data movement is eliminated at its root, and “one copy of data, many dimensions of analysis” becomes the default. Below we look at the role each of the five core data types plays.

2.1 LOB (Large Object)

LOB is the storage foundation of multi-model capability. The multimodal data in AI scenarios — images, text, audio, video — are all essentially large objects, and how efficiently they can be stored and accessed directly determines the throughput of model training and inference above. Through its SQL interface, OceanBase supports up to 512 MB per column and provides efficient TB-scale large-object access via the DBMS_LOB package, allowing bulky data such as video frame sequences, long documents, and raw logs to sit alongside structured fields. How solid this layer is determines how far the multi-model indexes and operators above it can go.

2.2 JSON

JSON plays a different role across three kinds of scenarios. In transactional workloads it serves as a schemaless, flexible column, well suited to write paths where the business structure evolves frequently. In analytical workloads, JSON multi-value indexes make flexible computation over multi-dimensional tags possible, without rebuilding the table schema for every tag combination. And in AI workloads, JSON acts as the bridge between models and applications — the standard input and output format for Agents and workflow pipelines.

Under the hood, OceanBase offers a rich set of computational expressions and multi-value indexing, so that complex JSON path expressions can execute efficiently with index support. On the storage side, it uses a JSON Binary format to optimize random reads and writes, and builds structured encoding on top of it: by extracting shared Schema information from structurally similar JSON documents, it further improves the compression ratio and markedly speeds up queries based on JSON Path. As a result, JSON retains its flexibility while approaching the execution efficiency of native columns under analytical loads.

2.3 Array / Roaring Bitmap / Map

Collection types address a class of problems that traditional relational models handle poorly: high-cardinality tags combined with large-scale analytics. Roaring Bitmap and Map target high-end data-mining scenarios such as large-scale tag analysis, audience segmentation, and deduplicated aggregation, using bitmap compression and on-demand decoding to dramatically cut memory and compute overhead. Array is a better fit for workloads that must preserve order and duplicate elements — log retrieval, behavioral trails, multi-dimensional tags — letting a variable-length event sequence be expressed within a single field. Pushing these types down into the database kernel means that recommendation, risk control, and similar workloads no longer need extra middleware for every collection operation.

2.4 Vector Type

The vector capability follows a fully in-house path built on vector algorithms plus the database, rather than bolting an external vector engine onto the side of a relational database as a plugin. The direct payoff of this choice is that the vector index can be deeply coupled with the relational query optimizer and transaction system: data is searchable the moment it is written, and index maintenance shares the same consistency semantics as DML. In public benchmarks such as VectorDBBench, OceanBase’s vector retrieval performance surpasses that of typical open-source vector database competitors, which means moving vector workloads into the primary database does not come at the cost of performance.

2.5 Full-Text Search

Full-text indexing is a native component in OceanBase, covering keyword-centric retrieval scenarios such as document search and log analysis. More importantly, full-text indexing can work alongside vector retrieval within a single SQL statement to form Hybrid Search: the keyword side handles exact matching and explainability, while the vector side handles semantic recall and similarity ranking. This combination maps precisely onto the dual demand of knowledge retrieval — to find accurately and to understand the meaning — and forms a key foundation for RAG-style applications.

3. How Multi-Model Capabilities Work Together in a Single SQL

Offering multiple data types within one system is not enough; the real value lies in whether they can work together in a single SQL statement. OceanBase is designed so that a multi-model query is no longer “an assembly of several single-model queries,” but a single plan understood by the optimizer and execution engine as a whole.

3.1 Fused Query Patterns

The most common fusion pattern is joint analysis of structured and semi-structured data. Standard SQL can directly filter, aggregate, and join over JSON fields and Array columns, with multi-value indexes ensuring such queries do not degrade into full table scans. The second pattern combines structured conditions with vector similarity search — for example, using a WHERE clause on a user-profile table to narrow down a segment, then ranking by vector distance in ORDER BY, all without any cross-system join. The third pattern is hybrid retrieval that uses structured filtering, full-text search, and vector retrieval together: keyword recall and semantic recall are merged within one query, and the optimizer decides the execution order. This is the core source of RAG recall quality.

The following is illustrative pseudo-code showing what a fused query looks like at the syntax level:

SELECT d.doc_id, d.title,
       MATCH(d.content) AGAINST ('OceanBase multi-model') AS bm25_score,
       VECTOR_DISTANCE(d.embedding, :query_vec) AS vec_dist
FROM   docs d
WHERE  d.tenant_id = 1001
  AND  JSON_VALUE(d.tags, '$.category') = 'whitepaper'
  AND  ARRAY_CONTAINS(d.audience, 'architect')
ORDER  BY 0.4 * bm25_score - 0.6 * vec_dist DESC
LIMIT  20;

This single query uses structured conditions, a JSON path, an Array containment check, full-text BM25 scoring, and vector distance all at once, and every operator is scheduled within one execution plan by a unified optimizer.

3.2 How the Underlying Engine Accelerates Multi-Model Analytics

Making these query patterns genuinely fast relies on several mutually reinforcing engine features. The first is a unified optimizer and execution engine: multi-model queries share the same cost model, vectorized execution engine, and parallel framework. The optimizer maintains a separate cost metric for columnar scans, automatically chooses a row-store or column-store path based on the shape of the data, and uses AUTO DOP to decide the degree of parallelism — no manual tuning required.

The second is a unified storage engine with hybrid row-column storage: incremental data accepts transactional writes in row-store form, while baseline data lands in column-store to speed up analytical scans, and the user’s DML operations remain completely unaware of the distinction. Multi-model types follow the same architecture; JSON, Array, and vectors all benefit on the column-store side from columnar compression and the scan savings of Skip Index.

The third is the vectorized execution engine: batch processing combined with SIMD instruction acceleration, with the batch size adapting to data characteristics, and TP and AP sharing the same execution framework. This mechanism applies not only to traditional numeric aggregation but also to multi-model operators such as JSON Path evaluation, Array element matching, and vector distance computation, so that “one SQL across multiple models” requires no performance compromise.

The fourth is a decoupled storage-compute architecture: the full dataset resides in object storage to keep costs down, while hot data is served through a local cache to guarantee query performance, and multi-model data needs no special treatment at this layer. Layered on top of the kernel-level integration of Btree indexes, JSON multi-value indexes, full-text indexes, and vector indexes, the entire multi-model query path — from storage through indexing to execution — is closed within a single stack.

4. Application Scenarios

Placing these capabilities into concrete workloads reveals how much a unified multi-model architecture simplifies the end-to-end design.

In intelligent recommendation and personalized marketing, the user profile is typically a set of structured fields, the behavioral sequence is well suited to Array, and product descriptions exist as vectors. With OceanBase’s fused queries, the recall stage can complete profile filtering, behavior-pattern matching, and product vector nearest-neighbor search in a single SQL statement, then hand off to a ranking model for scoring — the whole pipeline with no back-and-forth data movement between an OLTP database and a vector database.

In RAG knowledge-based Q&A, full-text search over enterprise documents, paragraph vector recall, and structured metadata filtering can all converge into one database. The retrieval stage leverages both keyword and semantic signals, permission and tenant boundaries are guaranteed natively by the database, and the context fragments needed to generate an answer can be fetched in a single query — noticeably reducing both the latency and the complexity of the end-to-end pipeline.

In real-time risk control, transaction records accept TP writes in row-store form, user tags carry multi-dimensional attributes as JSON and Bitmap, and anomaly patterns are represented as vectors. When a transaction arrives, the engine can complete rule-based hard-condition filtering, tag-based segmentation, and vector-based matching of similar suspicious patterns in milliseconds, fusing the multimodal signals into a single comprehensive judgment — avoiding the serial calls between a rule engine and a vector service that traditional architectures require.

In multimodal data lake integration, OceanBase connects to external data sources such as CSV, Parquet, ORC, and HDFS through its external table capability, bringing unstructured data that previously sat in the data lake into unified SQL analysis. No separate ETL pipeline is needed between the structured primary database and external lake data; teams can access internal and external tables with the same SQL semantics, turning the data lake from an “analytics island” into just another storage medium under a unified engine.

5. Summary and Outlook

Returning to the original premise, a unified multi-model architecture matters because it directly answers the two questions the AI-era data layer must solve: how to eliminate a new round of data silos, and how to reduce architectural complexity without sacrificing freshness or consistency. OceanBase’s differentiated positioning is not “hanging yet another vector engine next to the database,” but full-stack native integration of multi-model capabilities — from storage format and indexing system to optimizer and execution engine.

From an engineering standpoint, the value of this integration only grows as AI applications deepen: when Agents and workflows become the new data consumers, a unified SQL entry point, a unified permission model, and a unified execution engine will directly determine how fast the applications above can iterate. OceanBase will continue to strengthen full-text search, fused queries, and large-scale unstructured data processing, and round out a more complete set of data service interfaces around AI Agent scenarios — making “one copy of data, serving many forms of intelligence” a capability that engineering teams can rely on by default.

DEV Community: OceanBase User Group

Online Horizontal Scaling: How OceanBase Adds Nodes Transparently

How OceanBase Horizontal Scaling Works

Adjust One Parameter to Scale Out and Rebalance Data Automatically

How Does OceanBase Maintain Consistency During Data Migration?

Why Are Applications Unaware of the Change?

Performance Scales Near-Linearly as Nodes Are Added

Scaling Duration and Business Impact

When Should You Use Horizontal Scaling?

Summary

Further Reading

OceanBase Tenant-Level Scaling: Adjust Resources in Seconds

How OceanBase Manages Tenant Resources

Scaling by Adjusting Unit Configs

Why Scaling Is Fast and Transparent

Case Study: How POP MART Handles Launch-Day Traffic Spikes

Summary

Further Reading

No Manual Sharding: How OceanBase Builds Scalability into Its Database Kernel

The Pros and Cons of Database Sharding

Can Cloud-Native Databases Put an End to Sharding?

OceanBase: Hiding Distributed Complexity in the Database Kernel

1. Storing One Large Table Across Multiple Machines

2. After Data Is Distributed, How Does a Query Find the Right Node?

3. What If the SQL Query Does Not Include the Partitioning Key?

4. Can ACID Still Be Guaranteed for Cross-Node Reads and Writes?

5. Will Data Distribution Become Unbalanced as Data Grows or Nodes Are Added and Removed?

Architectural Comparison of Three Approaches

OceanBase in Production: Scaling a Large-Scale Social and Live-Streaming Platform

Summary

Further Reading

Start Small, Scale Big: Inside OceanBase’s Distributed Architecture

Key Takeaways

Database Scaling Often Arrives Earlier Than Expected

Existing Scaling Paths Still Involve Trade-offs

OceanBase’s Approach: One Architecture for Standalone and Distributed Deployment

Dynamic Log Stream: Combining Standalone Performance with Distributed Capability

Optimizing for the Common Path

Business Value and Best-Fit Scenarios

Adoption Path: Start with a Small Validation

Further Reading

OceanBase Real-Time Analytics: Ecosystem Compatibility and Supporting Capabilities

1. Introduction

2. Data Integration: Ingestion and Flow of Multi-Source Data

2.1 Batch Import

2.2 External Tables and Data Lake Integration

2.3 Real-Time Synchronization and Data Collection

2.4 Flink Connector Ecosystem

2.5 Spark Connector Ecosystem

3. SQL Compatibility: Lowering the Barrier to Entry

3.1 MySQL Compatibility

3.2 Oracle Compatibility

3.3 Standard SQL Analytical Capabilities

4. Database Management and Operations Ecosystem

4.1 Scheduling and Orchestration

4.2 Observability

4.3 Multi-Tenant Resource Management

5. BI and Data Visualization

6. AI/ML Integration

7. Data Governance

8. Summary

A Guide to AP Query Diagnosis and Tuning

1. Why AP Queries Need a Dedicated Tuning Approach

2. Common AP Query Problem Types

3. Diagnostic Framework

Diagnostic Toolchain

Core Analysis Dimensions for Each Tool

4. Optimization Capabilities Overview

Poor Join Order or Algorithm Selection

Excessive Scan Volume or Ineffective Indexes

Excessive Aggregation and Sort Overhead

Unexpanded Subqueries

Distributed Parallelism

Resource Management and Plan Stability

5. Tuning Recommendation Priority

6. Closed-Loop Process from Diagnosis to Optimization

Governing DOP Under Mixed Workloads

Introduction: Why Parallel Execution Deserves Attention

1. What DOP Actually Optimizes in OceanBase

1.1 Which Execution Stages DOP Mainly Accelerates