The Data Processing Holy Grail? Row vs. Columnar Databases
Tags: database, sql, mongodb, postgres
Published: 2019-12-09 11:53:25

---
title: The Data Processing Holy Grail? Row vs. Columnar Databases
published: true
description: Columnar databases are great for processing big amounts of data quickly. Let's explore how they perform when compared with row DBs like Mongo and PSQL.
tags: database, sql, mongodb, postgres
canonical_url: https://blog.jscrambler.com/the-data-processing-holy-grail-row-vs-columnar-databases/
cover_image: https://blog.jscrambler.com/content/images/2019/12/jscrambler-blog-data-processing-holy-grail.jpg
---

It is arguable that, in recent decades, data has become the #1 resource in the world, dethroning oil as the most valuable asset. However, it may only reach its full potential if properly processed — that is, extracted, stored and analyzed in a dynamic and productive fashion. Thus, in order to collect all the knowledge portrayed by your data, it is crucial to have effective data processing pipelines.

Throughout this blog post, we will cover the fundamentals that will enable you to build efficient data processing mechanisms, emphasizing analytical solutions in particular. To kick things off, let’s look at the two main data processing systems.

## OLAP vs. OLTP

**OLTP**, Online Transaction Processing, is the most traditional processing system. It is able to manage transaction-oriented applications and can be characterized by a large number of short, atomic database operations, such as *inserts, updates, and deletes*, quite common in your day-to-day application. Common examples include online banking and e-commerce applications.

**OLAP**, Online Analytical Processing, manages historical or archival data. It is characterized by a relatively low volume of transactions. OLAP systems are typically used for analytical purposes — to extract insights and knowledge from bulk data, merged from multiple sources. Unlike OLTP, the goal for OLAP systems is to have a limited amount of transactions, each consisting of mostly bulk *reads and writes*. Data warehouses are the typical infrastructure to maintain these systems.

<table style="height: 235px;" width="720">
<tbody>
<tr>
<td style="width: 430px; font-size: 18px;"><strong>OLTP</strong></td>
<td style="width: 430px; font-size: 18px;"><strong>OLAP</strong></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400;">Low volume of data</span></td>
<td style="width: 430px;"><span style="font-weight: 400;">High volume of data</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400;">High volume of transactions</span></td>
<td style="width: 430px;"><span style="font-weight: 400;">Low volume of transactions</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400;">Typically normalized data</span></td>
<td style="width: 430px;"><span style="font-weight: 400;">Denormalized data</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400;">ACID compliance </span></td>
<td style="width: 430px;"><span style="font-weight: 400;">Not necessarily ACID-compliant </span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400;">Require high availability&nbsp; </span></td>
<td style="width: 430px;"><span style="font-weight: 400;">Don&rsquo;t usually require high availability </span></td>
</tr>
</tbody>
</table>

<center><img src="https://blog.jscrambler.com/content/images/2019/12/jscrambler-blog-olap-schematics.png" alt="OLAP Schematics"></center>

Hopefully, by now you are able to distinguish both data processing systems easily. Both OLTP and OLAP systems have been around for quite some time, but recently, with the boom of data mining and machine learning techniques, the demand for OLAP systems has increased. Choosing a suitable technological infrastructure to host either system is also a crucial step to ensure your system or application is delivering the best performance for your needs.

## Row vs. Columnar Databases

You might have never heard of the terms *row* and *columnar* associated with databases, but you have most certainly come across them before. Row-oriented databases are your typical transactional ones, able to handle a large number of transactions, whereas column-oriented databases usually have fewer transactions, with a higher volume of data. By now, you probably have already guessed which type of database is more suitable for each processing system. Row-oriented DBs are commonly used in OLTP systems, whereas columnar ones are more suitable for OLAP. Some examples include:

<table style="height: 100%;" width="720">
<tbody>
<tr>
<td style="width: 430px; font-size: 18px;"><strong>Row-oriented</strong></td>
<td style="width: 430px; font-size: 18px;"><strong>Columnar</strong></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400;">MySQL</span></td>
<td style="width: 430px;"><span style="font-weight: 400;">Amazon Redshift</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400;">PostgreSQL</span></td>
<td style="width: 430px;"><span style="font-weight: 400;">MariaDB</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400;">Oracle</span></td>
<td style="width: 430px;"><span style="font-weight: 400;">ClickHouse</span></td>
</tr>
<tr>
<td style="width: 430px;">...</td>
<td style="width: 430px;">...</td>
</tr>
</tbody>
</table>

**But what makes them different internally?**

The key difference between both data stores is the way they are physically stored on disk. Common HDDs are organized in blocks and rely on expensive head seek operations, thus sequential *reads and writes* tend to be much faster than random accesses.

