Similar to databases in other categories like search, transactional, and graph databases, real-time analytics databases like Clickhouse have a dedicated data format that is purpose-built for the use case of real-time analytics.
Thanks to this unfortunate fact, we, and many others, have always needed to duplicate our data from different stores into Clickhouse in order to benefit from its impressive performance.
Although this duplication made a lot of sense when moving data from a transactional database like PostgreSQL or MySQL into ClickHouse (due to how much faster analytical queries run on columnar databases), around a year ago we started suspecting that this duplication might make much less sense when we stored the same data in our data warehouse and Clickhouse.
Internally, our ingestion pipeline wrote events both into ClickHouse (for fast customer-facing analytics, 1 month data retention) and into our Iceberg data warehouse (for data archiving and internal ETL jobs). Unlike our PostgreSQL<>Clickhouse sync though, the way Clickhouse stores data is very similar to how our data warehouse stores it:
- Both ClickHouse and Iceberg store data in a columnar format.
- Both ClickHouse and Iceberg use the same compression algorithms to compress data.
- Both ClickHouse and Iceberg split data into "chunks" - row groups in Parquet and granules in ClickHouse - and store min/max statistics for these chunks.
- Both in ClickHouse and Iceberg the system periodically compacts these chunks to make sure they are mostly ordered and easily prunable.
If this is true, we started asking ourselves, then why can't these two datastores share the same data? Is real-time analytics really so different from data warehousing that it deserves its own format?
Surprisingly, the answer to this question might have more to do with history than with purely technical reasons. While the Parquet format is relatively old - released in 2013 - ClickHouse and other open-source real-time analytics engines are actually even older. Both ClickHouse and Druid, for example, were already fully deployed in production before Parquet even existed (ClickHouse, specifically, was first deployed at Yandex in 2012).
This means that ClickHouse had to be built, optimized, and tuned around a new storage format developed from scratch because they had no other format to be built around. You can see this come up very clearly in benchmarks comparing the engine's performance across different storage formats:
While others adapted to the world of open data formats / parquets (e.g. Databricks adopting Parquet as its main storage format in 2017), ClickHouse and other real-time analytics engines, for some reason, remained "loyal" to their original storage formats as the main storage format they optimize.
This may be due to how difficult it was to change their underlying storage format, or due to - and this is just speculation! - the fact that it would be much easier to migrate away from these systems if the data stored in them were already in a standardized, open format.
A question does arise, though, which we started actively tinkering with after looking at our Iceberg <> Clickhouse data duplication:
If a modern real-time analytics engine were built from scratch natively on top of the open data formats organizations already have, could it match ClickHouse's performance?
While the journey to answer this question took a very long time, we're proud to finally share Pivot, an open-source database that proves that this is not only possible, but that it can actually be much faster than ClickHouse, or other real-time analytics engines currently on the market - while still running on open data formats:
Thanks to many modern data-processing algorithms and techniques used in Pivot (along with some novel ones that we plan to formally publish soon in blog posts and papers), together with many parquet specific optimizations (for example - soft ordering data), Pivot was eventually able to not only simplify and improve our internal data pipeline, but also, more broadly, to offer a generic ClickHouse alternative that runs directly on the open data formats companies already use in their data warehouses (all this while even having better performance than Clickhouse).
Pivot is open source and available at https://github.com/pivotlake/pivot. We're excited to share it with the community and hope others find it as useful as we have! 🙂