7 ms·

TimescaleDB 2.7 vs. PostgreSQL 14

117 points by carlotasoto 4y ago

didip 4y ago
What about TimescaleDB vs a lot of other OLAP databases like ClickHouse, Druid, Pinot, Databend, etc.? Or hybrid databases like StarRocks or TiDB?
- doliveira 4y ago
  As a sidenote, Databend's elevator pitch sounds quite good: from what I've seen there's quite a lot of complexity in managing co-located storage and compute like ClickHouse, Druid and Pinot. Leveraging object storage instead seems like a good alternative. Do you have some experience with it?
  - didip 4y ago
    Unfortunately not yet, but I am actively investigating.
- lgl 4y ago
  Not sure about the other ones, but they do have a comparison against ClickHouse from October 2021 that's available at: https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://www.timescale.com/blog/what-is-clickhouse-how-does-i...
- AdamProut 4y ago
  This is a reasonable benchmark from the Clickhouse folks for single table anlaytical query performance over smallish data sets (10s of GB of data). Most of the DW vendors are on there. https://benchmark.clickhouse.com/ https://benchmark.clickhouse.com/ Timescale apparently lags pretty far behind modern columnstore engines.
  - akulkarni 4y ago
    (Timescale co-founder) As with anything, it depends on what you want to do. If you have an OLAP heavy workload with long scans, etc (which is the type of queries prominent on the ClickHouse page - e.g., Q0 is "SELECT COUNT(*) FROM hits;"), then I would highly recommend systems other than Timescale. (Although we are also working on this ;-) ) But if you have time-series workload, or even, if you love Postgres and are building a time-series and/or analytical application, then I would recommend TimescaleDB. ClickHouse is great. I just believe in using the right tool for the right job. :-) There are many areas where column store engines beat TimescaleDB. But nothing comes for free - everything has a tradeoff.
    - didip 4y ago
      Genuinely curious, aren't a lot of time-series workloads OLAP oriented?
chrisco255 4y ago
Should be noted that article is titled Timescale + Postgres vs. Postgres alone. Timescale is built on Postgres so it's not really a competition per se.
- Helmut10001 4y ago
  Yes, I expected something like InfluxDB 2.0 vs. Postgres.
- g8oz 4y ago
  Right, the "vs" gives a much different and frankly misleading impression of the article than the "+".
- akulkarni 4y ago
  (Timescale co-founder) Yes, 100%. We deliberately choose the "+" symbol instead of "vs." for this blog title. We love PostgreSQL. :-)
boomskats 4y ago
Whenever I see these posts from TimescaleDB, I always want to ask them how it compares in performance to alternative extensions that implement the same features, rather than just comparing TimescaleDB to vanilla PostgreSQL. For example, they mention their automated data retention and how it's achieved with one SQL command, and how DELETEing records is a very costly operation, and how "even if you were using Postgres declarative partitioning you’d still need to automate the process yourself, wasting precious developer time, adding additional requirements, and implementing bespoke code that needs to be supported moving forward". There's zero mention anywhere of pg_partman, which does all of these things for you equally as simply, and is a fully OSS free alternative [0]. I get that it's a PG extension that competes with their product. I know that TimescaleDB does a few other things that pg_partman does not. But I can't help but find its (seemingly) purposeful omission in these, otherwise very thorough blog posts, misleading. [0] https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#retention https://github.com/pgpartman/pg_partman/blob/master/doc/pg_p...
- fzliu 4y ago
  Seconded. I understand that Timescale extends Postgres, but I'd still like to see performance comparisons relative to other time-series databases such as InfluxDB or even TiDB. Shameless self-plug: we're also building database technology, but for embedding vectors (https://milvus.io https://milvus.io) rather than time-series data - when doing query performance comparisons, we get several orders of magnitude of performance improvement over traditional databases. It's an unfair comparison, so we generally avoid it.
  - ebiester 4y ago
    I think it's fair to mention it, but with the specific caveat of "If your workload has these characteristics, we are a particularly good fit."
  - akulkarni 4y ago
    [Timescale co-founder] We have published many, many benchmarks versus other database systems. IIRC all of them also made the front page of HackerNews. Here are some of them, for your reading pleasure :-) TimescaleDB vs. InfluxDB: https://www.timescale.com/blog/timescaledb-vs-influxdb-for-time-series-data-timescale-influx-sql-nosql-36489299877/ https://www.timescale.com/blog/timescaledb-vs-influxdb-for-t... TimescaleDB vs. ClickHouse: https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://www.timescale.com/blog/what-is-clickhouse-how-does-i... TimescaleDB vs. Timestream: https://www.timescale.com/blog/timescaledb-vs-amazon-timestream-6000x-higher-inserts-175x-faster-queries-220x-cheaper/ https://www.timescale.com/blog/timescaledb-vs-amazon-timestr...
