7 ms·

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 heu

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.

oreoftw 7y ago
Could you name those good analytical databases? I'd love to learn more.
- georgewfraser 7y ago
  Snowflake, Redshift, BigQuery, Databricks, Presto.
  - dilyevsky 7y ago
    Github link pls
  - edmundsauto 7y ago
    Of those, it looks like only Presto is open source and/or free. So maybe it's a presto versus clickhouse comparison, which explains why so many choose clickhouse (it's one of only 2 options in its class).
    - deepsun 7y ago
      Presto is not really a database, it's the SQL layer on top of many other data storages, like Hive / any other SQL DB / Redis / Cassandra / etc.
    - jfim 7y ago
      Presto is mostly an engine that runs on top of other databases, although it does have its own query execution engine. The basic idea behind Presto is that it federates other databases, and supports doing joins across them. From what I understand, the problem that it solved at Facebook is bridging the gap between different teams; if a team has MySQL and another has files stored on HDFS, it doesn't really matter because all you do is query Presto and it'll query both under the covers. The alternative is setting up data pipelines, and dealing with the ongoing issues of maintaining those data pipelines.
  - deepsun 7y ago
    I can say for BigQuery and Databricks from personal experience. BigQuery is much slower and is much more expensive for both storage and query. Databricks (Spark) is even slower than that (both io and compute), although you can write custom code/use libs. You seem to underestimate how heavily ClickHouse is optimized (e.g. compressed storage).
    - derefr 7y ago
      > You seem to underestimate how heavily ClickHouse is optimized (e.g. compressed storage). Is it any more compressed than Apache Hive's ORC format (https://orc.apache.org https://orc.apache.org)? Because that's increasingly accepted as a storage format in a lot of these analytical systems.
      - deepsun 7y ago
        Yes, looks like it. According to these posts, ORC only uses snappy or zlib compression, while Clickhouse uses double-delta, Gorilla, and T64 algorithms. https://engineering.fb.com/core-data/even-faster-data-at-the-speed-of-presto-orc/ https://engineering.fb.com/core-data/even-faster-data-at-the... https://www.altinity.com/blog/2019/7/new-encodings-to-improve-clickhouse https://www.altinity.com/blog/2019/7/new-encodings-to-improv...
      - marcinzm 7y ago
        ORC or Parquet are file storage formats so without context their performance can be almost anything. Where is the data stored? S3? HDFS? Local ram disk? Clickhouse manages the whole distributed storage, ram caching, etc. thing for you. In my experience, a unified single purpose vertically integrated solution will be faster than a bunch of kitchen sink solutions bolted together.
  - deepsun 7y ago
    Also, Presto and Databricks(Spark) is just a layer on top of other storagea, it cannot optimize storage for querying, as you do storage yourself.
  - FridgeSeal 7y ago
    Snowflake doesn’t really keep up with Clickhouse (in my experience) and it costs money. DataBricks is essentially Spark, and I shouldn’t need a whole spark cluster just to get database functionality. It also costs money. Unless I’m mistaken, Presto is just a distributed query tool over the top of a separate storage layer, so that’s 2 things you have to setup. I have no experience with BigQiery but I’ve heard good things about it and Redshift, however but if the rest of your infra isn’t on GCP/AWS then that will probably be a blocker. Clickhouse is open source, comes with convenient clients in a bunch of languages as well as a HTTP API. It’s outrageously fast and has some cool features and makes the right trade-offs for its use-case, large range of supported input/output formats, built-in Kafka support and the replication and sharding is reasonably straightforward to setup.
  - bdcravens 7y ago
    According to https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html Clickhouse has better performance than 3 of those (the other 2 haven't been tested in that benchmark)
    - TheTank 7y ago
      I would be cautious using this as a proxy for performance ranking as some items (dataset, queries) are normalized, but the hardware setup is not.
      - jstrong 7y ago
        the hardware profile is listed in each row, also, the guy is totally meticulous!
        quod_2058 7y ago
        I don't think it's fair to say "A is faster than B" like in the above comments based on the order they appear in a list that mixes GPU clusters and laptops results. The author of the benchmark does nothing wrong deontologically, but the results table seems ordered by time and some people jump to quick conclusion or use it as a way to rank performance when it's not appropriate.
  - barrkel 7y ago
    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.
- phillc73 7y ago
  DuckDB perhaps[1]: https://www.duckdb.org 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.
  - ptrott2017 7y ago
    Curious, as a sometimes MOneyDB user, be interested to know why did you choose DuckDB over MonetDB?
    - phillc73 7y ago
      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#issuecomment-490589276 https://github.com/MonetDB/MonetDBLite-R/issues/38#issuecomm...
- wochiquan 7y ago
  Apache Druid: https://druid.apache.org/docs/latest/design/index.html https://druid.apache.org/docs/latest/design/index.html
  - wochiquan 7y ago
    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-time-insights-to-ensure-a-high-quality-experience-19e1e8568d06 https://netflixtechblog.com/how-netflix-uses-druid-for-real-...
atombender 7y ago
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 columns. A lot of people use Elasticsearch for analytics. Being based on Lucene, it's kind of columnar, and it can perform very well indeed on aggregations. InfluxDB may be good, but it's not fully open source.
- lflux 7y ago
  What about Greenplum or CitusDB?
  - quod_2058 7y ago
    Greenplum seems to like a way to throw more hardware at a problem. Queries are too slow with postgres? Shard your data across machines and distribute queries to speed things up by running in parallel. It's using scale as a means to compensate for low efficiency. On the opposite side of the spectrum you have other open source projects like questDB that have full focus on core performance: constantly optimise to get as much as possible from a single processor core. You can't scale out (at least yet), but given how fast it is on single core, it will be pretty powerful if they chose to go this route.
    - atombender 7y ago
      Note that Greenplum supports column-oriented tables. I haven't used it myself, so I can't comment on whether it's slower than ClickHouse.
  - deleted 7y ago
    [deleted]
  - atombender 7y ago
    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.
RhodesianHunter 7y ago
Performance at scale for certain workloads.
amyjess 7y ago
It's legitimately fast. My company migrated our time-series data from InfluxDB to ClickHouse last year (I personally led this, in fact), and the performance difference is night and day. While I liked a lot of what Influx could do, it was also nonstandard in bizarre ways (Clickhouse behaves more like a subset of SQL), sometimes shockingly immature, and despite appearing fast when we first started using it, so slow that it was a considerable bottleneck.
- deepsun 7y ago
  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.
  - dikei 7y ago
    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.
    - deepsun 7y ago
      That's why I mentioned VictoriaMetrics :) It supports push (using any protocol).
- jstrong 7y ago
  I think of influx performance as similar to pandas. It's fast enough for many things it was designed for, but it's pretty easy to hit a performance cliff. In particular, "select * from table" type queries are very slow, so don't expect to be pulling out large blocks of data to do analysis somewhere else. However, influx, being schemaless, can be outstanding for rapidly prototyping ephemeral metrics, as adding a new measurement is zero-cost (just start writing it). It also plays great with grafana for building dashboards. Finally, I much prefer the ergonomics enhancements of the influx query language (v1, the v2 "flux" language looks terrible to me personally), particularly how duration strings are a first-class datatype ("group by time(1h)"). Interested to hear more about clickhouse performance, haven't had a chance to use it for anything where performance would matter significantly, although am aware it can be very fast.
dunkelheit 7y ago
> 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 tricks make all the difference and a lot of love went into the clickhouse implementation. Just to give you a taste: there is a custom hash table with specializations for different key types (e.g. it will store a precomputed hash for strings but not for integers and use it to speed up equality test). Variable length data is stored in arenas to reduce allocator pressure. Data will of course be aggregated in several hash tables in different threads and then merged together, but if there is a lot of keys each table will additionally be sharded so that the merge step can be performed in parallel too. Of course you shouldn't trust random claims on the internet that clickhouse is fast and should do a small case study yourself. Then you'll appreciate how easy is to setup a clickhouse instance or a small cluster. It can easily slurp up most common formats. It is just a single binary with minimal dependencies that will run as-is on any modern linux. There is just a single node type (compare this to druid madness). You are right that there is a lot of limitations and, how should I put it, quirks. This is resoundingly not a general-purpose database and someone used to the comforts of e.g. postgres will encounter some nasty surprises. Bugs are unfortunately common, especially in the newer functionality. But performance is its main feature and it makes many users of clickhouse put up with its limitations.
- jfim 7y ago
  > Of course the way to implement GROUP BY is with a hash table but little tricks make all the difference and a lot of love went into the clickhouse implementation. Actually, for low cardinality columns, _not_ using a hash table will speed up things. For example, a dictionary-encoded column for states might have a few values in its dictionary (1 => CA, 2 => FL, 3 => NY) and the data looks like an array of numbers (eg. [1, 1, 1, 2, 1, 3, 3, 3, 1, 3, 2]). The fastest way to aggregate is to actually use an array, as the dictionary index conveniently maps to an array index. Then, when it comes to merging several of those arrays, they're turned into hash tables. Combine enough of those optimizations and proper data layouts, and you end up with several orders of magnitude of performance differences between engines.
bsg75 7y ago
Its somewhat unique in the open source category. As a column store engine that supports (hybrid) SQL, manages its own storage and clustering, and has only one external dependency (Zookeeper), I believe its main competitor is Vertica which can be very expensive. I assume Oracle, IBM, and MS have column stores as well and for a cost. Greenplum is a Postgres fork, and same scale requires much more hardware. Citus is row based, and therefore will lag in scan time for many OLAP query patterns. Presto, Hive, Spark, all of the "post-hadoop" options may scale larger, but will also lag in scan time, and have significant external dependencies - mainly storage. Clickhouse is easy to install, configure a cluster, load and query. It does have limitations, but currently all horizontally scaled database platforms do.
quantumwoke 7y ago
It's the first time I've seen it on HN, and a cursory search shows only a few submissions with significant discussion. Nonetheless, as other commenters have said the SQL-based query language and low external dependencies made it easy for me to integrate it into my workflow.
golover721 7y ago
Clickhouse is extremely optimized for performing analytics on time series style data. Things like clickstream data (hence the name). Performing queries to do funnel level analysis for example, are extremely fast compared to other analytics databases I have tried. As others have mentioned it is often compared to Apache Druid in its intent and feature set.
barrkel 7y ago
Low latency query execution and it scales down well to small datasets, are my reasons for really liking it. Top throughout when scanning TB+ of data on multiple nodes isn't my use case; it's answering a query in web request response times over a few hundred million rows of data. The low latency to query execution is really nice.
tadkar 7y ago
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?
econcon 7y ago
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.
pyppo 7y ago
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)

