Skipping-oriented partitioning for columnar layouts

Abstract

As data volumes continue to grow, modern database systems increasingly rely on data skipping mechanisms to improve performance by avoiding access to irrelevant data. Recent work [39] proposed a fine-grained partitioning scheme that was shown to improve the opportunities for data skipping in row-oriented systems. Modern analytics and big data systems increasingly adopt columnar storage schemes, and in such systems, a row-based approach misses important opportunities for further improving data skipping. The flexibility of column-oriented organizations, however, comes with the additional cost of tuple reconstruction. In this paper, we develop Generalized Skipping-Oriented Partitioning (GSOP), a novel hybrid data skipping framework that takes into account these rowbased and column-based tradeoffs. In contrast to previous columnoriented physical design work, GSOP considers the tradeoffs between horizontal data skipping and vertical partitioning jointly. Our experiments using two public benchmarks and a real-world workload show that GSOP can significantly reduce the amount of data scanned and improve end-to-end query response times over the state-of-the-art techniques.

The impact of columnar file formats on SQL‐on‐hadoop engine performance: A study on ORC and Parquet

“…The results showed 3‐7x improvements in the query response time compared to the traditional range partitioning. In their latest work, Sun et al presented a novel hybrid data skipping framework that optimizes the overall query performance by automatically balancing skipping effectiveness and tuple‐reconstruction overhead. It allows both horizontal and vertical partitioning of the data, which maximizes the overall query performance.…”

Optimal column layout for hybrid workloads

“…Partitioning a relation is NP-hard [72]. Data partitioning covers both the problem of partitioning a relation across multiple servers and within a single server [63,79,80]. Partitioning across both rows and columns is introduced by several systems to account for different read access patterns (e.g., on fact tables and dimension tables) [4,11,26].…”

ATUN-HL: Auto Tuning of Hybrid Layouts Using Workload and Data Characteristics

“…In [17,18], different partitioning approaches are presented, which help in selective queries. In [17], data is divided into multiple horizontal partitions and in each partition, data is stored row-wise, rather than column-wise.…”

“…This eventually gives a feature-vector for every tuple, which is then used for filtering partitions. A similar vector is also used in [18], however this time it utilizes hybrid layouts with column grouping, instead of fixed row layouts. The latter helps for both selection and projection queries.…”

“…The use of query log to optimize the parameters for future workloads is justified in [17,18], which conclude that filters are recurring and only a small portion are entirely new over time.…”