In our data-driven world, organisations are under pressure to improve the performance of real-time data analysis without incurring degradation in business transactions. That's why Fujitsu created Vertical Clustered Index (VCI), a proprietary advancement of In-Memory Column Store.
The In-Memory Column Store stores columns, tables, partitions and materialised views in a columnar format in memory, rather than the typical row format. The advantage is two-fold: the first being you have data in memory which naturally boosts performance and the second being that columnar storage is perfect for the type of analytic queries found in business intelligence solutions.
A column-oriented database management system can access the data it needs more precisely when answering a query because it doesn't have to spend time scanning and discarding unwanted row-oriented data. The result is faster query performance, especially in large datasets.
However, the adoption of this architecture is hindered by three critical issues:
- row-oriented data is not automatically reflected to its column-oriented counterpart
- the size of the column-oriented data is constrained by installed memory, plus
- while it solves the issue of data analysis performance, it introduces the problem of data update overhead due to the update of indexes.
The solution to this inherent conflict is to store both row-oriented and column-oriented data in a way that the system is optimised for both Online Analytical Processing (OLAP) and Online Transaction Processing (OLTP) workloads. However, most of these solutions do not automatically reflect changes in the row-oriented data to the column-oriented data and are affected by memory constraints.
Introducing Vertical Clustered Indexing (VCI) by Fujitsu
Fujitsu's Vertical Clustered Index (VCI) is a robust implementation of In-Memory Column Store found inside open source based Fujitsu Enterprise Postgres.
A memory efficient engine that utilises parallel-processing enables it to instantly update column-oriented data in response to changes in row-oriented data, and therefore process column-oriented data quickly.
It's so quick, that a server equipped with 24 CPU cores can run analyses up to 46 times faster than a database management system without VCI.
Even on smaller computer systems with very little memory, VCI enables real time data analysis with little interruption to business transaction processing. VCI data is stored in its own dedicated portion in the shared buffer value, containing row data for OLTP, and the engine ensures that consistency is maintained between them via asynchronous updates.>
More smarts in VCI
Still, a hybrid architecture such as this (where row data must be converted to columnar data) can typically impact OLTP. To solve this issue, Fujitsu's VCI provides two storage areas:
- Write Optimized Store (WOS) for row data, and
- Read Optimised Store (ROS) for columnar data.
During updates, data is written only to the WOS, without data compression or indexing. This avoids overhead caused by conversion to columnar orientation.
To further increase performance, only record ID and VCI-indexed columns are written to the WOS. After a certain amount of data is written, it is asynchronously converted to columnar data and written to the ROS using compression and indexing.
By separating the synchronous process of storing OLTP data this way, the overhead of conversion to columnar data is avoided, allowing the engine to balance data synchronisation and OLTP and OLAP performances. When data aggregation is performed, the WOS and the ROS are combined to reflect the current table data. From row-oriented data stored in the WOS, a temporary area with columnar data is created in the SQL processor called the Local ROS. This is compared against the ROS data already converted to columnar data and each record is judged in terms of visibility/invisibility. By performing the aggregation using this resulting data set, the query result is the same as if the OLTP data has been used.
In summary, VCI is an efficient and reliable asynchronous method designed to quickly convert row-oriented data to columnar data without affecting business transactions. It is a key feature inside Fujitsu Enterprise Postgres, the open source based data management system designed for organisations requiring advanced enterprise features and support on an open-source based platform with no vendor lock-in, and significantly reduced license fees and total cost of ownership when compared to standard proprietary-based systems.
Our database experts are on hand to answer your questions should you wish to consider a migration to open source based solutions such as Fujitsu Enterprise Postgres for reduced cost of ownership, and enterprise grade support and technologies. Feel free to contact us at enterprisepostgresql@fujitsu.com if you would like to discuss your requirements.
