Officially, it's a column-oriented database. This means that, internally, it stores columns together rather than rows together. In practice, it means that it's optimized for calculating analytics over large datasets.
I've found, from personal experience, that it makes a good replacement for time-series databases, even though it's technically not a time-series database. My employer migrated our KPIs and other metrics from InfluxDB to ClickHouse a couple of years ago, and the drastic improvements in performance were well worth the time it took to migrate our data. It also helped that ClickHouse uses a subset of SQL, unlike InfluxDB which uses a superficially SQL-like but practically very different proprietary language.
Every high-volume Influx/Grafana implementation I’ve used has been a disaster. I’m now at a place that uses ClickHouse and I can now see the utility of Grafana
The shape of the data matters. In particular the cardinality of the tags. If clickhouse works well for you, chances are that your use case will be well served by influxdb_iox too
Clickhouse/Druid/Pinot are all columnstores/column-oriented databases. Clickhouse is a relational engine while Druid/Pinot are a different (and older) design using heavy indexing and pre-aggregation. All of them store table data as per-column segments though which is a defining feature leading to high compression and I/O performance.
There's also the badly named wide-column database type like Cassandra, but this is really just advanced or nested key/value rather than what people would consider "columns".
I'd like to see a xkcd -"What if the moon was made of chees?", and with sections for different types of cheese.
I guess rot would be no problem, but the surface would probably liquefy during the day, and freeze at night, turning into a cheese sea (or maybe an under-crust sea beneath frozen-cheese ice crust?) affected by earth-induced tides, and possibly internal tidal heating which liquefies the inside of the moon too.
These comments gave me an extreme nostalgic feeling. It's like it's early 2000 again when everyone that could operate a keyboard would try to make his own dynamic site with PHP (read as: copy&paste some "guestbook" code from a tutorial). Oh, the memories and fun we had with those ...
Hehe, my first open source “project” was a comment box written in PHP, circa 2004 at the age of 13… It used a single txt file for storing the comments, so I’m afraid it wasn’t webscale™, but I didn’t know better at the time :]
Don't sell yourself short, it was probably more efficient than the wobbling tower of npm packages and ajax requests most comment systems are made of today.
It seems they are fetching the comments from the Hacker News Algolia index. Curiously they seem to un-sanitize the comment content before inserting it to the page DOM as one of the sibling comments proves.
The Algolia index contains the comments encoded as HTML entities, so they should be safe to insert as is, but seems like the page is first converting it back to HTML, which causes the script and span tags to render as HTML instead of text like here on HN.
Algolia has some rough edges. I've tried to work with them on a couple of occasions but it's a bit of a harder problem than meets the eye because apparently Algolia gets a feed directly from HN with the original comment contents as they were typed into the text box rather than the rendered HTML version of the comment, which ironically would probably be easier to deal with. I have a particular comment discussing HTML injection that I've worked with them twice to stop rendering as actual script tags, which means, yes, it actually regressed once.
Is it faster for querying than using Presto on top of Hive? The page comparing ClickHouse with other analytics solutions doesn’t list Presto, which is very popular these days.
Waay faster. In my experience it has been the fastest OLAP db I've ever used, and by a wide margin. I don't think any other system gets close for the price/performance ratio you get with ClickHouse.
In this blog [1] you can find a very nice comparison of different dbs on the same dataset/same queries
The fastest Presto benchmark there [2] vs ClickHouse on a single node core i5 [3] vs ClickHouse in cluster mode [4]
Kdb+ or it’s latest incarnation k9/shakti are likely faster, but they cost a few arms and a few legs. Once you factor price into the equation, click house is infinitely better :)
Absolutely. That's why I said price/performance might be the best. Also MapD (nowadays OmniSci) is way faster.... but it would cost you a couple of bodies probably to run on GPUs :)
Thank you for the links! Do you happen to know where Facebook’s Scuba, Google’s Procella, Google’s Dremel on Capacitor and Snowflake come on that list of benchmarks?
Scuba appears to be an in-memory-only database, so it’s possibly faster (not seen benchmarks nor used it) but at the cost of persistence.
Is Procella even usable by anyone not-google?
Haven’t used dremel + capacitor, but have used Snowflake and found it lacking. Promises a lot, performance was just kind of alright, support was useless, sales team was aggressive to the point of harassment, and it was eye-wateringly expensive on top of all that. Some people love it, I will never go near it again, it’s advantages just aren’t sufficient to warrant choosing it over ClickHouse or even Redshift.
Thanks for these links. Unrelated to ClickHouse, it was interesting to read about BrytlytDB--I'd never heard of it before. Essentially a PostgreSQL DB that uses GPUs.
I must say that I relied on the community since we adopted ClickHouse in production almost 2 years ago.
It was an essential part of the success of our project.
I'm grateful for their work and the passion they put assisting the community.
Has anyone used Clickhouse in production in anger? Our DevOps guy hates it and complains about how "fragile" it is.
Keeping zookeeper happy seems to be a huge pain. Getting consistent backups seems to be difficult for some reason, and there is some problem with multiple replicas. As in, if you have 3 replicas and one dies, when it comes back up it will be out of sync and will refuse to resync itself automatically. So someone has to go and prod it to make it work again.
In practice, none of this has ever been an issue and only comes up when he does his "chaos engineering" tests, but it makes him very nervous.
On the otherhand, it's been orders of magnitude faster than using AWS Redshift/Apache Drill/PostgreSQL whenever we try and benchmark them on comparable hardware, so we stick with it.
Hi, I ran a Clickhouse server in production at non-trivial scale for storage and query of application logs (semi-structured JSON documents).
In our case we were consuming from a kafka topic which was the source of truth, so we opted not to use Clickhouse's built-in write replication and instead manually sharded and wrote to each replica independently (this meant replicas weren't always in perfect sync for brand-new data, but in practice this was acceptable).
The distributed query side still worked perfectly despite this weird setup, even when we were doing weird things like re-sharding or taking some replicas down for one reason or another.
I can't speak to backups as, once again, we were considering the kafka stream the source of truth and not the clickhouse datastore. We archived the data directly out of kafka, in a format more suitable to long term archival instead of live query.
My experience with Clickhouse was that the core primitives were rock solid, but you were often on shaky ground when using more niche features - we encountered crashes when trying to use bloom filters for text search, for example.
The performance of Clickhouse absolutely blew me away. It could read, filter and aggregate results as fast as the underlying disks could serve it data. I'll point out though that while Clickhouse is very good at what it does, it puts a lot of onus on the person designing the schema and writing the query to make sure it does it in the right way. In our case this worked well because we only had a small number of "shapes" of query we needed to serve, and we had a CLI tool that translated the user's query written in a simple DSL (really just a key-matches-value filter expression) into the SQL that would result in an efficient query on clickhouse's end. But for more flexibility-demanding workloads this could be a major issue as you need to know the system pretty well to write good queries.
In my experience there is a bit of a learning curve until you understand how each of the parts fit together and how to optimize for your use case. ZooKeeper indeed was one of those pain points, but once you get it stable there is practically no need to touch it.
I found the tips in the docs on optimizing ZK really helpful [1].
Anoter mistake we did early on with ZooKeeper, that caused us a lot of pain was running it with network attached disks (we ran ClickHouse+ZK in the cloud). Once we switched to local disks, 99% problems disappeared.
Similar experience to vulkoingim: steep learning curve but quite stable once deployed properly.
Schema management in zookeeper has been the biggest pain point for us. Occasionally individual clickhouse shards will get out of sync during a schema update, which can be hard to diagnose.
We use a heavily modified version of clickhouse-backup[1], which works well for us.
As for hands-off replica reboot: you must have an automated process to reapply the same schema which exists in zookeeper, otherwise it won't resync. If the local schema gets out of sync with that in zookeeper, then you'll have issues again.
I expect a lot of these ergonomics issues will be fixed over time. It's already much easier to use than it was 3 years ago, and even if progress on usability and reducing the learning curve is slow the database performance makes it worth it.
They're using NuRaft from ebay to drop zookeeper. As far as I remember, it's actually already merged and available to test. Performance is ~3x slower in terms of write as they're coordinating everything themselves, but it should be _much_ easier to maintain.
I'm looking forward to this, too, and want to see more in terms of docs / support here :)
What you say is true, clustering clickhouse is hard. But we are happy in a very different way. What used to be 6+6 cluster of graphite is now served by a single clickhouse instance handling millions of unique metric datapoints everyday, that we haven't even had the need to cluster it yet. It's an order of magnitude difference in performance & cost. Scale, but just in a different way, not just horizontal but holistically.
Maybe when we need to cluster, we'll face the same issues.
How does the setup and operational ease compared to druid. Afaik, druid also uses zookeeper. Is there any reason someone would choose druid over clickhouse?
65 comments
[ 3.1 ms ] story [ 138 ms ] threadI've found, from personal experience, that it makes a good replacement for time-series databases, even though it's technically not a time-series database. My employer migrated our KPIs and other metrics from InfluxDB to ClickHouse a couple of years ago, and the drastic improvements in performance were well worth the time it took to migrate our data. It also helped that ClickHouse uses a subset of SQL, unlike InfluxDB which uses a superficially SQL-like but practically very different proprietary language.
Column store db, using the DataFusion SQL engine. Persistence using parquet files directly on object stores (e.g. S3)
Still under development
There's also the badly named wide-column database type like Cassandra, but this is really just advanced or nested key/value rather than what people would consider "columns".
<span style="font-size: 300px;"><marquee>hello</marquee></span>
I guess rot would be no problem, but the surface would probably liquefy during the day, and freeze at night, turning into a cheese sea (or maybe an under-crust sea beneath frozen-cheese ice crust?) affected by earth-induced tides, and possibly internal tidal heating which liquefies the inside of the moon too.
<img src="kitten.jpg" onerror="alert('meow!')">
:^)
Edit: meh, stupid CSP…
(Yep it worked)
The Algolia index contains the comments encoded as HTML entities, so they should be safe to insert as is, but seems like the page is first converting it back to HTML, which causes the script and span tags to render as HTML instead of text like here on HN.
Parquet is a column based file storage format.
Edit: capacitor also appears to be an internal storage format google uses inside of Big Query.
In this blog [1] you can find a very nice comparison of different dbs on the same dataset/same queries
The fastest Presto benchmark there [2] vs ClickHouse on a single node core i5 [3] vs ClickHouse in cluster mode [4]
[1] https://tech.marksblogg.com/benchmarks.html
[2] https://tech.marksblogg.com/billion-nyc-taxi-rides-spark-2-4...
[3] https://tech.marksblogg.com/billion-nyc-taxi-clickhouse.html NOTE: not really comparable, since it's on single machine, but I think it really shows what ClickHouse is capable of
[4] https://tech.marksblogg.com/billion-nyc-taxi-rides-clickhous...
Is Procella even usable by anyone not-google?
Haven’t used dremel + capacitor, but have used Snowflake and found it lacking. Promises a lot, performance was just kind of alright, support was useless, sales team was aggressive to the point of harassment, and it was eye-wateringly expensive on top of all that. Some people love it, I will never go near it again, it’s advantages just aren’t sufficient to warrant choosing it over ClickHouse or even Redshift.
I'm grateful for their work and the passion they put assisting the community.
Well done, everyone!
Keeping zookeeper happy seems to be a huge pain. Getting consistent backups seems to be difficult for some reason, and there is some problem with multiple replicas. As in, if you have 3 replicas and one dies, when it comes back up it will be out of sync and will refuse to resync itself automatically. So someone has to go and prod it to make it work again.
In practice, none of this has ever been an issue and only comes up when he does his "chaos engineering" tests, but it makes him very nervous.
On the otherhand, it's been orders of magnitude faster than using AWS Redshift/Apache Drill/PostgreSQL whenever we try and benchmark them on comparable hardware, so we stick with it.
In our case we were consuming from a kafka topic which was the source of truth, so we opted not to use Clickhouse's built-in write replication and instead manually sharded and wrote to each replica independently (this meant replicas weren't always in perfect sync for brand-new data, but in practice this was acceptable).
The distributed query side still worked perfectly despite this weird setup, even when we were doing weird things like re-sharding or taking some replicas down for one reason or another.
I can't speak to backups as, once again, we were considering the kafka stream the source of truth and not the clickhouse datastore. We archived the data directly out of kafka, in a format more suitable to long term archival instead of live query.
My experience with Clickhouse was that the core primitives were rock solid, but you were often on shaky ground when using more niche features - we encountered crashes when trying to use bloom filters for text search, for example.
The performance of Clickhouse absolutely blew me away. It could read, filter and aggregate results as fast as the underlying disks could serve it data. I'll point out though that while Clickhouse is very good at what it does, it puts a lot of onus on the person designing the schema and writing the query to make sure it does it in the right way. In our case this worked well because we only had a small number of "shapes" of query we needed to serve, and we had a CLI tool that translated the user's query written in a simple DSL (really just a key-matches-value filter expression) into the SQL that would result in an efficient query on clickhouse's end. But for more flexibility-demanding workloads this could be a major issue as you need to know the system pretty well to write good queries.
I found the tips in the docs on optimizing ZK really helpful [1].
Anoter mistake we did early on with ZooKeeper, that caused us a lot of pain was running it with network attached disks (we ran ClickHouse+ZK in the cloud). Once we switched to local disks, 99% problems disappeared.
[1] https://clickhouse.tech/docs/en/operations/tips/#zookeeper
Schema management in zookeeper has been the biggest pain point for us. Occasionally individual clickhouse shards will get out of sync during a schema update, which can be hard to diagnose.
We use a heavily modified version of clickhouse-backup[1], which works well for us.
As for hands-off replica reboot: you must have an automated process to reapply the same schema which exists in zookeeper, otherwise it won't resync. If the local schema gets out of sync with that in zookeeper, then you'll have issues again.
I expect a lot of these ergonomics issues will be fixed over time. It's already much easier to use than it was 3 years ago, and even if progress on usability and reducing the learning curve is slow the database performance makes it worth it.
[1] https://github.com/AlexAkulov/clickhouse-backup
They're using NuRaft from ebay to drop zookeeper. As far as I remember, it's actually already merged and available to test. Performance is ~3x slower in terms of write as they're coordinating everything themselves, but it should be _much_ easier to maintain.
I'm looking forward to this, too, and want to see more in terms of docs / support here :)
Maybe when we need to cluster, we'll face the same issues.
https://github.com/plausible/analytics/search?q=clickhouse
They use it to store analytics data.
[1] https://clickhousedb.slack.com/join/shared_invite/zt-nwwakmk... [2] https://clickhouse.tech/#community