4 ms·

Also wanted to share my overall positive experience with Clickhouse. UPSIDES * started a 3-node cluster using the official Docker images super quickly * inge

Also wanted to share my overall positive experience with Clickhouse. UPSIDES * started a 3-node cluster using the official Docker images super quickly * ingested billions of rows super fast * great compression (of course, depends on your data's characteristics) * features like https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/aggregatingmergetree/ https://clickhouse.tech/docs/en/engines/table-engines/merget... are amazing to see * ODBC support. I initially said "Who uses that??", but we used it to connect PostgreSQL and so we can keep the non-timeseries data in PostgreSQL but still access PostgreSQL tables in Clickhouse (!) * you can go the other way too: read Clickhouse from PostgreSQL (see https://github.com/Percona-Lab/clickhousedb_fdw https://github.com/Percona-Lab/clickhousedb_fdw, although we didn't try this) * PRs welcome, and quickly reviewed. (We improved the ODBC UUID support) * code quality is pretty high. DOWNSIDES * limited JOIN capabilities, which is expected from a timeseries-oriented database like Clickhouse. It's almost impossible to implement JOINs at this kind of scale. The philosophy is "If it won't be fast as scale, we don't support it" * not-quite-standard SQL syntax, but they've been improving it * limited DELETE support, which is also expected from this kind of database, but rarely used in the kinds of environments that CH usually runs in (how often do people delete data from ElasticSearch?) It's really an impressive piece of engineering. Hats off to the Yandex crew.

hodgesrm 6y ago
> It's really an impressive piece of engineering. Hats off to the Yandex crew. And thousands of contributors! Toward the end of 2020 over 680 unique users had submitted PRs and close to 2000 had opened issues. It's becoming a very large community.
yamrzou 6y ago
Could you share more details about the limited JOIN capabilities? AFAIK, Clickhouse has multiple join algorithms and supports on-disk joins to avoid out of memory: https://github.com/ClickHouse/ClickHouse/issues/10830 https://github.com/ClickHouse/ClickHouse/issues/10830 https://github.com/ClickHouse/ClickHouse/issues/9702#issuecomment-618299812 https://github.com/ClickHouse/ClickHouse/issues/9702#issueco...
- FridgeSeal 6y ago
  Maybe I’m not doing anything particularly challenging with it, but I’ve not found anything lacking with the join functionality.
  - neilknowsbest 6y ago
    One issue I've come across is that the query optimizer in Clickhouse does not propagate `where` clauses through a join. My terminology might be wrong, so consider this example: select * from a inner join b using (id) where b.foo = 'bar' Clickhouse will not evaluate `foo = 'bar'` before performing the join, so you might wind up with a join that produces a large intermediate result before the filtering happens. Postgres (probably other databases) will optimize this for you. To force Clickhouse to filter first, you would need to write something like select * from a inner join ( select * from b where foo = 'bar' ) b using (id) Maybe not a strict limitation, but the workaround is a bit janky.
    - FridgeSeal 6y ago
      Interesting, I swear I’ve done that before and has it respect the clause. You can also use PREWHERE I believe, which has the benefit of increasing performance.
FridgeSeal 6y ago
Most minor of nitpicks: > timeseries-oriented database Technically it’s a column oriented database that is good at time series stuff. I only say that because I know there are some databases that are even more specialised towards timeseries and ClickHouse can do way more.
- valyala 6y ago
  I'm working on VictoriaMetrics - a fast specialized time series database and monitoring solution built on top of ClickHouse architecture ideas. [1] [1] https://valyala.medium.com/how-victoriametrics-makes-instant-snapshots-for-multi-terabyte-time-series-data-e1f3fb0e0282 https://valyala.medium.com/how-victoriametrics-makes-instant...
IanCal 6y ago
I'd like to add an upside which is: Totally great and simple on a single node. I looked at a bunch of analytical databases and had a lot that started with "so here's a basic 10 node cluster". Clickhouse installed and worked instantly with decent but not "big" data with no hassle. A hundred million rows with lots of heavy text blobs and a lot of columns, that kind of thing. Happily dealt with triple nested joins over that, and with billions of entries in arrays on those columns didn't bat an eye. I'm sure I could do some great magic in postgres but naive work didn't give anywhere near the same results as clickhouse (obvious caveat for my workload). Pretty good with JSON data, my only issue there at the time (may have improved) was you had to format the JSON quite strictly.
- u678u 6y ago
  Thanks, this is just what I'm looking for.
