Column Store Research Articles

An Intelligent Optimized Compression Framework for Columnar Database

Instead of storing data in rows, a columnar database is a type of Database Management System (DBMS). To speed up the processing and reply to a question, a columnar database’s job is to efficiently write and read data to and from hard disc storage. One of the most crucial methods in the creation of column-oriented database systems is compression. For columns with Zero-length string types, all heavier and light-in-weight compression techniques have limitations. Processing of transactions online, these databases are substantially more effective for online analytical processing than for online transactional processing. This indicates that although they are made to examine transactions, they are not very effective at updating them. To overcome these issues a Zero Length Recurrent based Fruit Fly Optimization (ZLRFF) model is used. Additionally, a reduction technique is known as ZLRFF was designed to achieve a high compression ratio and allow straight lookups on compressed material without decompression first. ZLRFF’s main goal is to divide a Zero-length string written column vertically into smaller columns that can each be compressed using a separate lightweight compression technique. To search directly on compressed data, we also provide a search technique we call FF-search. Extensive testing demonstrates that ZLRFF supports direct searching on compressed data in addition to achieving a decent compression ratio, which enhances query performance.

Matching and Rewriting Rules in Object-Oriented Databases

Graph query languages such as Cypher are widely adopted to match and retrieve data in a graph representation, due to their ability to retrieve and transform information. Even though the most natural way to match and transform information is through rewriting rules, those are scarcely or partially adopted in graph query languages. Their inability to do so has a major impact on the subsequent way the information is structured, as it might then appear more natural to provide major constraints over the data representation to fix the way the information should be represented. On the other hand, recent works are starting to move towards the opposite direction, as the provision of a truly general semistructured model (GSM) allows to both represent all the available data formats (Network-Based, Relational, and Semistructured) as well as support a holistic query language expressing all major queries in such languages. In this paper, we show that the usage of GSM enables the definition of a general rewriting mechanism which can be expressed in current graph query languages only at the cost of adhering the query to the specificity of the underlying data representation. We formalise the proposed query language in terms declarative graph rewriting mechanisms described as a set of production rules L→R while both providing restriction to the characterisation of L, and extending it to support structural graph nesting operations, useful to aggregate similar information around an entry-point of interest. We further achieve our declarative requirements by determining the order in which the data should be rewritten and multiple rules should be applied while ensuring the application of such updates on the GSM database is persisted in subsequent rewriting calls. We discuss how GSM, by fully supporting index-based data representation, allows for a better physical model implementation leveraging the benefits of columnar database storage. Preliminary benchmarks show the scalability of this proposed implementation in comparison with state-of-the-art implementations.

A Performance Analysis of Hybrid and Columnar Cloud Databases for Efficient Schema Design in Distributed Data Warehouse as a Service

A Data Warehouse (DW) is a centralized database that stores large volumes of historical data for analysis and reporting. In a world where enterprise data grows exponentially, new architectures are being investigated to overcome the deficiencies of traditional Database Management Systems (DBMSs), driving a shift towards more modern, cloud-based solutions that provide resources such as distributed processing, columnar storage, and horizontal scalability without the overhead of physical hardware management, i.e., a Database as a Service (DBaaS). Choosing the appropriate class of DBMS is a critical decision for organizations, and there are important differences that impact data volume and query performance (e.g., architecture, data models, and storage) to support analytics in a distributed cloud environment efficiently. In this sense, we carry out an experimental evaluation to analyze the performance of several DBaaS and the impact of data modeling, specifically the usage of a partially normalized Star Schema and a fully denormalized Flat Table Schema, to further comprehend their behavior in different configurations and designs in terms of data schema, storage form, memory availability, and cluster size. The analysis is done in two volumes of data generated by a well-established benchmark, comparing the performance of the DW in terms of average execution time, memory usage, data volume, and loading time. Our results provide guidelines for efficient DW design, showing, for example, that the denormalization of the schema does not guarantee improved performance, as solutions performed differently depending on its architecture. We also show that a Hybrid Processing (HTAP) NewSQL solution can outperform solutions that support only Online Analytical Processing (OLAP) in terms of overall execution time, but that the performance of each query is deeply influenced by its selectivity and by the number of join functions.

