How to organize the analysis of large amounts of data in real time
As the complexity of IT systems and analytics tasks grows, the requirements for tool capabilities change. For many scenarios, solutions that can work with both historical data and data that is updated in real time become a priority. That is, analysts increasingly need a tool that works at the intersection of transactional and analytical (OLAP and OLTP) systems.
My name is Nikolay Karlov. I am the Director of Innovation Projects at VK Tech. In this article, I will tell you what HTAP systems are, what advantages they provide, and introduce our columnar DBMS Tarantool Column Store, which implements HTAP processing.
The article is based on the webinar “Analyzing data in Real-time”. You can watch it Here.
Beyond OLAP and OLTP
Users' demands on IT systems are growing. For example, people now want to be able to log into a bank application and not only see their account balance, but also evaluate expenses on different categories of purchases for any period. Moreover, it is important that the latest transactions are also taken into account (for example, purchases made a minute ago).
Such queries are not limited to online banking. The ability to analyze large amounts of data with constantly incoming updates is important in many scenarios: management, finance departments, telecom, machine learning specialists, manufacturing and more.
Here it is necessary to understand that classic OLAP and OLTP systems are used for different tasks: some systems are focused on working with historical data, while others are focused on processing data on the fly. The nuance is that the examples described at the beginning and dozens of other scenarios actually require going beyond the usual capabilities:
when transactional systems acquire analytical functions with the processing of historical data;
when analytical systems are able to take into account instantaneous changes in data.
With such requirements, systems capable of performing hybrid transaction/analytical processing (HTAP) are needed.
And there are many tasks that require HTAP systems – from scoring, antifraud, real-time marketing and machine learning to analytics of user actions and real-time transaction operations.
Example of tasks for HTAP: working with ML
One of the common scenarios for using HTAP systems is working with machine learning models. Everything is obvious here: when preparing ML models, both historical data and real-time data are processed – a classic task for HTAP.
Let's look at a small example to understand where and when HTAP solutions are needed when working with ML models.
So. Usually user data is formed from two components:
profile,
behavior.
Profiles include attributes that do not change at all or change rarely, such as gender, age, connected products, long-term interests. Data scientists usually consider these attributes as static features.
Behavior refers to metrics that change frequently and describe recent events, such as reposts, comments, new subscriptions. Data scientists can consider them as dynamic features.
It is important that in order to solve, for example, real-time marketing tasks, it is necessary to take into account both long-term (profile) and short-term (behavior) metrics. This is where the need for hybrid transactional-analytical data processing (HTAP) and a system capable of performing it (HTAP solution) arises.
That is, it is HTAP tools that allow you to implement a scenario in which a buyer enters an online store and immediately receives relevant offers based on past purchases, preferences, gender, and other factors. Moreover, when selecting recommendations, not only static metrics and features will matter, but also the actions taken by the client “here and now.” This is especially important if a person's needs change dramatically. For example, if he always bought sewing kits, and is now looking for a fishing rod: continuing to offer only sewing kits at this moment is a failure for the business.
Note: In one of our previous articles, we already described a detailed example of working with an HTAP system as part of building an anti-fraud system. You can read about it here Here.
HTAP architecture
In string databases, to get some records from individual columns, the database:
iterates through the index;
unpacks each line;
extracts values from strings;
adds them to an array or temporary ephemeral table, which is then used to process the request.
That is, the database is forced to perform a large amount of work.
In the case of columnar data placement, the situation is somewhat different: the data is conventionally “knocked down” into blocks of data of the same type.
For data scanning tasks, this is a much more efficient approach because:
the cache processor can take a large number of values at once;
aggregation functions and filtering can be performed using vectorization;
compression can be used.
But even in the case of such a storage format in the context of real-time processing there are pitfalls. Thus, when working with columnar DBMS:
single insert operations are inefficient;
UPDATE operations are either performed inefficiently or are impossible;
It is difficult to withstand the load of a large number of single readings.
These shortcomings need to be compensated for in one way or another. For example, using a multi-layered storage architecture, as well as approaches similar to LSM.
Ultimately, combining the benefits of each storage type, HTAP class databases typically use columnar engines along with row-oriented ones.
What is Tarantool Column Store
Tarantool's product portfolio also includes an HTAP solution. This is Tarantool Column Store — an in-memory column DBMS for transactional and analytical data processing in real time.
In our solution, we organized four storage layers.
Memtx. Urgent buffer with classic tables. Provides very high performance for OLTP, low for OLAP. Better suited for storing not a large array of data, but the latest records – hundreds or thousands.
Memcs. Columnar buffer with a new hybrid engine that allows storing data in a hybrid, intermediate format, making single updates and inserts, performing full scans. Provides high performance for OLTP and OLAP. Stores data directly in indexes using the bps-vector approach. Depending on the load profile, it can store from a million to several hundred million records.
Memtx. The main storage with block-column storage type. The layer provides high OLAP performance and supports data compression. As a result, it can store and process billions of records. However, updates here are not as efficient as in Memcs.
The data is stored in Apache Arrow format and is aligned for efficient vectorization.
Parquet. A layer where data that is not needed constantly is pushed out to disk or S3 storage (we prefer S3). Naturally, in this case the reading speed is lower than that of the main engine. At the same time, rare UPDATEs are supported.
That is, Tarantool Column Store combines data storage in RAM and the ability to dump it into permanent memory, guaranteeing both high speed access to information and the reliability of its storage.
It is important that engines of all levels are compatible. In combination with Tarantool reliability guarantees (WAL, snapshots and other techniques), this ensures stable, consistent and efficient data processing at all stages.
Query Engine
Now about query execution and the execution language itself. Using queries in one thread is a mistake for us. Therefore, we have implemented multi-threaded query execution with dividing large datasets into partitions and parallelization.
Moreover, we do not use the main transaction thread to execute read requests. This is possible thanks to the ReadView mechanism, which allows reading consistent snapshots of data outside the main thread. This mechanism was previously used to create consistent snapshots of data when saving snapshots to disk, but was later modified to work with user requests.
It is important that we have vectorized execution (SIMD). As a result, a large number of records of one column can be processed in one processor cycle, and the efficiency of using processor caches is also increased.
We have chosen PG SQL (like Postgres) as the main syntax. In addition, there is support for UDF (User Defined Functions) and extensions in Rust. Moreover, in addition to regular user functions, you can also make aggregation functions for different tasks and usage scenarios – they will also work effectively.
The query execution engine can work with data frames, execute queries from the SQL language, has several levels of logical physical query execution plan and optimizers.
Data pipeline architecture in Tarantool Column Store
We built the data processing pipeline in Tarantool Column Store as follows:
data goes into the row buffer, after which it is transferred to the column buffer;
from the column buffer, data goes to the main storage;
All changes from the row and column buffers are written to the transaction log (WAL Log).
The pipeline is not overloaded with unnecessary stages, which allows reducing the time for data processing without compromising the quality and efficiency of processing.
At the same time, we provide the possibility of using dictionaries and Tarantool spaces.
Architecture of Tarantool Column Store
The tool's architecture combines components that are responsible for interaction within the service, transfer and processing of requests, and other operations. Among them:
column engine;
row and column buffers;
Compute Kernel Engine (virtualized query execution system);
replication and disk storage layers.
At the same time, work on expanding the tool’s capabilities at the architectural level continues:
we are working on the ability to store and use vectors – we already have prototypes and successful pilots, including multidimensional vectors for approximate search;
We are developing a stream processor that will allow us to implement various stream analytics scenarios;
We are improving the ability to interpret external files as tables and use them as dictionaries or external tables.
Protocols
Tarantool Column Store supports several popular protocols at once.
Our main one is HTTP. It is traditionally considered that HTTP is a slow protocol. But in our case, this is not the case: according to our measurements, it takes only a few hundred microseconds to execute several hundred thousand requests from a neighboring server. Moreover, a full-fledged scan of the data is performed during this time.
It is important that HTTP works outside the main thread (TX Thread) – it is a separate HTTP server (we use Tokio Runtime as one of the most productive options). To work with Tarantool, it uses multi-threaded reading using ReadView, due to which reading and data processing do not depend on the main transactional thread Tarantool tx and do not slow it down.
Tarantool Column Store also supports Apache Flight protocols: Apache FlightRPC, Apache Flight SQL. These are columnar protocols that allow systems, including Apache Stack and Big Data, to work with data without copying it. For example, if a Python application receives data via such a protocol, it can be used immediately, without copying or converting. This is very effective: double copying, mapping, serialization, deserialization and other unnecessary operations, which can usually take more than 70% of the system's CPU time, can be removed from the processing pipeline.
We are also conducting research in the direction of using Shared Memory as a data transfer method.
Among other things, we plan to add support for ODBC/JDBC and ADBC protocols in upcoming releases.
What's the bottom line?
There are more and more tasks for which neither transactional (OLTP) nor analytical (OLAP) systems in their pure form are suitable. This forces companies to either build complex architectures with many layers and set up redundant data processing pipelines, or choose HTAP systems capable of performing hybrid transactional-analytical data processing. And companies are wisely increasingly choosing HTAP solutions, which are deservedly are a trend not only for 2024, but also, probably, for the following years.
It is important that HTAP systems are flexible in the context of their application – this is clearly illustrated by the example Tarantool Column Store. Our solution combines the advantages of different data storage types, technologies, protocols and functions. At the same time, it provides high efficiency of analytical processing with a performance of up to 1.5 billion scans per second per core, i.e. it is suitable for different scenarios and load profiles.
