13 ms·

Our anecdata: we store telemetry per thing. After loading a month worth of data - timescaldb as hosted by their cloud ran a difference aggregation in seconds. C

Our anecdata: we store telemetry per thing. After loading a month worth of data - timescaldb as hosted by their cloud ran a difference aggregation in seconds. Clickhouse routinely did it in 20 millis. Simple avg, etc were better, but always clickhouse was an order of magnitude faster than timescale. We didn't invest a whole bunch into optimization other than trying some indexing strategies in timescaledb. So for our use case the choice is clear.

ryanbooz 5y ago
(N.B. post author) Thanks for the feedback. Without knowing your situation, one of the things we show in the blog post is that TimescaleDB compression often changes the game on those kinds of queries (data is transformed to columnar storage when you compress). You don't mention if you did that or not, but it's something we've seen/noticed in every other benchmark at this point - that folks don't enable it for the benchmark. And second point of the article is that you have lots of options for whatever works in your specific situation. But, make sure you're using the chosen database features before counting it out. :-)
- maxmcd 5y ago
  I wonder if it's worth taking a page out of the MongoDB book and enabling these kinds of benchmark altering settings by default. We certainly selected clickhouse over tailscale internally because of major performance differences in our internal testing that might have gone the other way had we "known better".
  - ryanbooz 5y ago
    Indeed. Lots of discussion over this in the last few months. There are nuances, but I think you'll see some progress in this area over the next year.
claytonjy 5y ago
Was this for your primary source-of-truth, or more of a downstream data warehouse, or something else? I'm struggling to imagine a case where these are the two things being considered; Timescale is the obvious choice for a primary database, Clickhouse the obvious choice for a warehouse. I wouldn't let my user-facing app write to Clickhouse, and while I could potentially get away with a read-only Timescale replica for internal-facing reports I would expect to eventually outgrow that and reach for Clickhouse/Snowflake/Redshift.
- encoderer 5y ago
  > I wouldn't let my user-facing app write to Clickhouse I’ve been thinking of doing exactly that. What are your concerns?
  - claytonjy 5y ago
    I suppose it depends what you're going to let your user do, but OLAPs in general and Clickhouse in particular don't do well under row-oriented workloads, as described in the post here. I'm imagining users primarily operating on small numbers of rows and sometimes making updates to or deleting them, a worst-case scenario for Clickhouse but best-case for an OLTP like Postgres.
    - encoderer 5y ago
      Ah totally. Thanks for sharing your thoughts! In my case I’m evaluating clickhouse as a source of truth for customer telemetry data. Totally agree about the OLTP limitations.
      - willvarfar 5y ago
        (Remember that clickhouse is not reliable. It doesn’t pretend to be. Clickhouse is great for lots of common query workloads, but if losing your data would be a big deal then it makes a lot of sense to have your data in a reliable and backed up place (eg timescale or just s3 files or whatever) too. Of course lots of times people chuck stuff into clickhouse and it’s fine if they lose a bit sometimes. YMMV.)
        RhodesianHunter 5y ago
        I have not found this to be the case. Like any system you need to take precautions (replicas and sharding) to ensure no data loss, but I didn't find that to be challenging. In what way have you found ClickHouse particularly risky in this way?
GordonS 5y ago
How many data points were those aggregations being computed over? How much memory does your Postgres server have, and are you using SSD storage (with associated postgres config tweaks)?
- ryanbooz 5y ago
  (Post author) Howdy! We provided all of those details in the post and you're welcome to join us next week when we live-stream our setup and test! 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/#clickhouse-vs-timescaledb https://blog.timescale.com/blog/what-is-clickhouse-how-does-...
  - GordonS 5y ago
    I was responding to @andrejserafim, asking about their scenario, not the article.
    - ryanbooz 5y ago
      Gotcha! My apologies for not seeing the thread nature. HN threads get me sometimes. :-)
ants_a 5y ago
From my experience of benchmarking these databases on scientific data (highly regular timeseries) and looking at the internals of both, these kinds types of number happen when answering the query needs crunching through many rows, but the output has few. i.e. the queries are filtering and/or aggregating a ton of input rows, that can't be excluded by indexes or queried from preaggregations. From what I can tell it comes down to execution engine differences. TimeScale, even with compressed tables, uses a row by row execution engine architecturally resembling IE6 era JS engines. ClickHouse uses a batched and vectorized execution engine utilizing SIMD. Difference is one to two orders of magnitude of throughput in terms raw number of rows per core pushed through the execution engine. Postgres/TimeScale could certainly also implement a similar model of execution, but to call it an undertaking would be an understatement considering the breadth and extensibility of features that the execution engine would need to support. To my knowledge no one is seriously working on this outside of limited capability hacks like vops or PG-Strom extensions.
- ryanbooz 5y ago
  (post author) You do a great job summarizing some of the benefits of ClickHouse we mentioned in the post, including the vectorized engine! That said, I'm not sure I'd refer to PostgreSQL/TimescaleDB engine architecture as resembling IE6 JS support. Obviously YMMV, but every release of PG and TimescaleDB bring new advancements to query optimizations for the architecture they are designed for, which was the focus of the post. I'm personally still impressed, after 20+ years of working with SQL, relational databases, when any optimization engine can use statistics to find the "best" plan among (potentially) thousands in a few ms. Maybe I'm too easily impressed. :-D
  - ants_a 5y ago
    The optimization engine is of course great (despite occasionally missing hard), but I am not referring to it. I am referring to the way that PostgreSQL executes query plans, the way rows are pulled up the execution tree, is very similar to first iterations JavaScript engines - a tree based interpreter. Picking out columns from rows and evaluating expressions used to work the same until PG11, where we got a bytecode based interpreter and a JIT for those. But so far rows are still working the same way, and it hurts pretty bad when row lookup is cheap and the rows end up either thrown away or aggregated together with basic math.

