3 ms·

I haven't used ClickHouse nor TimescaleDB, but I thought TimescaleDB was competing with the likes of InfluxDB, QuestDB & Prometheus. I guess I'm not surprised t

I haven't used ClickHouse nor TimescaleDB, but I thought TimescaleDB was competing with the likes of InfluxDB, QuestDB & Prometheus. I guess I'm not surprised that it looses to an OLAP database on OLAP queries. Are people using ClickHouse as their timeseries backend? IIRC, Clickhouse doesn't perform all that well with millions of tiny inserts.

monstrado 5y ago
I've had a really positive experience using ClickHouse as an InfluxDB replacement. Initially I used the BUFFERED table type to overcome the "tiny inserts" problem, but ultimately just batch up writes in my custom line-protocol TCP server which translates line-protocol to JDBC inserts (RowBinary). Last time I checked I have a few hundred billion rows in the table with a significant compression ratio (not sure off hand). Most importantly, the table is ordered efficiently enough to allow me to query years of metrics (Grafana plugin) at millisecond speed. Side note, I recall ClickHouse developers mentioning they are currently working on an implementation change which will allow many tiny inserts to be much more performant and realistic to use in the real-world. Hope this helps!
sin7 5y ago
The answer is yes. I used ClickHouse to calculate and forecast sales of products at a dozen or so stores. The compression was huge because it's essentially the same data every day except for changes to the inventory. At the time I checked vanilla PostgreSQL, TimeScaleDB and ClickHouse. It wasn't even close when it came to storage or performance. ClickHouse allowed me to work off of an old workstation where I installed Ubuntu. In my case the data arrived in CSVs with around 20k skus. Had they arrived a couple at a time, I could have created a CSV and written to ClickHouse later or used any of the other storage methods available in ClickHouse.
- jasfi 5y ago
  What was your opinion of Postgres vs the others?
  - sin7 5y ago
    PostgreSQL is not in the game. Completely different purposes. PostgreSQL is great for what it's meant to do. Analytics is not it. Reads are too slow.
valyala 5y ago
At first, let's give the definition of `time series`. This is a series of (timestamp, value) pairs ordered by timestamp. The `value` may contain arbitrary data - a floating-point value, a text, a json, a data structure with many columns, etc. Each time series is uniquely identified by its name plus an optional set of {label="value"} labels. For example, temperature{city="London",country="UK"} or log_stream{host="foobar",datacenter="abc",app="nginx"}. ClickHouse is perfectly optimized for storing and querying of such time series, including metrics. That's true that ClickHouse isn't optimized for handling millions of tiny inserts per second. It prefers infrequent batches with big number of rows per each batch. But this isn't the real problem in practice, because: 1) ClickHouse provides Buffer table engine for frequent inserts. 2) It is easy to create a special proxy app or library for data buffering before sending it to ClickHouse. TimescaleDB provides Promscale [1] - a service, which allows using TimescaleDB as a storage backend for Prometheus. Unfortunately, it doesn't show outstanding performance comparing to Prometheus itself and to other remote storage solutions for Prometheus. Promscale requires more disk space, disk IO, CPU and RAM according to production tests [2], [3]. [1] https://github.com/timescale/promscale https://github.com/timescale/promscale [2] https://abiosgaming.com/press/high-cardinality-aggregations/ https://abiosgaming.com/press/high-cardinality-aggregations/ [3] https://valyala.medium.com/promscale-vs-victoriametrics-resource-usage-on-production-workload-91c8e3786c03 https://valyala.medium.com/promscale-vs-victoriametrics-reso... Full disclosure: I'm CTO at VictoriaMetrics - competing solution for TimescaleDB. VictoriaMetrics is built on top of architecture ideas from ClickHouse.

monstrado 5y ago

I've had a really positive experience using ClickHouse as an InfluxDB replacement. Initially I used the BUFFERED table type to overcome the "tiny inserts" problem, but ultimately just batch up writes in my custom line-protocol TCP server which translates line-protocol to JDBC inserts (RowBinary). Last time I checked I have a few hundred billion rows in the table with a significant compression ratio (not sure off hand). Most importantly, the table is ordered efficiently enough to allow me to query years of metrics (Grafana plugin) at millisecond speed. Side note, I recall ClickHouse developers mentioning they are currently working on an implementation change which will allow many tiny inserts to be much more performant and realistic to use in the real-world. Hope this helps!

sin7 5y ago

The answer is yes. I used ClickHouse to calculate and forecast sales of products at a dozen or so stores. The compression was huge because it's essentially the same data every day except for changes to the inventory. At the time I checked vanilla PostgreSQL, TimeScaleDB and ClickHouse. It wasn't even close when it came to storage or performance. ClickHouse allowed me to work off of an old workstation where I installed Ubuntu. In my case the data arrived in CSVs with around 20k skus. Had they arrived a couple at a time, I could have created a CSV and written to ClickHouse later or used any of the other storage methods available in ClickHouse.

jasfi 5y ago
What was your opinion of Postgres vs the others?
- sin7 5y ago
  PostgreSQL is not in the game. Completely different purposes. PostgreSQL is great for what it's meant to do. Analytics is not it. Reads are too slow.

valyala 5y ago

At first, let's give the definition of `time series`. This is a series of (timestamp, value) pairs ordered by timestamp. The `value` may contain arbitrary data - a floating-point value, a text, a json, a data structure with many columns, etc. Each time series is uniquely identified by its name plus an optional set of {label="value"} labels. For example, temperature{city="London",country="UK"} or log_stream{host="foobar",datacenter="abc",app="nginx"}. ClickHouse is perfectly optimized for storing and querying of such time series, including metrics. That's true that ClickHouse isn't optimized for handling millions of tiny inserts per second. It prefers infrequent batches with big number of rows per each batch. But this isn't the real problem in practice, because: 1) ClickHouse provides Buffer table engine for frequent inserts. 2) It is easy to create a special proxy app or library for data buffering before sending it to ClickHouse. TimescaleDB provides Promscale [1] - a service, which allows using TimescaleDB as a storage backend for Prometheus. Unfortunately, it doesn't show outstanding performance comparing to Prometheus itself and to other remote storage solutions for Prometheus. Promscale requires more disk space, disk IO, CPU and RAM according to production tests [2], [3]. [1] https://github.com/timescale/promscale https://github.com/timescale/promscale [2] https://abiosgaming.com/press/high-cardinality-aggregations/ https://abiosgaming.com/press/high-cardinality-aggregations/ [3] https://valyala.medium.com/promscale-vs-victoriametrics-resource-usage-on-production-workload-91c8e3786c03 https://valyala.medium.com/promscale-vs-victoriametrics-reso... Full disclosure: I'm CTO at VictoriaMetrics - competing solution for TimescaleDB. VictoriaMetrics is built on top of architecture ideas from ClickHouse.