How ClickHouse saved our data (2020)
mux.com
mux.com
Queries went from a 12 hour table scan to ~2 minutes, and production queries from 45 sec to 400ms.
This is without doing anything particularly tricky on the clickhouse side, nor even putting it on a particularly large machine. When benchmarking it, I put it on a 10yr old laptop, and I was seeing production queries in the seconds range,
Hmm, why do you have such large tables and then do full scans?
Edit: and if it's analytics why were you using a row store instead of a columnar database? It's like saying changing to a Ford Mustang was 10x faster than a rusty John Dere combine.
Production queries will select either one row by primary key, or sum metrics on up to a million rows over a set of the values.
This point confuses me, parent is telling us how they went from a row-based (pgsql) to a column-based DB (Clickhouse), and you asked them why they didn't?
And in the very, very rare case it is absolutely necessary to do a full scan, then Postgres might not be a good idea. Why is it being bashed? I read "Postgres was 12h slow" not "we picked the wrong tool and of course Postgres was slow". Don't blame the hammer.
IME, it's common for programmers to not be educated on what modern databases can do. This was made worse with all the NoSQL mania ending up with the MongoDB is web scale. We are still dealing with the fallout of that.
There were many places where the right choices weren't made for this, and could have been made better. But even with optimizations in the schema speeding things up by a factor of 3, we would have been looking at something that still didn't hit the performance needs. I'm guessing that to do the reorg on the full DB, we would be looking at O(week). Effective indexes are great, but when one optimized index is tens of GB and takes a day to create, you start to look elsewhere.
I took issue with your original comment as it was short on details and could be interpreted in a different light.
According to this comment (https://news.ycombinator.com/item?id=26187645) they were using a columnar extension in Postgresql.
I ran: `select count (distinct foo)` and got ~9000 distinct items over 2B rows in 12 hours. Running the same query in Clickhouse was O(minute)
As a little extra comparison, the total size of the Clickhouse database is 1/2 the size of the indexes in Postgres.
was your PG DB been compressed?
It got converted to a table with ~25 Float32 columns + a couple of key columns.
Queries on the PG setup here are back to the bad old days of being limited by bulk disk performance.
You could reduce the gap by running PG on compressed filesystem, so it would be more apples to apples comparison.
For whoever is interested to try it out, be aware that you really should understand how "parts" and "merges" work and your write-workload should take that into consideration (for example dynamically lower the workload when "merges" are running, take into consideration the I/O needed to delete pending old "parts", etc...) - if you don't then you're very likely to get bad performance if your workload is write-heavy. The amount of columns and the sort order is as well extremely important.
Last year I decided to start using Clickhouse to host Collecd's metrics ( https://www.segfault.digital/how-to-directly-use-the-clickho... ) and so far it's working well. I upgraded twice the version of the CH-software, and killed once by mistake its VM but afterwards I never had problems ("so far", hehe).
I have one column that's an incrementing integer per partition, and for a million rows the total storage for that field is ~20k. That's a savings of something like 10% over the whole dataset.
Sorry, I'm probably misunderstanding you - I'm not sure about that "10%", what does it it refer to?
Anyway, I do like a lot in Clickhouse to be able to chain codecs (I personally like to think about "Delta/DoubleDelta/Gorilla/T64 codecs" being "encodings" and the "general purpose codecs LZ4/LZ4HC/ZSTD" being "compression codecs").
I don't have a good math background => about Delta & DoubleDelta I liked this explanation ( https://altinity.com/blog/2019/7/new-encodings-to-improve-cl... ) which defines "delta" as tracking "distance" and "doubledelta" tracking "acceleration" between consecutive column values.
In the end I tested, using relatively small datasets (which were different between usecases), all combinations of encodings (delta/doubledelta/gorilla/t64) and ZSTD-compression levels (mostly 1/3/9) (I ignored LZ4*).
- "Delta"&"DoubleDelta" were often interesting (but in general for my data using "Delta"+ZSTD was already good enough compared to the rest).
- "Gorilla" somehow never gave me any benefits if compared to other codecs and/or compression algos.
- "T64" is a bit a mistery for me, anyway in some tests it delivered excellent results compared to the other combinations, therefore I'm currently using just T64 for some columns, and for some other columns as T64+ZSTD(9).
EDIT: sorry, I think I got it - you probably meant something like "just by doing that on that specific column, the overall storage needs were reduced by 10% for the whole dataset", right?
If you want, there’s a detailed analysis of various db’s performance on various tasks here:
https://tech.marksblogg.com/benchmarks.html
It’s one of the top performing ones, even the single machine setup comfortably beats the likes of Presto, Athena, etc
I am migrating a lot of complex Scala queries to CH SQL and I am surprised at everything I can do, very deep and rich API backed by great performance. Also, Clickhouse is a bit more operationally involved than a solution like BigQuery, but at a fraction of the performance per dollar cost.
I'd like to use Clickhouse but on a very small team without an ops person, it feels like a liability managing a server ourselves.
Disclaimer: I work for Altinity.
"ClickHouse is still faster than the sharded Postgres setup at retrieving a single row, despite being column-oriented and using sparse indices. I'll describe how we optimized this query in a moment."
To be transparent, we included Citus in original versions of the post, but decided to take it out because it didn't feel like it was a fair representation of Citus™. As Adam mentioned, what we were using was heavily customized and based on a pretty outdated fork by the time we transitioned.
It’s stupid fast, I know it’s capable of workload sizes past where I’ve pushed it so far. I’ve used it in anger and even then I was able to solve the issues I had easily. The out-of-the-box integrations (like the Kafka one) have made my life so much easier. I get excited whenever I get to use the instance I manage at work, or I demo something about it to someone because I know I’ll often blow their minds a bit.
AFAIK there was no evidence that solarwind fiasco was because of jetbrains [0], also the clickhouse is open source [1] under apache2 license and you can check the source and question it by yourself.
[0] - https://www.zdnet.com/article/jetbrains-denies-being-involve...
While Clickhouse can be lightning fast, is it really designed to be a main backend database?
The obsession with Clickhouse is the phenomenal performance for the OLAP use case, a scenario where there were not many open source, easy to install and maintain options. For the most part you can treat it as a “normal” database, insert data into it and query it without messing about with file format conversions and so on. The fact that it is blindingly fast is a big bonus!
While Clickhouse is great at what it does, I would expect a „normal“ database to support transactions. Don’t use it to handle bank accounts.
This allows to safely use ClickHouse for billing data.
https://stackoverflow.com/questions/60163070/is-clickhouse-d...
This is not your application backend database.
https://clickhouse.tech/docs/en/engines/table-engines/merget...
They are not like indexes for point lookups, but also sparse. Actually they are the best you can do without blowing up the storage.