Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Turning PostgreSQL into a queue serving 10k jobs per second (2013)

gist.github.com

Post reply on HN
Print view View original

101–110 of 146 posts

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#101

Post by michelpp »

aldoushuxley001 wrote: post #37
bgentry wrote: post #15

The author of this post, Chris Hanks, created the Que queueing library for Ruby: https://github.com/que-rb/que It’s changed significantly since this post as the 1.x betas use a very different structure which should actually be more efficient, use fewer Postgres connections, cause less lock contention, and cause less table bloat. Not sure if the benchmarks have been run recently or not but I’m definitely curious how t…

Wish I could use that in a Django app. Doesn't seem to be a viable python queueing library that allows using postgresql

There is a Python library that uses SKIP LOCKED https://github.com/malthe/pq

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#102

Post by balfirevic »

adverbly wrote: post #85
Exuma wrote: post #20

Earlier quoted context omitted.

Because Que has transactional/ACID guarantees, and Redis does not.

Actually, redis makes several guarantees. Just not as acid as rds. Source: https://redis.io/topics/transactions

Enqueueing jobs in Redis is not transactional with regard to your primary data store.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#103

Post by zahreeley »

bgentry wrote: post #27
Diggsey wrote: post #11

I have also found the lack of transactional guarantees in typical job queues to be very problematic. One problem with using PostgreSQL in this way (using either advisory locks or LOCK FOR UPDATE) is that it requires you to keep an open connection to the database whilst the job is being worked on. For a MySQL database, this would be just fine, but PostgreSQL uses a process-per-connection model which caps the number of…