oreoftw 7y ago

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

georgewfraser 7y ago
Snowflake, Redshift, BigQuery, Databricks, Presto.
- dilyevsky 7y ago
  Github link pls
- edmundsauto 7y ago
  Of those, it looks like only Presto is open source and/or free. So maybe it's a presto versus clickhouse comparison, which explains why so many choose clickhouse (it's one of only 2 options in its class).
  - deepsun 7y ago
    Presto is not really a database, it's the SQL layer on top of many other data storages, like Hive / any other SQL DB / Redis / Cassandra / etc.
  - jfim 7y ago
    Presto is mostly an engine that runs on top of other databases, although it does have its own query execution engine. The basic idea behind Presto is that it federates other databases, and supports doing joins across them. From what I understand, the problem that it solved at Facebook is bridging the gap between different teams; if a team has MySQL and another has files stored on HDFS, it doesn't really matter because all you do is query Presto and it'll query both under the covers. The alternative is setting up data pipelines, and dealing with the ongoing issues of maintaining those data pipelines.
- deepsun 7y ago
  I can say for BigQuery and Databricks from personal experience. BigQuery is much slower and is much more expensive for both storage and query. Databricks (Spark) is even slower than that (both io and compute), although you can write custom code/use libs. You seem to underestimate how heavily ClickHouse is optimized (e.g. compressed storage).
  - derefr 7y ago
    > You seem to underestimate how heavily ClickHouse is optimized (e.g. compressed storage). Is it any more compressed than Apache Hive's ORC format (https://orc.apache.org https://orc.apache.org)? Because that's increasingly accepted as a storage format in a lot of these analytical systems.
    - deepsun 7y ago
      Yes, looks like it. According to these posts, ORC only uses snappy or zlib compression, while Clickhouse uses double-delta, Gorilla, and T64 algorithms. https://engineering.fb.com/core-data/even-faster-data-at-the-speed-of-presto-orc/ https://engineering.fb.com/core-data/even-faster-data-at-the... https://www.altinity.com/blog/2019/7/new-encodings-to-improve-clickhouse https://www.altinity.com/blog/2019/7/new-encodings-to-improv...
    - marcinzm 7y ago
      ORC or Parquet are file storage formats so without context their performance can be almost anything. Where is the data stored? S3? HDFS? Local ram disk? Clickhouse manages the whole distributed storage, ram caching, etc. thing for you. In my experience, a unified single purpose vertically integrated solution will be faster than a bunch of kitchen sink solutions bolted together.
