3 ms·

Interesting to match with the observations from the practice of using ClickHouse[1][2] for time series: 1. Reordering to SOA helps a lot - this is the whole po

Interesting to match with the observations from the practice of using ClickHouse[1][2] for time series: 1. Reordering to SOA helps a lot - this is the whole point of column-oriented databases. 2. Specialized codecs like Gorilla[3], DoubleDelta[4], and FPC[5] lose to simply using ZSTD[6] compression in most cases, both in compression ratio and in performance. 3. Specialized time-series DBMS like InfluxDB or TimescaleDB lose to general-purpose relational OLAP DBMS like ClickHouse [7][8][9]. [1] https://clickhouse.com/blog/optimize-clickhouse-codecs-compression-schema https://clickhouse.com/blog/optimize-clickhouse-codecs-compr... [2] https://github.com/ClickHouse/ClickHouse https://github.com/ClickHouse/ClickHouse [3] https://clickhouse.com/docs/en/sql-reference/statements/create/table/#gorilla https://clickhouse.com/docs/en/sql-reference/statements/crea... [4] https://clickhouse.com/docs/en/sql-reference/statements/create/table/#doubledelta https://clickhouse.com/docs/en/sql-reference/statements/crea... [5] https://clickhouse.com/docs/en/sql-reference/statements/create/table/#fpc https://clickhouse.com/docs/en/sql-reference/statements/crea... [6] https://github.com/facebook/zstd/ https://github.com/facebook/zstd/ [7] https://arxiv.org/pdf/2204.09795.pdf https://arxiv.org/pdf/2204.09795.pdf "SciTS: A Benchmark for Time-Series Databases in Scientific Experiments and Industrial Internet of Things" (2022) [8] https://gitlab.com/gitlab-org/incubation-engineering/apm/apm/-/issues/ https://gitlab.com/gitlab-org/incubation-engineering/apm/apm... https://gitlab.com/gitlab-org/incubation-engineering/apm/apm/-/issues/4 https://gitlab.com/gitlab-org/incubation-engineering/apm/apm... [9] https://www.sciencedirect.com/science/article/pii/S1877050919310439/pdf?md5=ecc390cfc9d0be432ca0c218985c94d5&pid=1-s2.0-S1877050919310439-main.pdf https://www.sciencedirect.com/science/article/pii/S187705091...

xyzzy_plugh 4y ago
Putting your obvious bias due to affiliation with ClickHouse aside, this is quite disingenuous. Comparing OLAP and OLTP is usually just a waste of time. They solve different problems. This blog post linked within your gitlab issue is quite impartial, relatively speaking, and disputes most of these claims: https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://www.timescale.com/blog/what-is-clickhouse-how-does-i... This also similarly matches my experience operating properly tuned instances. Lastly, regarding the following > Specialized codecs like Gorilla[3], DoubleDelta[4], and FPC[5] lose to simply using ZSTD[6] compression in most cases, both in compression ratio and in performance. I'd love to see some data because this is a very broad generalization that has never remotely been true in my experience. At comparable block sizes, with proper tuning, double delta can absolutely smoke zstd. In fact, while zstd can achieve quite impressive compression and performance, I almost never reach for it as it's rarely optimal when I benchmark, compared to lz4 or xz or snappy or even sometimes gzip. It depends on many factors -- what data, how much data, block size, disk or storage medium, network. Your whole post puts a rather sour taste in my mouth about ClickHouse.
- zX41ZdbW 4y ago
  I have tested these codecs on the metrics data (like CPU usage). One experiment is detailed here: https://www.youtube.com/live/0fSp9SF8N8A?feature=share&t=1764 https://www.youtube.com/live/0fSp9SF8N8A?feature=share&t=176... Another experiment is described in the linked blog post: https://clickhouse.com/blog/optimize-clickhouse-codecs-compression-schema https://clickhouse.com/blog/optimize-clickhouse-codecs-compr... It compares codes on the weather dataset (NOAA). The comment about "DoubleDelta" codec from the ClickHouse codebase: /** NOTE DoubleDelta is surprisingly bad name. The only excuse is that it comes from an academic paper. * Most people will think that "double delta" is just applying delta transform twice. * But in fact it is something more than applying delta transform twice. */

xyzzy_plugh 4y ago

Putting your obvious bias due to affiliation with ClickHouse aside, this is quite disingenuous. Comparing OLAP and OLTP is usually just a waste of time. They solve different problems. This blog post linked within your gitlab issue is quite impartial, relatively speaking, and disputes most of these claims: https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://www.timescale.com/blog/what-is-clickhouse-how-does-i... This also similarly matches my experience operating properly tuned instances. Lastly, regarding the following > Specialized codecs like Gorilla[3], DoubleDelta[4], and FPC[5] lose to simply using ZSTD[6] compression in most cases, both in compression ratio and in performance. I'd love to see some data because this is a very broad generalization that has never remotely been true in my experience. At comparable block sizes, with proper tuning, double delta can absolutely smoke zstd. In fact, while zstd can achieve quite impressive compression and performance, I almost never reach for it as it's rarely optimal when I benchmark, compared to lz4 or xz or snappy or even sometimes gzip. It depends on many factors -- what data, how much data, block size, disk or storage medium, network. Your whole post puts a rather sour taste in my mouth about ClickHouse.

zX41ZdbW 4y ago
I have tested these codecs on the metrics data (like CPU usage). One experiment is detailed here: https://www.youtube.com/live/0fSp9SF8N8A?feature=share&t=1764 https://www.youtube.com/live/0fSp9SF8N8A?feature=share&t=176... Another experiment is described in the linked blog post: https://clickhouse.com/blog/optimize-clickhouse-codecs-compression-schema https://clickhouse.com/blog/optimize-clickhouse-codecs-compr... It compares codes on the weather dataset (NOAA). The comment about "DoubleDelta" codec from the ClickHouse codebase: /** NOTE DoubleDelta is surprisingly bad name. The only excuse is that it comes from an academic paper. * Most people will think that "double delta" is just applying delta transform twice. * But in fact it is something more than applying delta transform twice. */