__pgboss__cron taking long to complete and blocking main thread

[thakkaryash94]

@timgit First of all, thank you so much for creating this package. I need your help with the below scenario.

I have a mobile application where I am sending daily notifications to the user's selected preference time. eg. 8:00 AM, 3:15 PM with timezone. So I have created schedules for everyone like below. I am using NestJS.

boss.schedule(notification-job-${user.id}, ${minute} ${hour} * * *, obj, { tz: user.currentTimeZone })

Currently, there are around 1300 users, meaning 1300 records schedules table. The problem is '__pgboss__cron' is taking around 20 seconds and it is running on main thread so for every minute's first 20-30 seconds, the actual request goes into pending state, after the job is completed, the request takes max 400-500 ms to respond. Below are the logs.

Before deleting the schedules

"createdon","startedon","completedon","diff"
2022-03-08 13:03:01.488 +0530,2022-03-08 13:04:01.492 +0530,2022-03-08 13:04:01.530 +0530,"00:00:00.037963"
2022-03-08 13:02:01.455 +0530,2022-03-08 13:03:01.465 +0530,2022-03-08 13:03:01.490 +0530,"00:00:00.0248"
2022-03-08 13:01:21.429 +0530,2022-03-08 13:02:01.439 +0530,2022-03-08 13:02:01.456 +0530,"00:00:00.017288"

To check the issue, I deleted all the schedules and now the jobs are completed in ms.
After deleting the schedules

"createdon","startedon","completedon","diff"
2022-03-08 13:00:21.714 +0530,2022-03-08 13:01:01.730 +0530,2022-03-08 13:01:21.432 +0530,"00:00:19.702801"
2022-03-08 12:59:22.034 +0530,2022-03-08 13:00:02.055 +0530,2022-03-08 13:00:21.715 +0530,"00:00:19.660851"
2022-03-08 12:58:22.287 +0530,2022-03-08 12:59:02.298 +0530,2022-03-08 12:59:22.037 +0530,"00:00:19.738827"

So can you please help me with how can I improve this?

[timgit]

Thanks! I have not run pg-boss in a mobile application before, so the concept of "main thread" is not something I have a lot of experience in mitigating. However, I think scheduling could be optimized to improve latency since each scheduling interaction is a distinct call to postgres. If the cause is related to very popular schedule times within the same zone, the current usage of pMap() over the matched schedules in timekeeper.js would need to be replaced with a batch insertion technique that is still compatible with upserts to honor the current debouncing configuration used.