Do I need a queue manager over the database generating execution requests?

Question

I am developing a multi-user application where a user interacts with the UI and submits execution requests (ER). Each user can submit multiple ERs one after the other and multiple users may submit ERs at the same time. Each ER will take roughly 3-5 hours to finish and use one or more cores for execution. The user is informed via email when their ER is finished and they can log-in again to view the results of the analysis.

I write the ERs to a PostgreSQL database and use NodeJS server as the execution manager. The task of the NodeJS process is to execute the set of tasks that form the ER submitted by a user. Currently, I use a database trigger to generate a notification and "watch" for the notification in my NodeJS process (inspired from this source).

The relevant code looks like below:

    pgClient.on('notification', async (data) => {
        const payload = JSON.parse(data.payload);
        console.log('row added!', payload)
        const userid = payload.user_id;
        const simdatetime = payload.sim_date_time;
        const status = payload.status;
        const a_id = payload.analysis_id;

        if (status == 'inserted') {
            console.log("Row inserted successfully - begin the process")
            console.log("Invoking R script... at:", rscript_update_dc);
            callR(rscript_update_dc, a_id)
                .then(result => {
                    console.log("finished with result:", result);
                })
                .catch(error => {
                    console.log("Finished with error:", error);
                });
        }
    })

The NodeJS process receives the data payload and runs the Rscript that performs the analysis. I am wondering do I need a queue manager like bull to manage the incoming ERs or is this design sufficient? I am wanting to create a basic workflow that works and then make it robust over time. I am mainly concerned because bull utilizes another data store (Redis) and makes me wonder if I am introducing un-necessary redundancy since I am already using a persisting database that stores the ER_id (primary key) and submission time (timestamp). Also, I think given the uncertain computational requirement of an ER, I feel sequential ER execution may not be the most optimal.

Answer 1

I think since you are targetting a robust system and concern about performance, it is better to use a queue system for Job execution and set up priority.

I am not seeing any fault or problem storing ER and using triggers from the database but my question is why should we use triggers when we can use a fault-tolerant job queue system which is ideal for such scenarios.

So the alternate architecture can be like following

Users will submit their single or multiple ER requests which will be queued using any queue system say Bull and at the same time, we will keep the entry in the database to track progress, the status of ER which will show the exact status of each Job.

At any point base on configuration on failure Bull will take care of retires or job adjustment as per priority.