OceanBase 4.3.3 Released | The first GA version for real-time analysis

We are excited to announce the release of OceanBase 4.3.3, OceanBase's first General Availability (GA) version for real-time analytical processing (AP) scenarios. OceanBase 4.3.3 offers substantial optimization and enhancements across multiple areas, better meeting the diverse needs of users in real-time analysis and various business scenarios with its integrated product capabilities.

In early 2024, we introduced OceanBase 4.3.0, a fundamental advancement towards real-time AP, featuring a columnar storage engine based on the LSM-Tree architecture. Following rigorous polishing and validation in dozens of real-world use cases, OceanBase 4.3.3 further improves its AP performance and functionality, providing users with faster response times and better throughput in complex hybrid workload environments.

OceanBase 4.3.3 marks several key breakthroughs. Significant performance optimization has been achieved for multi-workload scenarios, particularly focusing on AP features. The columnar engine has been optimized for more application scenarios, including columnstore tables, columnstore indexes, hybrid rowstore-columnstore tables, and columnar replicas.

Furthermore, the implementation of Vectorized Engine 2.0, along with enhanced support for materialized views, external tables, and complex data types such as RoaringBitmap and Array, has made the system more flexible in handling diverse data. The introduction of a new form of columnar replicas ensures physical isolation between transaction processing (TP) and analytical processing (AP) resources in hybrid transaction/analytical processing (HTAP) scenarios, guaranteeing that the system is not affected by analytical business when handling transactional workloads, particularly in real-time data analysis and decision-making scenarios, maintaining high performance and stability.

According to the TPC-H 1TB benchmark test, OceanBase 4.3.3 has improved the query time from 99 seconds in 4.3.0 to 60 seconds, achieving a 64% improvement. This version demonstrates the ability to fulfill diverse requirements for data storage and analysis across various business scenarios and delivers shorter response times and higher throughput in large-scale data analysis. 

In terms of AI support, OceanBase 4.3.3 introduces support for vector types and indexes for AI-related analysis and processing, combined with multi-model integration and distributed storage capabilities, greatly simplifying the AI application technology stack and enabling enterprises to efficiently build AI applications.

The release of OceanBase 4.3.3 represents a significant milestone for OceanBase in real-time AP and the integration of AI and vectors. Next, let's explore the main features and highlights of OceanBase 4.3.3:

• Columnar storage

• Vectorized Engine 2.0

• Materialized views

• External tables

• Data import and export

• Complex data types

• Vector types and indexes

• Full-text index

• Reliability enhancement

Key Features for Analytical Processing (AP) 

Columnar Storage

In scenarios involving large-scale data analytics or extensive ad-hoc queries, columnar storage stands out as a crucial feature of an analytical processing (AP) database. Columnar storage is a way to organize data files. Different from row-based storage, columnar storage physically arranges data in a table by column. When data is stored by column, the system can scan only the columns involved in the query and calculation, instead of scanning the entire row. This way, the consumption of resources such as I/O and memory is reduced and the calculation is accelerated. In addition, columnar storage naturally has better data compression conditions and usually offers a higher compression ratio, thereby reducing the required storage space and network transmission bandwidth.

Building upon the LSM-Tree architecture, OceanBase extends the storage engine to support columnar storage in OceanBase 4.3.x. This implements integrated row-column storage on one OBServer node with one set of code and one architecture, thus achieving a balance between the performance of TP queries and that of AP queries. Various solutions tailored to distinct business scenarios have also been provided based on the columnar storage engine.

• Columnstore tables: Ideal for pure AP businesses, offering better analysis performance. In scenarios with high-performance point query requirements, rowstore indexes can be added to the corresponding tables.

• Columnstore indexes: For TP businesses with a small amount of analytical demand, consider creating columnstore indexes for the columns used in analysis on the rowstore tables.

• Hybrid rowstore-columnstore tables: For business workloads in a hybrid TP and AP environment, which involve both online transactions and real-time analysis, hybrid rowstore-columnstore tables can be created. The optimizer can automatically decide whether to use row or columnar storage based on the cost of SQL. By using resource groups, resource isolation can be implemented at the USER or SQL level.

• Columnar replicas: To implement physical isolation between TP resources and AP resources in HTAP scenarios, OceanBase 4.3.3 introduces a new deployment form that allows users to add an independent zone for storing read-only columnar replicas based on the original cluster. In the new zone, all user tables are stored in columnar storage mode. In this way, users can access only columnar replicas for queries and analysis in weak-consistency read mode, without affecting TP business.

Vectorized Engine 2.0

