Depends on the project. We have some that use PostgreSQL backed queues. Some using Redis. As you get more traffic RDBMS resources are precious - wasting them on queues starts to become a poor tradeoff. Redis is so generally useful we tend to use it on most projects anyways, so it doesn't add another moving part. And we tend to be really conservative about adding new things to our production environment, and despite that Redis has found a home in our stack.
Turning PostgreSQL into a queue serving 10k jobs per second (2013)
21–30 of 146 posts
Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)
#22One 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.
Is that fixed by the VACUUM process?
It naturally grows due to long running transactions because the vacuum process cannot clean dead tuples that a transaction can "see". Though more aggressive vacuum settings might solve this problem.
Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)
#23One 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.
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 running, Postgres only marks the row as deleted, and keeps it around in case a transaction would want to access it at some point. Each time you need to process a job, Postgres needs to lock the row. The way this mechanism works involves iterating over the rows until you find one you can lock on. If your transaction is running 30 minutes, each job would have to iterate through 30k dead rows (deleted, but still around for the sake of the transaction). Slowing down lock time leads to overal degreaded performance of the job queue, which leads to jobs being added faster than they're being processed, which further exacerbates the problem
Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)
#24One 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 assignments are handled in an in-memory/unlogged table. Jobs are also automatically assigned to a free worker upon being enqueued via LISTEN/NOTIFY and an unlogged table of available workers. And jobs are locked in batches by each worker process, not one-at-a-time. So polling becomes much less frequent and is far more efficient when it happens.
This does not mean that it’s suddenly ok for jobs to take out transactions which run a long time (that is usually a bad idea in a production database) but it does substantially minimize the scenarios where these problems might occur.
Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)
#25I 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. And then connect again to change the row's status to "finished".
This is how it's always designed, as I have seen. The locking problem is for querying rows where status="waiting" and then instantly change it.
It's not to "keep the record locked, and DB connection up, until I finish my batch job". That would be a bad design.
Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)
#26The Minion job queue is another implementation of this idea. But using the even more efficient and safe FOR UPDATE SKIP LOCKED. https://github.com/mojolicious/minion
Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)
#27I 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 they themselves decide to open.
Otherwise, I completely agree on the benefits of transactional job enqueueing. This lets you push off so much complexity until you actually need it (when you’ve scaled such that a single database doesn’t handle your needs well). At that point, you get to solve the same challenges you will have been solving all along to deal with jobs that might run which depend on transactions that may not have committed (yet or ever). I believe this model is almost always the right starting point for a web application, barring some unusual job requirements or massive initial scale.
Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)
#28I 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…
Is this what you’re talking about?
Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)
#29How 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.
Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)
#30Depends on the project. We have some that use PostgreSQL backed queues. Some using Redis. As you get more traffic RDBMS resources are precious - wasting them on queues starts to become a poor tradeoff. Redis is so generally useful we tend to use it on most projects anyways, so it doesn't add another moving part. And we tend to be really conservative about adding new things to our production environment, and despite t…
You're allowed to use more than one PostgreSQL instance for your queue if RDMS resources are precious