Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Choose Postgres queue technology

adriano.fyi

Post reply on HN
Print view View original

61–70 of 369 posts

Re: Choose Postgres queue technology

#61

Post by klysm »

orangepanda wrote: post #23
aduffy wrote: post #2

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

Depends on how many consumers you have. If you need order guarantees, then something like the outbox pattern is probably a better fit.

Re: Choose Postgres queue technology

#62

Post by samus »

matsemann wrote: post #3
aduffy wrote: post #2

For 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

I've done even simpler without locks (as no transaction logic), where I select a row, and then try to update a field about it being taken. If 1 row is affected, it's mine. If 0, someone else did it before me and I select a new row. I've used this for tasks at big organizations without issue. No need for any special deployments or new infra. Just spin up a few worker threads in your app. Perhaps a thread to reset aban…

I recently got introduced to this system at work, and also built a new job using it. It works fine, but since I had to implement work stealing to deal with abandoned jobs in a timely manner, I wouldn't dare to use it for actions that absolutely must not happen twice.

Re: Choose Postgres queue technology

#63

Post by sorentwo »

paulddraper wrote: post #25

USE. 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.)

To do anything safe and interesting you’ll need transactions. Using SKIP LOCKED won’t be your bottleneck, your application will. Job queues are about side effects and the rest of your application needs to keep up.

Oban is able to run over 1m jobs a minute, and the ultimate bottleneck is throttling in application code to prevent thrashing the database: https://getoban.pro/articles/one-million-jobs-a-minute-with-...

Re: Choose Postgres queue technology

#64

Post by calrain »

andrelaszlo wrote: post #6
matsemann wrote: post #3

Earlier quoted context omitted.

I've done even simpler without locks (as no transaction logic), where I select a row, and then try to update a field about it being taken. If 1 row is affected, it's mine. If 0, someone else did it before me and I select a new row. I've used this for tasks at big organizations without issue. No need for any special deployments or new infra. Just spin up a few worker threads in your app. Perhaps a thread to reset aban…

I guess you update it with the assigned worker id, where the "taken by" field is currently null? Does it mean that workers have persistent identities, something like an index? How do you deal with workers being replaced, scaled down, etc? Just curious. We maintained a custom background processing system for years but recently replaced it with off the shelf stuff, so I'm really interested in how others are doing simil…

I've done this successfully with a web service front that retrieves jobs to send to workers for processing, by using a SQL table queue. That web service ran without a hitch for a long time, serving about 10 to 50 job consumers for fast and highly concurrent queues.

My approach was:

- Accept the inbound call

- Generate a 20 character random string (used as a signature)

- Execute a sql query that selects the oldest job without a signature and write the signature, return the primary key of the job that was updated.

- If it errors for any reason, loop back and attempt again, but only 10 times, as some underlying issue exists (10 collisions is statistically improbable for my use case)

- Read the primary key returned by that sql query and read it, comparing it's signature to my random one.

- If a hit, return the job to the caller

- If a miss, loop back and start again, incrementing attempts by 1.

The caller has to handle the possibility that a call to this web service won't return anything, either due to no jobs existing, or the collision/error threshold being reached.

In either case, the caller backs for it's configured time, then calls again.

Callers are usually in 'while true' loops, only existing if they get an external signal to close or an uncontrolled crash.

If you take this approach, you will have a function or a web service that converts the SQL table into a job queue service. When you do that, you can build metrics on the amount of collisions you get while trying to pull and assign jobs to workers.

I had inbuilt processes that would sweep through jobs that were assigned (had a job signature) and weren't marked as complete, it actioned those to handle the condition of a crashed worker.

There are many many other services the proper job queues offer, but that usually means more dependencies, and code libraries / containers, so just build in the functionality you need.

If it is accurate, fast enough, and stable, you've got the best solution for you.

/edited for formatting

Re: Choose Postgres queue technology

#65

Post by skybrian »

theptip wrote: post #54
5id wrote: post #26

One 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 transact…

Worth being clear that bridging to another non-idempotent system necessarily requires you to pick at-least-once or at-most-once semantics. So for emails, if you fail awaiting confirmation of your email you still need to pick between failing your transaction and potentially duplicating the email, or continuing and potentially dropping it. The big advantage is for code paths which async modify your DB; these can be don…

Email might never arrive, though. The only way to know they got it is to have them follow a link to confirm.

Re: Choose Postgres queue technology

#66

Post by pqdbr »

andrelaszlo wrote: post #4

One issue with Redis as a queue backend seems to be that persistence is quite expensive, at least for managed Redis instances. Using PG seems like it could be much cheaper, especially if you already have an instance with room to spare. I thought it was an interesting article, and I'd love to hear more from people using PG for queues in production (my intuition would say you'd get a lot of table bloat and/or vacuum la…

Same here. Sidekiq + Rails in a Rails app is a powerhouse, simple and reliable, but I do worry about losing the queue in Redis. It would be great to have that in Postgres as well.

Re: Choose Postgres queue technology

#67

Post by __jem »

5id wrote: post #26

One 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 transact…

I'm not sure this is really an issue with transactionality as a single request can obviously be split up into multiple transactions, but rather that even if you correctly flag the email as pending/errored, you either need to process these manually, or have some other kind of background task that looks for them, at which point why not just process them asynchronously.

Re: Choose Postgres queue technology

#68

Post by dilyevsky »

dlisboa wrote: post #22
levkk wrote: post #12

Running 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.

I’ve seen it used for up to 1000 jobs per second with concurrency of 3-12

Re: Choose Postgres queue technology

#69

Post by adatta02 »

aduffy wrote: post #2

For 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

This is more or less how graphile, https://github.com/graphile/worker is implemented.

Re: Choose Postgres queue technology

#70

Post by klysm »

paulddraper wrote: post #25

USE. 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.)

Not all use cases are high throughput. That’s not what makes it a toy
Post reply on HN