In earlier versions, OceanBase implemented a vectorized engine based on uniform data descriptions, which obviously improves the performance in contrast to non-vectorized engines but is incompetent in deep AP scenarios. The new version implements Vectorized Engine 2.0 which is based on column data format descriptions. This avoids memory use, serialization, and read/write overheads caused by ObDatum maintenance. Based on the column data format descriptions, OceanBase also reimplements many operators and expressions to achieve higher performance in AP scenarios.

Materialized Views

The materialized view feature has been introduced since OceanBase 4.3.0. This feature is a key feature for AP business scenarios. It pre-evaluates and stores the view query results to improve the query performance and simplify the query logic by reducing real-time evaluations. This feature applies to quick report generation and AP scenarios.

A materialized view stores query result sets to improve the query performance and depends on the data in the base table. When data in the base table changes, the data in the materialized view must be updated accordingly to ensure synchronization. Therefore, this version introduces a materialized view refresh mechanism, which supports two strategies: complete refresh and fast refresh. In a complete refresh of a materialized view, the system re-executes the query statements corresponding to the materialized view and overwrites the original query result sets with new ones. Complete refreshes apply to scenarios with small amounts of data. In a fast refresh, the system needs to process only the data changed since the last refresh. To implement accurate fast refreshes, OceanBase provides the materialized view log feature, which is similar to Oracle Materialized View Log (MLOG). The incremental data updates in the base table are logged to ensure that materialized views can be fast refreshed. Fast refreshes apply to business scenarios with large amounts of data and frequent data changes.

For business scenarios with high real-time requirements, the capabilities have been further extended to include support for real-time materialized views. This provides the capacity to perform immediate calculations based on materialized views and materialized log (MLOG) data, addressing the needs of businesses with critical real-time analysis requirements. 

In addition, the new version also supports automatic rewriting of materialized views. If you create a materialized view and specify ENABLE QUERY REWRITE when the system variable QUERY_REWRITE_ENABLED is set to True, the system can automatically rewrite a table query as a materialized view query to effectively reduce the workload in business modifications.

The latest update also introduces the ability to define primary key constraints on materialized views, helping users to fine-tune performance for specific queries such as single-row lookups, range queries, and joins where the primary key is a determining factor. 

External Tables

OceanBase has supported external tables for CSV files for some time. In the new version, you can import compressed files in GZIP, DEFLATE, and ZSTD formats. Furthermore, as AP businesses gradually expand, there has been a widespread demand for reading external data sources in Parquet format in data lake scenarios. Starting from 4.3.2, OceanBase now supports Parquet files in external tables. This allows users to import data from these files into OceanBase internal tables, or directly use external tables for federated query analysis across data sources.

The external table partitioning feature allows you to manage data in a way similar to LIST partitioning of a regular table and supports automatic and manual partition creation. In automatic partition creation mode, the system groups files by partition based on the definition of the partitioning key. In manual partition creation mode, you need to specify the path to the data file of each partition. In this case, the system implements partition pruning based on the partitioning conditions for an external table query, thereby reducing the number of files to scan and significantly improving the query performance.

Additionally, to ensure the files scanned by external tables are up-to-date, the new version introduces an automatic refresh feature for file directories. When creating an external table, users can specify the file list refresh method (manual, real-time, or periodic) through the AUTO_REFRESH option, and manage scheduled refresh tasks using the DBMS_EXTERNAL_TABLE.REFRESH_ALL_TABLE (interval int) system package subprogram.

Data Import and Export

In TP business, insert values are common for writing, while AP business often has a demand for high-performance batch data import and data processing. OceanBase currently supports various import methods, including direct load, external table import, partition exchange, insert overwrite, local import from the client, and general import.

• Direct load: OceanBase allows you to insert data into a database by using direct load. In other words, OceanBase can directly write data into data files. With the direct load feature, you can get around APIs at the SQL layer and directly allocate space and insert data into data files, thereby improving the data import efficiency. OceanBase supports two direct load methods: full direct load and incremental direct load. Full direct load is to write a complete data set into the data file of a database at one time and is generally used for database initialization, data migration, or quick load of a large amount of data. Incremental direct load is to write new data into the data file of a database when a large amount of data already exists in the file. Incremental direct load is generally used in data write scenarios with a high throughput, such as large-scale real-time data collection and log write.

• External table import: Currently, to achieve better analysis performance, external data can be imported into OceanBase through the method of INSERT INTO internal table or SELECT from the external table. External table imports can also use direct load capabilities to improve performance.

• Partition exchange: The partition exchange feature of OceanBase allows you to quickly import data into partitioned tables for efficient data loading. To be specific, this feature quickly inserts data into a new non-partitioned table through full direct load and then imports data from the non-partitioned table to an existing partitioned table to speed up incremental data import for the partitioned table.

