Job scheduling: save nextExecution in the DB to be more resilient to service outages

[matthiasfeist]

As far as I understand it, pgBoss requires that an instance of the service is running at the exact time when a job is scheduled to be executed.
If my service happens to be restarting, deploying or whatever during that time, the scheduled job will not get executed.

If I understand that correctly, maybe a suggestion could be to save the next scheduled execution time in the DB on the schedule. When the service comes online, bgBoss can then check if the next_scheduled_time is in the past. If it is, that indicates that an execution was missed and the event can be fired right then and there.

This should probably be configurable, of course.

What do you think?

[sudall]

I've thought about this as well and came up with this solution for now:

    const singletonHours = 3;

    await jobs.schedule(
      name,
      CronTime.every(singletonHours).hours(),
      undefined,
      {
        singletonHours: singletonHours,
      },
    );
    // run once on start in case the server was down for a while
    await jobs.send({
      name: name,
      options: {
        singletonHours: singletonHours,
      },
    });

The way I understand it, this should only allow one job to be created in each 3 hour window (0-3, 3-6, 6-9, etc.), then a job is attempted for the current window on start up.

I was just experimenting with it and discovered however that singleton_on column is not being set on the cron-created jobs, for some reason. I'm going to make an issue.

[timgit]

I think the idea is great, but I'm not confident on what the implementation should look like quite yet. The queue table could store the previous and next scheduled job based on one or more schedules that may exist, and that could be checked on a schedule, for example. On the other hand, the timekeeper monitoring already tolerates brief outages during job processing, which is why per-second cron is not currently supported (it looks for any missed jobs in the last 30-60s).

[matthiasfeist]

I just randomly stumbled over another postgres based queueing library that seems to have that feature. I haven't looked into how it's implemented but it might be interesting to take a look for inspiration: https://worker.graphile.org/docs/cron#limiting-backfill