OSSInsight: Scalable GitHub Analysis

GitHub is a platform hosting code, enabling collaboration, and supporting version control for a global community of over 100 million developers. The need for free tools is crucial for researching open-source software. Based on our research, we found out that existing tools lack real-time GitHub data processing or have limited functionalities. This demonstration presents OSSInsight, an open source tool for researching and analyzing GitHub repositories. We first present the architecture of the tool including its access to nearly 7 billion archived & real time data and how it is powered by TiDB. The demonstration shows how OSSInsight provides analysis of GitHub data along three dimensions: developers, repositories and organizations. All these analysis are based on generated SQL queries submitted to TiDB database. TiDB possesses HTAP capabilities, utilizing its row store for simple SQL queries while relying on its column store for more complex queries. Users can view and edit these SQL queries and also view their execution plan. Finally, OSSInsight provides an innovative tool based on OpenAI, that conducts data analysis using input in English text, yielding visual representations in the form of charts and graphs.

Modern approaches to data storage: comparison of relational and cloud data warehouses using etl and elt methods

The paper analyses various aspects of the use of relational and cloud data warehouses as well as methods of integrating ETL and ELT data. A comparative analysis of these approaches, their advantages and disadvantages are provided. A central relational data warehouse is proposed that provides a single version of truth (SVOT), which allows standardising and structuring data, avoiding differences and providing the access to the same information for all users of an organisation. It is analysed the methodological approaches to implementing a data warehouse: top-down, bottom-up, and from middle. It is described cloud data warehouses that use cloud technologies to provide scalability, availability and fault tolerance, which is important for the companies with huge amounts of data. The advantages and disadvantages of ETL and ELT are analysed: ETL transforms data before it is loaded into the warehouse, which makes it easier to maintain data confidentiality. ELT performs transformation after loading, which allows for more flexible data processing directly in the warehouse. In the article, we deal with the approaches to implementing a data warehouse: top-down is suitable for strategic planning, bottom-up allows for faster results, and the middle approach combines both methods to achieve optimal efficiency. We considered cloud data storage: compared to relational storage, cloud storage is more flexible, scalable and efficient, providing speed and reducing infrastructure costs. It is described cloud storage architectures: massive parallel processing, hybrid architectures, lambda architectures, and multi-structured architectures. They provide high performance and flexibility in data processing. It is described data storage technologies: Data Lake, Polyglot Persistence, Apache Iceberg, Apache Parquet, and columnar databases that provide efficient storage and processing of large amounts of data

Impact of Columnar Storage Optimizations in Redshift and BigQuery

With the data explosion, companies are utilizing "new age" data warehouses, such as Amazon Redshift and Google BigQuery, to process this information. Space-efficient storage structures (specifically column-oriented storage formats) are the path to improved query performance and lower costs. Columnar storage stores the data sorted by columns and not rows so that only the columns being queried are accessed when a query is made. Fewer disk reads mean faster execution time, and that's good for analytical processing

LST-Bench: Benchmarking Log-Structured Tables in the Cloud

