Deep Dive Into ClickHouse

Pragati Srivastava
~ 3 min read | Published on May 24, 2023





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.

features supported by Clickhouse

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.

Call to Action
Feel free to comment or write to us in case you have any further questions at support@zipy.ai. We would be happy to help you. In case you want to explore Zipy for your app, you can sign up here or book a demo here.

Call to Action
Feel free to comment or write to us in case you have any further questions at support@zipy.ai. We would be happy to help you. In case you want to explore Zipy for your app, you can sign up here or book a demo here.

Call to Action
Feel free to comment or write to us in case you have any further questions at support@zipy.ai. We would be happy to help you. In case you want to explore Zipy for your app, you can sign up here or book a demo here.

Call to Action
Feel free to comment or write to us in case you have any further questions at support@zipy.ai. We would be happy to help you. In case you want to explore Zipy for your app, you can sign up here or book a demo here.

Call to Action
Feel free to comment or write to us in case you have any further questions at support@zipy.ai. We would be happy to help you. In case you want to explore Zipy for your app, you can sign up here or book a demo here.

Call to Action

Feel free to comment or write to us in case you have any further questions at support@zipy.ai. We would be happy to help you. In case you want to explore for your app, you can sign up or book a demo.

Fix bugs faster with Zipy!

  • Session replay
  • Network calls
  • Console Logs
  • Stack traces
  • User identification
Get Started for Free





Fix bugs faster with Zipy!

Get Started for Free
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Want to solve customer bugs even before they're reported?

zipy
for developers
with
Loved by developers
by developers just like you
© 2023 Zipy AI | All rights reserved
Integrately - Integrate Your Apps In 1 Click
SOC for Service Organizations
SOC 2 Type II Certified