> Row-oriented databases store the whole row in the same block, if possible. Columnar databases store columns in subsequent blocks.

**But what does this mean in practice?**

Amazon Redshift provides a simple and concise explanation highlighting the differences between both databases. The figure shown below consists of row-wise database storage, where each row is stored in a sequential disk block. Picking the ideal block size is important to grant optimal performance, since having a block size that’s too small or too big results in inefficient use of disk space.

<center><img src="https://blog.jscrambler.com/content/images/2019/12/jscrambler-blog-redshift-1.png" alt="Redshift"></center>

The figure below portrays columnar database storage, where each disk block stores values of a single column for multiple rows. In this example, using columnar storage requires one-third of I/O disk operations to extract the columns’ values, compared to a row-wise database.

<center><img src="https://blog.jscrambler.com/content/images/2019/12/jscrambler-blog-redshift-2.png" alt="Redshift"></center>

## Performance Example

Let’s look at a simple example to fully understand the differences between both databases.

Consider a database storing a total of **100GB of data, with 100 million rows and 100 columns (1GB per column)**. For simplification purposes, consider that the database administrator is a rookie and hasn’t implemented any indexes, partitioning, or any other optimization process on the database. With this in mind, for the analytical query:

> What is the average age of males?

We would have these possibilities:

Row wise DB  ->  Has to read all the data in the database — **100GB to read**
Columnar DB  ->  Has to read only the columns age and gender — **2GB to read**

This is obviously an extreme example — hardly any database will completely lack indexes or other optimization processes, but the goal is to give you an overview of columnar databases’ true potential for analytical purposes.

## Row and Columnar Databases Wrap-up

Now that you have an overview of row-oriented and columnar databases, as well as the main differences between them, highlighting their advantages and disadvantages shouldn’t be too hard:

<table style="height: 100%;" width="720">
<tbody>
<tr>
<td style="width: 430px; font-size: 18px;"><strong>Row-oriented DB: <em><span style="font-weight: 400;">for OLTP</span></em></strong></td>
<td style="width: 430px; font-size: 18px;"><strong>Columnar DB: <em><span style="font-weight: 400;">for OLAP</span></em></strong></td>
</tr>
<tr>
<td style="width: 430px;"><span style="color: #339966;"><span style="font-weight: 400;">Performs fast multiple </span><em><span style="font-weight: 400;">read, write, update, delete </span></em><span style="font-weight: 400;">operations</span></span></td>
<td style="width: 430px;"><span style="color: #ff0000;"><span style="font-weight: 400;">Performs slowly if required to perform multiple </span><em><span style="font-weight: 400;">read, write, update, delete </span></em><span style="font-weight: 400;">operations</span></span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400; color: #ff0000;">Bulk operations (<em>read</em> and <em>write)</em> aren&rsquo;t particularly fast</span></td>
<td style="width: 430px;"><span style="font-weight: 400; color: #339966;">Performs fast bulk operations (mostly <em>read</em> and <em>write</em>)</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400; color: #339966;">ACID compliance</span></td>
<td style="width: 430px;"><span style="color: #ff0000;">No ACID compliance</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400; color: #ff0000;">Typically inefficient data compression</span></td>
<td style="width: 430px;"><span style="color: #339966;">Improved data compression &mdash; columns are individually compressed and each one is of the same type</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400; color: #ff0000;">High storage size (indexes, etc.)</span></td>
<td style="width: 430px;"><span style="font-weight: 400; color: #339966;">Low storage size</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400; color: #ff0000;">Typically require multiple indexes, depending on queries</span></td>
<td style="width: 430px;"><span style="font-weight: 400; color: #339966;">Relies on a single index per column (self-indexing)</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400; color: #339966;">Easy to add a single row (1 insert operation)</span></td>
<td style="width: 430px;"><span style="font-weight: 400; color: #ff0000;">Hard to add a single row (multi-column insert operation)</span></td>
</tr>
<tr>
<td style="width: 430px;"><span style="font-weight: 400; color: #ff0000;">Hard to add a single column (multi-row insert operation)</span></td>
<td style="width: 430px;"><span style="font-weight: 400; color: #339966;">Easy to add a single column (1 insert operation)</span></td>
</tr>
</tbody>
</table>

## Benchmarks

Everyone loves theoretical concepts (well, at least let’s suppose so), but why not put them to practice? With this in mind, we set up an experiment to compare their performance in a real case scenario. For this purpose, we used different technologies, aiming to compare their performance on both OLTP and OLAP systems. The technologies selected were:

**OLTP**