krn 4y ago
If integration with PostgreSQL is not a requirement, I would suggest looking at QuestDB[1] for the best performing open-source timeseries database currently on the market. [1] https://questdb.io/blog/2021/07/05/comparing-questdb-timescaledb-influxdb/ https://questdb.io/blog/2021/07/05/comparing-questdb-timesca...
- tda 4y ago
  Are you sure it is the best performing? The article only compares to influx and timescale, which are very far from the fastest timeseries databases out there. How does it compare to clickhouse? I found this https://questdb.io/blog/2022/05/26/query-benchmark-questdb-versus-clickhouse-timescale/ https://questdb.io/blog/2022/05/26/query-benchmark-questdb-v..., but the numbers are so far off (and clickhouse is much slower than timescale in their examples) that I kind of struggle to believe them.
  - krn 4y ago
    Clickhouse is a data warehouse, not a timeseries database. Timeseries databases are designed with different bottlenecks in the mind: https://questdb.io/blog/2020/11/26/why-timeseries-data/ https://questdb.io/blog/2020/11/26/why-timeseries-data/
alecco 4y ago
How is this on HN frontpage?
- LoriP 4y ago
  folks like to chat about benchmarks
znpy 4y ago
No mention of ha in the documentation. I can’t understand whether HA would rely on the standard postgresql tooling or if you have to pay for some kind of enterprise license to get it.
- zokier 4y ago
  https://docs.timescale.com/timescaledb/latest/how-to-guides/replication-and-ha/ https://docs.timescale.com/timescaledb/latest/how-to-guides/...
- LoriP 4y ago
  Timescale doesn't charge for any of its software. Revenue comes from providing hosting services that are optimized towards TimescaleDB and PostgreSQL at scale. The code is source-available, license philosophy is explained in this blog post https://www.timescale.com/blog/building-open-source-business-in-cloud-era-v2/ https://www.timescale.com/blog/building-open-source-business... That includes the HA implementation, and I think someone else shared the docs for that. Hope this helps.
srcreigh 4y ago
> For example, one recent query planner improvement excludes data more efficiently for relative now()-based queries (e.g., WHERE time >= now()-’1 week’::interval). This could be done by just calculating the start date in code too. > When hypertables are compressed the amount of data that queries need to read is reduced, leading to dramatic increases in performance of 1000x or more. At my workplace we recently experimented with storing time series data in an array in a postgres row. This gets compressed as a TOAST array, can store thousands of ints in just a few DB pages (aka loading it is about the same as an index scan). We also use Timescale for a different service mind you. I'm sure this format is more efficient than the Timescale format too. In Timescale you would need rows containing (for example) (date, user_id, time_on_site), one row per day. The postgres array format (start_date, user_id, time_on_site_by_date) indexing where 0=start_date, 1=start_date+1 is like 1/3rd the size uncompressed. And yea, even if something is compressed, you still gotta put the uncompressed version in memory somewhere.
- djk447 4y ago
  Disclosure, I work at Timescale. We are kindred spirits I think! I did this too [0] a while back at a previous company and it actually served as part of the inspiration for our compression work! It's fun, but a bit difficult to query at times. Our compressed columns do also get TOASTed and stored out of line. I'm not sure that it's going to be much more efficient than the Timescale format once it's compressed, we have some pretty good compression algos, but I might be missing something about your case, we generally can achieve close to 10x compression, but right now you can't write directly compressed data, so you would save on the write side I suppose. It is true that you need to put the uncompressed version into memory at some point, but we do try to limit that and in many cases you end up IO limited moreso than memory limited. We're also thinking about doing some work to push processing down towards the compressed data, but that's still in the "glint in our eye" stage, but I think it has a lot of promise. (As a side note, TOAST is still the best acronym around ;) ). [0]: https://www.youtube.com/watch?v=sPoz1OPuRUU https://www.youtube.com/watch?v=sPoz1OPuRUU
  - miohtama 4y ago
    If I am running already compressed filesystem like ZFS with zstd, can I disable TOAST and compressed columns altogether somehow? https://dba.stackexchange.com/questions/315063/disable-toast-compression-for-all-columns https://dba.stackexchange.com/questions/315063/disable-toast...