Data processing engines increasingly leverage distributed file systems for scalable, cost-effective storage. While the Apache Parquet columnar format has become a popular choice for data storage and retrieval, the immutability of Parquet files renders it impractical to meet the demands of frequent updates in contemporary analytical workloads. Log-Structured Tables (LSTs), such as Delta Lake, Apache Iceberg, and Apache Hudi, offer an alternative for scenarios requiring data mutability, providing a balance between efficient updates and the benefits of columnar storage. They provide features like transactions, time-travel, and schema evolution, enhancing usability and enabling access from multiple engines. Moreover, engines like Apache Spark and Trino can be configured to leverage the optimizations and controls offered by LSTs to meet specific business needs. Conventional benchmarks and tools are inadequate for evaluating the transformative changes in the storage layer resulting from these advancements, as they do not allow us to measure the impact of design and optimization choices in this new setting. In this paper, we propose a novel benchmarking approach and metrics that build upon existing benchmarks, aiming to systematically assess LSTs. We develop a framework, LST-Bench, which facilitates effective exploration and evaluation of the collaborative functioning of LSTs and data processing engines through tailored benchmark packages. A package is a mix of use patterns reflecting a target workload; LST-Bench makes it easy to define a wide range of use patterns and combine them into a package, and we include a baseline package for completeness. Our assessment demonstrates the effectiveness of our framework and benchmark packages in extracting valuable insights across diverse environments. The code for LST-Bench is open source and is available at https://github.com/microsoft/lst-bench/.

LeCo: Lightweight Compression via Learning Serial Correlations

Lightweight data compression is a key technique that allows column stores to exhibit superior performance for analytical queries. Despite a comprehensive study on dictionary-based encodings to approach Shannon's entropy, few prior works have systematically exploited the serial correlation in a column for compression. In this paper, we propose LeCo (i.e., Learned Compression), a framework that uses machine learning to remove the serial redundancy in a value sequence automatically to achieve an outstanding compression ratio and decompression performance. LeCo presents a general approach to this end, making existing algorithms such as Frame-of-Reference (FOR), Delta Encoding, and Run-Length Encoding (RLE) special cases under our framework. Our microbenchmark with three synthetic and eight real-world data sets shows that a prototype of LeCo achieves a Pareto improvement on both compression ratio and random access speed over the existing solutions. When integrating LeCo into widely-used applications, we observe up to 5.2× speed up in a data analytical query in the Arrow columnar execution engine, and a 16% increase in RocksDB's throughput.

An Empirical Evaluation of Columnar Storage Formats

Columnar storage is a core component of a modern data analytics system. Although many database management systems (DBMSs) have proprietary storage formats, most provide extensive support to open-source storage formats such as Parquet and ORC to facilitate cross-platform data sharing. But these formats were developed over a decade ago, in the early 2010s, for the Hadoop ecosystem. Since then, both the hardware and workload landscapes have changed. In this paper, we revisit the most widely adopted open-source columnar storage formats (Parquet and ORC) with a deep dive into their internals. We designed a benchmark to stress-test the formats' performance and space efficiency under different workload configurations. From our comprehensive evaluation of Parquet and ORC, we identify design decisions advantageous with modern hardware and real-world data distributions. These include using dictionary encoding by default, favoring decoding speed over compression ratio for integer encoding algorithms, making block compression optional, and embedding finer-grained auxiliary data structures. We also point out the inefficiencies in the format designs when handling common machine learning workloads and using GPUs for decoding. Our analysis identified important considerations that may guide future formats to better fit modern technology trends.

Standardizing Open Table Formats for Big Data Analysis: Implications for Machine Learning and AI Applications

The digital age has ushered in an era of unprecedented data proliferation, both in complexity and volume, challenging traditional data management paradigms. To address these challenges, the big data ecosystem has witnessed the rise of innovative open table formats, with Apache Parquet, Apache ORC, and Delta Lake at the forefront. These formats revolutionize data handling through advanced features like columnar storage, dynamic schema evolution, and optimized retrieval mechanisms. This paper delves into the critical need for standardizing open table formats, with a particular focus on their transformative potential in Machine Learning (ML) and Artificial Intelligence (AI) domains. We present a comprehensive comparative analysis, dissecting the features, advantages, and limitations of widely adopted open table formats. Our investigation extends to how these formats enhance the trifecta of data processing efficiency, model training effectiveness, and cross-tool data consistency in ML and AI ecosystems. The paper further explores the pivotal role of standardization in fostering interoperability, scalability, and widespread adoption of big data systems. By examining the integration capabilities across heterogeneous platforms, we highlight the far-reaching implications of standardized formats. This study aims to elucidate how the standardization of open table formats can catalyze a paradigm shift in big data analysis methodologies. Ultimately, we posit that this standardization could significantly accelerate innovation and enhance outcomes in the rapidly evolving landscapes of ML and AI.