- [MongoDB](https://www.mongodb.com/) — a NoSQL database, widely used on several applications. Excels at delivering fast and dynamic transactions, made possible by its schema-free document-oriented mechanism.
- [PostgreSQL](https://www.postgresql.org/) — a free and open-source database, widely dynamic and extensible. Uses range from small applications to data warehouses.
- [Citus](https://www.citusdata.com/) — a “worry-free Postgre built to scale-out”. It is a PostgreSQL extension that distributes data and queries across several nodes.

**OLAP**

- [Cstore_fdw](https://github.com/citusdata/cstore_fdw) — an open-source Postgres extension, developed by Citus Data, that *transforms* the original row-oriented Postgres database into a columnar database. 
- [ClickHouse](https://clickhouse.yandex/) — a recent, open-source columnar database developed by Yandex. It claims to be capable of delivering real-time analytical data reports using a SQL-like syntax.

### Queries Performance

To conduct the benchmark, we used a database with approximately 135 million records of web event logs, distributed across 4 different tables. All the technologies were used on-premise, on a local machine with an i5-9600k processor clocked at 3.70 GHz coupled with 32GB of RAM.

We made a set of queries that are typically considered *analytical* (i.e. focus mostly on columns rather than rows). In order to make the process as homogeneous as possible, for each query we ran, we flushed the respective storage engine’s cache and restarted the machine each time we switched technology. Additionally, for the row-wise databases, we built the most efficient indexes for the queries we created. We built no indexes for the columnar storages other than the ones that are created by default upon populating these databases. The results are displayed in the table below.

<table style="width: 694px;">
<tbody>
<tr>
<td style="width: 202px;">
<p><strong>Queries (s)</strong></p>
</td>
<td style="width: 92px;">
<p><strong>Mongo DB</strong></p>
</td>
<td style="width: 71px;">
<p><strong>PSQL</strong></p>
</td>
<td style="width: 86px;">
<p><strong>Citus</strong></p>
</td>
<td style="width: 153px;">
<p><strong>PSQL cstore_fdw</strong></p>
</td>
<td style="width: 100px;">
<p><strong>Click House</strong></p>
</td>
</tr>
<tr>
<td style="width: 202px;">
<p><strong>Q1: Total events</strong></p>
</td>
<td style="width: 92px;">
<p><span style="font-weight: 400;">1</span></p>
</td>
<td style="width: 71px;">
<p><span style="font-weight: 400;">92</span></p>
</td>
<td style="width: 86px;">
<p><span style="font-weight: 400;">46</span></p>
</td>
<td style="width: 153px;">
<p><span style="font-weight: 400;">3</span></p>
</td>
<td style="width: 100px;">
<p><strong>&lt;</strong><span style="font-weight: 400;"> 1</span></p>
</td>
</tr>
<tr>
<td style="width: 202px;">
<p><strong>Q2: Total events of type &lsquo;A&rsquo;</strong></p>
</td>
<td style="width: 92px;">
<p><span style="font-weight: 400;">207</span></p>
</td>
<td style="width: 71px;">
<p><span style="font-weight: 400;">94</span></p>
</td>
<td style="width: 86px;">
<p><span style="font-weight: 400;">46</span></p>
</td>
<td style="width: 153px;">
<p><span style="font-weight: 400;">3</span></p>
</td>
<td style="width: 100px;">
<p><strong>&lt;</strong><span style="font-weight: 400;"> 1</span></p>
</td>
</tr>
<tr>
<td style="width: 202px;">
<p><strong>Q3: Daily events distribution</strong></p>
</td>
<td style="width: 92px;">
<p><span style="font-weight: 400;">462</span></p>
</td>
<td style="width: 71px;">
<p><span style="font-weight: 400;">87</span></p>
</td>
<td style="width: 86px;">
<p><span style="font-weight: 400;">51</span></p>
</td>
<td style="width: 153px;">
<p><span style="font-weight: 400;">18</span></p>
</td>
<td style="width: 100px;">
<p><span style="font-weight: 400;">3</span></p>
</td>
</tr>
<tr>
<td style="width: 202px;">
<p><strong>Q4: Events distribution by operation</strong></p>
</td>
<td style="width: 92px;">
<p><span style="font-weight: 400;">357</span></p>
</td>
<td style="width: 71px;">
<p><span style="font-weight: 400;">96</span></p>
</td>
<td style="width: 86px;">
<p><span style="font-weight: 400;">15</span></p>
</td>
<td style="width: 153px;">
<p><span style="font-weight: 400;">8</span></p>
</td>
<td style="width: 100px;">
<p><strong>&lt;</strong><span style="font-weight: 400;"> 1</span></p>
</td>
</tr>
<tr>
<td style="width: 202px;">
<p><strong>Q5: Top 10 events distribution by operation</strong></p>
</td>
<td style="width: 92px;">
<p><span style="font-weight: 400;">288</span></p>
</td>
<td style="width: 71px;">
<p><span style="font-weight: 400;">91</span></p>
</td>
<td style="width: 86px;">
<p><span style="font-weight: 400;">26</span></p>
</td>
<td style="width: 153px;">
<p><span style="font-weight: 400;">5</span></p>
</td>
<td style="width: 100px;">
<p><strong>&lt;</strong><span style="font-weight: 400;"> 1</span></p>
</td>
</tr>
<tr>
<td style="width: 202px;">
<p><strong>Q6: # of events containing images</strong></p>
</td>
<td style="width: 92px;">
<p><span style="font-weight: 400;">266</span></p>
</td>
<td style="width: 71px;">
<p><span style="font-weight: 400;">99</span></p>
</td>
<td style="width: 86px;">
<p><span style="font-weight: 400;">97</span></p>
</td>
<td style="width: 153px;">
<p><span style="font-weight: 400;">216</span></p>
</td>
<td style="width: 100px;">
<p><span style="font-weight: 400;">4</span></p>
</td>
</tr>
<tr>
<td style="width: 202px;">
<p><strong>Q7: # of events containing scripts</strong></p>
</td>
<td style="width: 92px;">
<p><span style="font-weight: 400;">270</span></p>
</td>
<td style="width: 71px;">
<p><span style="font-weight: 400;">276</span></p>
</td>
<td style="width: 86px;">
<p><span style="font-weight: 400;">143</span></p>
</td>
<td style="width: 153px;">
<p><span style="font-weight: 400;">456</span></p>
</td>
<td style="width: 100px;">
<p><span style="font-weight: 400;">14</span></p>
</td>
</tr>
</tbody>
</table>

By looking at the data above, it is no surprise that columnar databases (i.e. PostgreSQL cstore_fdw and ClickHouse) have considerably shorter running times when compared to the other technologies. However, cstore_fdw is under-optimized for queries that require joining tables (e.g. left join) and perform a text search, as denoted by the running times for queries Q6 and Q7.

ClickHouse, on the other hand, outperforms every single technology by far, especially when it comes to cstore_fdw’s caveats: joins combined with text search. While ClickHouse doesn’t inherently support joining tables, this issue can be bypassed by using subqueries. The image below displays a comparative view of all the technologies and their performance on the analytical queries (lower is better).

<center><img src="https://blog.jscrambler.com/content/images/2019/12/jscrambler-blog-data-processing-queries-performance-graph.png" alt="Data Processing Queries Performance"></center>

### Storage Size

As you might recall from our first comparison table, columnar storage has improved compression mechanisms over row-wise databases. This happens essentially because each column, compressed as a single file, has a unique data type. Besides, as mentioned in the previous section, these databases don’t have indexes besides the one created by default for the (primary) key. Such features allow for highly efficient storage space, as shown on the graph below, with ClickHouse taking 10GB of space, followed by the raw data itself with 76GB occupied and, finally, PostgreSQL, with 78GB of taken storage space. This is a 780% increase over ClickHouse.

![jscrambler-blog-data-processing-storage-size-graph](https://blog.jscrambler.com/content/images/2019/12/jscrambler-blog-data-processing-storage-size-graph.png)

Note that for PostgreSQL (and other row-wise databases), besides the space data itself occupies, indexes also contribute to the substantial increase of storage space.

## Final Remarks

Columnar databases are rather incredible at what they do ⁠— processing massive amounts of data in a matter of seconds or even less. There are many examples out there — Redshift, BigQuery, Snowflake, MonetDB, Greenplum, MemSQL, ClickHouse — and all offer optimal performance for your analytical needs.

However, their underlying architecture introduces a considerable caveat: data ingestion. They offer poor performance for mixed workloads, that require real-time high-throughput. In other words, they can’t match a transactional database’s real-time data ingestion performance which allows the insertion of data into the system quite fast.

Combining fast ingestion and querying is the holy grail of data processing systems. Achieving an optimal mixed workload is probably not possible at this point since there is no single do-it-all technology that excels at both. Having a database for each purpose is the typical way to go, but it largely limits the performance of the whole system.

Data processing is a complex but incredibly interesting technical design area. We firmly believe that scalable real-time high-throughput analytical databases are possible, but they don’t exist.

Yet.

---

Originally published on the [Jscrambler Blog](https://blog.jscrambler.com/the-data-processing-holy-grail-row-vs-columnar-databases/?utm_source=dev.to&utm_medium=referral).