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.
I'm glad the state of things has improved, since I really love database-backed job queues and all transactional guarantees it gives you.
Percona has a good article on this https://www.percona.com/blog/2018/08/10/tuning-autovacuum-in...
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
Guess you lose job atomicity that way.
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.