In recent decades, we’ve experienced an explosion in data volume. In the IT world, this translates into a deluge of logs, metrics, traces… Processing and storing all this information is an important step. But to truly harness the value of our data, we need to query it quickly and flexibly. This is where OLAP (Online Analytical Processing) technologies come into play. But what exactly does OLAP mean?
Depending on the type of workloads they support, database management systems (DBMS) can generally be classified into two categories:
OLTP
OLAP
- Online Transactional Processing (OLTP): Designed for short, frequent operations like recording a sale or updating a user profile.
- Online Analytical Processing (OLAP): Optimized for complex queries that scan millions or billions of rows to compute aggregations and perform data analysis.
Typically, database systems focus on one use case or the other, since optimizing for OLTP often hinders OLAP performance and vice versa. In practice, however, systems attempt to support as many use cases as possible and are increasingly incorporating features from both paradigms to boost adoption.
A perfect use case illustrating the need for OLAP is system observability. In modern distributed environments, traditional monitoring—checking whether a known error has occurred—is no longer enough. Engineers need to freely explore data (logs, traces, metrics) to understand unexpected behaviors by asking questions they didn’t know they needed to ask, such as:
“What was the average request latency for users of a specific client using a particular version of the mobile app during yesterday’s traffic peak?”
This is a pure OLAP query: complex, over a large volume of data, and requiring a fast response to be useful.
ClickHouse is an open-source, column-oriented database management system specifically designed for Online Analytical Processing (OLAP). While it is gradually working to implement new features that bring it closer to the OLTP (Online Transaction Processing) world—such as support for traditional transactions, which is currently in beta—it remains focused on OLAP.
What makes ClickHouse different?
Several technical features make ClickHouse particularly effective for OLAP workloads:
Columnar Storage: Unlike traditional databases that store data by rows, ClickHouse stores data by columns. This means each column is saved in separate files. So, when you run an analytical query that only requires 3 columns out of a 50-column table, ClickHouse reads only those 3 columns. This drastically reduces disk I/O, which is often a major bottleneck. Additionally, storing data by column makes it more homogeneous, allowing compression algorithms to work much more efficiently. ClickHouse uses modern codecs such as LZ4 and ZSTD to achieve high compression ratios. In analytical contexts, where only a subset of columns is typically needed, columnar databases can significantly improve query performance. However, this approach can be inefficient when all columns from a single or just a few rows are needed, as it would require opening 50 different files to read just a few data points.- Vectorized Processing: ClickHouse does not process data value by value. Instead, it operates on batches or “vectors” of values from a column, making the most of modern CPUs to efficiently process data in blocks.
- Sparse Indexes: Instead of indexing every row, ClickHouse stores a marker (or “mark”) for every large block of data (e.g., every 8,192 rows). For this to work, the data must be physically ordered on disk according to a key (ORDER BY). These sparse indexes ensure that even with massive datasets, the index remains small enough to fit in memory, enabling fast selective scanning of data blocks. However, while this is highly effective for processing large volumes of data, it can be inefficient when searching for a specific document. Since the data is not indexed row by row, the system must load entire blocks containing thousands of rows, potentially reading a lot of irrelevant data. As previously mentioned, optimizing for OLAP or OLTP usually involves trade-offs. This is a clear example of how a technical decision that favors OLAP use cases can negatively impact OLTP scenarios.
ClickHouse also has some drawbacks
Although ClickHouse is incredibly fast at processing large amounts of data, it also has its own Achilles’ heels. Data in ClickHouse is written to disk in immutable parts or blocks. Once written, these blocks cannot be changed. Therefore, when an UPDATE or DELETE operation is issued, ClickHouse does not search for and modify the row directly. Instead, it initiates an asynchronous and resource-intensive process called a “mutation.” This process completely rewrites each data block that contains rows needing modification, applying the changes to the new copy and marking the old blocks for later deletion. This is a costly and slow operation, designed to be used sparingly. However, in recent years, ClickHouse introduced “lightweight DELETEs,” which work more like other databases: rows can be marked for deletion so that queries filter out those rows, and in the future, when blocks are merged, those rows will be discarded.
How does ClickHouse compare to other systems?
On the official ClickHouse blog, they conducted a comparison between ClickHouse and other database management systems called The Billion Docs JSON Challenge. The goal was to compare how different technologies store one billion JSON documents and to evaluate the performance of various aggregations on these data. Regarding disk usage, the comparison results were as follows:
As we can see on the left, the raw JSON files occupy 482 GB. When applying the compression algorithm, they occupy 124 GB. Regarding databases, it would be logical for the documents to take up more space, even using the same ZSTD algorithm, since databases need to store additional metadata and data structures such as indexes. However, we see that in ClickHouse the data occupies even less space than the compressed original files! The reason for this, as mentioned before, is that ClickHouse is a columnar database, which helps the data compress better. Additionally, remember that ClickHouse uses sparse indexes that create an entry for every few thousand documents, so the indexes take up less space than in other systems.
On the other hand, MongoDB also achieves decent compression, but not to the level of ClickHouse, since MongoDB is not a columnar database and its indexes take up more space. Regarding Elasticsearch, in this comparison, they tried tuning several parameters to make the comparison fairer, but in reality, it is still not entirely fair since Elasticsearch is optimized for text search, which implies certain design decisions and extra metadata that must be stored.
Finally, regarding PostgreSQL, it’s worth noting that by default it usually does not use data compression unless columns are very large, which was not the case in this comparison where the JSON document fields were small. Therefore, it could be said that PostgreSQL does not use compression, and the database occupies the same space as the uncompressed JSON files plus additional space for indexes and other metadata.
ClickHouse was also the fastest technology when executing certain aggregations (the results of these aggregations were calculated using the best compression type). For example, here are the results of running a query that calculates how many posts users of BlueSky write, “repost,” or “like” per hour of the day:
Finally, we can also add that there are other options that could compete with ClickHouse in this comparison. For example, PostgreSQL has an extension called TimescaleDB, which allows us to get the best of both worlds. Initially, data is stored more or less normally, but later, based on defined policies, it can move to a columnstore where the data is stored in a columnar format. Some comparisons have shown that TimescaleDB can perform better than ClickHouse for certain use cases. However, both have their strengths.
Conclusion
ClickHouse is a database management system primarily designed for OLAP use cases, excelling at storing and processing large amounts of data. However, achieving this involves certain technical decisions, such as storing information in a columnar format or using sparse indexes, which help deliver high performance when storing and processing large volumes of data, but can hinder other OLTP use cases where something as simple as constantly updating individual documents can be quite inefficient.
At a time when the ability to quickly analyze large volumes of data is more critical than ever for use cases like observability, business intelligence, or product analytics, understanding the strengths and trade-offs of tools like ClickHouse is essential to building data architectures that are powerful, scalable, and efficient.
