zlacker

>>hyzyla+(OP)
You really don't need anything fancy to implement a queue using SQL. You need a table with a primary id and a "status" field. An "expired" field can be used instead of the "status". We used the latter because it allows easy retries.

1. SELECT item_id WHERE expire = 0. If this is empty, no items are available.

2. UPDATE SET expire = some_future_time WHERE item_id = $selected_item_id AND expire = 0. Then check whether UPDATE affected any rows. If it did, item_id is yours. If not, loop. If the database has a sane optimizer it'll note at most one document needs locking as the primary id is given.

All this needs is a very weak property: document level atomic UPDATE which can return whether it changed anything. (How weak? MongoDB could do that in 2009.)

Source code at https://git.drupalcode.org/project/drupal/-/blob/9.2.x/core/... (We cooked this up for Drupal in 2009 but I am reasonably sure we didn't invent anything new.)

Of course, this is not the fastest job queue there is but it is quite often good enough.

>>_ugfj+z2
Oh please stop using databases as queues. I spent a disproportionate amount of time in the last 20 years undoing that decision. It doesn’t scale at all well.

>>hughrr+gc
For the scale of most tasks, the existing RDBMS that a small app is using for its data is perfectly sufficient for the queuing needs of said app. This reduces complexity in the app and removes the need to maintain additional tools.

Frameworks like Laravel make this easy because they allow you choose different backends for your DB, queue, and cache, among other things, all or some of which can be an RDBMS like Postgres or MySQL/MariaDB.

When your app's need outscale what the RDBMS can rationally handle, then you can dedicate the resources to switch to a more specific tool as your growth has indicated you should.

I can't say what the percentage of apps that can handle things this way, but the anecdotes mentioned in this thread are clearly the cases where growth outstripped what a traditional RDBMS like Postgres are well-suited for, and so migrating was the right thing. The probably was likely identifying the need or delaying the transition until it was too painful. I don't think there's a magic bullet here, but I also don't think that starting with a dedicated queuing store tool is also always the right thing to do.