Are NoSQL Databases Affected by Schema?

During data modelling, NoSQL databases are usually considered schema-less databases. They do not support rigid schema as traditional databases. Instead, offer flexibility and support dynamic schema, which means schema design can be changed at run time to support analytical tasks. NoSQL databases allow storing similar information with multiple schemas, which facilitates data evolution by eliminating restrictions on the schema. It raises the question of whether the schema matters in NoSQL databases, given that explicit schema declaration is not required before data storage. Furthermore, if the schema does matter, what is its potential impact on application performance? This paper examines these questions by taking three types of NoSQL databases: Document store, Column store, and Key-Value store. All possible schema structure alternatives are designed using a sample Entity Relationship (ER) model. Then, a set of common queries are performed on various schema alternatives to analyse the impact of query performance in terms of query response time, speedup factor, read and write Latency, and database size. The results suggest that the scheme alternatives affect the database performance in different NoSQL models.

BtrBlocks: Efficient Columnar Compression for Data Lakes

Analytics is moving to the cloud and data is moving into data lakes. These reside on object storage services like S3 and enable seamless data sharing and system interoperability. To support this, many systems build on open storage formats like Apache Parquet. However, these formats are not optimized for remotely-accessed data lakes and today's high-throughput networks. Inefficient decompression makes scans CPU-bound and thus increases query time and cost. With this work we present BtrBlocks, an open columnar storage format designed for data lakes. BtrBlocks uses a set of lightweight encoding schemes, achieving fast and efficient decompression and high compression ratios.

Grouping Time Series for Efficient Columnar Storage

Columnar storage is now an industry standard design in most open-source or commercial time series database products, making them HTAP systems. The time column of a time series serves as the key for identifying the other value column, namely single-column storage scheme. When multiple time series share a similar set of timestamps, very likely in a module of multiple sensors, it is natural to group them together, i.e., one time column identifies multiple value columns in a single-group storage scheme. While multiple value columns sharing the same time column reduce the space cost of repeating timestamps, it may introduce extra space cost for recording null values. The reason is that time series may not be exactly aligned on each timestamp, owing to missing values, distinct data collection frequencies, unsynchronized clocks and so on. The columngroups storage scheme is thus to divide columns into multiple groups, within which the value columns share the same time column. Unfortunately, the problem of finding the optimal column groups for the minimum space cost is highly challenging, NP-hard according to our analysis. Thereby, we propose a heuristic algorithm for automatically grouping time series for efficient columnar storage. The column groups storage has been deployed in Apache IoTDB, an open-source time series database. The extensive performance analysis, over real-world data from our industrial partners, demonstrates that the proposed column groups achieve near optimal storage, more concise than the storage of single-column or single-group schemes. Interestingly, both the flushing and querying time costs of column groups are comparable to those of single-column or singlegroup, i.e., without incurring extra time cost.

BOUNCE: memory-efficient SIMD approach for lightweight integer compression

Integer compression plays an important role in columnar database systems to reduce the main memory footprint as well as to speedup query processing. To keep the additional computational effort of (de)compression as low as possible, the powerful Single Instruction Multiple Data (SIMD) extensions of modern CPUs are heavily applied. While a scalar compression algorithm usually compresses a block of N consecutive integers, the state-of-the-art SIMDified implementation scales the block size to k cdot N with k as the number of elements which could be simultaneously processed in an SIMD register. On the one hand, this scaling SIMD approach improves the performance of (de)compression. But on the other hand, it can lead to a degradation of the memory footprint of the compressed data. Within this article, we analyze this degradation effect for various integer compression algorithms and present a novel SIMD concept to overcome that effect. The core idea of our novel SIMD concept called BOUNCE is to concurrently compress k different blocks of size N within SIMD registers, guaranteeing the same compression ratio as scalar variant. As we are going to show, our proposed SIMD idea works well on various Intel CPUs and may offer a new generalized SIMD concept to optimize further algorithms.

