ClickHouse Cloud - Pay As You Go

I generally use ClickHouse  for group by queries, and it is really fast in ClickHouse  to do the group by and show the summation, metrics, and latencies.

Performance and scalability of ClickHouse are huge, allowing us to store billions of records while returning records in a very fast way, with latencies hugely optimized now.

ClickHouse has positively impacted our organization by optimizing costs and significantly reducing latencies, which has improved customer experience greatly.

Regarding specific metrics, latencies are hugely reduced; previously, we had a latency of one minute, but now we have a one-second latency, marking a huge improvement.

The pain points I have experienced with ClickHouse include the initial learning curve, which was somewhat challenging.

I gave ClickHouse a nine out of ten because, despite its great performance, the initial learning curve and the need for more detailed documentation kept it from being a perfect ten.

I am using ClickHouse for about two years, and our main use case is to show dashboard analytics queries.

ClickHouse is very stable in our experience, and we have evaluated Apache's solution related to Druid  and other Apache databases.

In terms of scalability, ClickHouse has really good scalable engines and features, showing solid core performance and implementation.

I have not really interacted with ClickHouse support; we generally find our solution on online portals such as Stack Overflow or AI, and once I interacted on ClickHouse's Slack channel.

We have not used any solution other than ClickHouse, as we directly use this based on our market research.

I have seen a return on investment with ClickHouse, having saved a lot of money and time, and now our database is up and running with few outages or problems.

My experience with pricing, setup cost, and licensing for ClickHouse was good; it was straightforward, and I am satisfied with it.

For others looking into using ClickHouse, I advise you to learn about it very thoroughly, utilizing YouTube videos, documentation, and general guides, as it will be helpful for optimized usage.

In general, I think it was a great experience using ClickHouse, and I have no additional thoughts.

I think ClickHouse is doing great, providing good documentation and tutorials on how to use ClickHouse or how to write queries effectively to improve performance.

I gave this review a rating of nine out of ten.

ClickHouse  has been used for the last one year.

The primary use case involves IoT devices. Software has been developed to onboard IoT devices, which send data at varying frequencies. Analysis must be provided to users based on these different data transmission patterns. A dashboard allows users to onboard their IoT devices and analyze their data. The volume of data is substantial. For example, if a company has one lakh IoT devices sending data every 10 minutes, the data generated in one month can reach several GB to TB. Real-time analysis is required to determine how many times devices were active or inactive, week-wise device activity, total average voltage for energy meters, and many other analytical insights.

ClickHouse  has delivered exceptional performance for this use case. Testing was conducted on over 10 million rows, performing count, sum, average, aggregation by week, aggregation by month, ordering, and sorting operations. ClickHouse provides responses within a few seconds, typically two to three seconds, which is impressive. An AI agent has also been built on top of ClickHouse for user-based queries. When a user asks a question such as how many devices are inactive for more than a month, the system directly contacts OpenAI, generates a ClickHouse query from the response, and submits it to ClickHouse. ClickHouse responds within ten seconds. Testing has been performed on over 10 million rows, and it is working well for the use case.

The two main use cases are analysis and an AI agent built on top of ClickHouse.

ClickHouse has delivered exceptional performance for this use case. Testing was conducted on over 10 million rows, performing count, sum, average, aggregation by week, aggregation by month, ordering, and sorting operations. ClickHouse provides responses within a few seconds, typically two to three seconds, which is impressive. An AI agent has also been built on top of ClickHouse for user-based queries. When a user asks a question such as how many devices are inactive for more than a month, the system directly contacts OpenAI, generates a ClickHouse query from the response, and submits it to ClickHouse. ClickHouse responds within ten seconds. Testing has been performed on over 10 million rows, and it is working well for the use case. The two main use cases are analysis and an AI agent built on top of ClickHouse.

Speed is the main valuable feature. Setup is straightforward. Several features are utilized including Materialized Views, simple views, ReplaceMergeTree, and Aggregation Tree. These features are used to aggregate results that remain unchanged. For example, monthly, weekly, and daily summaries are aggregated and remain unchanged because they are historical data. Materialized View is one of the most used and valuable features being leveraged.

Everything appears to function well. From a software engineering perspective, one consideration involves eventual consistency. In the case of ReplaceMergeTree, data duplication eventually gets corrected during the merge process. When merge parts are combined into one, duplication is removed. If duplication could be removed in real-time, that would be better. An info table has been created to provide the latest data per device. However, when the same device data is inserted again, it takes time. When merge parts combine, duplication is removed. At that point, when data is sent to the UI, it must be grouped by device ID and the last created date must be picked to avoid showing duplicate data. This is understood to be a limitation of the append-only nature, but a solution might exist to address this issue.

