4 ms·

TimescaleDB is great, not just for timeseries data, but also if you're trying to scale your Postgres database (with some cavaets, obviously.) One often has a s

by eloff 6y ago

TimescaleDB is great, not just for timeseries data, but also if you're trying to scale your Postgres database (with some cavaets, obviously.) One often has a small number of tables, often a single table, with a high write workload. Changing it to a timescale hypertable, provided you can work with the additional restrictions that has, can let you instantly 10X your write throughput (or more with multi-node timescale.) That's a lot cheaper than trying to scale your database write IO by 10x. I like multi-cloud services like this because it reduces the vendor lock-in that happens with these public clouds.

mfreed 6y ago
(Timescale person) This is also true for Timescale Cloud -- especially once people are using it for TimescaleDB (which, as you probably know, is implemented as a low-level extension to Postgres), we see some users start to migrate over their more traditional Postgres workloads to Timescale Cloud as well. One database platform for all their data, one (great) support team to speak with, etc. And thanks for the compliment!
ants_a 6y ago
Why wouldn't you use built in partitioning for this?
- mulmen 6y ago
  I think its a time/money tradeoff. I recall a comment along those lines (paraphrased) the last time I asked about TimeseriesDB. It's the fundamental calculation of SaaS/Cloud.
  - ants_a 6y ago
    I don't feel there is a significant time investment with using the builtin way. Timescale has some neat features for handling time series data, but for just sharding out data for scalability I can't see it offering anything substantial. And in that vein, I feel the marketing is somewhat disingenuous in the performance claims. When comparing to vanilla PostgreSQL it is comparing against a very naive usage pattern. People have been getting the same kinds of performance increases from partitioning out time series data for a loooong time.
    - akulkarni 6y ago
      The advantage on inserts with TimescaleDB is the automation. The built-in way on Postgres still requires a lot of manual maintenance, especially as data patterns change. TimescaleDB adjusts automatically. (Nothing against Postgres - it just that Postgres needs to design for the general use case, while we can optimize for time-series.) But TimescaleDB is much more than automatic partitioning. You also get: * 15x native compression (95%+ storage savings) [0] * Faster queries for time-series (b/c of our optimizations at the query planning / execution level) * Automatic data retention policies (for automatically deleting old data) [1] * Continuous aggregates (real-time materialized views automatically refreshed) [2] (and lots more) Happy to answer any more questions! [0] https://docs.timescale.com/latest/using-timescaledb/compression https://docs.timescale.com/latest/using-timescaledb/compress... [1] https://docs.timescale.com/latest/using-timescaledb/data-retention https://docs.timescale.com/latest/using-timescaledb/data-ret... [2] https://docs.timescale.com/latest/using-timescaledb/continuous-aggregates https://docs.timescale.com/latest/using-timescaledb/continuo...
      - sa46 6y ago
        > 15x native compression That is a rather remarkable compression ratio. Do you have a link to technical details? The lightweight compression outlined in the Gorilla TSDB and implemented in Uber's m3db is 11x which I thought was best in class: https://cs.brown.edu/courses/csci2270/papers/gorilla.pdf https://cs.brown.edu/courses/csci2270/papers/gorilla.pdf
        mfreed 6y ago
        Yep, absolutely. Here's a blog post outlining our general approach, which applies type-specific compression based on a column's datatype. (While m3db only supports floats, TimescaleDB supports a whole range of 40+ data types.) https://blog.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://blog.timescale.com/blog/building-columnar-compressio... For example, by default we employ: - Gorilla for floats (+ some optimizations for faster decoding) - 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 And the architecture is extensible to support new/updated compression algorithms for different data types. The above are all lossless algorithms; obviously one could drop precision to achieve higher rates. We also wrote a post describing these various approaches in a bit more detail for folks interested in learning (although a summary compared to the Facebook paper): https://blog.timescale.com/blog/time-series-compression-algorithms-explained/ https://blog.timescale.com/blog/time-series-compression-algo... Obviously, your compression rate depends heavily on workload, but the number 15x comes from median performance that users see in the field against their real databases, not just a synthetic or single workload.
