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

Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

blog.timescale.com

Post reply on HN
Print view View original

81–90 of 184 posts

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#81

Post by arunmu »

akulkarni wrote: post #61
arunmu wrote: post #54

What is the difference w.r.t the comparison done by Altinity of clickhouse with timescale ? Clickhouse performed better there for the same test. What gives ?

If you are referring to this post: https://altinity.com/blog/clickhouse-for-time-series That post was written in November 2018 - 3 years ago - when TimescaleDB was barely 1.0. A lot has changed since then: 1. TimescaleDB launched native columnar compression in 2019, which completely changed its story around storage footprint and query performance [0] 2. TimescaleDB has gotten much better 3. PostgreSQL has also gotten…

Thank you. My only nit is the way the ratio (CH/TS) is shown. What is the purpose of that ? It will show a bigger percentage for cases in which TS is better, but lower percentage for cases where CH is giving better results. From the data representation perspective, I do not thinnk that is fair.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#82

Post by PeterZaitsev »

pradeepchhetri wrote: post #69
ryanbooz wrote: post #48

Earlier quoted context omitted.

(Post author) Howdy! All of the details about our TSBS settings in the performance section of the docs. Also, we'll be streaming a sample benchmark of the two databases next Wednesday at 10AM ET/4PM CET. https://blog.timescale.com/blog/what-is-clickhouse-how-does-... twitch.tv/timescaledb

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-cl... ) 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 recomme…

This is what tend to make all vendor benchmarks "benchmarketing" - while many of us fully intend to give a fair shot to other technologies we tend to know best practices for our own software better than "competition"

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#84

Post by willvarfar »

encoderer wrote: post #64
claytonjy wrote: post #50

Earlier quoted context omitted.

I suppose it depends what you're going to let your user do, but OLAPs in general and Clickhouse in particular don't do well under row-oriented workloads, as described in the post here. I'm imagining users primarily operating on small numbers of rows and sometimes making updates to or deleting them, a worst-case scenario for Clickhouse but best-case for an OLTP like Postgres.

Ah totally. Thanks for sharing your thoughts! In my case I’m evaluating clickhouse as a source of truth for customer telemetry data. Totally agree about the OLTP limitations.

(Remember that clickhouse is not reliable. It doesn’t pretend to be.

Clickhouse is great for lots of common query workloads, but if losing your data would be a big deal then it makes a lot of sense to have your data in a reliable and backed up place (eg timescale or just s3 files or whatever) too.

Of course lots of times people chuck stuff into clickhouse and it’s fine if they lose a bit sometimes. YMMV.)

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#85

Post by ryanbooz »

pradeepchhetri wrote: post #69
ryanbooz wrote: post #48

Earlier quoted context omitted.

(Post author) Howdy! All of the details about our TSBS settings in the performance section of the docs. Also, we'll be streaming a sample benchmark of the two databases next Wednesday at 10AM ET/4PM CET. https://blog.timescale.com/blog/what-is-clickhouse-how-does-... twitch.tv/timescaledb

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-cl... ) 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 recomme…

Two quick responses:

- The code that TSBS uses was contributed by Altinity[1]. If there is a better setup, please feel free to submit a PR. As stated elsewhere, we did have a former CH engineer review and even updated ClickHouse to the newest version __yesterday__ based on his suggestion to ensure we had the best numbers. (and some queries did improve after upgrading, which are the numbers we presented)

- It seems like you read the article (great job - it was long!!), so I'm sure you understand that we were trying to answer performance and feature questions at a deeper level than almost any benchmark we've seen to date. Many just show a few graphs and walk away. We fully acknowledged that smaller batches are not recommended by CH, but something many (normally OLTP) users would probably have. It matters and nobody (that we know of) has shown those numbers before. And in our test, larger batch sizes do work well, but not to some great magnitude in this one server setup. Did 10k or 20k rows maybe go a little faster for CH? Sometimes yes, sometimes negligible. The illustration was that we literally spent months and hundreds of benchmark cycles trying to understand the nuances.

I think we're pretty clear in the post that CH is a great database for the intended cases, but it has shortcomings just like TimescaleDB does and we tried to faithfully explore each side.

[1]: https://github.com/timescale/tsbs/pull/26

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#87

Post by ryanbooz »

PeterZaitsev wrote: post #80
ryanbooz wrote: post #58

Earlier quoted context omitted.

(Post author) The two big things, which we discuss at length in the post, are: - Altinity (and others) did not enable compression in TimescaleDB (which converts data into columnar storage) and provides improvement in querying historical data because it can retrieve individual columns in compressed format similar to CH - They didn't explore different batch sizes to help understand how each database is impacted at vari…

Have you from your side followed all Clickhouse best practices? Clickhouse design in particular suggests doing ingest request approximately once per second and if you do much more than that when you use it outside of intended usage and if you need that you usually have some sort of queue between whatever produces the data and Clickhouse. Note the ingest in small batches also can significantly affect query performance

Yep - it's all detailed in the post! The question is how it compares to TimescaleDB, which is an OLTP time-series database that has a lot of other possible use cases (and extensibility). I think it's very fair to explore how smaller batches work since others haven't ever actually shown that (as far as we can see) so that users that would normally be coming from a database like PostgreSQL can understand the impact something like small batches would have.

As for ingest queueing, TSBS does not queue results. We agree, and tell most users that they should queue and batch insert in larger numbers. Not every app is designed that way and so we wanted to understand what that would look like.

But CH did amazingly well regardless of that with batches above 1k-2k and lived up to it's name as a really fast database for ingest!

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#89

Post by csdvrx »

rkwasny wrote: post #17

There is some creative engineering going here :) have a look: https://github.com/timescale/tsbs/blob/master/scripts/load/l... vs https://github.com/timescale/tsbs/blob/master/scripts/load/l...

> There is some creative engineering going here

Agreed. At a previous work, clickhouse outperformed timescale by several orders of magnitude, under about every condition.

The timescale team seems to recognize that (look for the comment about clickhouse being a bulldozer) but they seem to say timescale can be better suited.

In my experience, in about 1% of the cases, yes, timescale will be a better choice (ex: if you do very small batches of insertions, if you need to remove some datapoints) but in 99% of the usecases for a time series database, clickhouse is the right answer.

There seems to have been several improvements to timescale since 2018, with columnar storage, compression, etc. and that's good because more competition is always better.

But in 2021, clickhouse vs timescale for a timeseries is like postgres vs mongo for a regular database: unless you have special constraints [*], the "cool" solution (timescale or mongo) is the wrong one.

[*]: you may think you have a unique problem and you need unique features, but odds are, YAGNI

https://en.wikipedia.org/wiki/You_aren%27t_gonna_need_it

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#90

Post by PeterZaitsev »

ryanbooz wrote: post #79
PeterZaitsev wrote: post #74

Earlier quoted context omitted.

Good question. Was it Open Source version of TimeScaleDB compared or Source Available ?

Sure. As we shared in the blog post it was tested (like other benchmarks) on dedicated EC2 instances using the freely available Community version.

This does not answer the question - is it Open Source License or Source Available (TSL) https://www.timescale.com/legal/licenses
Post reply on HN