4 ms·

Author here. Thanks for sharing our post. It’s one of the many reasons why we’re so excited about the future of Postgres.

nickserv 9y ago
We're looking into Timescale to replace our InfluxDB servers as we've found InfluxDB to be resource-hungry, the lack of open source clustering is also a problem. The use of PostreSQL in Timescale is a big selling point, it's something we know how to manage and scale. A big stumbling block though is with the retention of older data. For now we do mainly service/machine monitoring and event logging without needing high granularity for older data and keeping disk usage contained. For this we've found InfluxDB to be well suited, as the retention policy system is easy to set up and fits our needs perfectly. Do you plan on introducing similar functionality in Timescale? I've found this issue in Github but don't know if this is something that is being pursued for the official product. https://github.com/timescale/timescaledb/issues/350 https://github.com/timescale/timescaledb/issues/350
- mfreed 9y ago
  Hi, thanks for the question about rollups. Timescale indeed enables you to do rollups, much like the functionality you describe. (Right now these are scheduled via a crontab, but in-DB scheduling is in the works.) But unlike InfluxDB (at least what we've been told by users), the important thing about TimescaleDB's rollups is that they correctly handle late data. So if you are performing a rollup every 10 minutes, but the data corresponding to 9:58 actually arrives at 10:05, it's easy to ensure that you subsequently reperform the rollup and "correct" that previous answer. This happens because we correctly support UPSERTS: http://docs.timescale.com/v0.9/using-timescaledb/writing-data#upsert http://docs.timescale.com/v0.9/using-timescaledb/writing-dat... You can also read more about one of our user's experience with rollups (compared to via Influx) in their recent blog post: https://blog.dnsfilter.com/3-billion-time-series-data-points-dnsfilter-replaced-influxdb-with-timescaledb-d9f827702f8b https://blog.dnsfilter.com/3-billion-time-series-data-points... As to that feature request in the github issue, it's actually asking for a much subtler and more powerful functionality. In particular, it wants the "rollup" to be hidden from the user, such that they don't worry about querying the raw vs. rollup table, and just see a single table view across multiple different levels of aggregation. Further, the planner would be smart enough which one to choose based on the time aggregation being asked for, as well as transparently merge data across different aggregation levels. This functionality is definitely on top of mind, but my understanding is that Influx doesn't support anything like this as well: If you have a rollup table there (say, to 10 minutes), you need to explicitly ask for data from the 10min rollup table, and you don't see any data that has been written in the last 10 minutes. TimescaleDB similarly supports this today.
  - nickserv 9y ago
    Thanks much for your detailed reply. If I understand correctly, the way to do this is to: 1) Use PostgreSQL's built-in aggregate functions and/or Timescale API (time_bucket?) to feed rollup tables; 2) Delete the raw, non-aggregated data; 3) trigger these queries using cron according to need. This would be totally OK for us (even if in-DB scheduling is indeed easier), and as you say, in some ways superior to Influx. I will test it out. Do you have any tutorials or examples of using rollups in practice? Thanks!
    - mfreed 9y ago
      Pretty much, except I'd use our data retention mechanisms instead of #2 (directly deleting the rows), as it's much more efficient. So to review: 1) Create a "raw" and "rollup" table. Define the rollup table with the right UNIQUE constraint (say, on timestamp and server_id). Call create_hypertable() to convert them both to Timescale hypertables. 2) Write a rollup UPSERT query similar to following: INSERT INTO rollup VALUES ( SELECT time_bucket('10 minutes', time) as time, server_id, sum(value) as sum, count(value) as count FROM raw WHERE time > now() - interval '20 minutes' GROUPBY time, server_id) ON CONFLICT (time, server_id) DO UPDATE SET sum = excluded.sum, count = excluded.com; 3) Write a data retention query: SELECT drop_chunks(interval '6 hours', 'raw'); 4) Then schedule the rollup query to run every 10 minutes, and the data retention query to run, e.g., every 3 hours. Note that the rollup query above will properly handle late data. A more detailed tutorial is in the works (e.g., using triggers for late data rather than the "20 minute sweep" trick as above). But also happy to help out in our slack channel: http://slack.timescale.com/ http://slack.timescale.com/
- mfreed 9y ago
  Realize that you might have just been asking about data retention policies, rather than continuous rollups (which is what the github issue is about). If so, data retention policies are easy with TimescaleDB: http://docs.timescale.com/v0.9/using-timescaledb/data-retention http://docs.timescale.com/v0.9/using-timescaledb/data-retent...

