Deep Dive Into ClickHouse

What made us think about ClickHouse?

It is no less than a nightmare when a feature request from the product team is not getting fit with the current system. So this feature request required some heavy aggregated functions which did not give satisfactory results with a row-based database.

We diverted ourselves to columnar databases. And at that moment ClickHouse gained our interest and we started digging deep into ClickHouse against various parameters which I have elucidated below.

ClickHouse is a fast, open-source, column-oriented SQL database (db) that is very useful for data analysis and real-time analytics. ClickHouse support for real-time query processing makes it suitable for applications that require sub-second analytical results.

DB Engines and Table Engines supported by ClickHouse

There is an extensive range of DB and Table engines supported by ClickHouse.

DB Engine

After reading and doing POC we found the best DB engine to use for ClickHouse is Atomic as other DB engines were used case specific which was not colliding with our system’s use case. Atomic is the most robust engine provided by ClickHouse. DB engines, for example, MYSQL, SQLite, PostgreSQL, and others are very specific to making connections and handshaking with these databases.

Table Engine

There are multiple table engines supported by ClickHouse, for us best suited was MergeTree. MergeTree table engine is preferred where there is a regular insert required. Since it is a columnar DB and writing takes some time, ClickHouse writes data by part and performs merging later because data in .bin is stored in a compressed format which makes real-time insertion tedious.

Features supported by ClickHouse

As mentioned earlier ClickHouse is a column-oriented SQL database.ClickHouse is an open-source database with an Apache2.0 license which makes it more reliable. The ClickHouse team has implemented the support for ML algorithms, which makes it much easier and faster to run ML over ClickHouse data.

Just like other columnar databases ClickHouse is also read optimized. ClickHouse support for real-time query processing makes it suitable for applications that require sub-second analytical results.

Also, ClickHouse supports partitioning, indexing, joins, and other DML statements like update, and delete. It gives support to batch processing.

Limitations of ClickHouse

As the famous saying goes, “Nothing is perfect in this world, everything has its pros and cons…”

If someone needs any of the below properties then one should not consider ClickHouse.

1. There is no support for transactions.
2. No real-time delete/update support.
3. There is no support for transactions.
4. By default, when performing aggregations, the intermediate query states must fit in the RAM on a single server. In such cases, ClickHouse can be configured to spill on the disk.
5. Do not use ClickHouse for OLTP. ClickHouse expects data to remain immutable. Even though it is technically possible to remove big chunks of data from the ClickHouse database, it is not fast. ClickHouse simply isn't designed for data modifications. It's also inefficient at finding and retrieving single rows by keys, due to sparse indexing.
6. ClickHouse does not fully support ACID transactions.

Conclusion

ClickHouse is a very robust database with a variety of table engines. It supports mostly all DML, DDL, and DCL which MySQL supports. ClickHouse gains more popularity because of its open-source nature. Columnar DBs come with some overhead cost of time when it performs DML, so we have to choose wisely.