ithrow 4y ago
Is Timescale suited for keeping the history of the data and then query against any point in time?
- jxi 4y ago
  No. For that you need Temporal Tables: https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/t...
- RedShift1 4y ago
  Yes
PeterZaitsev 4y ago
I wonder in those benchmarks are we speaking about TimescaleDB Apache Edition or non OSS TimescaleDB Community edition ? Looks like these are quite different in features https://docs.timescale.com/timescaledb/latest/timescaledb-edition-comparison/ https://docs.timescale.com/timescaledb/latest/timescaledb-ed...
jxi 4y ago
It may be faster for specific benchmark cases, but in my experience Timescale has had wildly unpredictable performance when used in a production setting. It's also very annoying that you have to remember to run certain timescale functions for backups or restores and there's many more additional background processes running.
maierru 4y ago
tldr "readonly" database :( Update/delete of current chunk data blocked during compression of old data https://github.com/timescale/timescaledb/issues/4432 https://github.com/timescale/timescaledb/issues/4432
nextaccountic 4y ago
Hey, about the column-wide data compression thing. If I have a column with strings that are highly similar to each other (maybe they are urls, maybe they are HTML documents that were scraped from the same site, but they are expected to contain redundancies) What's the best approach to get excellent compression? Can I exploit this redundancy within the column somehow? I don't even know the right search terms for this. TimescaleDB compression will work only for integer columns, right? And in any case, this is not a time series.
- mfreed 4y ago
  TimescaleDB supports compression for all data types, it just uses type-aware compression algorithms that it applies automatically/transparently based on typing. - Gorilla compression for floats - Delta-of-delta + Simple-8b with run-length encoding compression for timestamps and other integer-like types - Whole-row dictionary compression for columns with a few repeating values (+ LZ compression on top) - LZ-based array compression for all other types https://www.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://www.timescale.com/blog/building-columnar-compression... So as to your question, just turn on compression; it's very common to see 94-97% reduction in storage. (Timescale co-founder)
lyeniac 4y ago
Love TimescaleDB, actually used it for one of our recent product feature launches: https://webapp.io/blog/storing-millions-of-vm-metrics-with-timescaledb/ https://webapp.io/blog/storing-millions-of-vm-metrics-with-t...
- PaulWaldman 4y ago
  What did you use to collect and injest the metrics?

117 points by carlotasoto 4y ago

What about TimescaleDB vs a lot of other OLAP databases like ClickHouse, Druid, Pinot, Databend, etc.? Or hybrid databases like StarRocks or TiDB?

doliveira 4y ago
As a sidenote, Databend's elevator pitch sounds quite good: from what I've seen there's quite a lot of complexity in managing co-located storage and compute like ClickHouse, Druid and Pinot. Leveraging object storage instead seems like a good alternative. Do you have some experience with it?
- didip 4y ago
  Unfortunately not yet, but I am actively investigating.
lgl 4y ago
Not sure about the other ones, but they do have a comparison against ClickHouse from October 2021 that's available at: https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://www.timescale.com/blog/what-is-clickhouse-how-does-i...
AdamProut 4y ago
This is a reasonable benchmark from the Clickhouse folks for single table anlaytical query performance over smallish data sets (10s of GB of data). Most of the DW vendors are on there. https://benchmark.clickhouse.com/ https://benchmark.clickhouse.com/ Timescale apparently lags pretty far behind modern columnstore engines.
- akulkarni 4y ago
  (Timescale co-founder) As with anything, it depends on what you want to do. If you have an OLAP heavy workload with long scans, etc (which is the type of queries prominent on the ClickHouse page - e.g., Q0 is "SELECT COUNT(*) FROM hits;"), then I would highly recommend systems other than Timescale. (Although we are also working on this ;-) ) But if you have time-series workload, or even, if you love Postgres and are building a time-series and/or analytical application, then I would recommend TimescaleDB. ClickHouse is great. I just believe in using the right tool for the right job. :-) There are many areas where column store engines beat TimescaleDB. But nothing comes for free - everything has a tradeoff.
  - didip 4y ago
    Genuinely curious, aren't a lot of time-series workloads OLAP oriented?

chrisco255 4y ago

Should be noted that article is titled Timescale + Postgres vs. Postgres alone. Timescale is built on Postgres so it's not really a competition per se.

