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

61–70 of 184 posts

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

#61

Post by akulkarni »

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 better (which in turn makes TimescaleDB better)

In fact, IIRC Altinity used and contributed ClickHouse to the TSBS [1], which is also what this newer benchmark uses as well

(Disclaimer: TimescaleDB co-founder)

[0] https://blog.timescale.com/blog/building-columnar-compressio...

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

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

#62

Post by ryanbooz »

GordonS wrote: post #53
ryanbooz wrote: post #40

Earlier quoted context omitted.

(Post author) Howdy! We provided all of those details in the post and you're welcome to join us next week when we live-stream our setup and test! https://blog.timescale.com/blog/what-is-clickhouse-how-does-...

I was responding to @andrejserafim, asking about their scenario, not the article.

Gotcha! My apologies for not seeing the thread nature. HN threads get me sometimes. :-)

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

#64

Post by encoderer »

claytonjy wrote: post #50
encoderer wrote: post #33

Earlier quoted context omitted.

> I wouldn't let my user-facing app write to Clickhouse I’ve been thinking of doing exactly that. What are your concerns?

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.

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

#65

Post by dreyfan »

encoderer wrote: post #33
claytonjy wrote: post #15

Earlier quoted context omitted.

Was this for your primary source-of-truth, or more of a downstream data warehouse, or something else? I'm struggling to imagine a case where these are the two things being considered; Timescale is the obvious choice for a primary database, Clickhouse the obvious choice for a warehouse. I wouldn't let my user-facing app write to Clickhouse, and while I could potentially get away with a read-only Timescale replica for…

> I wouldn't let my user-facing app write to Clickhouse I’ve been thinking of doing exactly that. What are your concerns?

https://blog.cloudflare.com/http-analytics-for-6m-requests-p...

has some good thoughts. The main thing you'll likely need is some sort of a buffer layer so you can do bulk inserts. Do not write a high-volume of single-row inserts into Clickhouse.

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

#66

Post by dominotw »

claytonjy wrote: post #15
andrejserafim wrote: post #4

Our anecdata: we store telemetry per thing. After loading a month worth of data - timescaldb as hosted by their cloud ran a difference aggregation in seconds. Clickhouse routinely did it in 20 millis. Simple avg, etc were better, but always clickhouse was an order of magnitude faster than timescale. We didn't invest a whole bunch into optimization other than trying some indexing strategies in timescaledb. So for our…

Was this for your primary source-of-truth, or more of a downstream data warehouse, or something else? I'm struggling to imagine a case where these are the two things being considered; Timescale is the obvious choice for a primary database, Clickhouse the obvious choice for a warehouse. I wouldn't let my user-facing app write to Clickhouse, and while I could potentially get away with a read-only Timescale replica for…

> Clickhouse the obvious choice for a warehouse > Clickhouse/Snowflake/Redshift.

but clickhouse is very unlike the other two. when i think of a warehouse i think star schema, data modeling ect not something that hates joins.

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

#67

Post by encoderer »

dreyfan wrote: post #65
encoderer wrote: post #33

Earlier quoted context omitted.

> I wouldn't let my user-facing app write to Clickhouse I’ve been thinking of doing exactly that. What are your concerns?

https://blog.cloudflare.com/http-analytics-for-6m-requests-p... has some good thoughts. The main thing you'll likely need is some sort of a buffer layer so you can do bulk inserts. Do not write a high-volume of single-row inserts into Clickhouse.

Thanks for sharing the link! I’ve heard the bulk insert thing before and to be honest I’ve always thought that RDBMSs don’t love single row inserts either. Seems clickhouse takes that to a new level.

In our case we are using sqs and usually insert 20-100 rows into the db at a time so I’m going to benchmark how that does in clickhouse.

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

#68

Post by akulkarni »

qaq wrote: post #55

ClickHouse shines at scales that timescale has no hope of ever supporting. Hence the choice of workloads in the test. Cloudflare was ingesting 6,000,000 rows per second into 36 node (dual E5-2630) ClickHouse cluster (2018) was something like 20PB of data per year.

(TimescaleDB co-founder)

6,000,000 rows inserted per second is great! And if you need that for your workload, then you probably should choose ClickHouse over TimescaleDB (well at least, for now ;-)

The reason we don't include that in the benchmark is that most developers _don't need_ 6,000,000 rows inserted per second.

And also - that performance doesn't come for free, but requires giving up a lot of things that most developers may need: e.g., no transactions, immutable tables (can't easily update / delete data), SQL-like but not quite SQL query language, inefficient joins, inefficient for point queries retrieving single rows by their keys, etc. (We go into much more detail in the blog post.)

So it comes down to the fundamental analogy used in the post: Do you need a car (versatility) or a bull dozer (narrow specialization)?

If the answer is that you need to support 6,000,000 rows inserted per second, then by all means, choose the bull dozer.

> ClickHouse shines at scales that timescale has no hope of ever supporting.

I'm not sure if this was a throwaway line, or if it was the result of a detailed analysis of TimescaleDB's architecture, but if you don't mind, I'll share this: with TimescaleDB multi-node [0] we are getting close to that performance, and the product keeps getting better.

[0] https://blog.timescale.com/blog/building-a-distributed-time-...

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

#69

Post by pradeepchhetri »

ryanbooz wrote: post #48
pradeepchhetri wrote: post #23

How can i replicate the results of the benchmarks ? I am interested to look at the CH table schema you used.

(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 recommend keep this even more higher around (10x of current value).

Humble Suggestion: There are many things not quite properly interpreted about CH and reading through the blog it seems like you're focusing more on areas which CH is lacking/missing. Please don't do these things.

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

#70

Post by ryanbooz »

qaq wrote: post #55

ClickHouse shines at scales that timescale has no hope of ever supporting. Hence the choice of workloads in the test. Cloudflare was ingesting 6,000,000 rows per second into 36 node (dual E5-2630) ClickHouse cluster (2018) was something like 20PB of data per year.

(post author)

Those are great, impressive numbers. We certainly don't claim to be all things to all people, but the benchmark was run using single instances mostly because that is what most other benchmarks published by others have done.

With a multi-node TimescaleDB cluster, ingest does literally scale to millions of rows/second and we have a numerous users achieving great numbers. One Fortune 100 company has a 25+ TimescaleDB cluster backing their network monitoring stack and loves it for their use case.

At some point, when we can, I'm sure we'll start to do more with multi-node benchmarking too to give some input to the conversation.

Post reply on HN