kureikain 6y ago
Can clickhouse deal with medium-large blob data? Say the size of a normal email? We are using Postgres to store email at my app: https://hanami.run https://hanami.run The log is append only and getting scrub daily. Can clickhouse deal with that? The query is very simple, just need to match exactly a single column(domain) and pagination?
- leesalminen 6y ago
  Don’t know how you feel about vendor lock in, but I use Dynamo to store many millions of email logs and it works great. Got tired of having that data in MySQL.

hodgesrm 6y ago

> It's really an impressive piece of engineering. Hats off to the Yandex crew. And thousands of contributors! Toward the end of 2020 over 680 unique users had submitted PRs and close to 2000 had opened issues. It's becoming a very large community.

yamrzou 6y ago

Could you share more details about the limited JOIN capabilities? AFAIK, Clickhouse has multiple join algorithms and supports on-disk joins to avoid out of memory: https://github.com/ClickHouse/ClickHouse/issues/10830 https://github.com/ClickHouse/ClickHouse/issues/10830 https://github.com/ClickHouse/ClickHouse/issues/9702#issuecomment-618299812 https://github.com/ClickHouse/ClickHouse/issues/9702#issueco...

FridgeSeal 6y ago
Maybe I’m not doing anything particularly challenging with it, but I’ve not found anything lacking with the join functionality.
- neilknowsbest 6y ago
  One issue I've come across is that the query optimizer in Clickhouse does not propagate `where` clauses through a join. My terminology might be wrong, so consider this example: select * from a inner join b using (id) where b.foo = 'bar' Clickhouse will not evaluate `foo = 'bar'` before performing the join, so you might wind up with a join that produces a large intermediate result before the filtering happens. Postgres (probably other databases) will optimize this for you. To force Clickhouse to filter first, you would need to write something like select * from a inner join ( select * from b where foo = 'bar' ) b using (id) Maybe not a strict limitation, but the workaround is a bit janky.
  - FridgeSeal 6y ago
    Interesting, I swear I’ve done that before and has it respect the clause. You can also use PREWHERE I believe, which has the benefit of increasing performance.

FridgeSeal 6y ago

Most minor of nitpicks: > timeseries-oriented database Technically it’s a column oriented database that is good at time series stuff. I only say that because I know there are some databases that are even more specialised towards timeseries and ClickHouse can do way more.

valyala 6y ago
I'm working on VictoriaMetrics - a fast specialized time series database and monitoring solution built on top of ClickHouse architecture ideas. [1] [1] https://valyala.medium.com/how-victoriametrics-makes-instant-snapshots-for-multi-terabyte-time-series-data-e1f3fb0e0282 https://valyala.medium.com/how-victoriametrics-makes-instant...

IanCal 6y ago

I'd like to add an upside which is: Totally great and simple on a single node. I looked at a bunch of analytical databases and had a lot that started with "so here's a basic 10 node cluster". Clickhouse installed and worked instantly with decent but not "big" data with no hassle. A hundred million rows with lots of heavy text blobs and a lot of columns, that kind of thing. Happily dealt with triple nested joins over that, and with billions of entries in arrays on those columns didn't bat an eye. I'm sure I could do some great magic in postgres but naive work didn't give anywhere near the same results as clickhouse (obvious caveat for my workload). Pretty good with JSON data, my only issue there at the time (may have improved) was you had to format the JSON quite strictly.

u678u 6y ago
Thanks, this is just what I'm looking for.

kureikain 6y ago

Can clickhouse deal with medium-large blob data? Say the size of a normal email? We are using Postgres to store email at my app: https://hanami.run https://hanami.run The log is append only and getting scrub daily. Can clickhouse deal with that? The query is very simple, just need to match exactly a single column(domain) and pagination?

leesalminen 6y ago
Don’t know how you feel about vendor lock in, but I use Dynamo to store many millions of email logs and it works great. Got tired of having that data in MySQL.