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

51–60 of 76 posts

Re: TimescaleDB vs ClickHouse

#51

Post by zepearl »

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 read [0] when it was originally posted here but it didn't convince me.

The article mentioned flaws of Clickhouse which in my opinion in the context of a TSDB are irrelevant (e.g. "no transactions", "inability to modify data at a high rate", etc...). I'm saying this because in my mind I associate TSDBs to "server metrics collection", therefore it's no big deal even if some/many datapoints are lost, there is usually no need to modify that data, and so on. But I might be wrong, maybe the usecases that you have in your mind are different (e.g. transactional accounting data?).

About deleting data: tables that host timeseries data in Clickhouse are usually partitioned by the fraction (day/month/year) that has to be deleted later => dropping one or multiple such partitions is easy & fast & extremely light on the system (it just gets rid of the underlying files and directories).

The article didn't directly show SQLs about how the tables were defined nor about how the tests were performed, you linked your benchmark suite [1] but I honestly don't want to dig into that as it seems to be complex => to be honest it sounds like something engineered to be better than your antagonist (even if maybe it's not, dunno).

In general Clickhouse has many knobs & levers that can be changed/tweaked which can backfire if not set appropriately, I personally think that whoever uses Clickhouse MUST understand it (I did not at the beginning => got totally screwed up), but at the same time those many knobs & levers provide a lot of flexibility. Btw. indirectly they seem like a "filter" to ensure that only people that are able to use that DB will end up using it :P

So, summarized, I raised my eyebrows a couple of times while reading [0]. E.g. Clickhouse does "merges" in the background, and they can queue up (depending on a lot of stuff), and all that can stress a lot the disks & CPU, so I have no clue what was going on when you got your 156% performance advantage against CH. Maybe you're right, maybe you're not, I just don't know, so I didn't trust that article nor I do now.

Maybe you could be right if you would point to the "reliability" of your DB? E.g. because of the "merges" that are triggered at "unknown" intervals by Clickhouse in the background which can in turn create hotspots of CPU&disk on the host(s) and therefore have negative repercussions on many insert/query-ops, your TimescaleDB could definitely have an advantage here (if it doesn't perform deferred maintenance like CH does), but if that's true then in my opinion that was lost in the article [0].

Cheers

Re: TimescaleDB vs ClickHouse

#52

Post by ensignavenger »

Croftengea wrote: post #47
ensignavenger wrote: post #41

Earlier quoted context omitted.

>>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...

Right- did you look at any of the source files in that dir? They all have a header that says they are under the "TimeScale License" and if you look it up, you see that the TimeScale license is a proprietary, not Open Source, but rather Source Available license.

Here you can see a chart outlining all the features that are proprietary and which are Open Source in the Open-Core TimeScale DB- https://docs.timescale.com/timescaledb/latest/timescaledb-ed...

Re: TimescaleDB vs ClickHouse

#53

Post by nnx »

Not sure it would change results dramatically but the table schemas do not seem fair.

TimescaleDB schema in this benchmark uses NUMERIC (variable size, exact precision) versus Float32 or Float64 for Clickhouse schema.

Would be interesting to see the results with TimescaleDB schema updated to more fair REAL (Timescale/Postgres's float32 equivalent) and DOUBLE (float64) columns as per Clickhouse's schema.

Re: TimescaleDB vs ClickHouse

#54

Post by pradeepchhetri »

nnx wrote: post #53

Not sure it would change results dramatically but the table schemas do not seem fair. TimescaleDB schema in this benchmark uses NUMERIC (variable size, exact precision) versus Float32 or Float64 for Clickhouse schema. Would be interesting to see the results with TimescaleDB schema updated to more fair REAL (Timescale/Postgres's float32 equivalent) and DOUBLE (float64) columns as per Clickhouse's schema.

That's a valid point. I am also not sure, so will definitely run everything with float32/float64 datatype as followup and update the blog.

Re: TimescaleDB vs ClickHouse

#55

Post by shaklee3 »

qaq wrote: post #38
csdvrx wrote: post #33

Earlier quoted context omitted.

Sometimes, after trying to engage positively and giving the benefit of doubt, I start to notice some disturbing things. When that happens, I speak my mind, and escalate progressively depending on how trustworthy I believe the person I'm talking to is. Here, I provided a link to the previous discussion, because personally, I do not appreciate being mislead. I encourage you to check the technical details there if you d…

"When people talk about doing a millions of tiny inserts" from CH update it sounds that support for this use case has landed or is about to land

it already does with buffered tables

Re: TimescaleDB vs ClickHouse

#56

Post by qaq »

shaklee3 wrote: post #55
qaq wrote: post #38

Earlier quoted context omitted.

"When people talk about doing a millions of tiny inserts" from CH update it sounds that support for this use case has landed or is about to land

it already does with buffered tables

21.11 has asynchronous inserts (no need for buffered tables or kafka)

Re: TimescaleDB vs ClickHouse

#57

Post by mrits »

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 used TimescaleDB in a project and it worked really well. I'd recommend making it more complicated. It only took a few hours to get a working prototype going that AFAIK is still being run in production.

Re: TimescaleDB vs ClickHouse

#58

Post by dd444fgdfg »

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 think you have a marketing problem, not a technical one.

People are cross-shopping Clickhouse/TimescaleDB rightly or wrongly, and it's not clear to community when they should use which. What overlaps on the Venn-diagram and what doesn't, and when they do why would I go one way or the other.

You have to do a better job of showing how you're solving customer problems. Benchmarks are next to useless, an unsolvable problem, I wouldn't waste time on it. If customers are succeeding on your platform, you'll succeed.

Re: TimescaleDB vs ClickHouse

#59

Post by jasfi »

sin7 wrote: post #28
nemothekid wrote: post #26

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.

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 installe…

What was your opinion of Postgres vs the others?

Re: TimescaleDB vs ClickHouse

#60

Post by sarlalian »

dd444fgdfg wrote: post #58
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 think you have a marketing problem, not a technical one. People are cross-shopping Clickhouse/TimescaleDB rightly or wrongly, and it's not clear to community when they should use which. What overlaps on the Venn-diagram and what doesn't, and when they do why would I go one way or the other. You have to do a better job of showing how you're solving customer problems. Benchmarks are next to useless, an unsolvable pro…

While I'm not a current customer of Timescale, I do use the open source version of Timescale extensively, so I feel like I can summarize some of the benefits of Timescale over other TSDB's. The company is a mid size, with awkward data 4+PB unstructured data, with our Postgres cluster hosting about 20 TB of data.

The main advantage from my perspective, is that you can query across data business data and time series data with all the advantages that Postgres has. Time series data while useful on its own, becomes incredibly powerful when it can be combined with your business and production data.

A great example is our outbound network data monitoring. We use pmacct http://www.pmacct.net/ to send network flows to Postgres from our firewall, host inventory data in Postgres, and a foreign data wrapper around our LDAP data to determine user / host assignment, and from that we can correlate every data flow to the user who is assigned to the host that generated that particular flow. This makes for some pretty powerful security reporting. Outside of that, we use Timescale's hypertables in a number of places that aren't explicitly timeseries data, like syslog data, web server logs, etc. This allows for some pretty amazing reporting on log data that is timeboxed, like "give me all the 500 errors from our HTTP log that have an ip address in Finland (did I mention that we load GeoIP data into Postgres every night) in the last 3.5 hours.

Timescale is excellent on its own, and honestly competitive with other TSDB's on its own. Having access to the full Postgres ecosystem with your timeseries data makes Timescale way ahead of everyone else. My story might change when I hit the limits of what a single Postgres host can ingest, but I'm not even close to that scale yet.

Other advantages of Timescale, is having access to real SQL, you don't have to learn a new domain specific query language, you can just use SQL. This admittedly can be a double edge sword. SQL is more complicated than PromQL / InfluxQL, however that comes with quite a lot of extra capability, and the ability to transfer that knowledge into other domains.

I personally really like Timescale, and feel that regardless of anyones benchmarks, no matter how well thought out or not, the advantages outweigh the disadvantages by a pretty large margin.

Post reply on HN