zlacker

I'm increasingly of the opinion that relational databases are absolutely the right way to build queue systems for most projects.

One of the biggest advantages comes when you start thinking about them in terms of transactions. Transactional guarantees are really useful here: guarantee that a message will be written to the queue if the transaction commits successfully, and guarantee that a message will NOT be written to the queue otherwise.

https://brandur.org/job-drain describes a great pattern for achieving that using PostgreSQL transactions.

>>simonw+(OP)
The transaction feature seems nice but how often is your application dropping queue messages because something happened between tx.commit() and queue.send(msg)? My experience has been that this is not an issue.

>>daenz+Q1
If you're big enough to worry about the scalability of Postgres, you're big enough to experience this failure fairly often IMO.

>>peterh+T3
Scalability was the second of two concerns I listed. The first was additional application complexity that real message queues hide from you by virtue of being a system built for that usage pattern.

>you're big enough to experience this failure fairly often IMO

Please explain how? You would either have to suffer from frequent network connectivity issues that affects only your db and not your queue, or your process must be mysteriously dying in the microseconds between those 2 operations. Either of those cases are not something I would consider things that happen "fairly often," even if you were processing trillions of messages per day.

In my experience, the vast majority of message processing failures happen at the worker level.

>>daenz+Q1
Oh that happens fairly often. In fact, some message will be lost every time your queue server reboots due to a power outage, PSU failure, kernel panic, OOM, etc. (Unless it spends almost all of its time idle in which case I guess no messages will be in flight)

You’re guaranteed to break the invariant sooner or later so you end up with all the usual complexity of keeping stuff in sync.

>>NavinF+99
Your queue server rebooting is completely orthogonal to whether the application submitting the message can do so atomically or not. Use a cloud service if you care about durability.

Edit>> I see you edited your post after I responded. None of those scenarios qualify as "fairly often."

>>daenz+Q1
I am SUPER worried about this when it affects something really important, like getting the client/customer/merchant/cardholder their money. It seems like the world has moved on without me —- “ohh, three nines is enough”… “hrmmm maybe?…”

>>daenz+J9
Wish we would stop saying "use a cloud service" for everything. People can and do operate their own hardware, manage their own databases, and build and maintain their own application stacks. I don't know how we got to this point of learned helplessness where now we just have to use cloud providers for everything.

>>daenz+n4
If the queue goes down you end up updating the db without enqueuing a job and now an engineer needs to go in and re enqueue the missing jobs manually.

>>VWWHFS+dg1
It's not learned helplessness to avoid re-inventing the wheel. At the end of the day, the goal is to deliver value to customers, not invent a queueing system, unless your company's product is queueing systems.