Comment by the-alchemist | Hacker News Reader

Comment by the-alchemist | Hacker News Reader

Parent Full thread

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

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.

Thanks, this is just what I'm looking for.

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.

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

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/9702#issueco...

Maybe I’m not doing anything particularly challenging with it, but I’ve not found anything lacking with the join functionality.

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.

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.

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

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.

Reply on news.ycombinator.com