Add native scheduler which executes SQL queries (similar to InfluxDB tasks).

[newskooler]

The problem
Usually QuestDB users would have very large time-series table. Often these tables need to be re-sampled in some fashion in order to be easier to read / use. One way to do this is via cron (on Linux) or via other time-based schedulers (Airflow, Luigi, Prefect, etc.). All of the other scheduler would require additional setup on the user end - some much more than others.
Also, some would be slower to run and quite heavy - thus if your job is time sensitive, execution will be slower.

Proposed simple solution
InfluxDB offers Tasks - a native time-based scheduler. I propose that QuestDB adds such a native scheduler for SQL code to run, thus enabling such re-samping from table A to table B to be done (as well as anything else doable via SQL). This will make such a time-based SQL code quick to setup and maintain.

Proposed advanced solution features

• Ability to schedule based on DB event (count / sum / etc.) vs time.
• Ability to re-trigger failed execution attempts
• Run sub 1min (cron is limited to 1 min schedules, so this would be quite valuable)
• Be super fast and low
• Ability to switch it off (so that ppl who don't use it still run QDB in a lighter way)

I believe many of you will find this extremely useful, so I thought I'd open a discussion on the subject here as well. You can find the original issue here.

If you have proposals on how to improve this idea - please share. 👍
If you like the idea - it's important you give it a thumbs up, so the QDB team can prioritize accordingly. 🥇

[ospqsp]

a watch condition would be good (perhaps what you mean by DB event) to trigger the code

[newskooler]

Yes - exactly.

[nicgrobler]

Sounds good - one thing that often gets overlooked though is making ‘metrics’ easily available to show which tasks are being run, how long they take etc.

[newskooler]

I agree it's nice to have all these things, however the only thing that worries is me that QDB is about performance and speed. Such thing may weigh very heavily on it.

[javier]

Is this something that could be maybe solved also with a view rather than a scheduler? It would be awesome if the view can be materialized and auto-refreshed, but even a regular view might already be good enough for the use case you mention of exposing lower-resolution easier to consume data

[newskooler]

Can you expand on this more? A view is simply an SQL query, right? I don't see how this is the same.

[javier]

A view is simply a SQL query, but for the problem you mentioned "Often these tables need to be re-sampled in some fashion in order to be easier to read / use", this might be good enough, if all you need is to expose the data so other tools downstream can see the re-sampled/filtered data.

Of course if you are concerned about the execution speed of those queries and you would prefer to create a second table with the sampled data, then a materialized view could do the trick. A materialized view takes the output of a view and materialize it on an internal table. Then every time you want to get fresh results, you need to run a REFRESH (or similar) statement. Some data stores also support autorefresh when the underlying data is updated.

So I was thinking if this could be an alternative to the native scheduler

[newskooler]

Thanks for the explanation.
I'd say it would be great if we both exist. I do care a lot about the speed and have found that a query is just too slow and also too impactful on the RAM.