Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

TimescaleDB vs ClickHouse

pradeepchhetri.xyz

Post reply on HN
Print view View original

41–50 of 76 posts

Re: TimescaleDB vs ClickHouse

#41

Post by ensignavenger »

csdvrx wrote: post #2

Key quote from the article: > Overall, although some TimescaleDB queries became faster by enabling compression but many others became bit slower probably due to decompression overhead. This may be the reason why TimescaleDB disable compression by default This matches my experience: ClickHouse is generally faster, and a better solution for time series (more robust, more mature, ...) unless you have a highly specific s…

>>especially if you care about the license not adding too many restrictions.

Compression is one of the many closed-source/proprietary features in Timescale. Timescale is a great idea, as its just a postgres extension, so no need to add another database, but with such an important feature being proprietary, I end up looking at the fully Open Source ClickHouse and I see the operational overhead of another DB as reasonable trade-off for keeping my stack Open Source and avoiding vendor lock-in.

Re: TimescaleDB vs ClickHouse

#42

Post by bvrmn »

polskibus wrote: post #30

I remember reading that Clickhouse is quite bad at joins, which can be important if you have to build a snowflake schema. Is that still true? Is this something TimescaleDB would be better at?

If you data to join looks like not very huge dictionaries[1] (locations, types, etc) then ClickHouse can show amazing speeds. I had no any problems with a speed of usual joins though.

[1] https://clickhouse.com/docs/en/sql-reference/dictionaries/

Re: TimescaleDB vs ClickHouse

#44

Post by pvorb »

Wonnk13 wrote: post #37
dreyfan wrote: post #36

Earlier quoted context omitted.

I honestly don't know what time-series databases do that's particularly unique. I've worked databases for 20+ years and a date or datetime has always been an integral part of the dataset and thus everything to me is time-series. I always seem them compared against key-value stores or document-oriented databases or NoSQL platforms, which more speaks to people not knowing how to use the correct datastore in the first p…

Perhaps I'm wrong, but "timeseries" databases are typically some combination of LSM style append only logs and eventual consistency. In an ACID relational database, i'm not sure you can simultaneously write and read millions of rows per second? If you can I'd love to learn something new :)

But doesn't TimescaleDB maintain all the guarantees of ACID?

Re: TimescaleDB vs ClickHouse

#45

Post by preetamjinka »

dreyfan wrote: post #36
akulkarni wrote: post #18

(Timescale co-founder) I'll answer this here with a similar response that I gave Pradeep (the author) via Twitter. I think ClickHouse is a great technology. It totally beats TimescaleDB for OLAP queries. I'll be the first to admit that. What our (100+ hour, 3 month analysis) benchmark showed is that for _time-series workloads_, TimescaleDB fared better. [0] Pradeep's analysis - while earnest - is essentially comparin…

I honestly don't know what time-series databases do that's particularly unique. I've worked databases for 20+ years and a date or datetime has always been an integral part of the dataset and thus everything to me is time-series. I always seem them compared against key-value stores or document-oriented databases or NoSQL platforms, which more speaks to people not knowing how to use the correct datastore in the first p…

It might help to think about time-series databases from the requirements they're addressing. "Time-Series Database Requirements" [0] is a good summary of the problem space.

[0] https://www.xaprb.com/blog/2014/06/08/time-series-database-r...

Re: TimescaleDB vs ClickHouse

#46

Post by dreyfan »

preetamjinka wrote: post #45
dreyfan wrote: post #36

Earlier quoted context omitted.

I honestly don't know what time-series databases do that's particularly unique. I've worked databases for 20+ years and a date or datetime has always been an integral part of the dataset and thus everything to me is time-series. I always seem them compared against key-value stores or document-oriented databases or NoSQL platforms, which more speaks to people not knowing how to use the correct datastore in the first p…

It might help to think about time-series databases from the requirements they're addressing. "Time-Series Database Requirements" [0] is a good summary of the problem space. [0] https://www.xaprb.com/blog/2014/06/08/time-series-database-r...