- deepsun 7y ago
  Also, Presto and Databricks(Spark) is just a layer on top of other storagea, it cannot optimize storage for querying, as you do storage yourself.
- FridgeSeal 7y ago
  Snowflake doesn’t really keep up with Clickhouse (in my experience) and it costs money. DataBricks is essentially Spark, and I shouldn’t need a whole spark cluster just to get database functionality. It also costs money. Unless I’m mistaken, Presto is just a distributed query tool over the top of a separate storage layer, so that’s 2 things you have to setup. I have no experience with BigQiery but I’ve heard good things about it and Redshift, however but if the rest of your infra isn’t on GCP/AWS then that will probably be a blocker. Clickhouse is open source, comes with convenient clients in a bunch of languages as well as a HTTP API. It’s outrageously fast and has some cool features and makes the right trade-offs for its use-case, large range of supported input/output formats, built-in Kafka support and the replication and sharding is reasonably straightforward to setup.
- bdcravens 7y ago
  According to https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html Clickhouse has better performance than 3 of those (the other 2 haven't been tested in that benchmark)
  - TheTank 7y ago
    I would be cautious using this as a proxy for performance ranking as some items (dataset, queries) are normalized, but the hardware setup is not.
    - jstrong 7y ago
      the hardware profile is listed in each row, also, the guy is totally meticulous!
      - quod_2058 7y ago
        I don't think it's fair to say "A is faster than B" like in the above comments based on the order they appear in a list that mixes GPU clusters and laptops results. The author of the benchmark does nothing wrong deontologically, but the results table seems ordered by time and some people jump to quick conclusion or use it as a way to rank performance when it's not appropriate.
- barrkel 7y ago
  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.
phillc73 7y ago
DuckDB perhaps[1]: https://www.duckdb.org 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.
- ptrott2017 7y ago
  Curious, as a sometimes MOneyDB user, be interested to know why did you choose DuckDB over MonetDB?
  - phillc73 7y ago
    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#issuecomment-490589276 https://github.com/MonetDB/MonetDBLite-R/issues/38#issuecomm...
wochiquan 7y ago
Apache Druid: https://druid.apache.org/docs/latest/design/index.html https://druid.apache.org/docs/latest/design/index.html
- wochiquan 7y ago
  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-time-insights-to-ensure-a-high-quality-experience-19e1e8568d06 https://netflixtechblog.com/how-netflix-uses-druid-for-real-...

atombender 7y ago

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 columns. A lot of people use Elasticsearch for analytics. Being based on Lucene, it's kind of columnar, and it can perform very well indeed on aggregations. InfluxDB may be good, but it's not fully open source.

lflux 7y ago
What about Greenplum or CitusDB?
- quod_2058 7y ago
  Greenplum seems to like a way to throw more hardware at a problem. Queries are too slow with postgres? Shard your data across machines and distribute queries to speed things up by running in parallel. It's using scale as a means to compensate for low efficiency. On the opposite side of the spectrum you have other open source projects like questDB that have full focus on core performance: constantly optimise to get as much as possible from a single processor core. You can't scale out (at least yet), but given how fast it is on single core, it will be pretty powerful if they chose to go this route.
  - atombender 7y ago
    Note that Greenplum supports column-oriented tables. I haven't used it myself, so I can't comment on whether it's slower than ClickHouse.
- deleted 7y ago
  [deleted]
- atombender 7y ago
  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.

RhodesianHunter 7y ago

Performance at scale for certain workloads.

amyjess 7y ago

It's legitimately fast. My company migrated our time-series data from InfluxDB to ClickHouse last year (I personally led this, in fact), and the performance difference is night and day. While I liked a lot of what Influx could do, it was also nonstandard in bizarre ways (Clickhouse behaves more like a subset of SQL), sometimes shockingly immature, and despite appearing fast when we first started using it, so slow that it was a considerable bottleneck.

deepsun 7y ago
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.
- dikei 7y ago
  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.
  - deepsun 7y ago
    That's why I mentioned VictoriaMetrics :) It supports push (using any protocol).
jstrong 7y ago
I think of influx performance as similar to pandas. It's fast enough for many things it was designed for, but it's pretty easy to hit a performance cliff. In particular, "select * from table" type queries are very slow, so don't expect to be pulling out large blocks of data to do analysis somewhere else. However, influx, being schemaless, can be outstanding for rapidly prototyping ephemeral metrics, as adding a new measurement is zero-cost (just start writing it). It also plays great with grafana for building dashboards. Finally, I much prefer the ergonomics enhancements of the influx query language (v1, the v2 "flux" language looks terrible to me personally), particularly how duration strings are a first-class datatype ("group by time(1h)"). Interested to hear more about clickhouse performance, haven't had a chance to use it for anything where performance would matter significantly, although am aware it can be very fast.

dunkelheit 7y ago

> 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 tricks make all the difference and a lot of love went into the clickhouse implementation. Just to give you a taste: there is a custom hash table with specializations for different key types (e.g. it will store a precomputed hash for strings but not for integers and use it to speed up equality test). Variable length data is stored in arenas to reduce allocator pressure. Data will of course be aggregated in several hash tables in different threads and then merged together, but if there is a lot of keys each table will additionally be sharded so that the merge step can be performed in parallel too. Of course you shouldn't trust random claims on the internet that clickhouse is fast and should do a small case study yourself. Then you'll appreciate how easy is to setup a clickhouse instance or a small cluster. It can easily slurp up most common formats. It is just a single binary with minimal dependencies that will run as-is on any modern linux. There is just a single node type (compare this to druid madness). You are right that there is a lot of limitations and, how should I put it, quirks. This is resoundingly not a general-purpose database and someone used to the comforts of e.g. postgres will encounter some nasty surprises. Bugs are unfortunately common, especially in the newer functionality. But performance is its main feature and it makes many users of clickhouse put up with its limitations.

jfim 7y ago
> Of course the way to implement GROUP BY is with a hash table but little tricks make all the difference and a lot of love went into the clickhouse implementation. Actually, for low cardinality columns, _not_ using a hash table will speed up things. For example, a dictionary-encoded column for states might have a few values in its dictionary (1 => CA, 2 => FL, 3 => NY) and the data looks like an array of numbers (eg. [1, 1, 1, 2, 1, 3, 3, 3, 1, 3, 2]). The fastest way to aggregate is to actually use an array, as the dictionary index conveniently maps to an array index. Then, when it comes to merging several of those arrays, they're turned into hash tables. Combine enough of those optimizations and proper data layouts, and you end up with several orders of magnitude of performance differences between engines.

bsg75 7y ago

Its somewhat unique in the open source category. As a column store engine that supports (hybrid) SQL, manages its own storage and clustering, and has only one external dependency (Zookeeper), I believe its main competitor is Vertica which can be very expensive. I assume Oracle, IBM, and MS have column stores as well and for a cost. Greenplum is a Postgres fork, and same scale requires much more hardware. Citus is row based, and therefore will lag in scan time for many OLAP query patterns. Presto, Hive, Spark, all of the "post-hadoop" options may scale larger, but will also lag in scan time, and have significant external dependencies - mainly storage. Clickhouse is easy to install, configure a cluster, load and query. It does have limitations, but currently all horizontally scaled database platforms do.

quantumwoke 7y ago

It's the first time I've seen it on HN, and a cursory search shows only a few submissions with significant discussion. Nonetheless, as other commenters have said the SQL-based query language and low external dependencies made it easy for me to integrate it into my workflow.

golover721 7y ago

Clickhouse is extremely optimized for performing analytics on time series style data. Things like clickstream data (hence the name). Performing queries to do funnel level analysis for example, are extremely fast compared to other analytics databases I have tried. As others have mentioned it is often compared to Apache Druid in its intent and feature set.

barrkel 7y ago

Low latency query execution and it scales down well to small datasets, are my reasons for really liking it. Top throughout when scanning TB+ of data on multiple nodes isn't my use case; it's answering a query in web request response times over a few hundred million rows of data. The low latency to query execution is really nice.

tadkar 7y ago

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?

econcon 7y ago

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.

pyppo 7y ago

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)