Data orientation is the representation oftabular data in a linear memory model such asin-disk orin-memory. The two most common representations arecolumn-oriented (columnar format) androw-oriented (row format).[1][2]
The choice of data orientation is atrade-off and anarchitectural decision indatabases, query engines, and numerical simulations.[1] As a result of these tradeoffs, row-oriented formats are more commonly used inonline transaction processing (OLTP) and column-oriented formats are more commonly used inonline analytical processing (OLAP).[2]
Examples of column-oriented formats includeApache ORC,[3]Apache Parquet,[4]Apache Arrow,[5] formats used byBigQuery,Amazon Redshift andSnowflake. Predominant examples of row-oriented formats include CSV, formats used in mostrelational databases (Oracle,MySQL etc.), the in-memory format ofApache Spark, andApache Avro.[6]
Tabular data is two dimensional — data is modeled as rows and columns. However, computer systems represent data in alinear memory model, both in-disk and in-memory.[7][8][9] Therefore, a table in a linear memory model requires mapping its two-dimensional scheme into a one-dimensional space. Data orientation is to the decision taken in this mapping. There are two prominent mappings: row-oriented and column-oriented.[1][2]
In a row-oriented database, also known as a rowstore, the elements of the table
| column 1 | column 2 | column 3 |
|---|---|---|
| item 11 | item 12 | item 13 |
| item 21 | item 22 | item 23 |
are stored linearly as
| item 11 | item 12 | item 13 | item 21 | item 22 | item 23 |
I.e. each row of the table is located one after the other. In this orientation, values in the same row are close in space (e.g. similar address in an addressable space).
In a column-oriented database, also known as a columnstore, the elements of the table
| column 1 | column 2 | column 3 |
|---|---|---|
| item 11 | item 12 | item 13 |
| item 21 | item 22 | item 23 |
are stored linearly as
| item 11 | item 21 | item 12 | item 22 | item 13 | item 23 |
I.e. each column of the table is located one after the other. In this orientation, values on the same column are close in space (e.g. similar address in an addressable space).
Seelist of column-oriented DBMSes for more examples.
Data orientation is an importantarchitectural decision of systems handling data because it results in importanttradeoffs inperformance andstorage.[8] Below are selected dimensions of this tradeoff.
Row-oriented benefits from fast random access of rows. Column-oriented benefits from fast random access of columns.In both cases, this is the result of fewer page or cache misses when accessing the data.[8]
Row-oriented benefits from fast insertion of a new row. Column-oriented benefits from fast insertion of a new column.
This dimension is an important reason why row-oriented formats are more commonly used inonline transaction processing (OLTP), as it results in faster transactions in comparison to column-oriented.[2]
Row-oriented benefits from fast access under a filter. Column-oriented benefits from fast access under aprojection.[4][3]
Column-oriented benefits from fast analytics operations. This is the result of being able to leverageSIMD instructions.[5]
Column-oriented benefits from smaller uncompressed size. This is the result of the possibility that this orientation offers to represent certain data types with dedicated encodings.[4][3]
For example, a table of 128 rows with a Boolean column requires 128 bytes in a row-oriented format (one byte per Boolean) but 128 bits (16 bytes) in a column-oriented format (via a bitmap). Another example is the use ofrun-length encoding to encode a column.
Column-oriented benefits from smaller compressed size. This is the result of a higher homogeneity within a column than within multiple rows.[4][3]
Because both orientations represent the same data, it is possible to convert a row-oriented dataset to a column-oriented dataset and vice versa at the expense of compute. In particular, advanced query engines often leverage each orientation's advantages, and convert from one orientation to the other as part of their execution. As an example, anApache Spark query may