The FastLanes Compression Layout: Decoding > 100 Billion Integers per Second with Scalar Code

The open-source FastLanes project aims to improve big data formats, such as Parquet, ORC and columnar database formats, in multiple ways. In this paper, we significantly accelerate decoding of all common Light-Weight Compression (LWC) schemes: DICT, FOR, DELTA and RLE through better data-parallelism. We do so by re-designing the compression layout using two main ideas: (i) generalizing the value interleaving technique in the basic operation of bit-(un)packing by targeting a virtual 1024-bits SIMD register, (ii) reordering the tuples in all columns of a table in the same Unified Transposed Layout that puts tuple chunks in a common "04261537" order (explained in the paper); allowing for maximum independent work for all possible basic SIMD lane widths: 8, 16, 32, and 64 bits. We address the software development, maintenance and future-proofness challenges of increasing hardware diversity, by defining a virtual 1024-bits instruction set that consists of simple operators supported by all SIMD dialects; and also, importantly, by scalar code. The interleaved and tuple-reordered layout actually makes scalar decoding faster, extracting more data-parallelism from today's wide-issue CPUs. Importantly, the scalar version can be fully auto-vectorized by modern compilers, eliminating technical debt in software caused by platform-specific SIMD intrinsics. Micro-benchmarks on Intel, AMD, Apple and AWS CPUs show that FastLanes accelerates decoding by factors (decoding >40 values per CPU cycle). FastLanes can make queries faster, as compressing the data reduces bandwidth needs, while decoding is almost free.

Control Flow Duplication for Columnar Arrays in a Dynamic Compiler