Real-time deduplication would be beneficial for clarity on when it occurs. Duplication will exist before the merge, and duplicates will be removed after the merge. ClickHouse provides ReplaceMergeTree, MergeTree, SummingTree, and AggregationTree. A tree family that guarantees deduplication with no duplicity would be valuable. Information should be provided to customers regarding performance trade-offs, such as a X to Y performance reduction, so they can decide if the trade-off is worth it. In this case, the latest data must be displayed, and users must see correct data. Currently, grouping is done outside the main query. A tree family could be provided that guarantees one hundred percent no duplication data, though the FINAL keyword is currently available, it requires developers to add it before and after queries with careful consideration.

Configuration complexity presents another improvement opportunity. Difficulty levels exist ranging from eight to nine out of ten. Setting nine is reasonable, as there must remain some improvement scope. Too many issues exist for beginners to set up ClickHouse. Many parameters must be configured, such as maximum scatter part settings that determine when writing to a table stops. Many parameters require careful setup, making it very difficult for beginners. Unlike PostgreSQL  or MongoDB, which can be downloaded and run without difficulty, ClickHouse is not easy for beginners to set up. Improvement scope exists to enable easier setup. Default settings could be provided so that anyone can set up ClickHouse easily.

Scalability was the main concern. Feedback has been very positive regarding ClickHouse's scalability.

Customer support at ClickHouse has not been reached because cloud service has not been utilized. However, documentation and blogs have been thoroughly reviewed, and all issues have been resolved using these resources.

The transition was made from MongoDB to ClickHouse. MongoDB was used when data from IoT devices required bulk writing due to frequent write operations. InfluxDB  and Cassandra  were considered as alternatives. However, when MongoDB was evaluated for analysis, it did not provide good performance. The decision was made to switch to an analytics-focused database. ClickHouse was discovered and selected, and has been in use since that decision.

RocksetDB and Google Spanner , along with other open-source solutions, were evaluated. After reading blogs, documentation, and reviews, and after testing some solutions including Spanner, Spanner was found to provide similar performance, but cost is the main concern. ClickHouse was selected because it is open-source and can be run on-premises, which is the primary requirement due to data security considerations.

Speed is the main advantage, and setup is straightforward. Several features are utilized including Materialized Views, simple views, ReplaceMergeTree, and Aggregation Tree. These features are used to aggregate results that remain unchanged. For example, monthly, weekly, and daily summaries are aggregated and remain unchanged because they are historical data. Materialized View is one of the most used and valuable features being leveraged.

Licensing and cost details are not available as these matters are managed by the DevOps team. An 8-core machine with 32 GB RAM is being used to run ClickHouse.

RocksetDB and Google Spanner , along with other open-source solutions, were reviewed. After reading blogs, documentation, and reviews, and after testing some solutions including Spanner, Spanner was found to provide similar performance, but cost is the main concern. ClickHouse was selected because it is open-source and can be run on-premises, which is the primary requirement due to data security considerations.

An exact monetary value cannot be provided, but time savings from query execution can be quantified. Testing was conducted on three to four lakh rows using sum aggregation in both PostgreSQL  and MongoDB. PostgreSQL and MongoDB required five to ten seconds on an 8 GB machine with a four-core CPU. The same ClickHouse instance provided results within one second. This represents approximately six to seven times faster query execution. ClickHouse delivers results within one second while MongoDB and PostgreSQL deliver results on the same data within six to seven seconds.

The main concern is that too many issues exist for beginners to set up ClickHouse. Many parameters must be configured, which can complicate setup for beginners. This hinders ease of setup compared to databases such as PostgreSQL or MongoDB, which are straightforward to run for beginners.

For initial settings, focus on reading ClickHouse's documentation. Specific default settings may be available to simplify initial setup. After reviewing various blogs, one problem encountered was the 'too many merge parts' error. This error occurred when frequently inserting data from APIs via Kafka. After adjusting the setup, the Kafka consumer had to be reset, and specific flags had to be tuned to prevent the error from recurring.

When using ReplaceMergeTree, caution must be exercised regarding duplication. Directly displaying data to end-users without considering duplication can lead to discrepancies in unique entries and potentially mislead customers, creating issues for stakeholders. To avoid this, opt for strict reading settings using accurate queries, tailoring adjustments to specific needs.

This review has been rated nine out of ten.

