For Rails apps, you can do this using the ActiveJob interface via
https://github.com/bensheldon/good_job
Had it in production for about a quarter and it’s worked well.
Choose Postgres queue technology
21–30 of 369 posts
Re: Choose Postgres queue technology
#22Running this exact implementation with 47M jobs processed and counting. SKIP LOCKED is great for VACUUM, and having durable storage with indexes make otherwise expensive patterns like delayed jobs, retries, status updates, "at least once", etc. really easy to implement.
Do you have some idea of how many jobs per minute or hour do you have? Just want to compare with what we have on Redis at work.
Do you also have any idea on the concurrency? How many workers you have pulling from Postgres.
I’ve used this approach before (ages ago) when Redis wasn’t even a thing, though not at high throughout requirements.
Re: Choose Postgres queue technology
#23For several projects I’ve opted for the even dumber approach, that works out of the box with every ORM/Query DSL framework in every language: using a normal table with SELECT FOR UPDATE SKIP LOCKED https://www.pgcasts.com/episodes/the-skip-locked-feature-in-... It’s not “web scale” but it easily extends to several thousand background jobs in my experience
As I understand, with SKIP LOCKED rows would no longer be processed in-order?
Re: Choose Postgres queue technology
#24For several projects I’ve opted for the even dumber approach, that works out of the box with every ORM/Query DSL framework in every language: using a normal table with SELECT FOR UPDATE SKIP LOCKED https://www.pgcasts.com/episodes/the-skip-locked-feature-in-... It’s not “web scale” but it easily extends to several thousand background jobs in my experience
batch inserts
process tasks in batches and it is pretty much webscale
Re: Choose Postgres queue technology
#25USE. ADVISORY. LOCKS.
Do not use SKIP LOCKED unless it is a toy/low throughout.
Row locks require transactions and disk writes.
Advisory locks require neither. (However, you do have to stay inside the configurable memory budget.)
Re: Choose Postgres queue technology
#26One of the biggest benefits imo of using Postgres as your application queue, is that any async work you schedule benefits from transactionality.
That is, say you have a relatively complex backend mutation that needs to schedule some async work (eg sending an email after signup). With a Postgres queue, if you insert the job to send the email and then in a later part of the transaction, something fails and the transaction rollbacks, the email is never queued to be sent.
Re: Choose Postgres queue technology
#27It seems like listen/notify doesn't play well with a serverless architecture. Would it make sense for Postgres to make a web request when there's work in the queue? Is that a thing?
Re: Choose Postgres queue technology
#28Few things.
1. The main downside to using PostgreSQL as a pub/sub bus with LISTEN/NOTIFY is that LISTEN is a session feature, making it incompatible with statement level connection pooling.
2. If you are going to do this use advisory locks [0]. Other forms of explicit locking put more pressure on the database while advisory locks are deliberately very lightweight.
My favorite example implementation is que [1] which is ported to several languages.
[0] https://www.postgresql.org/docs/current/explicit-locking.htm...
Re: Choose Postgres queue technology
#29Earlier quoted context omitted.
No, just update set taken=1. If it was a change to the row, you updated it. If it wasn't, someone updated before you. Our tasks were quick enough so that all fetched tasks would always be able to be completed before a scale down / new deploy etc, but we stopped fetching new ones when the signal came so it just finished what it had. I updated above, we did have logic to monitor if a task got taken but never got a fini…
That is the sort of thing that bites you hard when it bites. It might run perfectly for years but that one period of flappy downtime at a third party or slightly misconfigured DNS will bite you hard.
But compared to our rabbit setup where I work now, it was dead stable. No losing tasks or extra engineering effort on maintaining yet another piece of tech. Our rabbit cluster acting up has led to multiple disasters lately.
Re: Choose Postgres queue technology
#30MS SQL server, Postgres and MySQL all support SKIP LOCKED, which means they are all suitable for running queues.
I built a complete implementation in Python designed to work the same as SQS but be more simple:
https://github.com/starqueue/starqueue
Alternatively if you just want to quickly hack something into your application, here is a complete solution in one Python function with retries (ask ChatGPT to tell you what the table structure is):
import psycopg2
import psycopg2.extras
import random
db_params = {
'database': 'jobs',
'user': 'jobsuser',
'password': 'superSecret',
'host': '127.0.0.1',
'port': '5432',
}
conn = psycopg2.connect(**db_params)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
def do_some_work(job_data):
if random.choice([True, False]):
print('do_some_work FAILED')
raise Exception
else:
print('do_some_work SUCCESS')
def process_job():
sql = """DELETE FROM message_queue
WHERE id = (
SELECT id
FROM message_queue
WHERE status = 'new'
ORDER BY created ASC
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;
"""
cur.execute(sql)
queue_item = cur.fetchone()
print('message_queue says to process job id: ', queue_item['target_id'])
sql = """SELECT * FROM jobs WHERE id =%s AND status='new_waiting' AND attempts