Columnar databases are an established way to speed up online analytical processing (OLAP) queries. Nowadays, data processing (e.g., storage, visualization, and analytics) is often performed at the programming language level, hence it is desirable to also adopt columnar data structures for common language runtimes. While there are frameworks, libraries, and APIs to enable columnar data stores in programming languages, their integration into applications typically requires developer interference. In prior work, researchers implemented an approach for *automated* transformation of arrays into columnar arrays in the GraalVM JavaScript runtime. However, this approach suffers from performance issues on smaller workloads as well as on more complex nested data structures. We find that the key to optimizing accesses to columnar arrays is to identify queries and apply specific optimizations to them. In this paper, we describe novel compiler optimizations in the GraalVM Compiler that optimize queries on columnar arrays. At JIT compile time, we identify loops that access potentially columnar arrays and duplicate them in order to specifically optimize accesses to columnar arrays. Additionally, we describe a new approach for creating columnar arrays from arrays consisting of complex objects by performing **multi-level storage transformation**. We demonstrate our approach via an implementation for JavaScript `Date` objects. [ full abstract at https://doi.org/10.22152/programming-journal.org/2023/7/9 ]

Accelerating Columnar Storage Based on Asynchronous Skipping Strategy

Many database applications, such as OnLine Analytical Processing (OLAP), web-based information extraction or scientific computation, need to select a subset of fields based on several user-defined filters. Developers of these applications require effective assembly methods for on-demand filtering and aggregation, which raises new challenges in deploying parallel computing components on top of columnar storage. To efficiently generate qualified records, an asynchronous skipping strategy is presented to speed up filtering and decoding in the column-based storage. Concentrating on filtering-pushdown in parallel analytical workloads, we offer in-depth analysis on record assembly. We highlight the bottleneck of traditional record-wise assembling methods in the cases of evaluating analytical tasks on a nested schema. With a concurrent queue structure, an asynchronous skipping strategy is presented to evaluate column scan separately by a software pipeline involving a different set of threads. We show how to intensively read the sequential blocks of each column, and how to effectively eliminate invalid payloads by integrating filtering-pushdown in an asynchronous I/O stack. We implement a columnar storage supporting filtering-pushdown in nested schema. Our experiments are conducted on a de-facto standard benchmark using both variant-selectivity scans and ad-hoc queries. The results revealed that in parallel I/O-intensive workloads, our implementation improved the I/O performance of the state-of-the arts by 1.3X∼2.7X. Coupling the asynchronous strategy with filtering-pushdown, our implementation remarkably outperforms its competitors with heavyweight coding workloads on both HDD and SSD.

Finding Stable Periodic-Frequent Itemsets in Big Columnar Databases

Stable periodic-frequent itemset mining is essential in big data analytics with many real-world applications. It involves extracting all itemsets exhibiting stable periodic behaviors in a temporal database. Most previous studies focused on finding these itemsets in row (temporal) databases and disregarded the occurrences of these itemsets in columnar databases. Furthermore, the naïve approach of transforming a columnar database into a row database and then applying the existing algorithms to find interesting itemsets is not practicable due to computational reasons. With this motivation, this paper proposes a framework to discover stable periodic-frequent itemsets in columnar databases. Our framework employs a novel depth-first search algorithm that compresses a given columnar database into a unified dictionary and mines it recursively to find all stable periodic-frequent itemsets. The dictionary holds the information pertaining to itemsets and their temporal occurrences in a database. Experimental results on six databases demonstrate that the proposed algorithm is computationally efficient and scalable.

Benchmarking Redis and HBase NoSQL Databases using Yahoo Cloud Service Benchmarking tool

The Not Structured Query Language (NoSQL) databases have become more relevant to applications developers as the need for scalable and flexible data storage for online applications has increased. Each NoSQL database system provides features that fit particular types of applications. Thus, the developer must carefully select according to the application’s needs. Redis is a key-value NoSQL database that provides fast data access. On the other hand, the Apache HBase database is a column-oriented database that offers scalability and fast data access, is a promising alternative to Redis in some types of applications. In this research paper, the goal is to use the Yahoo Cloud Serving Benchmark (YCSB) to compare the performance of two databases (Redis and HBase). The YCSB platform has been developed to determine the throughput of both databases against different workloads. This paper evaluates these NoSQL databases with six workloads and varying threads.

High-Performance Row Pattern Recognition Using Joins

The SQL standard introduced MATCH_RECOGNIZE in 2016 for row pattern recognition. Since then, MATCH_RECOGNIZE has been supported by several leading relation systems, they implemented this function using Non-Deterministic Finite Automaton (NFA). While NFA is suitable for pattern recognition in streaming scenarios, the current uses of NFA by the relational systems for historical data analysis scenarios overlook important optimization opportunities. We propose a new approach to use Join to speed up row pattern recognition in historical analysis scenarios for relational systems. Implemented as a logical plan rewrite rule, the new approach first filters the input relation to MATCH_RECOGNIZE using Joins constructed based on a subset of symbols taken from the PATTERN expression, then run the NFA-based MATCH_RECOGNIZE on the filtered rows, reducing the net cost. The rule also includes a specialized cardinality model for the Joins and a cost model for the NFA-based MATCH_RECOGNIZE operator for choosing an appropriate symbol set. The rewrite rule is applicable when the query pattern's definition is self-contained and either the input table has no duplicates or there is a window condition. Applying the rewrite rule to a query benchmark with 1,800 queries spanning over 6 patterns and 3 pattern definitions, we observed median speedups of 5.4X on Trino (v373 with ORC files on Hive), 57.5X on SQL Server (2019) using column store and 41.6X on row store.