Hacker News Reader

Comment by atombender | Hacker News Reader

Whenever this comes up, I argue for moving the queue orchestration out of Postgres. Everyone will be happier that way.

By this I mean that you keep your jobs in Postgres, but instead move the ordering -- the physical queue data structure -- out into something like RabbitMQ or Redis.

To schedule a job, create a job row, then push its ID to Rabbit or whatever. To process the next job, grab the next ID, look up the job row, then do the processing.

That gives you the best of both worlds, without ever running into issues with locking and dead tuples.

(There is of course a race condition here where you may create a job but not publish it (due to Rabbit temporarily failing, for example). But there are ways around that.)

View on HN

I would argue that is not a pragmatic approach. The article makes a good point about the huge benefits of this approach for relatively small workloads. Transactional guarantees are hugely useful! Being able to rollback something like a user create including a queued job to send out a welcome email is incredibly nice.

IMHO HN conversations tend too much toward solving problems at google scale. We often forget here that there are tons of high-value business critical systems that generate mere 10s, hundreds, or a few thousand background jobs a day. Given the numbers quoted in the article, DB backed queue systems scale quite far. I've successfully used them to handle tens of thousands of events per day with no problem over a timespan of years. It's clear that this is a great approach for some situations, and that one size fits all doesn't make sense here.

> Transactional guarantees are hugely useful! Being able to rollback something like a user create including a queued job to send out a welcome email is incredibly nice.

That would work in this case - the job is added to the postgres table in a transaction, which eventually rolls back. The ID of the job was put into a separate queue. When the queue consumer tries to get the job from the database by ID, it doesn't see it because the transaction never committed. It can discard that job ID from its queue.

Yes but then you need to support a whole additional piece of infrastructure and you have to write and support more code. For a lot of use cases that's a waste of time and money. This is a case of YAGNI for the use cases I mentioned.

As a counterpoint, I would say "is it pragmatic to solve complex database issues in the middle of the night?" or "Do you prioritise fire safety or fire fighting"

Using the wrong tool for the job will sometimes be pragmatic. But sometimes it will burn you, burn you hard, and it'll always be in the middle of the night (note the article's graph starts at midnight :-)). As long as you accept that, all is good in the world!

What often happens though is that people set out with a simple aim, and rather than spending a litle bit of time honesting looking at what's out there, they re-invent the wheel. Because "How hard can it be?"

one option to push events (jobs) into RabbitMQ using NOTIFY is this little thing https://github.com/subzerocloud/pg-amqp-bridge

Reply on news.ycombinator.com