Altinity Blog

If you need Advanced SQL Support ClickHouse is not there (Yet) but if you need high performance for relatively basic queries ClickHouse is great. It is developed mostly by ClickHouse staff but there is at least one company https://www.altinity.com/ which offers Commercial Support, Consulting, Trainin for ClickHouse

ClickHouse uses a single primary key index, which matches the sort order, plus skip indexes, which knock out blocks to scan. Here's a writeup that explains skip indexes. https://altinity.com/blog/clickhouse-black-magic-skipping-indices You can also check out the following webinar, which explains how ClickHouse indexes work in general. Here's a link to the discussion of indexes. https://youtu.be/1TGGCIr6dMY?t=1933 p.s. The blog article is missing some images that WordPress seems to have lost but you'll still get the idea. (Should be fixed shortly.) Disclaimer: I work for Altinity

For context, there are multiple Clickhouse companies. This is the one that spun out of Yandex and took the Clickhouse name as their namesake for the company! Altinity has been in the space for a while offering Clickhouse services as well: https://altinity.com/ EDIT: brazenly was the wrong word here :)

Few comments: - The CH table schema generated by TSBS isn't optimized for the queries. First of all, it doesn't uses CODEC (https://altinity.com/blog/2019/7/new-encodings-to-improve-clickhouse) and many other optimizations CH have. > We tried multiple batch sizes and found that in most cases there was little difference in overall insert efficiency This is wrong in CH world where batch size matters a lot. I would recommend keep this even more higher around (10x of current value). Humble Suggestion: There are many things not quite properly interpreted about CH and reading through the blog it seems like you're focusing more on areas which CH is lacking/missing. Please don't do these things.

Aiven will offer a managed Clickhouse service too: https://landing.aiven.io/2020-upcoming-aiven-services-webinar/ And also Altinity is a trustable partner with a great know-how about Clickhouse internals. They have started to offer managed instances in AWS: https://altinity.com/altinity-cloud-test-drive/ At last, Alibaba Cloud has an option to use: https://www.alibabacloud.com/product/clickhouse Are there any other ones?

I wonder how this will play out with https://altinity.com who have been doing enterprise support for quite some time..

Yes to both, Altinity maintains the ClickHouse Grafana plugin https://altinity.com/blog/2019/12/28/creating-beautiful-grafana-dashbo… And Superset has a recommendation of a ClickHouse connector https://superset.apache.org/docs/databases/clickhouse

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-a…

Worth to note clickhouse-local: full featured ClickHouse SQL engine for files in CSV/TSV/JSONLines, whatever... https://www.altinity.com/blog/2019/6/11/clickhouse-local-the-power-of-…

That makes sense. If you do ever want to check out Clickhouse and want someone to run it for you, Percona or Altinity [1] can probably help. Not affiliated with either, I just read their Clickhouse-related content. [1](https://www.altinity.com)

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-Inspire… 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…

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-exp…

https://www.scylladb.com/2019/12/12/how-scylla-scaled-to-one-billion-r… I like this one where they put a dataset on 80 machines only then for someone to put the same dataset on 1 Intel NUC and outperform in query time. https://altinity.com/blog/2020-1-1-clickhouse-cost-efficiency-in-actio… Datasets never become big enough…

Altinity isn't just a vendor, it's THE ClickHouse vendor before ClickHouse became a company. https://altinity.com/blog/big-news-in-the-clickhouse-community always nice to see a database ceo be "one of us" and/or "write like a real human being".

Storing telemetry efficiently is only part of what Monitoring is supposed to do. The other part is querying: ad-hoc queries, dashboards, alerting queries executed each 15s or so. For querying to work fast, there has to be an efficient index or multiple indexes depending on the query. Since you referred ClickHouse as efficient columnar storage, please see what makes it different from a time series database - https://altinity.com/wp-content/uploads/2021/11/How-ClickHouse-Inspire…

> Moving 1 billion rows is no easy task This isn't an accurate premise. Modern OLAP databases make dealing with billions to trillions of rows manageable, including on a single server. Exporting "select * from table" from an OLTP such as Postgres or MySQL into an OLAP is trivial and quite fast, and if 100M rows/sec on commodity servers isn't fast enough, there's always performance tuning [1]. [1] https://altinity.com/blog/loading-100b-rows-in-minutes-in-altinity-clo…

I'm not sure how far the author has gone, but they should check out Gorilla compression[1] (just the compression part, not the whole database). It works well for time-series data, and might be suitable here? Basically if your numbers don't deviate massively--think of a CPU metric that stays in the same place throughout the day, inside the bounds of 0-100--the compression is really effective. Clickhouse supports Gorilla and some others[2] that might also be of use. [1]: https://www.vldb.org/pvldb/vol8/p1816-teller.pdf [2]: https://altinity.com/blog/2019/7/new-encodings-to-improve-clickhouse

Not in particular sorry, most of the good content I've found is on Altinity [1] and Alibaba's technical blogs [2][3]. These tend to be mostly focused on how the data itself is stored and how to use Clickhouse, but don't really dive into the specifics of how query processing is performed. [1] https://altinity.com/blog/ [2] https://www.alibabacloud.com/blog/clickhouse-kernel-analysis-storage-s… [3] https://www.alibabacloud.com/blog/clickhouse-analysis-of-the-core-tech…

They would have saved a whole lot of time just using Clickhouse which is a US company and has completely severed ties with Russia [1]. Altinity has offered managed clickhouse since 2020 [2], and now Clickhouse Cloud does too [3] [1] https://clickhouse.com/blog/we-stand-with-ukraine [2] https://altinity.com/cloud-database/ [3] https://clickhouse.com/cloud

I would have thought better compression was one, but it looks like Clickhouse now supports all the time series-optimized encodings like delta and gorilla - https://altinity.com/blog/2019/7/new-encodings-to-improve-clickhouse.

Another interesting comparison would be clickhouse-local: https://altinity.com/blog/2019/6/11/clickhouse-local-the-power-of-clic…

Your statement is misleading at least. In this short article nowhere said that Druid is faster than ClickHouse in 8 times. They claimed: "Druid is simply 2 times faster than ClickHouse" (actually by total runtime it's only 1.5 times faster) There are also newer ClickHouse benchmark which total runtime = 0.779s This is almost the same number as in Imply statement. https://altinity.com/blog/ultra-fast-data-loading-and-testing-in-altin…

We're big fans of ClickHouse here at GraphCDN, our entire analytics stack is based on it[0] and it's been scaling well from tens of thousands of events to now billions of events! [0]: https://altinity.com/blog/delivering-insight-on-graphql-apis-with-clic…

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

clickhouse as aggregate functions (Pearson correlation coefficient,quantiles , etc... )[0]. Associated with their Materialized view and Liveview features, you can achieve observability [1]. [0]https://clickhouse-docs.readthedocs.io/en/latest [1]https://www.altinity.com/blog/2019/11/13/making-data-come-to-life-with… Edit: fixed broken link.

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-… https://www.altinity.com/blog/2019/7/new-encodings-to-improve-clickhou…

At the end of 2018 Altinity benchmarked ClickHouse against the TSBS and documented it. https://www.altinity.com/blog/clickhouse-for-time-series

Cool project! I've had success with ClickHouses's local utility which is extremely fast. It helps that its basically a "local" version of an already insanely efficient columnar database. https://www.altinity.com/blog/2019/6/11/clickhouse-local-the-power-of-…

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&… [2] https://altinity.com/blog/clickhouse-nails-cost-efficiency-challenge-a…