8 ms·

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

(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-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://blog.timescale.com/blog/what-is-clickhouse-how-does-... [1] https://github.com/timescale/tsbs https://github.com/timescale/tsbs [2] https://twitter.com/p_chhetri/status/1455216425807745025 https://twitter.com/p_chhetri/status/1455216425807745025 [3] https://www.timescale.com/careers https://www.timescale.com/careers

PeterZaitsev 5y ago
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
- akulkarni 5y ago
  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.
dreyfan 5y ago
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.
- preetamjinka 5y ago
  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-requirements/ https://www.xaprb.com/blog/2014/06/08/time-series-database-r...
  - dreyfan 5y ago
    That's a really helpful post, thank you
- Wonnk13 5y ago
  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 :)
  - pvorb 5y ago
    But doesn't TimescaleDB maintain all the guarantees of ACID?
    - sarlalian 5y ago
      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.
zepearl 5y ago
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
mrits 5y ago
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.
dd444fgdfg 5y ago
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.
- sarlalian 5y ago
  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/ 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.

PeterZaitsev 5y ago

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

akulkarni 5y ago
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.

dreyfan 5y ago

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.

preetamjinka 5y ago
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-requirements/ https://www.xaprb.com/blog/2014/06/08/time-series-database-r...
- dreyfan 5y ago
  That's a really helpful post, thank you
Wonnk13 5y ago
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 :)
- pvorb 5y ago
  But doesn't TimescaleDB maintain all the guarantees of ACID?
  - sarlalian 5y ago
    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.

zepearl 5y ago

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

mrits 5y ago

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.

dd444fgdfg 5y ago

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.

sarlalian 5y ago
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/ 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.