4 ms·

I talked to the timescale CTO at pg conf a few years ago and asked him what timescale does differently than a standard columnar database that makes it better su

I talked to the timescale CTO at pg conf a few years ago and asked him what timescale does differently than a standard columnar database that makes it better suited for time oriented data. He said a bunch of things and I said “but columnar databases do those things.” Then he got mad at me. I guess it’s just another columnar dbms after all?

yuretz 1y ago
Few years have passed and your guess is still wrong.
dangoodmanUT 1y ago
They don't do well on benchmarks https://benchmark.clickhouse.com/ https://benchmark.clickhouse.com/
- dengolius 1y ago
  Yes, TigerData aka Timescale tried to make a fuss a few years ago comparing Clickhouse and TimescaleDB, but they failed.
- jascha_eng 1y ago
  I'd argue we do okay, but of course it's Clickhouses own benchmark it's hard to outperform them there. It's also not apples to apples. Clickhouse has much less transactional guarantees and isn't postgres SQL compatible. The great thing about Timescale is that you only need one DB for all your analytics and transactional needs. Combined with pgvector postgres also handles search quite well. In a way Timescale is just postgres on steroids. Sure if you really know your use-case well, are fine with giving up some postgres nicenes, are willing to learn a new query language and are fine with using and syncing multiple data stores you'll outperform timescale. But I think it is still really cool to see how close you can get with essentially just a better postgres.
  - hodgesrm 1y ago
    > Clickhouse has much less transactional guarantees ... Is this relevant? The benchmark is just reads.
    - jascha_eng 1y ago
      Depends on your workload? If you don't care about ACID compliance in your use-case, and query speed is all that is relevant to you probably not. You might still be better of with Timescale/TigerData if your query pattern uses a lot of joins as we do much better there than Clickhouse does. We have our own benchmark too and perform better than Clickhouse on those kind of queries: https://rtabench.com/ https://rtabench.com/ But also transactions often make your life as a dev easier in my experience, and being able to use a single DB and stick with 100% postgres compatible SQL without having to change your application is often worth more than squeezing out the last few bit of query performance. I'm just saying that single-benchmark comparisons rarely tell the full story when evaluating database technologies. ClickHouse is undoubtedly impressive engineering, and it excels in many scenarios. Ultimately the optimal choice depends on your specific use case.
      - hodgesrm 1y ago
        I'm glad you answered as my comment was not very complete. It left out mentioning that transactions should not be expensive on reads unless you are doing something wrong. In general MVCC--which PostgreSQL uses--does not have a lot of performance overhead for this case. ClickHouse also maintains snapshots when reading, so to a certain extent they do the same work. Transactions don't seem like a very strong argument here, since you would be conceding that your implementation is inefficient. I agree with the other points. ClickHouse is not strong on joins [yet]. It's also nice to have a single database for everything. Yet so far nobody has been able to achieve one that delivers high concurrency, fast updates, and petabyte-level scaling. Mike Stonebraker et al. called this problem out in 2007. [0] It appears they called it right and we'll continue to see 2-3 major categories of databases for the foreseeable future. [0] https://www.vldb.org/conf/2007/papers/industrial/p1150-stonebraker.pdf https://www.vldb.org/conf/2007/papers/industrial/p1150-stone...
- akulkarni 1y ago
  It depends on which benchmarks you use. "ClickBench evaluates databases using a single table of clickstream data, representative of workloads like web analytics, BI, and log aggregation. It also favors full-table large scans and large-scale aggregations on denormalized data. Real-time analytics inside applications is different and needs a new benchmark." [0] This is why we published RTABench. [1] We believe that it is more representative of real-time analytical workloads. [0] https://www.tigerdata.com/blog/benchmarking-databases-for-real-time-analytics-applications https://www.tigerdata.com/blog/benchmarking-databases-for-re... [1] https://rtabench.com/ https://rtabench.com/
- freilanzer 1y ago
  DuckDB seems to be the most interesting there.
  - qoega 1y ago
    It is meant for single reader/writer workload so not meant to be used as a service
viccis 1y ago
Do you think all time series databases (like InfluxDB for example) are useless compared to "columnar databases" that "do those things" or just Timescale?
fellatio 1y ago
Unfair anecdote as you don't mention what he said before and after "he got mad" (whatever that means).
victorbjorklund 1y ago
it does sound like a pretty dumb question. Many things do similar things. That is like asking what postgres does that other sql databases doesnt.
deleted 1y ago
[deleted]

