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

61–70 of 77 posts

Re: Clickhouse Local

#61

Post by barrkel »

georgewfraser wrote: post #8
oreoftw wrote: post #7

Earlier quoted context omitted.

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

Snowflake, Redshift, BigQuery, Databricks, Presto.

How well do those work on a single 8GB node? Because ClickHouse works very well at that scale, with a single C++ executable.

There's large complexity and cost overheads to Hadoop solutions, and not everyone has actual big data problems. ClickHouse hugely outperforms on query patterns that would devolve into table scans in a row store, while working at row store volumes of data without a bunch of big nodes.

Re: Clickhouse Local

#62

Post by georgewfraser »

dunkelheit wrote: post #48
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…

> 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.

Re: Clickhouse Local

#64

Post by justinsaccount »

atombender wrote: post #37
lflux wrote: post #20

Earlier quoted context omitted.

What about Greenplum or CitusDB?

CitusDB is not relevant here, I believe, as it still uses Postgres' table storage, so it's not columnar. It might be good for analytical workloads, but I very much doubt it will perform anywhere close to ClickHouse. Greenplum: I've not used it, but it does support columnar tables, so maybe it's comparable.

https://github.com/citusdata/cstore_fdw

Re: Clickhouse Local

#65

Post by atombender »

wochiquan wrote: post #54
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…

I know a few companies that use Imply Cloud to set up and manage Druid. I linked it if you want to check it out. https://imply.io/product/imply-cloud

No pricing information. I doubt it's very affordable.

Re: Clickhouse Local

#66

Post by tadkar »

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…

To summarise a lot of the responses here, Clickhouse is extremely fast on very modest hardware, very easy to set up, very easy to get started (mostly normal SQL) and free. For our workloads and scale of data, nothing comes close in terms of performance (redshift, BQ, spark) and especially TCO. It is super simple to try out, why not give it a go? Or is the question borne from a previous bad experience?

Re: Clickhouse Local

#67

Post by econcon »

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…

Its main use in analytics and real time dashboard/report generation in areas like adtech.

If a conversion happens, you need to generate the views/cubes again in reporting dashboard, clickhouse makes it cheap and easy to run such operations over commodity hardware.

If you don't have this, you'll be using big query and it might not be as fast.

Re: Clickhouse Local

#68

Post by phillc73 »

ptrott2017 wrote: post #42
phillc73 wrote: post #28

Earlier quoted context omitted.

DuckDB perhaps[1]: https://www.duckdb.org [1] I say "perhaps" because I've only just started using it having migrated from MonetDB, but have no experience of alternatives like Presto.

Curious, as a sometimes MOneyDB user, be interested to know why did you choose DuckDB over MonetDB?

Monetdb-Lite disappeared from CRAN and after some investigation, it appears that the development team is now focused on a new product, DuckDB[1]

[1] https://github.com/MonetDB/MonetDBLite-R/issues/38#issuecomm...

Re: Clickhouse Local

#69

Post by pyppo »

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…

Perspective from sentry.io (we use it in production as wikibob pointed out below): Besides all the performance considerations discussed in this thread, which are 100% correct, a few other features we really like are:

- the multiple table engines that are heavily optimized for specific data access patterns: ReplacingMergeTrees which essentially make records mutable, SummingMergeTrees that allow us to progressively build pre-aggregated data, AggregateMergeTrees that allow storing the intermediate aggregation state of most aggregation function and compose them at query time over multiple groups (example, store a p95 aggregation state hourly and query the daily p95 by composing them), and more.

- column data types is extensive and includes nested columns

- the architecture is relatively simple making it easy for developers and on prem users to deploy a single node local clickhouse very easily

- it is very efficient in inserting big batches of data which works really well for our use case were we ingest massive amount of errors.

- data skipping indexes, bloom filter indexes

(yes, as vlad@sentry.io mentioned below we are hiring for the team that manages storage and thus clickhouse)

Re: Clickhouse Local

#70

Post by peferron »

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…

> 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 automatically load from S3 some of the data that server used to carry, in order to reach the desired replication factor again.

Last time I checked ClickHouse had no automatic rebalancing at all, which sounds horrendous unless you're running at very small scale or willing to have people babysit it. I haven't operated ClickHouse at large scale though, so if I'm wrong I'd be happy to hear how people manage ordinary tasks like scaling up and down, replacing dead instances, changing instance types to adjust CPU/mem/disk, etc... with let's say a 100 TB compressed dataset.

Another difference is that Druid can index all dimensions. So if you plan to run queries with filters that only match a small fraction of rows, then Druid can be faster than ClickHouse. Conversely, if your queries have filters that match many rows, then ClickHouse will be faster because it has higher raw scan speed. (At least that was the case about a year ago. Since then, Druid has added vectorized aggregation, which I haven't benchmarked, but I'd bet that ClickHouse is still faster at doing full table scans.)

IMO these 2 things are the main elements to think about when choosing between ClickHouse and Druid.

Post reply on HN