Helmut10001 4y ago
Yes, I expected something like InfluxDB 2.0 vs. Postgres.
g8oz 4y ago
Right, the "vs" gives a much different and frankly misleading impression of the article than the "+".
akulkarni 4y ago
(Timescale co-founder) Yes, 100%. We deliberately choose the "+" symbol instead of "vs." for this blog title. We love PostgreSQL. :-)

boomskats 4y ago

Whenever I see these posts from TimescaleDB, I always want to ask them how it compares in performance to alternative extensions that implement the same features, rather than just comparing TimescaleDB to vanilla PostgreSQL. For example, they mention their automated data retention and how it's achieved with one SQL command, and how DELETEing records is a very costly operation, and how "even if you were using Postgres declarative partitioning you’d still need to automate the process yourself, wasting precious developer time, adding additional requirements, and implementing bespoke code that needs to be supported moving forward". There's zero mention anywhere of pg_partman, which does all of these things for you equally as simply, and is a fully OSS free alternative [0]. I get that it's a PG extension that competes with their product. I know that TimescaleDB does a few other things that pg_partman does not. But I can't help but find its (seemingly) purposeful omission in these, otherwise very thorough blog posts, misleading. [0] https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#retention https://github.com/pgpartman/pg_partman/blob/master/doc/pg_p...

fzliu 4y ago
Seconded. I understand that Timescale extends Postgres, but I'd still like to see performance comparisons relative to other time-series databases such as InfluxDB or even TiDB. Shameless self-plug: we're also building database technology, but for embedding vectors (https://milvus.io https://milvus.io) rather than time-series data - when doing query performance comparisons, we get several orders of magnitude of performance improvement over traditional databases. It's an unfair comparison, so we generally avoid it.
- ebiester 4y ago
  I think it's fair to mention it, but with the specific caveat of "If your workload has these characteristics, we are a particularly good fit."
- akulkarni 4y ago
  [Timescale co-founder] We have published many, many benchmarks versus other database systems. IIRC all of them also made the front page of HackerNews. Here are some of them, for your reading pleasure :-) TimescaleDB vs. InfluxDB: https://www.timescale.com/blog/timescaledb-vs-influxdb-for-time-series-data-timescale-influx-sql-nosql-36489299877/ https://www.timescale.com/blog/timescaledb-vs-influxdb-for-t... TimescaleDB vs. ClickHouse: https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://www.timescale.com/blog/what-is-clickhouse-how-does-i... TimescaleDB vs. Timestream: https://www.timescale.com/blog/timescaledb-vs-amazon-timestream-6000x-higher-inserts-175x-faster-queries-220x-cheaper/ https://www.timescale.com/blog/timescaledb-vs-amazon-timestr...

krn 4y ago

If integration with PostgreSQL is not a requirement, I would suggest looking at QuestDB[1] for the best performing open-source timeseries database currently on the market. [1] https://questdb.io/blog/2021/07/05/comparing-questdb-timescaledb-influxdb/ https://questdb.io/blog/2021/07/05/comparing-questdb-timesca...

tda 4y ago
Are you sure it is the best performing? The article only compares to influx and timescale, which are very far from the fastest timeseries databases out there. How does it compare to clickhouse? I found this https://questdb.io/blog/2022/05/26/query-benchmark-questdb-versus-clickhouse-timescale/ https://questdb.io/blog/2022/05/26/query-benchmark-questdb-v..., but the numbers are so far off (and clickhouse is much slower than timescale in their examples) that I kind of struggle to believe them.
- krn 4y ago
  Clickhouse is a data warehouse, not a timeseries database. Timeseries databases are designed with different bottlenecks in the mind: https://questdb.io/blog/2020/11/26/why-timeseries-data/ https://questdb.io/blog/2020/11/26/why-timeseries-data/

alecco 4y ago

How is this on HN frontpage?

LoriP 4y ago
folks like to chat about benchmarks

znpy 4y ago

No mention of ha in the documentation. I can’t understand whether HA would rely on the standard postgresql tooling or if you have to pay for some kind of enterprise license to get it.

zokier 4y ago
https://docs.timescale.com/timescaledb/latest/how-to-guides/replication-and-ha/ https://docs.timescale.com/timescaledb/latest/how-to-guides/...
LoriP 4y ago
Timescale doesn't charge for any of its software. Revenue comes from providing hosting services that are optimized towards TimescaleDB and PostgreSQL at scale. The code is source-available, license philosophy is explained in this blog post https://www.timescale.com/blog/building-open-source-business-in-cloud-era-v2/ https://www.timescale.com/blog/building-open-source-business... That includes the HA implementation, and I think someone else shared the docs for that. Hope this helps.