yuretz 1y ago

Few years have passed and your guess is still wrong.

dangoodmanUT 1y ago

They don't do well on benchmarks https://benchmark.clickhouse.com/ https://benchmark.clickhouse.com/

dengolius 1y ago
Yes, TigerData aka Timescale tried to make a fuss a few years ago comparing Clickhouse and TimescaleDB, but they failed.
jascha_eng 1y ago
I'd argue we do okay, but of course it's Clickhouses own benchmark it's hard to outperform them there. It's also not apples to apples. Clickhouse has much less transactional guarantees and isn't postgres SQL compatible. The great thing about Timescale is that you only need one DB for all your analytics and transactional needs. Combined with pgvector postgres also handles search quite well. In a way Timescale is just postgres on steroids. Sure if you really know your use-case well, are fine with giving up some postgres nicenes, are willing to learn a new query language and are fine with using and syncing multiple data stores you'll outperform timescale. But I think it is still really cool to see how close you can get with essentially just a better postgres.
- hodgesrm 1y ago
  > Clickhouse has much less transactional guarantees ... Is this relevant? The benchmark is just reads.
  - jascha_eng 1y ago
    Depends on your workload? If you don't care about ACID compliance in your use-case, and query speed is all that is relevant to you probably not. You might still be better of with Timescale/TigerData if your query pattern uses a lot of joins as we do much better there than Clickhouse does. We have our own benchmark too and perform better than Clickhouse on those kind of queries: https://rtabench.com/ https://rtabench.com/ But also transactions often make your life as a dev easier in my experience, and being able to use a single DB and stick with 100% postgres compatible SQL without having to change your application is often worth more than squeezing out the last few bit of query performance. I'm just saying that single-benchmark comparisons rarely tell the full story when evaluating database technologies. ClickHouse is undoubtedly impressive engineering, and it excels in many scenarios. Ultimately the optimal choice depends on your specific use case.
    - hodgesrm 1y ago
      I'm glad you answered as my comment was not very complete. It left out mentioning that transactions should not be expensive on reads unless you are doing something wrong. In general MVCC--which PostgreSQL uses--does not have a lot of performance overhead for this case. ClickHouse also maintains snapshots when reading, so to a certain extent they do the same work. Transactions don't seem like a very strong argument here, since you would be conceding that your implementation is inefficient. I agree with the other points. ClickHouse is not strong on joins [yet]. It's also nice to have a single database for everything. Yet so far nobody has been able to achieve one that delivers high concurrency, fast updates, and petabyte-level scaling. Mike Stonebraker et al. called this problem out in 2007. [0] It appears they called it right and we'll continue to see 2-3 major categories of databases for the foreseeable future. [0] https://www.vldb.org/conf/2007/papers/industrial/p1150-stonebraker.pdf https://www.vldb.org/conf/2007/papers/industrial/p1150-stone...
akulkarni 1y ago
It depends on which benchmarks you use. "ClickBench evaluates databases using a single table of clickstream data, representative of workloads like web analytics, BI, and log aggregation. It also favors full-table large scans and large-scale aggregations on denormalized data. Real-time analytics inside applications is different and needs a new benchmark." [0] This is why we published RTABench. [1] We believe that it is more representative of real-time analytical workloads. [0] https://www.tigerdata.com/blog/benchmarking-databases-for-real-time-analytics-applications https://www.tigerdata.com/blog/benchmarking-databases-for-re... [1] https://rtabench.com/ https://rtabench.com/
freilanzer 1y ago
DuckDB seems to be the most interesting there.
- qoega 1y ago
  It is meant for single reader/writer workload so not meant to be used as a service

viccis 1y ago

Do you think all time series databases (like InfluxDB for example) are useless compared to "columnar databases" that "do those things" or just Timescale?

fellatio 1y ago

Unfair anecdote as you don't mention what he said before and after "he got mad" (whatever that means).

victorbjorklund 1y ago

it does sound like a pretty dumb question. Many things do similar things. That is like asking what postgres does that other sql databases doesnt.

deleted 1y ago

[deleted]