• Insert overwrite: OceanBase supports table-level overwrite based on the INSERT OVERWRITE statement since 4.3.2. Specifically, the database can empty old data and write new data in a table in an atomic manner. This feature applies to AP business scenarios that involve periodic data updates, data conversion, data cleansing, and data correction. However, in OceanBase 4.3.2, you can overwrite only a whole table, and cannot overwrite the data of specific partitions or columns. In OceanBase 4.3.3, you can specify partitions, subpartitions, or columns of the destination table in the INSERT OVERWRITE statement, allowing for more flexibility in data overwriting and making it applicable to a wider range of business scenarios.

• Local import from the client: OceanBase supports the LOAD DATA LOCAL INFILE statement for local import from the client. You can use the feature to import local files through streaming file processing. Based on this feature, developers can import local files for testing without uploading files to the server or object storage, improving the efficiency of importing a small amount of data.

• General import: Different from the direct load, general import requires SQL optimization, making it suitable for scenarios with many data constraints. 

OceanBase now supports various data import commands to facilitate real-time data ingestion. However, real-time imports require waiting for the import to be completed during the process and cannot interrupt the session, which is not very user-friendly for large-scale data imports. The new version introduces asynchronous task scheduling capabilities based on DBMS_SCHEDULER, allowing users to create asynchronous import tasks, check the status, and cancel tasks using commands such as submit job, show job status, and cancel job.

OceanBase of earlier versions allows you to use the SELECT INTO OUTFILE statement to export multiple files. However, you cannot export data in a partition-wise manner. OceanBase 4.3.3 allows you to export data by partition to obtain a clearer directory structure. You can also build a partitioned external table based on the file directories and improve external table query efficiency through partition pruning.

Complex Data Types

As the era of big data evolves, enterprises are increasingly looking to mine and analyze user data. Roaring bitmap, known for its space-saving and computational efficiency, plays a significant role in scenarios such as user profiling, personalized recommendations, and precision marketing. Starting from OceanBase 4.3.2, the roaring bitmap data type is supported, enhancing the performance of large-scale data set computations and deduplication by storing and operating on a set of unsigned integers. To cater to multidimensional analysis needs, this version includes support for over 20 expressions related to cardinality calculation, set operations, bitmap evaluations, bitmap construction, bitmap output, and aggregate operations.

ARRAY is a complex data type commonly used in AP business scenarios to store multiple elements of the same type. It is an ideal choice for managing and querying multi-valued attributes that cannot be effectively represented by relational data. Starting from OceanBase 4.3.3, the ARRAY data type is supported in MySQL mode. You can define a column as either a numeric or character array, including nested arrays, when creating a table. It supports expressions for querying or writing to array objects, array_contains expressions, and the ANY operator to check if an element is present in the array. Additionally, it supports operators like +, -, =, and != for calculations and comparisons of array elements. 

The MySQL mode of OceanBase supports the multi-valued index feature, which is very useful for JSON documents and other set data types. By using the multi-valued index feature, you can create indexes on an array or a set to improve query efficiency based on JSON array elements.

Improved AP Performance

Benchmark Testing

TPC-H 1T, Decrease from 99s to 60s

In the TPC-H 1TB benchmark test, OceanBase 4.3.3 shows a significant performance improvement compared to 4.3.0. The query time decreases from 99 seconds to 60 seconds, achieving an approximately 64% performance improvement. The graph below demonstrates that across multiple queries, OceanBase 4.3.3 version exhibits noticeable performance advantages compared to 4.3.0, further validating OceanBase's optimization in real-time analysis scenarios.

Improved ClickBench Performance

During the ClickBench benchmark test, ClickHouse demonstrated an execution time of 139.57 seconds in the Cold Run, whereas OceanBase 4.3.3 recorded an execution time of 90.91 seconds, signifying a 54% performance enhancement over ClickHouse. In Hot Run 1 and 2, ClickHouse's execution times were 44.05 seconds and 36.63 seconds, while OceanBase's times were 34.92 seconds and 34.08 seconds, showcasing a 26% and 6% performance improvement over ClickHouse, respectively. These findings illustrate OceanBase's notable performance advantages in multiple execution scenarios.

Scenario-specific parameter templates

OceanBase, as an integrated database, supports various business types, such as express OLTP, complex OLTP, OLAP, HTAP, and KV. A single set of default system parameters cannot effectively cater to all scenarios. For example, the default value of ob_query_timeout is 10 seconds, which is not suitable for OLAP businesses. Therefore, starting from 4.3.2, OceanBase has identified the optimal configurations for several key parameters based on different business scenarios. In the future, tools like OCP and OBD will also be used to initialize these parameters for specific business scenarios.