I elaborated a bit on this elsewhere ( https://news.ycombinator.com/item?id=21537414 ) but Que’s design has changed significantly and no longer holds open a connection or transaction for the duration of working a job. It holds one database connection per worker process. Each worker process handles all job locking and assignment to the individual worker threads in that process, each of which only use connections that…

Good job

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#104

Post by qaq »

voldacar wrote: post #81

I'm not very knowledgable about db internals so sorry if this comes off as ignorant, but in an era where cpus execute billions of instructions per second per core , is 10000 jobs per second supposed to be impressive? Is this kind of problem bottlenecked by memory?

by IOPS you fsync transaction to Write Ahead Log (WAL) on commit.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#105

Post by wjd2030 »

bufferoverflow wrote: post #35

Now compare to OpenAMQ, ZeroMQ, RabbitMQ, NSQ, Kafka. I have seen benchmarks reaching millions of messages per second.

Exactly. It is so easy to achieve using a dedicated queuing system. You could just as easily achieve much higher throughput by passing a jobId as a message to Rabbit and have the worker pop the required data out of the db. Thats assuming you dont want to just pass in a serialized object. All this talk of it not being a durable system is just wrong. Rabbit has strong durability guarantees across a cluster with queue mirroring, confirmations and acknowledgements.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#106

Post by Diggsey »

bgentry wrote: post #27
Diggsey wrote: post #11

I have also found the lack of transactional guarantees in typical job queues to be very problematic. One problem with using PostgreSQL in this way (using either advisory locks or LOCK FOR UPDATE) is that it requires you to keep an open connection to the database whilst the job is being worked on. For a MySQL database, this would be just fine, but PostgreSQL uses a process-per-connection model which caps the number of…

I elaborated a bit on this elsewhere ( https://news.ycombinator.com/item?id=21537414 ) but Que’s design has changed significantly and no longer holds open a connection or transaction for the duration of working a job. It holds one database connection per worker process. Each worker process handles all job locking and assignment to the individual worker threads in that process, each of which only use connections that…

That is a very neat solution. The one downside I see is that you need to be much more careful about gracefully handling errors within workers.

For example: if a single worker thread crashes but the process doesn't realise it, it may be possible for jobs to become stuck, because other workers rely on the process releasing the job back to the queue. It's definitely solvable but something to be aware of.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#107

Post by outworlder »

dimino wrote: post #45
n4r9 wrote: post #9

Earlier quoted context omitted.

You may be right in terms of performance (I've no idea) but article states: > many developers have started going straight to Redis-backed queues (Resque, Sidekiq) or dedicated queues (beanstalkd, ZeroMQ...), but I see these as suboptimal solutions - they're each another moving part that can fail, and the jobs that you queue with them aren't protected by the same transactions and atomic backups that are keeping your p…

I find this argument to be pretty weak, and seems to be a stand-in for, "I don't want to use a new technology because it'd require me to learn something, so I'm going to shoehorn something I feel more comfortable with, even though it's not the best tool for the job." Edit: I'm rate limited so to elaborate a bit. Queueing technologies are not "new interesting bit of technology", they're tailored solutions to solve a s…

> I cannot stress this enough; fear of learning is anathema to software, and trying to hide it behind a veneer of caution is not only disingenuous but potentially malicious as well, maximizing exclusively for the benefit of the individual against the interests of the group.

This is wrong.

You are ultimately getting paid to solve the company's problems. If those problems actually require deployment of a new technology to satisfy the requirements, then it is fine. Note that I said satisfy requirements, not exceed them.

But more often than not, * that's not the case *. If your queue requirements can be served with PostgreSQL, and you already have it, then why not? To do otherwise would be over engineering.

Because you may know a new and fancy technology that would be objectively better. Cool. Now, do you have enough know-how in the company to use it effectively? Do you know what the best practices are? Can you deploy, monitor, audit and patch this in production? If you have answered NO to any of those questions, you'll either hire people or you shouldn't deploy. Period.

And if you have only one person answering YES to the previous questions, you still shouldn't deploy it. Because one day that person will leave, and you are now left the company in a worse situation than it was before. For no reason other than satisfying your engineering itch.

THAT is anathema to working, reliable production software. Toys, you can do whatever.

If you want to introduce something new, you can. But you need to do it responsibly. It needs a reason to exist - a valid reason, not "it's better". How does it being "better" help the company? Will it reduce costs? Maintenance? Future development work will happen faster? Will it make a faster user experience? Scale to the projected company growth?

It will have a cost that will have to be accounted for – including opportunity costs.

> By trying to use one tool for everything, you lose out on all kinds of optimizations, features, and performance enhancements that are specific to the problem you're trying to solve.

You only care about this if you have a reason to care about this. Otherwise it's all irrelevant.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#108

Post by wjd2030 »

bufferoverflow wrote: post #52
madhadron wrote: post #48

Earlier quoted context omitted.

The article is discussing job queues, not messaging. The MQ systems plus Kafka that you are referring to are message transport systems.

What's the actual difference between a job queue and a message queue filled with job IDs?

The difference is nothing. A message can be a serialized object representing a job. Or the message can be a jobId that points to a record in a db.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#109

Post by wjd2030 »

mobilemidget wrote: post #63
dpedu wrote: post #3

How does this compare to Redis? Seems like Redis would handily beat it.

I'm still waiting for a work project to come by where I can replace Redis with https://keydb.dev and test that in action.

I have tried this in our dev environment several times and failed b/c it didnt support all RESP2 commands (Using stackexchange.redis as the lib)

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#110

Post by grumpy8 »

bgentry wrote: post #27
Diggsey wrote: post #11

I have also found the lack of transactional guarantees in typical job queues to be very problematic. One problem with using PostgreSQL in this way (using either advisory locks or LOCK FOR UPDATE) is that it requires you to keep an open connection to the database whilst the job is being worked on. For a MySQL database, this would be just fine, but PostgreSQL uses a process-per-connection model which caps the number of…

I elaborated a bit on this elsewhere ( https://news.ycombinator.com/item?id=21537414 ) but Que’s design has changed significantly and no longer holds open a connection or transaction for the duration of working a job. It holds one database connection per worker process. Each worker process handles all job locking and assignment to the individual worker threads in that process, each of which only use connections that…

> I believe this model is almost always the right starting point for a web application, barring some unusual job requirements or massive initial scale.

Would you run this job queue on the same postgres database as the rest of the application or rather use a different one specific for workers?

Post reply on HN