Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Clickhouse Local

clickhouse.tech

Post reply on HN
Print view View original

71–77 of 77 posts

Re: Clickhouse Local

#71

Post by ddorian43 »

atombender wrote: post #18
georgewfraser wrote: post #6

Why do people on HN love Clickhouse so much? As far as I can tell, it’s an ordinary column store, with a bunch of limitations around distributed joins and a heuristic-based query planner. There are several good analytical databases that will give you the same scan performance and a much better query planner and executor. This is not a rhetorical question, I would really like to know why it gets so much attention here…

Because it's one of the few mature, fast, scalable analytics-oriented databases that is also completely open source. The closest open source thing that matches its feature set is Presto, but that one is quite different. Apache Druid is supposed to be very mature, but also very difficult to set up and manage. I've not used it myself. There's also Vespa, but I don't know how well it performs with large numbers of colum…

Vespa is inverted-index (many extras inside). Actually their aggregations isn't as optimized as elasticsearch (depends on scenario). To do aggs, the fields must reside in-memory.

Re: Clickhouse Local

#72

Post by dunkelheit »

georgewfraser wrote: post #62
dunkelheit wrote: post #48

Earlier quoted context omitted.

> There are several good analytical databases that will give you the same scan performance The notion that you will get approximately the same query performance with all column stores is false. There can easily be an order of magnitude difference depending on the implementation. Take GROUP BY as a paradigmatic example of what OLAP stores do. Of course the way to implement GROUP BY is with a hash table but little tric…

All of the optimizations you describe are in every columnar analytical database built in the last 10 years, from Vertica to Snowflake.

Probably you are right about vertica and snowflake (where can I browse the source code to be sure?) but with these clickhouse competes on price.

Re: Clickhouse Local

#73

Post by wochiquan »

wochiquan wrote: post #55
oreoftw wrote: post #7

Earlier quoted context omitted.

Could you name those good analytical databases? I'd love to learn more.

Apache Druid: https://druid.apache.org/docs/latest/design/index.html

Use case blog of Apache Druid by Netflix if ya'll want to take a look: https://netflixtechblog.com/how-netflix-uses-druid-for-real-...

Re: Clickhouse Local

#74

Post by 1996 »

atombender wrote: post #17
1996 wrote: post #9

Earlier quoted context omitted.

I cringe a bit inside at people using say nosql approaches when it makes literally no sense to do so. Therefore I think the lack of OLTP will not matter much and that clickhouse will be widely used, but also misused when it becomes too fashionable.

This makes no sense. For example, aside from the lack of transactions, Clickhouse is designed for insertion. There's an INSERT statement, but no UPDATE or DELETE statements. You can rewrite tables (there's ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE), but they're intended for large batch operations, and the operations potentially asynchronous, meaning that they complete right away, but you only see results late…

I should have phrased that differently: if something is good enough in some key metric, it extends to other uses - even if it makes a poor fit.

So I insist: everyone will WANT to use clickhouse as a general purpose database, and will create ways to make it so (ex: copy table with the columns you don't want filtered out, drop the original, rename)

It is just too fast and too good for many other things, so it will expand from these strongholds to the rest.

A personal example: I am migrating my cold storage to clickhouse, because I can just copy the files in place and be up and running.

I know about insert and the likes, I have a great existing system - but this lets me simplify the design, and deprecate many things. Fewer moving parts is in general better.

After that is done, there is a database where I would benefit from things like alter tables or advanced joins, but keeping PostgreSQL and ClickHouse side by side, just for this? No. PostgreSQL will go. Dirty tricks will be deployed. Data will be duplicated if necessary.

Re: Clickhouse Local

#75

Post by hodgesrm »

1996 wrote: post #74
atombender wrote: post #17

Earlier quoted context omitted.

This makes no sense. For example, aside from the lack of transactions, Clickhouse is designed for insertion. There's an INSERT statement, but no UPDATE or DELETE statements. You can rewrite tables (there's ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE), but they're intended for large batch operations, and the operations potentially asynchronous, meaning that they complete right away, but you only see results late…

I should have phrased that differently: if something is good enough in some key metric, it extends to other uses - even if it makes a poor fit. So I insist: everyone will WANT to use clickhouse as a general purpose database, and will create ways to make it so (ex: copy table with the columns you don't want filtered out, drop the original, rename) It is just too fast and too good for many other things, so it will expa…

Advanced joins (specifically merge joins) and object storage are on the way. See the following PRs:

* https://github.com/ClickHouse/ClickHouse/pulls?q=is%3Apr+mer... -- Recent work to enable merge joins

* https://github.com/ClickHouse/ClickHouse/pulls?q=is%3Apr+s3 -- Same thing for managing data on S3 compatible object storage

There's been a lot of community interest in both topics. Merge join work is largely driven by the ClickHouse team at Yandex. Object storage contributions are from a wider range of teams.

That said I don't see ClickHouse replacing OLTP databases any time soon. It's an analytic store and many of the design choices favor fast, resource efficient scanning and aggregation over large datasets. ClickHouse is not the right choice for high levels of concurrent users working on mutable point data. For this Redis, PostgreSQL, or MySQL are your friends.

Re: Clickhouse Local

#76

Post by oddtodd »

peferron wrote: post #70
atombender wrote: post #18

Earlier quoted context omitted.

Because it's one of the few mature, fast, scalable analytics-oriented databases that is also completely open source. The closest open source thing that matches its feature set is Presto, but that one is quite different. Apache Druid is supposed to be very mature, but also very difficult to set up and manage. I've not used it myself. There's also Vespa, but I don't know how well it performs with large numbers of colum…

> Apache Druid is supposed to be very mature, but also very difficult to set up and manage I wouldn't be surprised if this inverted at large scale (say 30+ machines). Druid data servers are rebalanced automatically; if you're on AWS and decide to scale up by adding a new data server, it will automatically load its assigned subset of data from S3. If AWS kills one of your data servers, then other data servers will aut…

I have worked with Druid and ClickHouse extensively in the past year.

I do think rebalancing is a weak point for ClickHouse, although for our use case that would not be so much of an issue, and it feels like it is on the roadmap for ClickHouse this year, but we will see. And if you are on Kubernetes, some of that headache may be handled for you with the ClickHouse Kubernetes operator.

I will say, that Druid indexing comes at a heavy cost in hardware for ingestion.

We find ClickHouse can easily ingest at least 3x the rate of Druid on the same hardware, and since Druid is asymmetric in design, you then have to get even more hardware to handle the queries.

Even with the vectorized aggregation, ClickHouse is beating Druid for full table scans at least, especially high cardinality data. But the vectorized aggregation has some restrictions to get on the fast paths, so that may improve. as those are removed.

Overall, I find ClickHouse much easier to work with and manage compared to Druid. ymmv

Re: Clickhouse Local

#77

Post by deepsun »

dikei wrote: post #51
deepsun wrote: post #39

Earlier quoted context omitted.

Clickhouse is not really time-series database, it's more general analytical DB (e.g. also can handle strings, logs, user IPs). But if you have a lot of time-series metrics (only numbers), you might be better with specialized time-series databases like Prometheus + VictoriaMetrics with Grafana for visualizing it.

Prometheus is not really a database, because the only operation allowed is query, you can't insert/delete data manually. For many use cases, that's not acceptable.

That's why I mentioned VictoriaMetrics :) It supports push (using any protocol).
Post reply on HN