srcreigh 4y ago

> For example, one recent query planner improvement excludes data more efficiently for relative now()-based queries (e.g., WHERE time >= now()-’1 week’::interval). This could be done by just calculating the start date in code too. > When hypertables are compressed the amount of data that queries need to read is reduced, leading to dramatic increases in performance of 1000x or more. At my workplace we recently experimented with storing time series data in an array in a postgres row. This gets compressed as a TOAST array, can store thousands of ints in just a few DB pages (aka loading it is about the same as an index scan). We also use Timescale for a different service mind you. I'm sure this format is more efficient than the Timescale format too. In Timescale you would need rows containing (for example) (date, user_id, time_on_site), one row per day. The postgres array format (start_date, user_id, time_on_site_by_date) indexing where 0=start_date, 1=start_date+1 is like 1/3rd the size uncompressed. And yea, even if something is compressed, you still gotta put the uncompressed version in memory somewhere.

djk447 4y ago
Disclosure, I work at Timescale. We are kindred spirits I think! I did this too [0] a while back at a previous company and it actually served as part of the inspiration for our compression work! It's fun, but a bit difficult to query at times. Our compressed columns do also get TOASTed and stored out of line. I'm not sure that it's going to be much more efficient than the Timescale format once it's compressed, we have some pretty good compression algos, but I might be missing something about your case, we generally can achieve close to 10x compression, but right now you can't write directly compressed data, so you would save on the write side I suppose. It is true that you need to put the uncompressed version into memory at some point, but we do try to limit that and in many cases you end up IO limited moreso than memory limited. We're also thinking about doing some work to push processing down towards the compressed data, but that's still in the "glint in our eye" stage, but I think it has a lot of promise. (As a side note, TOAST is still the best acronym around ;) ). [0]: https://www.youtube.com/watch?v=sPoz1OPuRUU https://www.youtube.com/watch?v=sPoz1OPuRUU
- miohtama 4y ago
  If I am running already compressed filesystem like ZFS with zstd, can I disable TOAST and compressed columns altogether somehow? https://dba.stackexchange.com/questions/315063/disable-toast-compression-for-all-columns https://dba.stackexchange.com/questions/315063/disable-toast...

ithrow 4y ago

Is Timescale suited for keeping the history of the data and then query against any point in time?

jxi 4y ago
No. For that you need Temporal Tables: https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/t...
RedShift1 4y ago
Yes

PeterZaitsev 4y ago

I wonder in those benchmarks are we speaking about TimescaleDB Apache Edition or non OSS TimescaleDB Community edition ? Looks like these are quite different in features https://docs.timescale.com/timescaledb/latest/timescaledb-edition-comparison/ https://docs.timescale.com/timescaledb/latest/timescaledb-ed...

jxi 4y ago

It may be faster for specific benchmark cases, but in my experience Timescale has had wildly unpredictable performance when used in a production setting. It's also very annoying that you have to remember to run certain timescale functions for backups or restores and there's many more additional background processes running.

maierru 4y ago

tldr "readonly" database :( Update/delete of current chunk data blocked during compression of old data https://github.com/timescale/timescaledb/issues/4432 https://github.com/timescale/timescaledb/issues/4432

nextaccountic 4y ago

Hey, about the column-wide data compression thing. If I have a column with strings that are highly similar to each other (maybe they are urls, maybe they are HTML documents that were scraped from the same site, but they are expected to contain redundancies) What's the best approach to get excellent compression? Can I exploit this redundancy within the column somehow? I don't even know the right search terms for this. TimescaleDB compression will work only for integer columns, right? And in any case, this is not a time series.

mfreed 4y ago
TimescaleDB supports compression for all data types, it just uses type-aware compression algorithms that it applies automatically/transparently based on typing. - Gorilla compression for floats - Delta-of-delta + Simple-8b with run-length encoding compression for timestamps and other integer-like types - Whole-row dictionary compression for columns with a few repeating values (+ LZ compression on top) - LZ-based array compression for all other types https://www.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://www.timescale.com/blog/building-columnar-compression... So as to your question, just turn on compression; it's very common to see 94-97% reduction in storage. (Timescale co-founder)

lyeniac 4y ago

Love TimescaleDB, actually used it for one of our recent product feature launches: https://webapp.io/blog/storing-millions-of-vm-metrics-with-timescaledb/ https://webapp.io/blog/storing-millions-of-vm-metrics-with-t...

PaulWaldman 4y ago
What did you use to collect and injest the metrics?