I am Joachim from Lasersoft Technologies, and I'm mostly working with data, so my designation is similar to a software engineer. I'm currently working on a migration project. Before that, I was working with an ETL pipeline. Basically, I'm not working with ClickHouse  in the migration project, but as I mentioned before, the ETL pipeline has sales-related data. That's why I'm using ClickHouse  database. It's mostly used in a server.

Mostly, I'm using ClickHouse for data warehousing, as we need to fetch data and load it into ClickHouse for analytical purposes. We need to use it for group by aggregation purposes, utilizing warehousing concepts, not for workflows.

Basically, we need to fetch data from an API, which consists of around 10,000 to 20,000 records per day. We need to load it into ClickHouse for analytical purposes, where we load the data into ClickHouse and fetch it using the Power BI JDBC driver to analyze the data for the client. It's very useful for us for analytical and group by operations. It's much faster than transactional databases, so we need to use that.

The best features ClickHouse offers are basically for aggregation and data group by functions, which is why we are using ClickHouse, as it's more oriented towards analyzing the data. When we use a group by or Windows function, it's much faster than a transactional database. The main best feature is that we have to install it on-premises, which is a big advantage for data warehousing that's created on on-premises servers.

I've seen that the speed and performance of these features have helped my work significantly, especially compared to other databases I've used, such as SQL Server  and Cosmos DB. With ClickHouse, since data is stored in a columnar way, we get aggregation functions that are much faster than transactional databases, such as SQL Server . Cosmos DB is more NoSQL, so you can't query as much as you can in SQL.

ClickHouse has positively impacted my organization, as I've seen a lot of improvement on the analytical side. Before, we used Cosmos DB for Power BI analytics, but after we started using ClickHouse, it's much faster than Cosmos DB for analytical purposes. The cost efficiency is also much reduced compared to Cosmos DB. Since we use it on-premises, the cost is nearly cut, which is very useful for us.

In terms of how ClickHouse can be improved, I don't think there are any improvements needed. However, in terms of challenges, the installation and setup of the database need attention. We need to install it on-premises, and it's more difficult. A single-click installation that automatically gets what we need would be helpful. That's more of a suggestion than an improvement or a challenge.

In terms of needed improvements, some enhancements in documentation are necessary. ClickHouse still doesn't support surrogate keys. I'm not that aware of it today, but a year ago when I was using it, ClickHouse database tables did not support surrogate keys. I'm not sure if it's still an issue, but that was the case. We also need more documentation added to the website and more videos or tutorial videos added to ClickHouse YouTube channel. It would be useful for most people.

Some features are still not supported in ClickHouse, such as surrogate keys. I'm not sure if it's supported now, but some features need to be added, and some tutorial videos need to be added to the YouTube channels, or the documentation needs to be better. It would be useful for people who are using ClickHouse for an on-premises database. We need more documentation.

I've been working with ClickHouse for around one to two years.

ClickHouse is stable in my experience, but it needed some improvement when I used it. I'm not aware of the current state, but it's mostly stable.

Scalability-wise, ClickHouse is good.

I haven't needed customer support that much, as we haven't had to communicate with customer support.

We used Cosmos DB and needed to switch to a columnar database. We needed an on-premises database, so we switched to ClickHouse for that.

We use an on-premises ClickHouse database and don't use it in a cloud-based way, so we don't have practical ideas for the cloud-based version.

We deploy ClickHouse in our organization on on-premises servers, so it's not cloud-based and is used on-premises for analytical purposes. It's more oriented towards reducing the cost, which is why it's used on-premises in our organization.

I've seen a return on investment, with money saved metrics around 50% to 75% for us by using ClickHouse. The return on investment is around 25% to 30%. For time-saving metrics, it's around 30% to 40% of time saved compared to transactional databases. The setup is also a time-saver for ClickHouse.

Pricing-wise, we are using ClickHouse on-premises, so we just need to maintain the on-premises servers. The setup is very easy compared to other servers, but it's more oriented toward using ClickHouse on Linux servers only. If it were supported on Windows, it might be better for those who use Windows-based products. The licensing is also good compared to others.

Before choosing ClickHouse, we did evaluate other options such as BigQuery  or Azure  warehousing concepts, which are more oriented towards spending money, but we needed a cost-reduced, on-premises solution. That's why we were ready to use ClickHouse for our analytics.

The advice I would give to others looking into using ClickHouse is to research the documentation before using it to see what use cases are needed. If they need an on-premises solution, as of today, ClickHouse is the best option. They need to think about on-premises versus cloud-based. We mostly use it on-premises. I have provided a review rating of 7 for ClickHouse.