Re: PostgreSQL as advanced job queuing system

On Fri, Mar 22, 2024 at 6:58 AM ushi <ushi(at)mailbox(dot)org> wrote:

> Hello List,
>
> i am playing with the idea to implement a job queuing system using
> PostgreSQL. To meet requirements the system needs to offer some advanced
> features compared to "classic" queuing systems:
>
> - users can create new queues at any time
> - queues have priorities
> - priorities of queues can change at any time
> - jobs in queues with the highest priority should be processed first
>

You can definitely do this. I wrote an enterprise scheduler, called
pgtask, which ochestates a very large amount of work each night.. Here's
a couple of screenshots. It's running a distributed analytics
enterprise analytics batch environment in the vein of airflow. Here's a
couple of screenshots. It's a single threaded stored
procedure architecture that uses dbink calls to distribute the work.

https://imgur.com/a/UqZt63V

https://imgur.com/a/68wyWBL

I'm running at a pretty high scale and it's reliable. It's not really
positioned as a proper queue, but more of a graph orchestration system, and
it supports realtime and quasirealtime. The main bottleneck is that dblink
does not have epoll style 'wait for first to finish or fail' (it really
needs to), forcing a loop in sql which bounds the active connections a bit.

Being able to manage the state explicitly in the database is wonderful, if
you know what you're doing.

merlin