Altinity, a ClickHouse services company, publishes technical posts on ClickHouse features, encodings, indexing, and cost-efficiency benchmarks.
HN commenters link it for ClickHouse details such as codecs and skip indexes, and mention Altinity as a long-time enterprise ClickHouse support provider.
VictoriaMetrics CTO here.
We at VictoriaMetrics took another approach. We tried using ClickHouse as a database for metrics in 2017, but then decided implementing a specialized database for metrics. This database uses ClickHouse architecture ideas for achieving the best performance and the lowest resource usage. The main difference between ClickHouse and VictoriaMetrics is that VictoriaMetrics is optimized solely for typical observability tasks. It supports all the popular data ingestion protocols, it provides promql-compatible querying API, it provides Graphite-compatible querying API, it provides Prometheus-compatible service discovery and relabeling, it provides Prometheus-compatible alerting and recording rules. It provides built-in web UI for quick exploration and analysis of the ingested metrics, with the ability to investigate the source of high cardinality. It consists of a single small executable (~20MB) without external dependencies with minimum configs and minimum maintenance. See https://altinity.com/wp-content/uploads/2021/11/How-ClickHouse-Inspired-Us-to-Build-a-High-Performance-Time-Series-Database.pdf for more details.
We used the same approach for building VictoriaLogs - a specialized database for logs. It uses the most appropriate architecture ideas from ClickHouse for achieving high performance and low resource usage. It is schemaless and zero-config. It contains of a single small executable without external dependencies. It accepts logs via popular data…
> 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-clickhouse ) 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…
You can't post results of TPC benchmarks without official audit. So it complicates posting results. You can't find common names that are usually compared with ClickHouse there [1]. So open standardized ClickBench tries to encourage benchmarking for everyone.
There are numerous benchmarks that use similar to TPC queries, but those are not standardized and can be misleading. For example a lot of work was done by Fivetran to get this report [0], but they show only overall geomean for those systems and you can't understand how they actually differ. Anyway their queries are not original TPC - variables are fixed in queries, they run first query when official query is a multiquery.
Contributors from Altinity run SSB with flattened and original schemas [2]. SSB is not well standardized and we see a lot of pairwise comparisons with controversial results - generally you can't just reproduce them and get all the results in single place for the same hardware.
[0] https://www.fivetran.com/blog/warehouse-benchmark
[1] https://www.tpc.org/tpcds/results/tpcds_results5.asp?orderby=dbms&version=3
[2] https://altinity.com/blog/clickhouse-nails-cost-efficiency-challenge-against-druid-rockset
Thanks for the thoughtful response.
On the compression point:
- I believe the Altinity Benchmarks [0] are from 2018, on TimescaleDB 0.12.1. TimescaleDB has gotten much better since then (now on version 1.7.2), and most notably, offers native compression now (it did not then).
- I believe manigandham's 70% comment is more of an offhand estimate and not a concrete benchmark. But perhaps he can weigh in. :-)
- Re: compression algorithms, TimescaleDB now employs several best-in-class algorithms, including delta-delta, gorilla, Simple-8b RLE. Much more than just LZ4. [1]
Overall, I don't think anyone has done a real storage comparison between TimescaleDB and Clickhouse since we launched native compression. It's on our todo list, but we also welcome external benchmarks. But based on what we've found versus similar systems, I suspect our storage usage would be really similar.
[0] https://www.altinity.com/blog/clickhouse-for-time-series
[1] https://blog.timescale.com/blog/time-series-compression-algorithms-explained/
1) you're absolutely right. 5k rows isn't "large". We also mentioned that we did hundreds of tests often going between 5k and 15k rows/batch. The overall ingest/query cycle didn't change dramatically in any of these. That is, 5k rows was within a few percentage of 10k rows. Interestingly, the benchmarks that Altinity has, only used 10k rows/sec (which we also did, it just didn't have any major impact in the grand scheme of things).
2) We did not specifically call OPTIMIZE before running queries. Again, learning from the leaders at Altinity and their published benchmarks, I don't see any references that they did either, and neither does the TSBS code appear to call it after ingest.
Happy to try both of these during our live stream next week to demonstrate and learn!
Altinity benchmark (10k rows/batch mention): https://altinity.com/blog/clickhouse-for-time-series
One of the best 2h practical course that I had was just write the fastest square matrix multiplication. You could use any language, any algorithm, just no libraries. The target was a 32 core CPU server (this was ~10 years ago). At 5000x5000 all the Java and Python attempts were running out of memory. In C, We tried some openmp, some optimized algorithm, but in the end the best trick was to flip one of the matrix so that memory could be always prefetched. Out of curiosity another student tried GNU Scientific Library, it turned out to be ~100 times faster. My take away was find the right tool for the job!
A fun read on cloud scale vs optimized code is this recent article comparing ClickHouse and ScyllaDB (https://www.altinity.com/blog/2020/1/1/clickhouse-cost-efficiency-in-action-analyzing-500-billion-rows-on-an-intel-nuc)