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

51–60 of 146 posts

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

#51

Post by ukd1 »

dickeytk wrote: post #4
dpedu wrote: post #3

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

The author addresses Redis: > So, 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 precious relational data safe and consistent.…

You can disagree, but you're still at least partially wrong; if you're using postgres already, adding redis WILL make your system less reliable as you now have another point of failure to deal with (something else to patch, keep up to date, restart, etc, etc). Unless you have super high traffic, low latency requirements - using postgres adds some load, plus saves you having to write code to deal with rollback / race conditions with postgres vs a-redis-queue.

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

#52

Post by bufferoverflow »

madhadron wrote: post #48
bufferoverflow wrote: post #35

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

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?

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

#53

Post by ukd1 »

shandor wrote: post #7
dickeytk wrote: post #4

Earlier quoted context omitted.

The author addresses Redis: > So, 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 precious relational data safe and consistent.…

I was also wondering about the "safe and consistent" part. Doesn't Redis Streams with persistence solve those pretty well? Edit: oh, someone mentioned this being from 2013. No Redis Streams back then.

Ya, it doesn't mean streams - even if it's consistent in isolation, you're using it with Postgres - how do you make actions in one consistent with actions in the other? (TLDR: you won't/don't)

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

#54

Post by mlyle »

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…

Fewer pieces in the technology stack is better, because each piece is operationally expensive (it is its own set of work in maintenance, upgrades, migrations, etc). There are also the mentioned costs (you can't atomically do things across divergent pieces of infrastructure, at least not very easily at all).

Fighting the tendency of everyone wanting to draw in each new interesting bit of technology is important.

Every piece of complexity you incur should be proven as required, and you should build things to just somewhat larger than near term projected scale... and not expect that you are a temporarily embarrassed unicorn with loads of unexpected demand showing up.

If you run out of performance, you have some headroom from tuning; from that point, you can then choose whether it makes sense to retrofit to a higher scale technology or throw larger hardware at the problem (or both).

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

#55

Post by elamje »

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

Recently was wishing this myself. I’ve been using rq, which although I hear is much faster than an rdbms is also another dependency and service to worry about failing. Trying to keep things simple is nice, I am already having trouble trying to grok what kind of weird state I can have in my application if Redis goes down, or my dB goes down but Redis remains

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

#56

Post by mlyle »

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?

In a decent job queue you know when jobs get completed, whether the worker died, and whether they're otherwise stuck.

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

#57

Post by ukd1 »

ukd1 wrote: post #50
faizshah wrote: post #44

Earlier quoted context omitted.

Did you guys ever evaluate Que? What made you choose QueueClassic?

We did; but I think it came around after we started. It was faster at that time, we looked at also it's well written - we'd probably have used it if it was out, but we didn't really mind about the speed - we're not latency sensitive for what we're doing. Fast forward a little, and we now maintain QueueClassic, and it's now comparable / slightly faster.

Maybe chanks recalls differently; but I think the backstory was he saw / was interested in this - but wanted to use newer features that came out in Postgres. QueueClassic's first release was early 2011; it wasn't fast support newer Postgres stuff, mostly as it was being used a lot (inside Heroku) and wanted to maintain support for users. I think (might recall wrong) that Chris ended up writing Que ~2013 because of QC was too slow to update / didn't want to do these features!

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

#58

Post by ukd1 »

truth_seeker wrote: post #29
dpedu wrote: post #3

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

Redis would be faster. In PG you can use UNLOGGED table which is basically a in-memory table.

UNLOGGED: You could, but if you get some types of crash, you'd be in trouble. More on non-durable settings here - https://www.postgresql.org/docs/current/non-durability.html

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

#59

Post by [deleted] »

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…

[deleted]

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

#60

Post by ukd1 »

jimktrains2 wrote: post #32
z92 wrote: post #25

Earlier quoted context omitted.

// 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. // Not necessarily. You lock the row for an instant update to a field, for example called "status" into "running" and then disconnect from the database within milliseconds. Finish your job taking as much time as you want…

What happens when the worker processing the job dies and never updates the status?

Some of these systems also check if the worker running the job is still alive, then if it's not kill the transaction. The job then restarts. Also, you can do this by time - when did it lock it, how long does it have to complete before you nuke. Of course there are edge cases, but you have to make a choice of how to deal with this; just leaving it means doing it manually.
Post reply on HN