nickserv 9y ago

We're looking into Timescale to replace our InfluxDB servers as we've found InfluxDB to be resource-hungry, the lack of open source clustering is also a problem. The use of PostreSQL in Timescale is a big selling point, it's something we know how to manage and scale. A big stumbling block though is with the retention of older data. For now we do mainly service/machine monitoring and event logging without needing high granularity for older data and keeping disk usage contained. For this we've found InfluxDB to be well suited, as the retention policy system is easy to set up and fits our needs perfectly. Do you plan on introducing similar functionality in Timescale? I've found this issue in Github but don't know if this is something that is being pursued for the official product. https://github.com/timescale/timescaledb/issues/350 https://github.com/timescale/timescaledb/issues/350

mfreed 9y ago
Hi, thanks for the question about rollups. Timescale indeed enables you to do rollups, much like the functionality you describe. (Right now these are scheduled via a crontab, but in-DB scheduling is in the works.) But unlike InfluxDB (at least what we've been told by users), the important thing about TimescaleDB's rollups is that they correctly handle late data. So if you are performing a rollup every 10 minutes, but the data corresponding to 9:58 actually arrives at 10:05, it's easy to ensure that you subsequently reperform the rollup and "correct" that previous answer. This happens because we correctly support UPSERTS: http://docs.timescale.com/v0.9/using-timescaledb/writing-data#upsert http://docs.timescale.com/v0.9/using-timescaledb/writing-dat... You can also read more about one of our user's experience with rollups (compared to via Influx) in their recent blog post: https://blog.dnsfilter.com/3-billion-time-series-data-points-dnsfilter-replaced-influxdb-with-timescaledb-d9f827702f8b https://blog.dnsfilter.com/3-billion-time-series-data-points... As to that feature request in the github issue, it's actually asking for a much subtler and more powerful functionality. In particular, it wants the "rollup" to be hidden from the user, such that they don't worry about querying the raw vs. rollup table, and just see a single table view across multiple different levels of aggregation. Further, the planner would be smart enough which one to choose based on the time aggregation being asked for, as well as transparently merge data across different aggregation levels. This functionality is definitely on top of mind, but my understanding is that Influx doesn't support anything like this as well: If you have a rollup table there (say, to 10 minutes), you need to explicitly ask for data from the 10min rollup table, and you don't see any data that has been written in the last 10 minutes. TimescaleDB similarly supports this today.
- nickserv 9y ago
  Thanks much for your detailed reply. If I understand correctly, the way to do this is to: 1) Use PostgreSQL's built-in aggregate functions and/or Timescale API (time_bucket?) to feed rollup tables; 2) Delete the raw, non-aggregated data; 3) trigger these queries using cron according to need. This would be totally OK for us (even if in-DB scheduling is indeed easier), and as you say, in some ways superior to Influx. I will test it out. Do you have any tutorials or examples of using rollups in practice? Thanks!
  - mfreed 9y ago
    Pretty much, except I'd use our data retention mechanisms instead of #2 (directly deleting the rows), as it's much more efficient. So to review: 1) Create a "raw" and "rollup" table. Define the rollup table with the right UNIQUE constraint (say, on timestamp and server_id). Call create_hypertable() to convert them both to Timescale hypertables. 2) Write a rollup UPSERT query similar to following: INSERT INTO rollup VALUES ( SELECT time_bucket('10 minutes', time) as time, server_id, sum(value) as sum, count(value) as count FROM raw WHERE time > now() - interval '20 minutes' GROUPBY time, server_id) ON CONFLICT (time, server_id) DO UPDATE SET sum = excluded.sum, count = excluded.com; 3) Write a data retention query: SELECT drop_chunks(interval '6 hours', 'raw'); 4) Then schedule the rollup query to run every 10 minutes, and the data retention query to run, e.g., every 3 hours. Note that the rollup query above will properly handle late data. A more detailed tutorial is in the works (e.g., using triggers for late data rather than the "20 minute sweep" trick as above). But also happy to help out in our slack channel: http://slack.timescale.com/ http://slack.timescale.com/
mfreed 9y ago
Realize that you might have just been asking about data retention policies, rather than continuous rollups (which is what the github issue is about). If so, data retention policies are easy with TimescaleDB: http://docs.timescale.com/v0.9/using-timescaledb/data-retention http://docs.timescale.com/v0.9/using-timescaledb/data-retent...