How to keep real-time analytical queries cost-stable under sustained writes
Two Pain Points of Real-Time AP
Real-time AP is about whether a system can keep delivering stable analytical capabilities while writes, changes, and queries all happen concurrently over the same dataset. For the business, the key question is not “can the SQL execute,” but rather: as data keeps changing, can the system still maintain low-latency responses, high throughput, and controllable resource costs.
From an engineering perspective, the challenges of real-time AP mainly show up at two levels.
First, the analytical target is constantly changing. Queries must work directly against high-frequency, dynamically updated data rather than a stable, static snapshot. Under dynamic data, the cost of reading data grows, which in turn increases the compute burden.
Second, when there is a large amount of dynamic data, suboptimal execution plans become more likely. An unreasonable execution plan multiplies cross-node transfer and compute pressure.
Therefore, the key to real-time AP is not just making a single SQL statement run fast, but continuously controlling the overall cost of analytical queries under sustained writes.
OceanBase Handles a Dataset Where “Baseline + Incremental” Coexist
In real-time AP scenarios, OceanBase does not process a single static dataset, but rather a dataset where “baseline data + incremental data” coexist. Baseline data that can be reused stably corresponds to the Major SSTable; newly written and newly changed data first goes into the MemTable, then converts into the Minor SSTable, forming the incremental path.
The significance of this organization in OceanBase is that the system does not have to sacrifice analytical capability for the sake of the write path, nor give up analytical efficiency for the sake of writing data.
If all data were immediately reorganized into a form better suited for analysis, the higher data-reorganization cost would be pushed onto the write path, directly affecting sustained write efficiency. Conversely, if the system relied solely on the incremental path over the long term, analytical queries would bear higher read amplification and merge costs, making query latency and resource usage hard to keep stable.
Therefore, the difficulty of real-time AP is not simply optimizing one side — writes or analysis — to the extreme, but maintaining a long-term balance between these two kinds of constraints. OceanBase’s basic philosophy is: the incremental path prioritizes write efficiency, while the baseline prioritizes analytical efficiency.
The incremental path first ensures that new data can be continuously written and become visible to queries in a timely manner. As dumping (minor compaction) and merging (major compaction) proceed, this data is gradually folded into the baseline, and large-scale analytical scans are mainly handled by the columnar baseline.
The primary goal of the incremental side is still to serve write efficiency and transactional consistency, so overall it is closer to a row-based access path; but this does not mean it gives up analytical read efficiency. At the physical storage level, OceanBase balances compression and read efficiency for hot data, reducing the extra cost incurred when incremental data participates in analytical queries — without adding too much write overhead.
The six layers of capability discussed below are all built on this premise: what OceanBase aims to solve is not a one-time analytical speedup over static data, but long-term, stable support for analytical queries under dynamic data conditions.
Read Less: How Column Store, Encoding, Skip Index, and Pushdown First Drive Down Read Cost
In real-time AP, the first layer of cost usually comes from unnecessary reads. If large amounts of irrelevant data are already being moved during the read phase, the benefits of subsequent computation and parallel optimization will be noticeably weakened. So the first thing this layer must solve is: how to read as little as possible, and complete filtering as early as possible.
OceanBase’s approach is to push column pruning, filtering, and partial aggregation as close to the data as possible. For baseline data, it uses a columnar organization better suited for analytical reads; for incremental data, it keeps a row-based path better suited for sustained writes. During query execution, the system reads only the required columns where possible, reducing the I/O overhead from irrelevant columns.
Columnar encoding and Skip Index mainly operate on baseline column-store data. Columnar encoding helps reduce read and decode overhead, and supports partial filtering directly on compressed data; Skip Index maintains block-level statistics for block-level skipping — when a data block, statistically, cannot possibly satisfy the filter conditions, the system skips that block directly, avoiding unnecessary reads and decoding.
Incremental data is still primarily organized in a row-based form, and is merged with baseline results according to transactional visibility at query time. Compared with the baseline column store, the incremental path prioritizes write efficiency; however, at the physical storage level, it also tries to balance compression and filtering efficiency over recent data — though such capabilities should not be simply equated with the Skip Index on the baseline column store.
At the same time, filtering and common aggregations (including some Group By operations) can also be pushed down to the storage layer for execution, further reducing the volume of intermediate data sent up to the execution layer.
This layer addresses the read-cost problem: minimize ineffective scans, ineffective transfers, and ineffective intermediate results, providing a smaller and cleaner input for the next layer of “fast computation.”
Compute Fast: How Vectorized Execution, Storage Vectorization, and SIMD Improve Batch Computation Efficiency
Once the scan scope converges, the performance bottleneck usually shifts from I/O to compute efficiency. Real-time AP queries often involve aggregation, sorting, expressions, and join computation; if execution is still primarily row-by-row, CPU cache utilization and instruction execution efficiency will be constrained.
OceanBase’s approach is to replace row-by-row execution with vectorized execution. The vectorized execution engine processes data in batches, reducing frequent function calls and branch decisions, and lets operators and expressions run over a more contiguous data layout. The point is not simply to increase the number of threads, but first to improve the efficiency with which a single operator processes a batch of data.
Meanwhile, storage vectorization aligns projection, predicate, and aggregation-related computations more closely with the vectorized data format, reducing unnecessary data conversion and movement. Combined with SIMD, the system can process more data elements within the same compute cycle, further lowering the compute cost per unit of data.
This layer addresses: after “reading less,” can the data also be “computed fast.” Going one level deeper, the problem is no longer just local compute efficiency — it rises to the overall cost of distributed execution.
Globally Better: One-Phase Distributed Plans, Join Order/Algorithm, Data Distribution, Parallelism, and Auto DOP
Complex analytical queries typically involve multi-table joins, cross-partition access, and data exchange. At this layer, local optimality does not equal global optimality: a join path that looks reasonable in isolation may incur higher cross-node transfer costs; an access path that is faster locally may make overall execution slower.
During optimization, OceanBase adopts a one-phase distributed plan generation approach, evaluating access paths, join order, join algorithms, data distribution properties, and parallel properties within a unified cost framework. In other words, the system considers distributed execution cost during the plan generation phase, rather than first selecting a plan that looks reasonable on a single machine and then bolting on distributed processing afterward. The reason is that these factors are inherently coupled: join order affects the size of intermediate results, data distribution affects whether redistribution is needed, and the parallel strategy directly affects resource usage and response time.
On the parallelism side, OceanBase improves the response speed of complex queries through parallel execution, but a higher degree of parallelism is not always better. Auto DOP (Automatic Degree of Parallelism) leaves the decision of how much parallelism to use to the optimizer; it combines query cost and system state to decide whether to parallelize and what the degree of parallelism should be, balancing query latency against resource consumption.
This layer addresses: how to make distributed queries more reasonable from a holistic perspective. But “better now” does not mean “always effective afterward,” so the next layer must address plan stability under changing data.
Continuously Effective: Statistics, Cardinality Estimation, and Plan Stability
In real-time AP, performance fluctuations in the same class of SQL across different time periods are common. Often, the root cause is not that the SQL text changed, but that after data size, data distribution, and update cadence change, cardinality estimation and cost judgments become skewed, leading to plan regression or frequent plan switching.
OceanBase’s approach is to consider statistics maintenance and plan stability together. Statistics collection is not a one-time action; it must be continuously maintained in light of table size, partition characteristics, update patterns, and maintenance windows. For very large tables or high-frequency change scenarios, there are also more targeted collection strategies.
The quality of cardinality estimation directly affects access path selection, join strategy, and parallel decisions. Once statistics lag or become distorted, the optimizer may make wrong judgments in subsequent steps. For this very reason, in real-time AP, statistics relate not only to “whether this plan is chosen correctly,” but also to “whether the same class of queries can remain stable going forward.”
Scale Read Throughput: Column-Store Replicas, Routing, and Read-Traffic Isolation
When the concurrency of analytical queries keeps rising, path optimization of a single SQL statement alone can hardly guarantee overall throughput. As analytical read requests increase, a dedicated analytical read path needs to be scaled out, and routing strategies are used to direct different types of read requests to the corresponding replicas, reducing resource contention.
OceanBase’s approach is to introduce column-store replicas (C replicas) as an analytical read-only path. Column-store replicas support independent deployment and routing strategies, allowing analytical queries to execute on a more suitable read path and avoiding having all read requests concentrated on the same path.
At the routing layer, the system can combine policy conditions to decide whether to prioritize the column-store replica path, and work with weak/strong consistency reads and fallback mechanisms to balance consistency, availability, and throughput. Its core value mainly manifests in two aspects: first, scaling analytical read throughput; second, achieving TP/AP read-traffic isolation, reducing mutual interference between different workloads.
This layer completes the read-path expansion. The next step is to handle the repeated computation that recurs frequently in high-frequency analysis.
Reduce Repeated Computation: Materialized Views, Refresh, Mlog, and Query Rewrite
Real-time AP often involves a large number of periodic, repeated queries, such as fixed reports, repeated aggregations, and stable dimensional statistics. If everything is recomputed from the base tables each time, the system will repeatedly scan and compute, hurting both throughput and resource efficiency.
OceanBase’s approach is to move part of the repeated computation forward into maintainable precomputed results. Materialized views materialize and persist query results, relying on refresh to catch up with base-table changes: full refresh suits scenarios with few updates that can tolerate a longer maintenance window; incremental refresh suits scenarios with frequent changes that require controlling refresh overhead. Real-time materialized views typically no longer bind query freshness to refresh frequency; instead, they compute the already-materialized results together with incremental changes at query time, so that even under frequent changes, queries can still yield results closer to the current data state, supporting efficient real-time analysis.
Mlog (materialized view log) provides the change basis for incremental maintenance, enabling the system to update materialized results by the scope of changes rather than fully recomputing every time. The query rewrite capability lets the optimizer automatically hit materialized results when conditions are met, reducing the cost of manually rewriting SQL on the business side.
This layer addresses: for analytical tasks with high-frequency, stable patterns, how to avoid recomputing from raw detail every time. At this point, the six layers of capability form a complete optimization path for analytical cost.
Typical Scenarios: How the Six Layers Combine to Take Effect
These six layers are not used in isolation; they combine in different ways depending on workload characteristics. For single-table or wide-table aggregations, the main benefit usually comes from “read less + compute fast”; columnar reads, block-level skipping, and vectorized execution often determine the bulk of the performance gain.
It is worth adding that real-time AP does not correspond only to “ultra-large-scale data” scenarios. In gaming businesses, the more common need is online analysis with “moderate data size but high timeliness requirements,” such as campaign monitoring, retention-and-conversion dashboards, player-behavior segmentation, and real-time operational-metric reviews. Such scenarios likewise require second-level responses and stable throughput, so the six layers of capability still hold — only the optimization focus shifts more toward query latency stability and controllable resources under read-write concurrency.
For detail filtering and multi-dimensional statistics, the capability combination usually extends further to “globally better + continuously effective.” Such scenarios require not only more stable cardinality estimation and more reasonable distributed plans, but also rely more on an appropriate parallel strategy to avoid hard-to-explain performance fluctuations in the same class of queries at different times.
For periodic reports and fixed analytical tasks, “reducing repeated computation” is often key; materialized views, refresh, and query rewrite can significantly lower the cost of repeated aggregation. For systems with continuously growing analytical concurrency, “scaling read throughput” is a necessary complement; column-store replicas and routing strategies determine how far the analytical read side can scale, and how well it can be isolated from other read traffic.
Conclusion
The core difficulty of real-time AP is not getting a single analytical SQL statement to run, but keeping query latency, throughput, and resource cost within an acceptable range over the long term, under conditions of sustained writes and continuous change.
OceanBase’s answer does not rely on any single feature. Instead, on the premise of “baseline + incremental coexistence,” it progressively reduces the overall cost of online analysis across several layers: reading, computation, distributed execution, plan stability, read-path expansion, and precomputation.
For this very reason, when OceanBase real-time AP becomes a long-term capability rather than a one-off performance test, this technical framework is easier to understand, validate, and iterate on — and is better suited to continuously supporting business evolution.
Top comments (0)