Comment by akulkarni | Hacker News Reader

Comment by akulkarni | Hacker News Reader

(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 comparing OLAP style queries using a dataset that is not very representative of time-series workloads. Which is why the time-series benchmark suite (TSBS) [1] exists (which we did not create, although we now maintain it). I've asked Pradeep to compare using the TSBS - and he said he'd look into it. [2]

As a developer, I'm very wary of technologies that claim to be better at everything - especially those who hide their weaknesses. We don't do that at TimescaleDB. For those who read our benchmark closely, we clearly show where ClickHouse beats TimescaleDB, and where TimescaleDB does better. And - despite what many commenters on here may want you to think - we heap loads of praise on ClickHouse.

As a reader of HackerNews, I'm also tired of all the negativity that's developing on this site. People who bully. People who default to accusing others of dishonesty instead of trying to have a meaningful dialogue and reach mutual understanding. People who enter debates wanting to be right, versus wanting to identify the right answer. Disappointingly, this includes some visible influencers whom I personally know. We should all strive to do better, to assume positive intent, and have productive dialogues.

(This is why one of our values at TimescaleDB is "Assume Positive Intent." [3] I think Hacker News - and the world in general - would be a much better, happier, healthier place if we all just did that.)

[0] https://blog.timescale.com/blog/what-is-clickhouse-how-does-...

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

[2] https://twitter.com/p_chhetri/status/1455216425807745025

[3] https://www.timescale.com/careers

I think TimescaleDB is amazing piece of technology but I think you're making arguments much broader than they can be made based on the facts.

The results which TimescaleDB showed to me seems to show what it is better than ClickHouse in TSBS benchmark (or particular configuration) not for Time Series workloads in general.

In my experience "Time Series" workloads can be defined very broadly (by casual user) and querying log of events can be often seen as such

If you would like to discuss facts: We have witnessed 100,000s+ of different time-series workloads over the past 4.5 years, and the patterns they share may surprise you. There is much more similarity than you may think - similarities that have been captured in the TSBS (and described by other TimescaleDB users in this discussion thread).

So while we can debate on an academic level what a "time-series" workload is, if we were to look at the facts we will find that the answer is far more specified that you may think.

Also, Peter, I wonder if you should be more forthcoming with your ClickHouse affiliation.

Everyone reading this thread is aware of my bias because I clearly state my TimescaleDB affiliation. But I didn't realize until very recently (when someone pointed this out to me) that you are affiliated with ClickHouse - eg perhaps as an investor or even founder in Altinity?

It is best practice on Hacker News to be forthcoming with affiliations so that readers can make their own decisions on how to correct for any natural biases made by commenters.

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 place than any particular feature of a TSDB.

Even looking at your benchmark queries, I'm confused what value it provides over a standard OLTP or OLAP setup.

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.

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.

The timeseries databases I have used are good (and fast) at answering queries like "mean value of sensor_1 for every 10 min bucket". It can answer this fast. It can handle that some buckets have 1000 points in them, some have 0 or 1. It can calculate the moving average, again correctly with possible missing/unevenly spaced values. It can calculate the rate of change (the deriviative) fast.

Often there are other time-related stuff in there as well, but I think the vast majority of use is fast calculation of "mean/max/first value of sensor(s) for X-second buckets".

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

> 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) :)

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.

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.

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.

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.

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

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

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.

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

Reply on news.ycombinator.com