research if the scheduling logic should be placed in the database or in application backend

a22erigr commented

2026-05-05 09:36:31 +00:00

Collaborator

Description

When implementing #727 the developer noted that a design decision needed to be made. Where should the "scheduling" logic be placed. This needs to be researched.

What needs to be done

Research where the "scheduling" logic should be placed. Leave process and cones and a conclusion/recommendation.

## Description When implementing #727 the developer noted that a design decision needed to be made. Where should the "scheduling" logic be placed. This needs to be researched. ## What needs to be done Research where the "scheduling" logic should be placed. Leave process and cones and a conclusion/recommendation.

a22erigr added the

backEnd

research

SQL

labels

2026-05-05 09:36:31 +00:00

a24vinla was assigned by a22erigr

2026-05-05 09:36:31 +00:00

a24vinla commented

2026-05-05 11:56:47 +00:00

Collaborator

We have two different choices regarding where scheduling logic for database tasks should be placed: in the postgres database or in the backend as a new service.

Database

If we decide to add it directly to the postgres database we would have to use an extension, and my recommendation is pg_cron. pg_cron, as far as I can gather, seems to be a standard extension for handling scheduling of tasks within databases (referenced in AWS postgres manual). With this option the scheduled database tasks would always run regardless of connection to our application services. It would however require us to revamp the postgres image/setup we are currently using, since the base postgres image does not have the pg_cron extension. This solution will require more time (i.e. sub-issues) but should not result in us having to change any existing communication with the DB.

Service/backend

The second option is having scheduling logic as a sub-service to our main service (like tempSensors). We would be able to use ASP.NET built-in scheduling, requiring no additional dependencies. This approach is more in line with how our project is structured and would allow our scheduling logic to be more understandable and more easily documented. It would also allow us to more easily integrate user/role-specific tasks which need to be scheduled. The main flaw would be, as is stated above, that it would require two components to run (service and database), meaning additional points of failure.

Conclusion

The main factors to take into consideration when making the choice between the two alternatives is if the scheduled tasks will require external information and the complexity of them. In the case of issue #727, where the logic is basic and requires zero external information, I would choose option 1. It is also important to note that these two aren't mutually exclusive, meaning future, more complex tasks requiring external data could use option 2.

We have two different choices regarding where scheduling logic for database tasks should be placed: in the postgres database or in the backend as a new service. ### Database If we decide to add it directly to the postgres database we would have to use an extension, and my recommendation is pg_cron. pg_cron, as far as I can gather, seems to be a standard extension for handling scheduling of tasks within databases (referenced in [AWS postgres manual](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html)). With this option the scheduled database tasks would always run regardless of connection to our application services. It would however require us to revamp the postgres image/setup we are currently using, since the base postgres image does not have the pg_cron extension. This solution will require more time (i.e. sub-issues) but should not result in us having to change any existing communication with the DB. ### Service/backend The second option is having scheduling logic as a sub-service to our main service (like tempSensors). We would be able to use ASP.NET built-in scheduling, requiring no additional dependencies. This approach is more in line with how our project is structured and would allow our scheduling logic to be more understandable and more easily documented. It would also allow us to more easily integrate user/role-specific tasks which need to be scheduled. The main flaw would be, as is stated above, that it would require two components to run (service and database), meaning additional points of failure. ### Conclusion The main factors to take into consideration when making the choice between the two alternatives is if the scheduled tasks will require external information and the complexity of them. In the case of issue #727, where the logic is basic and requires zero external information, I would choose option 1. It is also important to note that these two aren't mutually exclusive, meaning future, more complex tasks requiring external data could use option 2.

a24vinla commented

2026-05-05 11:57:49 +00:00

Collaborator

A third, secret option would be to run the tasks through shell commands and schedule them using OS-level cron/other scheduler. This would allow us to group multiple scheduled tasks together, disregarding of which database/service they are connected to. I do not recommend this option since it seems overly complicated, but wanted you to be aware of its existence.

a24vinla added the

needs review

label

2026-05-05 11:58:02 +00:00