Ordered processing by key

[tpodom]

Hi, we are looking at using pg-boss for a couple of use cases. One of the use cases would need something similar to singletonKey but instead of blocking the job insertion we would like to add the job but only allow one message to be consumed at a time for that key value. Does anyone have any thoughts on whether this could be achieved?

[timgit]

I'm assuming in your question that you want concurrency as high as possible, but only one worker per key. This can't be achieved in pg-boss in isolation (yet).

One option I think could work that has a fairly low performance impact would be adding a new unique constraint (singletonKey WHERE state='active') and then add an ON CONFLICT (singletonKey) DO NOTHING in the fetch command's update to fail gracefully when attempting to put the second job into active state.

[tpodom]

Ah ok, that seems like that would probably work. I think you understood the use case. One part I think that wasn't clear is that ultimately what we're trying to achieve is ordered sequential processing of jobs that have the same singletonKey. But to allow workers to process any other jobs concurrently.

Thanks!

[timgit]

The only caveat I see with this approach is batching would be unpredictable. Since the fetch could bring back multiple keys in a batch, if any of them fail, it will block the entire batch. This is mitigated by keeping teamSize/batchSize defaulted to 1 job. Hopefully that makes sense