Hacker News Reader

Comment by bdcravens | Hacker News Reader

Clickhouse is an analytic column-based RDBMS. It's not a timeseries database. Each class of product is used to solve different problems.

View on HN

Time-series data is just data where every record has a "time" field. That's it.

Any database can handle it, and columnstore RDBMS are designed to store and query trillion-row tables with full SQL functionality. The only advantage a "time-series" database gives you is some time-based query operators (like gap filling, last value, smoothing, etc). Those are now being added to SQL support for RDBMS so there's really nothing to be gained from a time-series database anymore.

"Time-series data is just data where every record has a "time" field. That's it."

That's a pretty big simplification. It's like saying one could go running in dress shoes. (Yes, it's possible, but don't you want to use the right tool for the job?)

As one time-series database example, because TimescaleDB [0] is focused on time-series data, its users benefit from [1]:

* 40-50x compression for metrics data (so storage costs for compressed data are 2-2.5% what they would normally be)

* Versatile continuous aggregate policies

* Variable data retention policies

* Overall much more efficient compute and memory utilization (because of faster insert and query rates)

* And yes, also time-based query operators for gap filling, first/last, LOCF, etc

(And I'm sure roskilli could describe M3DB's own advantages over non-time-series DBs.)

[0] Disclaimer to other readers, I'm a co-founder (although OP already knows this, as we've jousted on HN before :-) )

[1] All of our benchmarks and other engineering notes are published here: https://blog.timescale.com/tag/engineering/

I'd say you started with a standard RDBMS and added the other features like sharding, column-oriented storage, time-series helper functions to SQL, and more to it to end up similar to the other native column stores but with a more flexible and popular Postgres frontend.

At the very least, I think we both agree that the relational/SQL options work just fine compared to limited time-series databases like Influx. And for the record, we do use timescale so you've won me over on the PG usability front.

"And for the record, we do use timescale so you've won me over on the PG usability front."

Great! Didn't realize that.

And yes, I would agree that a relational/SQL time-series database like TimescaleDB can work quite well. :-)

It seems clickhouse also has most of those features, but is not considered a time series database. Is that wrong?

Clickhouse has a table engine for graphite. We've used it for a couple years now after out scaling InfluxDB and working around it several times. Clickhouse works _extremely_ well for graphite data, it can handle several orders of magnitude more load than Influx in my experience.

Clickhouse works exceptionally well as a TSDB.

While this is true, for a metrics workload it does not work great I have both seen and heard from others, mainly due to the fact it does not have an inverted index - so finding a small subset of metrics in a dataset of billions of metrics ends up taking significant time due to the scan required to find the timeseries matching the arbitrary number of dimensions specified to find the timeseries you're looking for.

If you're building it with a specific application and a concrete schema you can create which will result in fast queries and don't have requirements for arbitrary dimensions being specified for lookup, then yes it's great as a TSDB.

Prometheus, M3DB, etc all use an inverted index alongside the column store TSDB to help with metrics workloads.

Most practical applications using Clickhouse for metrics data store the metric index separately. What index you want really depends on the metric system, e.g. with graphite data you don't want an inverted index, you want a trie.

Yes I've seen that also work, it's a lot of stitching together things yourself and we had to put a lot of caching in front of the inverted index we were using, however definitely plausible. ClickHouse doesn't do any streaming of data between nodes as you scale up and down which was a big thing for us since we had large datasets and needed to rebalance when cluster expanded/shrunk.

With regards to trie vs inverted index for Graphite data, I'd actually still be inclined to say inverted index is better based on the amount of queries I saw at Uber with Graphite where people did `servers.*.disk.bytes-used` type queries which is way faster to do using an inverted index since you have a postings list for each part of the dot-separated metric name, rather than traversing a trie with thousands to tens of thousands of entries in index 1 host part of the Graphite name. This is what M3DB does[0].

[0]: https://github.com/m3db/m3/blob/b2f5b55e8313eb48f023e08f6d53...

Just to point out that there is inverted index implementation of graphite data working on clickhouse.

Regarding the auto-rebalance feature, I cannot much more agree with you. It's something that clickhouse definitely need to handle internally.

That's interesting, I had not heard of ClickHouse as a backend for Graphite with an inverted index. Let me know if you have any links to that.

I'm assuming this is an out of process inverted index used alongside ClickHouse? Or is it more of a secondary table contained by ClickHouse which can be searched to find the metrics, then the data is looked up?

The latter scales not as well with billions of unique metrics since it's always a scan across the unique metrics stored in the time window your query searches for (since any arbitrary dimensions can be specified, all must be evaluated). This is the drawback of PromHouse which is an implementation of Prometheus remote storage on top of ClickHouse - and the major reason why PromHouse was only ever a proof of concept rather than a production offering.

I also confirm that. Several companies have successfully transitioned their monitoring stack from graphite initial python implementation to a clickhouse based backend.

Not to bad-mouth Clickhouse but the original python implementation of graphite + carbon was setting the bar very low, though, and transitioning from there to anything would have increasing performances by orders of magnitude.

You should read the link below [1]. Even if it's not uber scale I suspect yandex to use something similar.

I agree that python implementation of graphite was not particularly fast but there was faster implementation in C that companies used first to significantly increase performance. Then coordination of storage backend becomes complex when you try to scale the initial design. This is where clickhouse really shine. It provide out of the box distributed storage with compaction, rollup and fast querying. The other layers are stateless, which means that they'll scale with your computing ressources.

M3DB is roughly doing the same thing as clickhouse but clickhouse is much more advanced database that has proven records of running at petabyte scale without a sweat. For example they now have tiered storage which means that you can store recent event in nvme and rollup to standard HDD...

[1]https://medium.com/avitotech/metrics-storage-how-we-migrated...

That is also my experience. Also in bencmark it is almost as fast as GPU analytical DBs or KDB+.

Hm... I would say that the workload is the same, is it not? After all yandex is using it for logs and for metrics.

Reply on news.ycombinator.com