A column-oriented DBMS (or columnar database management system) is a database management system (DBMS) that stores data tables by column rather than by row. 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 ] 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 data visualization tools. However, by storing data in columns rather than rows, the database can more precisely access the data it needs to answer a query rather than scanning and discarding unwanted data in rows.
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 identifier (EmpId), name fields (Lastname and Firstname) and a salary (Salary). This two-dimensional format is an abstraction. In an actual implementation, storage hardware requires the data to be serialized into one form or another.
The most expensive operations involving hard disks are seeks. In order to improve overall performance, related data should be stored in a fashion to minimize the number of seeks. This is known as locality of reference, and the basic concept appears in a number of different contexts. Hard disks are organized into a series of blocks of a fixed size, typically enough to store several rows of the table. By organizing the table's data so rows fit within these blocks, and grouping related rows onto sequential blocks, the number of blocks that need to be read or sought is minimized in many cases, along with the number of seeks.
A survey by Pinnecke et al.covers techniques for column-/row hybridization as of 2017.
A common method of storing a table is to serialize each row of data, like this:
001:10,Smith,Joe,60000; 002:12,Jones,Mary,80000; 003:11,Johnson,Cathy,94000; 004:22,Jones,Bob,55000;
As data is inserted into the table, it is assigned an internal ID, the
rowid that is used internally in the system to refer to data. In this case the records have sequential
rowids independent of the user-assigned
empid. In this example, the DBMS uses short integers to store
rowids. In practice, larger numbers, 64-bit or 128-bit, are normally used.
Row-based systems are designed to efficiently return data for an entire row, or record, in as few operations as possible. This matches the common use-case where the system is attempting to retrieve information about a particular object, say the contact information for a user in a rolodex system, or product information for an online shopping system. By storing the record's data in a single block on the disk, along with related records, the system can quickly retrieve records with a minimum of disk operations.
Row-based systems are not efficient at performing set-wide operations on the whole table, as opposed to a small number of specific records. For instance, in order to find all records in the example table with salaries between 40,000 and 50,000, the DBMS would have to fully scan through the entire table looking for matching records. While the example table shown above will likely fit in a single disk block, a table with even a few hundred rows would not, and multiple disk operations would be needed to retrieve the data and examine it.
To improve the performance of these sorts of operations (which are very common, and generally the point of using a DBMS), most DBMSs support the use of database indexes, which store all the values from a set of columns along with
rowid pointers back into the original table. An index on the salary column would look something like this:
55000:004; 60000:001; 80000:002; 94000:003;
As they store only single pieces of data, rather than entire rows, indexes are generally much smaller than the main table stores. Scanning this smaller set of data reduces the number of disk operations. If the index is heavily used, it can dramatically reduce the time for common operations. However, maintaining indexes adds overhead to the system, especially when new data is written to the database. Records not only need to be stored in the main table, but any attached indexes have to be updated as well.
The main reason why indexes dramatically improve performance on large datasets is that database indexes on one or more columns are typically sorted by value, which makes range queries operations (like the above "find all records with salaries between 40,000 and 50,000" example) very fast (lower time-complexity).
A number of row-oriented databases are designed to fit entirely in RAM, an in-memory database. These systems do not depend on disk operations, and have equal-time access to the entire dataset. This reduces the need for indexes, as it requires the same amount of operations to fully scan the original data as a complete index for typical aggregation purposes. Such systems may be therefore simpler and smaller, but can only manage databases that will fit in memory.
A column-oriented database serializes all of the values of a column together, then the values of the next column, and so on. For our example table, the data would be stored in this fashion:
10:001,12:002,11:003,22:004; Smith:001,Jones:002,Johnson:003,Jones:004; Joe:001,Mary:002,Cathy:003,Bob:004; 60000:001,80000:002,94000:003,55000:004;
In this layout, any one of the columns more closely matches the structure of an index in a row-based system. This may cause confusion that can lead to the mistaken belief a column-oriented store "is really just" a row-store with an index on every column. However, it is the mapping of the data that differs dramatically. In a row-oriented indexed system, the primary key is the rowid that is mapped from indexed data. In the column-oriented system, the primary key is the data, which is mapped from rowids.This may seem subtle, but the difference can be seen in this common modification to the same store wherein the two "Jones" items, above, are compressed into a single item with two rowids:
Whether or not a column-oriented system will be more efficient in operation depends heavily on the workload being automated. Operations that retrieve all the data for a given object (the entire row) are slower. A row-based system can retrieve the row in a single disk read, whereas numerous disk operations to collect data from multiple columns are required from a columnar database. However, these whole-row operations are generally rare. In the majority of cases, only a limited subset of data is retrieved. In a rolodex application, for instance, collecting the first and last names from many rows to build a list of contacts is far more common than reading all data for any single address. This is even more true for writing data into the database, especially if the data tends to be "sparse" with many optional columns. For this reason, column stores have demonstrated excellent real-world performance in spite of many theoretical disadvantages.
Partitioning, indexing, caching, views, OLAP cubes, and transactional systems such as write-ahead logging or multiversion concurrency control all dramatically affect the physical organization of either system. That said, online transaction processing (OLTP)-focused RDBMS systems are more row-oriented, while online analytical processing (OLAP)-focused systems are a balance of row-oriented and column-oriented.
Comparisons between row-oriented and column-oriented databases are typically concerned with the efficiency of hard-disk access for a given workload, as seek time is incredibly long compared to the other bottlenecks in computers. For example, a typical Serial ATA (SATA) hard drive has an average seek time of between 16 and 22 millisecondswhile DRAM access on an Intel Core i7 processor takes on average 60 nanoseconds, nearly 400,000 times as fast. Clearly, disk access is a major bottleneck in handling big data. Columnar databases boost performance by reducing the amount of data that needs to be read from disk, both by efficiently compressing the similar columnar data and by reading only the data necessary to answer the query.
In practice, columnar databases are well-suited for OLAP-like workloads (e.g., data warehouses) which typically involve highly complex queries over all data (possibly petabytes). However, some work must be done to write data into a columnar database. Transactions (INSERTs) must be separated into columns and compressed as they are stored, making it less suited for OLTP workloads. Row-oriented databases are well-suited for OLTP-like workloads which are more heavily loaded with interactive transactions. For example, retrieving all data from a single row is more efficient when that data is located in a single location (minimizing disk seeks), as in row-oriented architectures. However, column-oriented systems have been developed as hybrids capable of both OLTP and OLAP operations. Some of the OLTP constraints, faced by such column-oriented systems, are mediated using (amongst other qualities) in-memory data storage.Column-oriented systems suitable for both OLAP and OLTP roles effectively reduce the total data footprint by removing the need for separate systems.
Column data is of uniform type; therefore, there are some opportunities for storage size optimizations available in column-oriented data that are not available in row-oriented data. For example, many popular modern compression schemes, such as LZW or run-length encoding, make use of the similarity of adjacent data to compress. Missing values and repeated values, common in clinical data, can be represented by a two-bit marker.While the same techniques may be used on row-oriented data, a typical implementation will achieve less effective results.
To improve compression, sorting rows can also help. For example, using bitmap indexes, sorting can improve compression by an order of magnitude.To maximize the compression benefits of the lexicographical order with respect to run-length encoding, it is best to use low-cardinality columns as the first sort keys. For example, given a table with columns sex, age, name, it would be best to sort first on the value sex (cardinality of two), then age (cardinality of <128), then name.
Columnar compression achieves a reduction in disk space at the expense of efficiency of retrieval. The greater adjacent compression achieved, the more difficult random-access may become, as data might need to be uncompressed to be read. Therefore, column-oriented architectures are sometimes enriched by additional mechanisms aimed at minimizing the need for access to compressed data.
Column stores or transposed files have been implemented from the early days of DBMS development. TAXIR was the first application of a column-oriented database storage system with focus on information-retrieval in biologyin 1969. Clinical data from patient records with many more attributes than could be analyzed were processed in 1975 and after by a time-oriented database system (TODS). Statistics Canada implemented the RAPID system in 1976 and used it for processing and retrieval of the Canadian Census of Population and Housing as well as several other statistical applications. RAPID was shared with other statistical organizations throughout the world and used widely in the 1980s. It continued to be used by Statistics Canada until the 1990s.
Another column-oriented database was SCSS.
Later column-oriented database packages included:
Since about 2004 there have been additional open source and commercial implementations. MonetDB was released under an open-source license on September 30, 2004,followed closely by the now defunct C-Store.
C-store was a university project that eventually, with team member Michael Stonebraker staying on, led to Vertica, which he co-founded in 2005.
The MonetDB-related X100 project evolved into VectorWise.Druid is a column-oriented data store that was open-sourced in late 2012 and is now used by numerous organizations.
Classic Relational DBMS can use column-oriented strategies by mixing row-oriented and column-oriented tables. Despite the DBMS complexity, this approach has proven to be valuable from the years 2010 to present. For example in 2014 Citusdata introduced column-oriented tables for PostgreSQLand McObject added support for columnar storage with its release of eXtremeDB Financial Edition in 2012 which was then used to establish a new standard of performance for the independently audited STAC-M3 benchmark.
In computing, a data warehouse, also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis, and is considered a core component of business intelligence. DWs are central repositories of integrated data from one or more disparate sources. They store current and historical data in one single place that are used for creating analytical reports for workers throughout the enterprise.
A database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques.
Db2 is a family of data management products, including database servers, developed by IBM. They initially supported the relational model, but were extended to support object-relational features and non-relational structures like JSON and XML. The brand name was originally styled as DB/2, then DB2 until 2017 and finally changed to its present form.
Online analytical processing, or OLAP, is an approach to answer multi-dimensional analytical (MDA) queries swiftly in computing. OLAP is part of the broader category of business intelligence, which also encompasses relational databases, report writing and data mining. Typical applications of OLAP include business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas, with new applications emerging, such as agriculture.
In a relational database, a column is a set of data values of a particular simple type, one value for each row of the database. A column may contain text values, numbers, or even pointers to files in the operating system. Some relational database systems allow columns to contain more complex data types; whole documents, images or even video clips are examples. A column can also be called an attribute.
In Online transaction processing (OLTP), information systems typically facilitate and manage transaction-oriented applications.
C-Store is a database management system (DBMS) based on a column-oriented DBMS developed by a team at Brown University, Brandeis University, Massachusetts Institute of Technology and the University of Massachusetts Boston including Michael Stonebraker, Stanley Zdonik, and Samuel Madden. The last release of the original code was in 2006; Vertica a commercial fork, lives on.
SAP IQ is a column-based, petabyte scale, relational database software system used for business intelligence, data warehousing, and data marts. Produced by Sybase Inc., now an SAP company, its primary function is to analyze large amounts of data in a low-cost, highly available environment. SAP IQ is often credited with pioneering the commercialization of column-store technology.
Exasol is an analytics database management software company. Its product is called Exasol, an in-memory, column-oriented, relational database management system
Vertica Systems is an analytic database management software company. Vertica was founded in 2005 by database researcher Michael Stonebraker and Andrew Palmer. Palmer was the founding CEO. Ralph Breslauer and Christopher P. Lynch served as later CEOs.
A correlation database is a database management system (DBMS) that is data-model-independent and designed to efficiently handle unplanned, ad hoc queries in an analytical system environment.
Infobright is a commercial provider of column-oriented relational database software with a focus in machine-generated data. The company's head office is located in Toronto, Ontario, Canada. Most of its research and development is based in Warsaw, Poland. Support personnel are located in various offices around the world.
RCFile is a data structure that determines how to store relational tables on computer clusters. It was originally designed for systems using the MapReduce framework in 2010. Since then, RCFile has become a standard storage format for both conventional databases and distributed databases on clusters.
MemSQL is a distributed, relational, SQL database management system (RDBMS) that features ANSI SQL support and is known for speed in data ingest, transaction processing, and query processing. MemSQL supports blended workloads, commonly referred to as translytical,HTAP, HOAP, and ATP workloads. For queries, it compiles Structured Query Language (SQL) into machine code. The same MemSQL database engine runs wherever Linux does, including on-premise installations, public cloud providers and in private clouds, in containers via a Kubernetes operator, or as a hosted service in the cloud known as memSQL Helios.
NewSQL is a class of relational database management systems that seek to provide the scalability of NoSQL systems for online transaction processing (OLTP) workloads while maintaining the ACID guarantees of a traditional database system.
SAP HANA is an in-memory, column-oriented, relational database management system developed and marketed by SAP SE. Its primary function as a database server is to store and retrieve data as requested by the applications. In addition, it performs advanced analytics and includes extract, transform, load (ETL) capabilities as well as an application server.
IBM BLU Acceleration is a collection of technologies from the IBM Research and Development Labs for analytical database workloads. BLU Acceleration integrates a number of different technologies including in-memory processing of columnar data, Actionable Compression, CPU Acceleration, and Data Skipping. The term ‘BLU’ does not stand for anything in particular; however it has an indirect play on IBM's traditional corporate nickname Big Blue. BLU Acceleration does not require indexes, aggregates or tuning. BLU Acceleration is integrated in Version 10.5 of IBM DB2 for Linux, Unix and Windows,(DB2 for LUW) and uses the same storage and memory constructs, SQL language interfaces, and administration tools as traditional DB2 for LUW databases. BLU Acceleration is available on both IBM POWER and x86 processor architectures.
A wide-column store is a type of NoSQL database. It uses tables, rows, and columns, but unlike a relational database, the names and format of the columns can vary from row to row in the same table. A wide-column store can be interpreted as a two-dimensional key–value store.
ClickHouse is an open-source column-oriented DBMS for online analytical processing (OLAP).