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.
There is an extensive range of DB and Table engines supported by ClickHouse.
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.
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.
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.
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.
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.