That's a really helpful post, thank you

Re: TimescaleDB vs ClickHouse

#47

Post by Croftengea »

ensignavenger wrote: post #41
csdvrx wrote: post #2

Key quote from the article: > Overall, although some TimescaleDB queries became faster by enabling compression but many others became bit slower probably due to decompression overhead. This may be the reason why TimescaleDB disable compression by default This matches my experience: ClickHouse is generally faster, and a better solution for time series (more robust, more mature, ...) unless you have a highly specific s…

>>especially if you care about the license not adding too many restrictions. Compression is one of the many closed-source/proprietary features in Timescale. Timescale is a great idea, as its just a postgres extension, so no need to add another database, but with such an important feature being proprietary, I end up looking at the fully Open Source ClickHouse and I see the operational overhead of another DB as reasona…

Closed source? https://github.com/timescale/timescaledb/tree/master/tsl/src...

Re: TimescaleDB vs ClickHouse

#48

Post by jandrewrogers »

Wonnk13 wrote: post #37
dreyfan wrote: post #36

Earlier quoted context omitted.

I honestly don't know what time-series databases do that's particularly unique. I've worked databases for 20+ years and a date or datetime has always been an integral part of the dataset and thus everything to me is time-series. I always seem them compared against key-value stores or document-oriented databases or NoSQL platforms, which more speaks to people not knowing how to use the correct datastore in the first p…

Perhaps I'm wrong, but "timeseries" databases are typically some combination of LSM style append only logs and eventual consistency. In an ACID relational database, i'm not sure you can simultaneously write and read millions of rows per second? If you can I'd love to learn something new :)

You can write several million rows per second through indexing and storage while reading consistent views but it is not trivial. It requires a pretty sophisticated database kernel design even on modern hardware. LSM-style is not a good choice if you require these write rates. Time-series data models are relatively simple to scale writes for as such things go.

I would not want to try this on a traditional relational database kernel, they are not designed for workloads that look like this. They optimize their tradeoffs for slower and more complicated transactions.

Re: TimescaleDB vs ClickHouse

#50

Post by zepearl »

dreyfan wrote: post #36
akulkarni wrote: post #18

(Timescale co-founder) I'll answer this here with a similar response that I gave Pradeep (the author) via Twitter. I think ClickHouse is a great technology. It totally beats TimescaleDB for OLAP queries. I'll be the first to admit that. What our (100+ hour, 3 month analysis) benchmark showed is that for _time-series workloads_, TimescaleDB fared better. [0] Pradeep's analysis - while earnest - is essentially comparin…

I honestly don't know what time-series databases do that's particularly unique. I've worked databases for 20+ years and a date or datetime has always been an integral part of the dataset and thus everything to me is time-series. I always seem them compared against key-value stores or document-oriented databases or NoSQL platforms, which more speaks to people not knowing how to use the correct datastore in the first p…

> I honestly don't know what time-series databases do that's particularly unique.

I understand the "spirit" of your comment and I agree in general.

However something unique that comes to my mind about some TSDBs is automated aggregation of data points (using min/max/avg/whatever functions) into something less granular after that a metric's specific time becomes older than X.

By using a TSDB for example you'll be able to look at datapoints with a max resolution of 10 seconds for metrics collected during the past 7 days, but after that their max resolution will be (aggregated into) e.g. 60-seconds intervals, and so on.

I think that the theory behind it is that you're probably interested at the details for recent stuff, but the older the stuff gets the more you just want to look at the general trend without caring about the details. I can agree with that. In the end by doing these kinds of aggregations in theory everything should be faster & should use less storage (as for older data there are fewer data points).

This is how "Graphite/Carbon" ( https://graphite.readthedocs.io/en/latest/faq.html ) works.

I did use Graphite/Carbon for some years, but I didn't like a lot its architecture and had some performance problems => I've replaced it with Clickhouse (I'm not doing any kind of data aggregation) and that's using less space and is quicker (respectively it uses a lot less CPU & I/O) :)

Post reply on HN