4 ms·

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

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/