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

51–60 of 369 posts

Re: Choose Postgres queue technology

#51

Post by devoutsalsa »

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'm sure Redis is much faster than an RDBMS w/ all the ACID features turned on. The biggest concern I always have with Redis is simply overwhelming the in-memory storage limits when someone wants to do process a large number of good-sized messages at an inconvenient time. #tradeoffs

Re: Choose Postgres queue technology

#52

Post by somsak2 »

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

Fourth paragraph of the post:

>Applied to job records, this feature enables simple queue processing queries, e.g. SELECT * FROM jobs ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 1.

Re: Choose Postgres queue technology

#53

Post by leoqa »

tiagod wrote: post #45

Temporal, which AFAIK was made by the Uber Cadence team, which was also involved in SQS, uses postgres as a backend. I used it for a web automation system for an accounting client (automatically read files from a network share, lookup the clients on a database, submit the documents to government websites, using headless browsers, and put the resulting files in the directory). It allows for completely effortless deter…

Temporal is a pretty complicated system. It has sharding built in, stores the entire activity history and runs multiple queues for timers and events. I’m a big fan (worked at Uber) but it’s definitely not just postgres with a few indices.

Re: Choose Postgres queue technology

#54

Post by theptip »

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 done fully transactionally with exactly-once semantics since the Job consumption and DB update are in the same transaction.

Re: Choose Postgres queue technology

#55

Post by surprisetalk »

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 recently published a manifesto and code snippets for exactly this in Postgres!

  delete from task
  where task_id in
  ( select task_id
    from task
    order by random() -- use tablesample for better performance
    for update
    skip locked
    limit 1
  )
  returning task_id, task_type, params::jsonb as params
[1] https://taylor.town/pg-task

Re: Choose Postgres queue technology

#56

Post by repiret »

andrewstuart wrote: post #39

You don't even need a database to make a message queue. The Linux file system makes a perfectly good basis for a message queue since file moves are atomic. My guess is that many people are implementing queuing mechanisms just for sending email. You can see how this works in Arnie SMTP buffer server, a super simple queue just for emails, no database at all, just the file system. https://github.com/bootrino/arniesmtpbu…

That’s a key property leveraged in the Maildir mailbox format.

Re: Choose Postgres queue technology

#57

Post by simonw »

One of my favourite pieces of writing about worker queues is this by Brandur Leach:

Transactionally Staged Job Drains in Postgres - https://brandur.org/job-drain

It's about the challenge of matching up transactions with queues - where you want a queue to be populated reliably if a transaction completes, and also reliably NOT be populated if it doesn't.

Brandur's pattern is to have an outgoing queue in a database table that gets updated as part of that transaction, and can then be separately drained to whatever queue system you like.

Re: Choose Postgres queue technology

#58

Post by laurencerowe »

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…

> One of the biggest benefits imo of using Postgres as your application queue, is that any async work you schedule benefits from transactionality.

This is a really important point. I often end up using a combination of Postgres and SQS since SQS makes it easy to autoscale the job processing cluster.

In Postgres I have a transaction log table that includes columns for triggered events and the pg_current_xact_id() for the transaction. (You can also use the built in xmin of the row but then you have to worry about transaction wrap around.) Inserting into this row triggers a NOTIFY.

A background process runs in a loop. Selects all rows in the transaction table with a transaction id between the last run's xmin and the current pg_snapshot_xmin(pg_current_snapshot()). Maps those events to jobs and submits them to SQS. Records the xmin. LISTEN's to await the next NOTIFY.

Re: Choose Postgres queue technology

#59

Post by ris »

mikeocool wrote: post #46
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.)

Pretty common advice for scaling Postgres is to deploy pgbouncer in transaction mode in front of it to handle connection pooling. Advisory locks don’t work in this setup (and will start behaving in strange ways if you do try to use them.) Something to consider if you go this route.

Transaction-scoped advisory locks are very much a thing too.

Re: Choose Postgres queue technology

#60

Post by klysm »

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…

With what transaction isolation level?
Post reply on HN