[HN Gopher] TimescaleDB vs ClickHouse
___________________________________________________________________
TimescaleDB vs ClickHouse
Author : fartooreal
Score : 172 points
Date : 2021-11-03 16:04 UTC (6 hours ago)
(HTM) web link (pradeepchhetri.xyz)
(TXT) w3m dump (pradeepchhetri.xyz)
| clickok wrote:
| I see a lot of really divergent results with these time series
| database benchmarking posts. Timescale's open source benchmark
| suite[0] is a great contribution towards making different
| software comparable, but it seems like the tasks/metrics heavily
| favor TimescaleDB.
|
| This article has Clickhouse more-or-less spanking TimescaleDB,
| but the blog post it references[1] is basically the reverse. Are
| the use cases just that different?
|
| -----
|
| 0. https://github.com/timescale/tsbs
|
| 1. https://blog.timescale.com/blog/what-is-clickhouse-how-
| does-...
| zepolen wrote:
| As someone who has used both in production environments under
| various workloads, I can, without a doubt, tell you that
| Clickhouse spanks the crap out of TimescaleDB.
|
| The _only_ use case where TimescaleDB is more useful is the
| ability to mutating /deleting single rows but even there,
| Clickhouse offers some workarounds at the expense of a little
| extra storage until a compaction is run similar to VACUUM.
|
| Clickhouse is to TimescaleDB what Nginx was to Apache.
| PeterZaitsev wrote:
| I think both Clickhouse and TimeScaleDB are great systems
| with different design goals and approaches. Specifically I
| think Clickhouse is much better suited to "Event Logs" than
| "Metrics" storage (Clickhouse Inspired VM does well in this
| regard)
|
| I would just encourage all vendors to be more humble
| positioning their benchmarks. In my practice production
| behaviors for better or worse rarely resemble benchmark
| results
| eatonphil wrote:
| What is "Clickhouse Inspired VM"?
| PeterZaitsev wrote:
| Hm. Not sure why My previous response is marked as dead,
| I guess VM is swear word It refers to V-I-C _T_ O-R-I-A
| Metrics
| PeterZaitsev wrote:
| VictoriaMetrics https://victoriametrics.com/
| csdvrx wrote:
| > I can, without a doubt, tell you that Clickhouse spanks the
| crap out of TimescaleDB.
|
| Same. I'm ready to believe my experience is not
| representative, but I've rarely heard something different
| after talking to people who've seriously evaluated both.
|
| > Clickhouse is to TimescaleDB what Nginx was to Apache.
|
| Perfect comparison. Except I don't remember Apache cooking
| some tests to pretend they are faster than nginx, or
| astroturfing communities :)
|
| Different tools serve different purposes, simple as that.
|
| If TimescaleDB or Apache does the job for you, stick with
| them.
|
| When you will want to scale / increase performance or just
| rewrite, chose the better option of the day.
|
| In 2021, Clickhouse should be a recommended default, like
| nginx.
| xbar wrote:
| It sounds like ClickHouse is the default OLAP choice and
| TimeScaleDB is the time-series workload choice.
|
| Does anyone have a TimeScaleDB implementation that they love for
| time-series workloads that they are so happy with that they don't
| miss the non-timescale benefits of ClickHouse?
| nojito wrote:
| The biggest issue with comparisons and "benchmarks" is that
| almost no one uses clickhouse like this.
|
| Good example is what Uber is doing
|
| https://eng.uber.com/logging/
|
| Or alinity's great materialized view tutorials.
|
| Clickhouse is unmatched with these workflows.
| neonate wrote:
| http://web.archive.org/web/20211101212146/https://pradeepchh...
| qaq wrote:
| Things are getting really competitive in this space
| akulkarni wrote:
| (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
| dreyfan wrote:
| 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.
| Epa095 wrote:
| 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".
| Wonnk13 wrote:
| 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 wrote:
| But doesn't TimescaleDB maintain all the guarantees of
| ACID?
| jandrewrogers wrote:
| 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.
| preetamjinka wrote:
| 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...
| dreyfan wrote:
| That's a really helpful post, thank you
| PeterZaitsev wrote:
| 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
| [deleted]
| polskibus wrote:
| I remember reading that Clickhouse is quite bad at joins, which
| can be important if you have to build a snowflake schema. Is that
| still true? Is this something TimescaleDB would be better at?
| bvrmn wrote:
| If you data to join looks like not very huge dictionaries[1]
| (locations, types, etc) then ClickHouse can show amazing
| speeds. I had no any problems with a speed of usual joins
| though.
|
| [1] https://clickhouse.com/docs/en/sql-reference/dictionaries/
| PeterZaitsev wrote:
| In this case PostgreSQL may be able to come to the rescue :)
|
| There is Clickhouse FDW for PostgreSQL which in some cases can
| provide great speed with full join support
|
| https://github.com/adjust/clickhouse_fdw
| Shicholas wrote:
| yes, to take this a bit further, I love the idea that no
| matter what db is best, Postgres can be the starting point
| for all queries.
| monstrado wrote:
| This has been my experience with ClickHouse as well...that is,
| you can basically close your eyes while writing the schema and
| still maintain to get extremely impressive performance.
|
| That being said, ClickHouse also has a ton of clever levers you
| can pull to squeeze out better performance and compression which
| aren't used by default, such as using Delta/DoubleDelta CODECs
| with LZ4/ZSTD compression, etc. Not to mention, MATERIALIZED
| VIEWs and/or the relatively newer feature MergeTree
| Projections[1]
|
| [1] https://clickhouse.com/docs/en/engines/table-
| engines/mergetr...
| mr__anderson wrote:
| Clickhouse has done a performance benchmark with a much more
| bigger dataset and they have published the results in their
| website at [1] https://clickhouse.com/benchmark/dbms
| nemothekid wrote:
| 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.
| monstrado wrote:
| I've had a really positive experience using ClickHouse as an
| InfluxDB replacement. Initially I used the BUFFERED table type
| to overcome the "tiny inserts" problem, but ultimately just
| batch up writes in my custom line-protocol TCP server which
| translates line-protocol to JDBC inserts (RowBinary).
|
| Last time I checked I have a few hundred billion rows in the
| table with a significant compression ratio (not sure off hand).
| Most importantly, the table is ordered efficiently enough to
| allow me to query years of metrics (Grafana plugin) at
| millisecond speed.
|
| Side note, I recall ClickHouse developers mentioning they are
| currently working on an implementation change which will allow
| many tiny inserts to be much more performant and realistic to
| use in the real-world.
|
| Hope this helps!
| sin7 wrote:
| 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 installed Ubuntu.
|
| In my case the data arrived in CSVs with around 20k skus. Had
| they arrived a couple at a time, I could have created a CSV and
| written to ClickHouse later or used any of the other storage
| methods available in ClickHouse.
| monstrado wrote:
| While I really enjoyed this read, it'd be nice to see benchmarks
| which also measure Queries-Per-Second under a highly concurrent
| workload. I've been using ClickHouse to serve live analytics and
| this was something that I was most interested in.
|
| Again, though...great writeup!
| csdvrx wrote:
| Key quote from the article:
|
| > Overall, although some TimescaleDB queries became faster by
| enabling compression but many others became bit slower probably
| due to decompression overhead. This may be the reason why
| TimescaleDB disable compression by default
|
| This matches my experience: ClickHouse is generally faster, and a
| better solution for time series (more robust, more mature, ...)
| unless you have a highly specific set of constrains (ex: must be
| able to delete individual records, ...) and sacrificing
| performance for them is an acceptable tradeoff.
|
| I have no doubt that, as usual, akulkarni will make a good PR job
| / community outreach to explain why, numbers and experience be
| damned, TimescaleDB is better!
|
| But I suggest interested readers check the history of previous
| "creative engineering" around tests that has been done to make
| TimescaleDB come out ahead:
| https://news.ycombinator.com/item?id=28945903
|
| In 99% of the case, ClickHouse is the right choice, especially if
| you care about the license not adding too many restrictions.
| ensignavenger wrote:
| >>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 reasonable trade-off for
| keeping my stack Open Source and avoiding vendor lock-in.
| Croftengea wrote:
| Closed source? https://github.com/timescale/timescaledb/tree/
| master/tsl/src...
| gfody wrote:
| if you're going to compare these two you really ought to get
| into their materialized views where the real-world performance
| comes from, and ideally dive into their respective limitations
| akulkarni wrote:
| I have no doubt that, as usual, akulkarni will make a good PR
| job / community outreach to explain why, numbers and experience
| be damned, TimescaleDB is better!
|
| I don't like responding to bullies and people who enter
| dialogues without good intentions.
|
| But since this is a public forum, I'll answer your comment:
|
| In general: ClickHouse is better than TimescaleDB for OLAP.
| TimescaleDB is better for time-series. If you don't believe me,
| that's fine! Each workload is different and you should test it
| yourself.
|
| p.s. Let's keep HackerNews a more positive place. Negative
| comments are unnecessary, not productive, and honestly just
| make the author look immature.
| dexwiz wrote:
| Honestly, you sound like the bully here hiding behind the
| overly positive language of the day in order to insult the
| character of your opponent.
| staticassertion wrote:
| OP made accusations, response was leveled and asked for
| positivity.
| csdvrx wrote:
| 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
| don't believe me.
|
| But maybe not being 100% positive and supportive is no
| longer acceptable in 2021? Or maybe it's the complexity
| of the issues discussed?
|
| So let's give a simpler message: as rkwasni said it best
| just yesterday: "It's really quite easy, if you don't
| need DELETE ClickHouse wins every benchmark"
| https://news.ycombinator.com/threads?id=rkwasny
|
| It's simple as that: if you need deletion, consider
| TimescaleDB.
|
| For every other conceivable scenario, ClickHouse is
| likely to come ahead, unless you are doing something very
| very wrong with it: a virtualization example would be
| splitting cores across VM with no respect of their shared
| cache.
|
| When people talk about doing a millions of tiny inserts,
| it's a bit like that: a misconfiguration. And that's not
| how it work in the real world: even with plain Postgres,
| you often use a middle layer to avoid resource issues
| (increasing max_connections has a cost, that's why pgpool
| exist!), either directly in your app, or by putting some
| kind of buffer in front of the real table.
|
| ClickHouse has such features, to automatically handle the
| flushing to the real table:
| https://clickhouse.com/docs/en/engines/table-
| engines/special...
|
| I have spend some serious time with both, think of me
| what you may, but the CEO of TimescaleDB saying
| TimescaleDB performance can withstand the comparison with
| ClickHouse is like Intel marketing department saying
| Intel CPUs can withstand the comparison with AMD: unless
| you cook the tests with some highly specific workloads
| (say with lots of simd/AVX512 stuff, monocore...) to be
| non representative of the most common scenarios, you're
| not being honest.
|
| I believe such thinly veiled dishonesty is a much larger
| problem than a perceived positivity.
| qaq wrote:
| "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
| rbranson wrote:
| This outcome should also be entirely unsurprising and
| should pass people's basic sniff tests as Timescale works
| within the existing, mature architecture of PostgreSQL,
| where-as ClickHouse is a greenfield single-purpose
| system. Software makes trade-offs.
| ishikawa wrote:
| I'm totally in favor for more positivity. It is very easy to
| criticize something when you don't know what is happening on
| the other side. HN is a place for Hackers to discuss facts
| and not to imply what they think others think. If two
| comparisons differ, there might be several reasons as lack of
| trials on both sides or lack of a common ground for
| comparison but a lot here are doing their best to make lives
| of developers easier.
| Selva51 wrote:
| I have similar experience with ClickHouse, it just works
| Havoc wrote:
| What's the deal with the combatative database posts lately?
| PeterZaitsev wrote:
| Wait was not TimescaleDB completely leave Clickhouse in the dust
| both in terms of query performance and compression ?
|
| https://blog.timescale.com/blog/what-is-clickhouse-how-does-...
|
| I think this series of posts confirms the first law of
| Benchmarketing - for any system one can come up with "unbiased"
| benchmark which confirms its superiority
| radiospiel wrote:
| The timescale example speaks of 110 mio rows, the blogpost here
| only has 11 mio rows. And thta is probably only a start...
| pradeepchhetri wrote:
| I selected only 11M rows for this blog because I used the
| dataset linked in TimescaleDB docs[0]. The dataset linked in
| CH docs has 1.2B rows[1]. The goal was to make comparison on
| dataset which both of the databases agrees upon.
|
| [0] https://docs.timescale.com/timescaledb/latest/tutorials/n
| yc-...
|
| [1] https://clickhouse.com/docs/en/getting-started/example-
| datas...
| eatonphil wrote:
| Calling this "benchmarketing" sounds like you're saying the
| entire thing is disreputable which doesn't seem right. This
| blog post didn't remotely come off as shilling to me. The
| author does not (seem to) work for either company. They gave it
| a shot and shared a result. Whether or not it's a good
| benchmark or representative for your (anyone's) use case is
| debatable.
___________________________________________________________________
(page generated 2021-11-03 23:00 UTC)