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

61–70 of 76 posts

Re: TimescaleDB vs ClickHouse

#61

Post by sarlalian »

pvorb wrote: post #44
Wonnk13 wrote: post #37

Earlier quoted context omitted.

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?

They do you can do some pretty good optimizations if you know what type of data is being written to a table, and with Timescale, you are converting a specific table into a Timescale hypertable, which give you different tradeoffs from a standard Posgresql table. End result is really great performance for inserts and queries while maintaining ACID guarantees.

Re: TimescaleDB vs ClickHouse

#62

Post by mehdix »

sarlalian wrote: post #60
dd444fgdfg wrote: post #58

Earlier quoted context omitted.

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

This is exactly why I'm trying to add Timescale to our infra. InfluxQL is very limited and relying solely on Flux instead of reinforcing my SQL/Postgres understanding is not an option.

Re: TimescaleDB vs ClickHouse

#63

Post by TinyCuteGorilla »

ensignavenger wrote: post #52
Croftengea wrote: post #47

Earlier quoted context omitted.

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/t…

What you can also read on that linked page is that the only thing you cannot do when using the Timescale license is basically pull an AWS move and sell TimescaleDB as a service. So when you say "closed source" and "proprietary" that's just really not a good description of TImescaleDB imo. (on the other hand you can grab the Apache version and sell it as a DBaaS etc)

Re: TimescaleDB vs ClickHouse

#65

Post by hgamaral »

I get that TimescaleDB is good for time series and the like. HOWEVER, people are actually using its extension to just improve PostgreSQL performance on OLTP workloads without having to change a big piece of tech. I have experienced it myself: just enable TSDB in a regular PG database and you can expect things to work faster, no code change required.

EDIT: wording, grammar.

Re: TimescaleDB vs ClickHouse

#66

Post by valyala »

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…

Time series databases are actually a subset of OLAP databases. The main difference between time series databases and OLTP databases is the amounts of data stored and processed. While OLTP databases can process billions of rows per node, time series databases can deal with trillions of rows per node.

The main requirements for time series databases:

- Fast data ingestion (millions of rows per second).

- Good compression for the stored data, since the amounts of time series data is usually huge (trillions of rows per node). The compression also may improve query speed, since it reduces the amounts of data that needs to be read from disk during heavy queries.

- Fast search for time series with the given labels. For instance, search for temperature measurements across all the sensors in the given country with millions of temperature sensors.

- Fast rows processing for the found time series on the given time range. Usually the number of rows to process exceeds hundreds of millions per query.

Typical OLTP databases cannot meet these requirements.

Re: TimescaleDB vs ClickHouse

#67

Post by valyala »

sarlalian wrote: post #60
dd444fgdfg wrote: post #58

Earlier quoted context omitted.

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

FYI, ClickHouse supports querying PostgreSQL data [1], so it is quite easy to mix both analytical data from ClickHouse and relational data from Postgres in a single query.

[1] https://clickhouse.com/docs/en/engines/table-engines/integra...

Re: TimescaleDB vs ClickHouse

#68

Post by ensignavenger »

TinyCuteGorilla wrote: post #63
ensignavenger wrote: post #52

Earlier quoted context omitted.

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/t…

What you can also read on that linked page is that the only thing you cannot do when using the Timescale license is basically pull an AWS move and sell TimescaleDB as a service. So when you say "closed source" and "proprietary" that's just really not a good description of TImescaleDB imo. (on the other hand you can grab the Apache version and sell it as a DBaaS etc)

>>"closed source" and "proprietary" that's just really not a good description of TimescaleDB

I suppose we can agree to disagree on this. Perhaps "Open Core, Source Available" is a term you can agree to? I think my original comment was clear that part of Timescale is Open Source, or in other words Open Core.

>> the only thing you cannot do when using the Timescale license is basically pull an AWS move and sell TimescaleDB as a service

Actually, by virtue of this, it prevents me from paying some one else to host a Timescale fork for me... this in turn is a major stumbling block to creating a viable fork if my business interests diverge from Timescales's business interests for any reason. Thus leading to Vendor Lock-in, as per my original comment.

Re: TimescaleDB vs ClickHouse

#69

Post by LoriP »

sarlalian wrote: post #60
dd444fgdfg wrote: post #58

Earlier quoted context omitted.

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

If you ever would like to share hints and tips or experiences with other Timescale users please get in touch Timescale's community manager, email in profile

Re: TimescaleDB vs ClickHouse

#70

Post by valyala »

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.

At first, let's give the definition of `time series`. This is a series of (timestamp, value) pairs ordered by timestamp. The `value` may contain arbitrary data - a floating-point value, a text, a json, a data structure with many columns, etc. Each time series is uniquely identified by its name plus an optional set of {label="value"} labels. For example, temperature{city="London",country="UK"} or log_stream{host="foobar",datacenter="abc",app="nginx"}.

ClickHouse is perfectly optimized for storing and querying of such time series, including metrics. That's true that ClickHouse isn't optimized for handling millions of tiny inserts per second. It prefers infrequent batches with big number of rows per each batch. But this isn't the real problem in practice, because:

1) ClickHouse provides Buffer table engine for frequent inserts.

2) It is easy to create a special proxy app or library for data buffering before sending it to ClickHouse.

TimescaleDB provides Promscale [1] - a service, which allows using TimescaleDB as a storage backend for Prometheus. Unfortunately, it doesn't show outstanding performance comparing to Prometheus itself and to other remote storage solutions for Prometheus. Promscale requires more disk space, disk IO, CPU and RAM according to production tests [2], [3].

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

[2] https://abiosgaming.com/press/high-cardinality-aggregations/

[3] https://valyala.medium.com/promscale-vs-victoriametrics-reso...

Full disclosure: I'm CTO at VictoriaMetrics - competing solution for TimescaleDB. VictoriaMetrics is built on top of architecture ideas from ClickHouse.

Post reply on HN