GordonS 6y ago
If you want to drop data after a retention period, TimescaleDB is also great here. If you're going to delete millions of rows from a standard Postgres table, it can take a long time and require a lengthy vacuum to recover space - with TimescaleDB it's almost instant, since behind the scenes it is simply deleting the files (chunks) that fall within the time range.

by eloff 6y ago

mfreed 6y ago

(Timescale person) This is also true for Timescale Cloud -- especially once people are using it for TimescaleDB (which, as you probably know, is implemented as a low-level extension to Postgres), we see some users start to migrate over their more traditional Postgres workloads to Timescale Cloud as well. One database platform for all their data, one (great) support team to speak with, etc. And thanks for the compliment!

ants_a 6y ago

Why wouldn't you use built in partitioning for this?

mulmen 6y ago
I think its a time/money tradeoff. I recall a comment along those lines (paraphrased) the last time I asked about TimeseriesDB. It's the fundamental calculation of SaaS/Cloud.
- ants_a 6y ago
  I don't feel there is a significant time investment with using the builtin way. Timescale has some neat features for handling time series data, but for just sharding out data for scalability I can't see it offering anything substantial. And in that vein, I feel the marketing is somewhat disingenuous in the performance claims. When comparing to vanilla PostgreSQL it is comparing against a very naive usage pattern. People have been getting the same kinds of performance increases from partitioning out time series data for a loooong time.
  - akulkarni 6y ago
    The advantage on inserts with TimescaleDB is the automation. The built-in way on Postgres still requires a lot of manual maintenance, especially as data patterns change. TimescaleDB adjusts automatically. (Nothing against Postgres - it just that Postgres needs to design for the general use case, while we can optimize for time-series.) But TimescaleDB is much more than automatic partitioning. You also get: * 15x native compression (95%+ storage savings) [0] * Faster queries for time-series (b/c of our optimizations at the query planning / execution level) * Automatic data retention policies (for automatically deleting old data) [1] * Continuous aggregates (real-time materialized views automatically refreshed) [2] (and lots more) Happy to answer any more questions! [0] https://docs.timescale.com/latest/using-timescaledb/compression https://docs.timescale.com/latest/using-timescaledb/compress... [1] https://docs.timescale.com/latest/using-timescaledb/data-retention https://docs.timescale.com/latest/using-timescaledb/data-ret... [2] https://docs.timescale.com/latest/using-timescaledb/continuous-aggregates https://docs.timescale.com/latest/using-timescaledb/continuo...
    - sa46 6y ago
      > 15x native compression That is a rather remarkable compression ratio. Do you have a link to technical details? The lightweight compression outlined in the Gorilla TSDB and implemented in Uber's m3db is 11x which I thought was best in class: https://cs.brown.edu/courses/csci2270/papers/gorilla.pdf https://cs.brown.edu/courses/csci2270/papers/gorilla.pdf
      - mfreed 6y ago
        Yep, absolutely. Here's a blog post outlining our general approach, which applies type-specific compression based on a column's datatype. (While m3db only supports floats, TimescaleDB supports a whole range of 40+ data types.) https://blog.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://blog.timescale.com/blog/building-columnar-compressio... For example, by default we employ: - Gorilla for floats (+ some optimizations for faster decoding) - 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 And the architecture is extensible to support new/updated compression algorithms for different data types. The above are all lossless algorithms; obviously one could drop precision to achieve higher rates. We also wrote a post describing these various approaches in a bit more detail for folks interested in learning (although a summary compared to the Facebook paper): https://blog.timescale.com/blog/time-series-compression-algorithms-explained/ https://blog.timescale.com/blog/time-series-compression-algo... Obviously, your compression rate depends heavily on workload, but the number 15x comes from median performance that users see in the field against their real databases, not just a synthetic or single workload.

GordonS 6y ago

If you want to drop data after a retention period, TimescaleDB is also great here. If you're going to delete millions of rows from a standard Postgres table, it can take a long time and require a lengthy vacuum to recover space - with TimescaleDB it's almost instant, since behind the scenes it is simply deleting the files (chunks) that fall within the time range.