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

71–80 of 369 posts

Re: Choose Postgres queue technology

#71

Post by vore »

riku_iki wrote: post #50
orangepanda wrote: post #23

Earlier quoted context omitted.

As I understand, with SKIP LOCKED rows would no longer be processed in-order?

article says he also uses "order by" clause, but I am wondering if it will severely limit throughput since all messages will need to be sorted on each lookup, but this probably can be solved by introducing index.

It seems strictly worse to use ORDER BY in this case, since if you're using SKIP LOCKED you should be doing parallel processing anyway, and if you're doing parallel processing, ordering is already going out the window.

Re: Choose Postgres queue technology

#72

Post by vore »

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?

Yes, but if you're going through the queue with multiple workers in parallel, you lose ordering guarantees anyway.

Re: Choose Postgres queue technology

#73

Post by paulddraper »

klysm wrote: post #70
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

"Toy/low throughput" = "Toy or low throughout"

Re: Choose Postgres queue technology

#74

Post by ryanbrunner »

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…

Another benefit of this is that you're guaranteed that the transaction is completed before the job is picked up. With redis-backed queues (or really anything else), you very quickly run into the situation where your queue executes a job depending on a database record existing prior to the transaction being committed (and the fix for this is usually awkward / complex code).

Re: Choose Postgres queue technology

#75

Post by andrewstuart »

repiret wrote: post #56
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.

It was learning about this that led to me understanding file systems make perfectly acceptable queues.

Re: Choose Postgres queue technology

#76

Post by nsonha »

vore wrote: post #71
riku_iki wrote: post #50

Earlier quoted context omitted.

article says he also uses "order by" clause, but I am wondering if it will severely limit throughput since all messages will need to be sorted on each lookup, but this probably can be solved by introducing index.

It seems strictly worse to use ORDER BY in this case, since if you're using SKIP LOCKED you should be doing parallel processing anyway, and if you're doing parallel processing, ordering is already going out the window.

Parallel or not, the order is of importance in any queue system.

Re: Choose Postgres queue technology

#77

Post by AdamJacobMuller »

tylergetsay wrote: post #13
fbdab103 wrote: post #11

Earlier quoted context omitted.

I would set the taken field to a timestamp. Then you could have a cleanup job that looks for any lingering jobs aged past a reasonable timeout and null out the field.

it wont work with a timestamp because each write will have an affected row of 1 beacuse the writes happen at different times. setting a boolean is static

update row set taken=true,taken_by=my_id,taken_at=now() where taken is false;

Re: Choose Postgres queue technology

#78

Post by vhiremath4 »

We used postgres for some of our queues back when we were at ~10 msg/s. It scaled quite a bit, but, honestly, setting up SQS or some other queue stack in AWS, GCP, or Azure is so simple and purpose built for the task (with DL queues and the like built in), I don’t know why you wouldn’t just go that route and not have to worry about that system shitting the bed and affecting the rest of the DB’s health.

It seems foolish. I am a big fan of “use the dumbest tool”, but sometimes engineers take it too far and you’re left with the dumbest tool with caveats that don’t seem worth it given the mainstream alternative is relatively cheap and simple.

Re: Choose Postgres queue technology

#79

Post by doctor_eval »

andrewstuart wrote: post #49
doctor_eval wrote: post #47

Earlier quoted context omitted.

This is true, and I’ve worked on systems that use this, but it’s a lot more work than just a rename. I’d recommend that, if you have a Postgres database already, definitely use that instead. Your queues will be transactional and they will get backed up when the rest of your database does.

>> but it’s a lot more work than just a rename Such as?

Well, if you have multiple writers then you need to decide who’s responsible for rotating the queues, and you need to serialise writes; or if each writer has its own queue then the reader has to do more work. And then you need to worry about fsync, and backup. And of course you need to be careful to flush to the queue after each write to avoid partial writes.

Basically I’m saying that there are just a number of potential footguns when using files as queues - I speak from experience! - which are trivially taken care of by a database, especially if you have one already.

I’m not saying that it’s not possible, just that for non trivial applications, it’s certainly more complex than just an atomic file move.

Re: Choose Postgres queue technology

#80

Post by bicijay »

vhiremath4 wrote: post #78

We used postgres for some of our queues back when we were at ~10 msg/s. It scaled quite a bit, but, honestly, setting up SQS or some other queue stack in AWS, GCP, or Azure is so simple and purpose built for the task (with DL queues and the like built in), I don’t know why you wouldn’t just go that route and not have to worry about that system shitting the bed and affecting the rest of the DB’s health. It seems fooli…

Transactions, data consistency. This is the answer that you will not find in SQS.
Post reply on HN