Column-oriented DBMS

A column-oriented DBMS or columnar DBMS is a database management system (DBMS) that stores data tables by column rather than by row. Benefits include more efficient access to data when only querying a subset of columns (by eliminating the need to read columns that are not relevant), and more options for data compression. However, they are typically less efficient for inserting new data. Practical use of a column store versus a row store differs little in the relational DBMS world. Both columnar and row databases can use traditional database query languages like SQL to load data and perform queries. Both row and columnar databases can become the backbone in a system to serve data for common extract, transform, load (ETL) and tools. Description Background A relational database management system provides data that represents a two-dimensional table of columns and rows. For example, a database might have this table: This simple table includes an employee identifie

About this result

This page is automatically generated and may contain information that is not correct, complete, up-to-date, or relevant to your search query. The same applies to every other page on this website. Please make sure to verify the information with EPFL's official sources.

Related publications (14)

Related publications

Related people (2)

Related people

Related units (2)

Related units

Related concepts (9)

Related concepts

Related courses (4)

Related courses

Related lectures (26)

Related lectures

CS-422: Database systems

This course is intended for students who want to understand modern large-scale data analysis systems and database systems. It covers a wide range of topics and technologies, and will prepare students to be able to build such systems as well as read and understand recent research publications.

CS-322: Introduction to database systems

This course provides a deep understanding of the concepts behind data management systems. It covers fundamental data management topics such as system architecture, data models, query processing and optimization, database design, storage organization, and transaction management.

CS-401: Applied data analysis

This course teaches the basic techniques, methodologies, and practical skills required to draw meaningful insights from a variety of data, with the help of the most acclaimed software tools in the data science world (pandas, scikit-learn, Spark, etc.)

Pixels: An Efficient Column Store for Cloud Data Lakes

Anastasia Ailamaki, Haoqiong Bian

To benefit from the cloud’s higher elasticity and price-efficiency, most modern data-lake engines support S3-like cloud object storage (COS) services as their optional or preferred underlying storage. Meanwhile, the widespread column stores, such as Parquet, are applied in these data lakes to improve analytical performance. However, as these column stores were designed for on-premise HDFS, they often suffer from the high latency of COS and deliver sub-optimal query performance. We observe that by optimizing the storage layout and data access pattern, we can effectively hide and mitigate the high latency. In this paper, we present Pixels, a column store optimized for the cloud that solves the problem by (1) the workload-driven storage layout optimization within and across the row group boundaries; (2) the I/O scheduling concerning the optimized storage layout and the performance characteristics of COS. They collectively improve the analytical performance in a transparent way that does not affect data ingestion and query execution in data lakes. Evaluations show that Pixels outperforms the state-of- the-art column store on COS by more than one order of magnitude on real-world workload and by 1.93x on TPC-H. Moreover, the performance of Pixels is also portable to HDFS.

IEEE2022

Query Optimization in Oracle 12c Database In-Memory

Ramachandra Satyanarayana Valluri

Traditional on disk row major tables have been the dominant storage mechanism in relational databases for decades. Over the last decade, however, with explosive growth in data volume and demand for faster analytics, has come the recognition that a different data representation is needed. There is widespread agreement that in-memory column-oriented databases are best suited to meet the realities of this new world. Oracle 12c Database In-memory, the industry’s first dual-format database, allows existing row major on-disk tables to have complementary in-memory columnar representations. The new storage format brings new data processing techniques and query execution algorithms and thus new challenges for the query optimizer. Execution plans that are optimal for one format may be sub-optimal for the other. In this paper, we describe the changes made in the query optimizer to generate execution plans optimized for the specific format – row major or columnar – that will be scanned during query execution. With enhancements in several areas – statistics, cost model, query transformation, access pat h and join optimization, parallelism, and cluster - awareness – the query optimizer plays a significant role in unlocking the full promise and performance of Oracle Database In-Memory.

2015

ReCache: Reactive Caching for Fast Analytics over Heterogeneous Data

Anastasia Ailamaki, Tahir Azim, Manolis Karpathiotakis

As data continues to be generated at exponentially growing rates in heterogeneous formats, fast analytics to extract meaningful information is becoming increasingly important. Systems widely use in-memory caching as one of their primary techniques to speed up data analytics. However, caches in data analytics systems cannot rely on simple caching policies and a fixed data layout to achieve good performance. Different datasets and workloads require different layouts and policies to achieve optimal performance. This paper presents ReCache, a cache-based performance accelerator that is reactive to the cost and heterogeneity of diverse raw data formats. Using timing measurements of caching operations and selection operators in a query plan, ReCache accounts for the widely varying costs of reading, parsing, and caching data in nested and tabular formats. Combining these measurements with information about frequently accessed data fields in the workload, ReCache automatically decides whether a nested or relational column-oriented layout would lead to better query performance. Furthermore, ReCache keeps track of commonly utilized operators to make informed cache admission and eviction decisions. Experiments on synthetic and real-world datasets show that our caching techniques decrease caching overhead for individual queries by an average of 59%. Furthermore, over the entire workload, ReCache reduces execution time by 19-75% compared to existing techniques.

2017

Database

In computing, a database is an organized collection of data (also known as a data store) stored and accessed electronically through the use of a database management system. Small databases can be st

In-memory database

An in-memory database (IMDB, or main memory database system (MMDB) or memory resident database) is a database management system that primarily relies on main memory for computer data storage. It is c

Database normalization

Database normalization or database normalisation (see spelling differences) is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce