Columnar vs Row Storage: Analyzing Scan Cost Differences

What Columnar vs Row Storage Means

In databases, data can be stored in two primary ways: row-oriented (also known as horizontal) or column-oriented (vertical). In a row-oriented storage method, each record is stored contiguously on disk with all fields of that record together. Conversely, in columnar storage, each field value for the entire table is grouped together, allowing for efficient access to specific columns.

The choice between these two methods depends heavily on the type of queries being run and the nature of the data.

Analytical Scan Cost

When running an analytical query, the cost can be significantly influenced by whether the storage method is row-oriented or column-oriented. In a row-oriented database, every record must be read and then filtered to find matching rows, which can be inefficient if only a subset of columns are needed.

In contrast, columnar storage allows for 'column pruning,' where only relevant columns need to be read from disk, reducing the overall I/O cost. Additionally, predicate pushdown in columnar databases can further optimize performance by filtering data at the storage level before it is even retrieved.

Real-World Examples

For instance, consider a database storing financial transactions where each transaction has many fields like date, amount, and account details. If an analyst wants to query only the amounts for a specific day, columnar storage would be more efficient because it can quickly filter out irrelevant columns.

On the other hand, if the query involves joining multiple tables or performing complex operations on all fields of each record, row-oriented storage might perform better due to its simpler structure.

Optimization Techniques

To further optimize performance in columnar storage, techniques such as dictionary encoding and run-length encoding (RLE) can be applied. Dictionary encoding reduces the size of repeated values by storing a unique value once and referencing it multiple times. RLE compresses consecutive identical values into a single byte, reducing the overall data size.

These optimizations are particularly effective in columnar storage because they reduce the amount of data that needs to be read from disk.

Frequently asked questions

Try it live

Everything above runs in your browser — open Columnar vs Row Storage: Analytical Scan Cost and change the parameters while it is running. Nothing is installed, nothing is uploaded, the whole model lives in one tab.