ryanbooz 5y ago

(N.B. post author) Thanks for the feedback. Without knowing your situation, one of the things we show in the blog post is that TimescaleDB compression often changes the game on those kinds of queries (data is transformed to columnar storage when you compress). You don't mention if you did that or not, but it's something we've seen/noticed in every other benchmark at this point - that folks don't enable it for the benchmark. And second point of the article is that you have lots of options for whatever works in your specific situation. But, make sure you're using the chosen database features before counting it out. :-)

maxmcd 5y ago
I wonder if it's worth taking a page out of the MongoDB book and enabling these kinds of benchmark altering settings by default. We certainly selected clickhouse over tailscale internally because of major performance differences in our internal testing that might have gone the other way had we "known better".
- ryanbooz 5y ago
  Indeed. Lots of discussion over this in the last few months. There are nuances, but I think you'll see some progress in this area over the next year.

claytonjy 5y ago

Was this for your primary source-of-truth, or more of a downstream data warehouse, or something else? I'm struggling to imagine a case where these are the two things being considered; Timescale is the obvious choice for a primary database, Clickhouse the obvious choice for a warehouse. I wouldn't let my user-facing app write to Clickhouse, and while I could potentially get away with a read-only Timescale replica for internal-facing reports I would expect to eventually outgrow that and reach for Clickhouse/Snowflake/Redshift.

encoderer 5y ago
> I wouldn't let my user-facing app write to Clickhouse I’ve been thinking of doing exactly that. What are your concerns?
- claytonjy 5y ago
  I suppose it depends what you're going to let your user do, but OLAPs in general and Clickhouse in particular don't do well under row-oriented workloads, as described in the post here. I'm imagining users primarily operating on small numbers of rows and sometimes making updates to or deleting them, a worst-case scenario for Clickhouse but best-case for an OLTP like Postgres.
  - encoderer 5y ago
    Ah totally. Thanks for sharing your thoughts! In my case I’m evaluating clickhouse as a source of truth for customer telemetry data. Totally agree about the OLTP limitations.
    - willvarfar 5y ago
      (Remember that clickhouse is not reliable. It doesn’t pretend to be. Clickhouse is great for lots of common query workloads, but if losing your data would be a big deal then it makes a lot of sense to have your data in a reliable and backed up place (eg timescale or just s3 files or whatever) too. Of course lots of times people chuck stuff into clickhouse and it’s fine if they lose a bit sometimes. YMMV.)
      - RhodesianHunter 5y ago
        I have not found this to be the case. Like any system you need to take precautions (replicas and sharding) to ensure no data loss, but I didn't find that to be challenging. In what way have you found ClickHouse particularly risky in this way?

GordonS 5y ago

How many data points were those aggregations being computed over? How much memory does your Postgres server have, and are you using SSD storage (with associated postgres config tweaks)?

ryanbooz 5y ago
(Post author) Howdy! We provided all of those details in the post and you're welcome to join us next week when we live-stream our setup and test! 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/#clickhouse-vs-timescaledb https://blog.timescale.com/blog/what-is-clickhouse-how-does-...
- GordonS 5y ago
  I was responding to @andrejserafim, asking about their scenario, not the article.
  - ryanbooz 5y ago
    Gotcha! My apologies for not seeing the thread nature. HN threads get me sometimes. :-)

ants_a 5y ago

From my experience of benchmarking these databases on scientific data (highly regular timeseries) and looking at the internals of both, these kinds types of number happen when answering the query needs crunching through many rows, but the output has few. i.e. the queries are filtering and/or aggregating a ton of input rows, that can't be excluded by indexes or queried from preaggregations. From what I can tell it comes down to execution engine differences. TimeScale, even with compressed tables, uses a row by row execution engine architecturally resembling IE6 era JS engines. ClickHouse uses a batched and vectorized execution engine utilizing SIMD. Difference is one to two orders of magnitude of throughput in terms raw number of rows per core pushed through the execution engine. Postgres/TimeScale could certainly also implement a similar model of execution, but to call it an undertaking would be an understatement considering the breadth and extensibility of features that the execution engine would need to support. To my knowledge no one is seriously working on this outside of limited capability hacks like vops or PG-Strom extensions.

ryanbooz 5y ago
(post author) You do a great job summarizing some of the benefits of ClickHouse we mentioned in the post, including the vectorized engine! That said, I'm not sure I'd refer to PostgreSQL/TimescaleDB engine architecture as resembling IE6 JS support. Obviously YMMV, but every release of PG and TimescaleDB bring new advancements to query optimizations for the architecture they are designed for, which was the focus of the post. I'm personally still impressed, after 20+ years of working with SQL, relational databases, when any optimization engine can use statistics to find the "best" plan among (potentially) thousands in a few ms. Maybe I'm too easily impressed. :-D
- ants_a 5y ago
  The optimization engine is of course great (despite occasionally missing hard), but I am not referring to it. I am referring to the way that PostgreSQL executes query plans, the way rows are pulled up the execution tree, is very similar to first iterations JavaScript engines - a tree based interpreter. Picking out columns from rows and evaluating expressions used to work the same until PG11, where we got a bytecode based interpreter and a JIT for those. But so far rows are still working the same way, and it hurts pretty bad when row lookup is cheap and the rows end up either thrown away or aggregated together with basic math.