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

61–70 of 146 posts

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

#61

Post by biggestdecision »

bloody-crow wrote: post #23
Exuma wrote: post #18

Earlier quoted context omitted.

Is that fixed by the VACUUM process?

No, cause VACUUM can't kill those dead tuples while transaction is still running. Think of it this way... When you open a transaction, you need to have a guarantee that you can touch rows that existed at the moment when transaction has started. You job queue is chugging along and processes let's say a 1000 jobs per minute. Processing a job involves deleting the row from the queue, but since you have a transaction run…

I wonder if this could be solved by moving in-progress jobs to a separate table...

Guess you lose job atomicity that way.

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

#62

Post by bgentry »

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?

It helps to reference Que's schema [1] and source code to explain this further. But I'm also going from memory so it's possible I will miss some details :)

* If the entire worker process dies, then it will lose its Postgres connection which is holding an advisory lock on the jobs being worked. This releases those jobs to be worked by another worker. I don't recall how the built-in retry & back-off mechanisms work in this scenario. This advisory lock is indeed held for the entire time the jobs are being worked on, but only from a single supervisor connection (rather than one connection per job).

* If the job thread crashes, the worker supervisor catches this and the job is marked for a retry at a later time.

[1]: https://github.com/que-rb/que/blob/master/lib/que/migrations...

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

#64

Post by icheishvili »

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?

Message queues achieving the rate you said are typically not durable.

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

#65

Post by threeseed »

mlyle wrote: post #54
dimino wrote: post #45

Earlier quoted context omitted.

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…

These days it's pretty trivial to have a cloud managed component e.g. Redis that is maintained, upgraded and supported.

And then you have a component that is designed for the job instead of trying to use a database as a poor man's queue.

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

#66

Post by mlyle »

threeseed wrote: post #65
mlyle wrote: post #54

Earlier quoted context omitted.

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…

These days it's pretty trivial to have a cloud managed component e.g. Redis that is maintained, upgraded and supported. And then you have a component that is designed for the job instead of trying to use a database as a poor man's queue.

> These days it's pretty trivial to have a cloud managed component e.g. Redis that is maintained, upgraded and supported.

It's still another moving part. Thing should be as simple as they can be, but no simpler.

> trying to use a database as a poor man's queue.

Of course, it's not really a "poor man's" queue-- it's got some superior capabilities. It just loses on top-end performance. (Of course, using those capabilities is dangerous, because it creates some degree of lock-in, so go into it open-eyed).

For as much as you accuse others of looking down their nose / not willing to seriously consider other technologies... you seem to be inclined that way yourself.

Redis is great. But if you have Postgres already, and modest to moderate queuing requirements, why add another piece to your stack? Postgres-by-default is not a bad technology sourcing strategy.

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

#67

Post by codetrotter »

elamje wrote: post #55
aldoushuxley001 wrote: post #37

Earlier quoted context omitted.

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

> 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

If it is possible to set up a smaller test environment with one or a few instances of your DB and Redis, and you have time to do it, you could do some testing where you purposely shut down each of them at various points in time and inspect what happens to your application state compared to how it behaves when all is well.

Perhaps you could even outfit the testing version of your application with two proxies, one that will proxy the DB connection and one that will proxy the Redis connection, and have these proxies randomly decide within some threshold whether to pass the data along to their upstreams, or to simulate a broken connection. Then test the application with some different threshold values, for example, 1% probability of failure, 5% probability of failure, 50% probability of failure and 100% probability of failure. Repeat the tests some number of times for each threshold value and observe the behavior of the application each time. Also, make sure that you log the decisions made by the proxies each run, so that you are able to look at these afterwards.

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

#69

Post by oftenwrong »

dickeytk wrote: post #16
tracer4201 wrote: post #8

Earlier quoted context omitted.

If your requirements don’t necessitate such scale, I can see Postgres being a viable alternative. I’d rather not introduce another dependency unless it’s absolutely needed.

I’ve never in my life worked on something where we didn’t keep having to upgrade our database resources

When you scale your db, now you're scaling your queue, too. One thing to scale, one thing to monitor.

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

#70

Post by ukd1 »

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…

It's not bad-engineering at all; how many engineers have to operate at that level in your stack, ever. Close to zero, as normally they just use an abstraction on top of it - Rails for instance is ActiveJob. No one has to care what's behind it, except folks running production - and for them it's a trade off; it's simpler - one less moving part, but also more load on one single part. Is that worth it, or not? Depends.

I can't stress this enough; claiming anything is "vastly superior", especially with no examples, isn't useful - things aren't this black and white in reality. Most tech-choices like this are somewhere between trade-offs, preferences, ignorance or wrongly held opinions about something being vastly superior > something else.

Post reply on HN