3 ms·

Hi, thanks for the question about rollups. Timescale indeed enables you to do rollups, much like the functionality you describe. (Right now these are schedule

by mfreed 8y 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 8y 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 8y 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/

by mfreed 8y ago

nickserv 8y 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 8y 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/