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

31–40 of 146 posts

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

#31

Post by s0l1dsnak3123 »

ianai wrote: post #28
bgentry wrote: post #27

Earlier quoted context omitted.

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…

Is this what you’re talking about? https://github.com/que-rb/que

Yep!

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

#32

Post by jimktrains2 »

z92 wrote: post #25
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…

// 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?

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

#33

Post by bloody-crow »

bgentry wrote: post #24
bloody-crow wrote: post #14

One of the interesting unforeseen downsides of RDBMS-based queues is explored here: https://brandur.org/postgres-queues TDLR the lock time grows exponentially depending on the number of dead tuples in the table, which naturally grows as you use long running transactions.

While this is a great article and was completely accurate as of the time it was written, I believe it predates SKIP LOCKED, as well as some of the more recent design changes in Que which minimize the likielihood of this being an issue: https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock... In particular, Que’s new design locks jobs in a single connection per worker process, not a connection per-job. Job assi…

TIL. This is very useful. I believe 9.4 was the latest version around the time the article was published.

I'm glad the state of things has improved, since I really love database-backed job queues and all transactional guarantees it gives you.

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

#34

Post by drabinowitz »

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?

You can include a locked_at field and have your update query be for not_started rows and started rows where locked_at is older than the job timeout

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

#36

Post by ukd1 »

There is also QueueClassic (https://github.com/QueueClassic/queue_classic); another Ruby based project. Recently I benchmarked some improvements we made to it vs Que (before and after) - it's now faster which is kinda cool -https://github.com/QueueClassic/queue_classic/pull/303#issue...

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

#37

Post by aldoushuxley001 »

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

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

#39

Post by ukd1 »

ukd1 wrote: post #38

This post is pretty out of date; Que and QueueClassic moved to SKIP LOCKED at some point, over using advisory locks / lock head methods. It's much faster, but only supported in Postgres >= 9.5.

https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock... - is actually a pretty good post explaining the before (aka this gist) and after (aka skip locked). Another way of doing it was (now pointless) using http://www.cs.tau.ac.il/~shanir/nir-pubs-web/Papers/Lock_Fre... (you can see an implementation here - https://github.com/QueueClassic/queue_classic/blob/v3.2.0.RC...)

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

#40

Post by ukd1 »

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 think the biggest bonus for me of transactional queues like this is knowing that either everything worked - the inserted/updated/deleted things, and enqueuing of any jobs - or nothing did. Whilst using resque, or anything else outside of postgres, occasionally things would rollback but have sent jobs. This sucks; as you either get errors or unexpected things, or have to code around it to fix that.

FYI, we've been using QueueClassic at Rainforest for a while. Not sure how many actual jobs we do, as we've reset at least when moving hosts, but we're currently at 849202793 jobs (~850m) though QC.

[edit; looking at the last failed job from ~24h ago, we're doing 2.5-3m jobs per day]

Post reply on HN