Enhanced AP Stability

When an SQL query involves a large amount of data, the memory may be insufficient. In this case, the temporary intermediate results of some operators must be materialized. The execution of the SQL query fails if the disk space is fully occupied by the materialized data. OceanBase supports compressing temporary results of SQL queries. This feature effectively reduces the disk space occupied temporarily, so as to support query tasks with higher computing workload.

Vector types and indexes

The rise of AI applications has led to a surge in unstructured data like images, videos, and texts. Embedding algorithms are now able to represent this data as high-dimensional vectors for analysis and processing. This gave rise to vector databases, which are fully managed solutions for handling unstructured data, including storing, indexing, and retrieving embedding vectors. Vector indexes are a key feature, transforming keyword-based searches into vectorized retrievals. This shift turns deterministic searches into similarity searches, enabling the retrieval of large-scale, high-dimensional vectors.

OceanBase 4.3.3 now supports vector type storage, vector indexes, and embedding vector retrieval. It handles float vectors with up to 16,000 dimensions and supports basic operations like addition, subtraction, multiplication, comparison, and aggregation. It also offers precise search and Approximate Nearest Neighbor Search (ANNS) with Hierarchical Navigable Small World Network (HNSW) indexes for up to 2,000 dimensions. This can be applied to Retrieval-Augmented Generation (RAG) for scenarios such as image and video retrieval, behavior preference recommendations, security and fraud detection, and ChatGPT-like applications.

OceanBase has already been integrated with application development frameworks such as LlamaIndex and DB-GPT, supporting the rapid construction of AI applications. Other frameworks are also in the process of being adapted.

Full-text Search

In relational databases, indexes are often used to accelerate queries based on exact value matches, but ordinary B-tree indexes are not suitable for scenarios involving large amounts of text data that require fuzzy searches. In such cases, the only option is to perform a full table scan for fuzzy queries on each row, which often fails to meet performance requirements when dealing with large text and high data volumes. Additionally, some complex query scenarios, such as approximate matching and relevance sorting, are also difficult to support through SQL rewriting.

To address the issues mentioned above, OceanBase introduces the full-text indexing feature. By preprocessing text content to establish keyword indexes, it significantly enhances full-text search efficiency. Currently, it supports full-text indexing in MySQL mode, compatible with basic MySQL syntaxes.

Reliability Enhancement

The new version introduces the tenant cloning feature. Users can clone a specified tenant in the sys tenant efficiently. The cloned tenant is a standby tenant. Users can switch the cloned tenant to the PRIMARY role to provide services. The cloned tenant and original tenant share the physical macroblocks. However, new data changes and resource usage are isolated by tenant. If you want to perform temporary data analysis or other risky operations with high resource consumption on an online tenant, you can clone the tenant and perform analysis or verification on the cloned tenant to avoid affecting the online tenant. Users can also clone a tenant for disaster recovery. When an unrecoverable misoperation is performed on the original tenant, users can use the cloned tenant for data rollback.

The new version also supports a quick restore feature. In OceanBase Database of earlier versions, physical restore is a process of restoring the full data. A physical restore is completed only after all the data (minor compaction data and baseline data) and logs are restored. Then, you can log on to and use the restored tenant. If a large amount of data is to be restored to a tenant, the restore will take a long time and you need to reserve sufficient disk space for the tenant at the very beginning to ensure a successful restore. In some scenarios, a tenant is restored only for query and verification purposes and will be destroyed later. If only a few tablets are involved during the query, a full restore costs too high and is a waste of storage space, time, and network bandwidth. 

The new version provides a quick restore feature that allows you to provide read and write services by restoring only logs rather than data to your local server. In addition, the data backup feature allows you to build an intermediate-layer index for a backup SSTable based on the backup address. With this index, OBServer nodes can randomly read data from the backup SSTable like reading local data.

The release of OceanBase 4.3.3 represents a significant leap for OceanBase in real-time analysis and AP scenarios. In upcoming 4.3.x versions, we will continue to optimize and enhance AP functionality to deliver integrated product capabilities that consistently address diverse business needs.

We sincerely appreciate the support and contributions of every user and developer. Your feedback and suggestions are the driving force behind our ongoing product enhancement and technical breakthroughs. OceanBase is committed to partnering with users in our collective endeavor to build a more efficient and powerful distributed database.

OceanBase 4.3.3 Release Note: https://en.oceanbase.com/docs/common-oceanbase-database-10000000001719961