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

241–250 of 369 posts

Re: Choose Postgres queue technology

#241

Post by ritzaco »

zozbot234 wrote: post #236
ritzaco wrote: post #225

I often see the "engineers copy FAANG infrastructure because they want to be cool, even though their needs are completely different" take as a kind of attack on engineers. But I think a lot of it is also about knowledge and documentation. If I want to copy FAANG or another startup, and set up an infinitely scalable queue-based architecture, I can find dozens of high quality guides, tutorials, white papers etc, showin…

> If I want to use NOTIFY in postgres? The nice thing about "boring" tech like Postgres is that it has great documentation. So just peruse https://www.postgresql.org/docs/current/sql-notify.html . No need for google-fu.

Python, Flask, SQLAlchemy, and Postgres all have great documentation individually, but if I am building an application at the intersection often a guide on exactly how to join them all up is much faster than using each individually and trying to figure out the interactions in four places.

AWS white papers and engineering blogs tend to give me everything I need in one place, and I don't think there are any for apps built with NOTIFY.

Re: Choose Postgres queue technology

#242

Post by polyrand »

Some time ago, I wrote a queue using SQLite[0]. Instead of SKIP LOCKED, you can use RETURNING to lock-and-read a message and ensure only one worker is going to pick it up:

  UPDATE ... SET status = 'locked' ... RETURNING message_id
Or you can just use an IMMEDIATE transaction, SELECT the next message ID to retrieve, and UPDATE the row.

On top of that, if you want to be extra safe, you can do:

  UPDATE Queue SET status = 'locked' WHERE status = 'ready' AND message_id = '....'
To make sure you that the message you are trying to retrieve hasn't been locked already by another worker.

[0]: https://github.com/litements/litequeue/

[1]: https://github.com/litements/litequeue/blob/3fece7aa9e9a31e4...

Re: Choose Postgres queue technology

#243

Post by tmpX7dMeXU »

natmaka wrote: post #230
ritzaco wrote: post #225

I often see the "engineers copy FAANG infrastructure because they want to be cool, even though their needs are completely different" take as a kind of attack on engineers. But I think a lot of it is also about knowledge and documentation. If I want to copy FAANG or another startup, and set up an infinitely scalable queue-based architecture, I can find dozens of high quality guides, tutorials, white papers etc, showin…

Another point is: you don't need scalable now, but may (or even hope) to need it later, and you know that when you will need it you probably won't have time to invest into migrating this component. Also: you may think that you may one day want to be hired by a FAANG.

Yeah. Just keep it to side-projects only. Anyone practicing resume-driven development on my team will be (and I’m exaggerating here) shown the door.

Re: Choose Postgres queue technology

#244

Post by ranting-moth »

ritzaco wrote: post #225

I often see the "engineers copy FAANG infrastructure because they want to be cool, even though their needs are completely different" take as a kind of attack on engineers. But I think a lot of it is also about knowledge and documentation. If I want to copy FAANG or another startup, and set up an infinitely scalable queue-based architecture, I can find dozens of high quality guides, tutorials, white papers etc, showin…

> Yes maintenance is higher

This is what kills you if you're a small startup. Of course it gives you a lot too. But if you're belly up then it doesn't matter.

Of course go for whatever solution gives you the most benefits while not distracting you too much from your main goal.

I've seen a startup where devs spent around 80% of the time fighting their tools and infrastructure. They had a 3 month runway and today there's a massive hole at the end of that runway. I still shudder form just the thought of it.

Re: Choose Postgres queue technology

#245

Post by singron »

klauserc wrote: post #233
kdps wrote: post #224

Earlier quoted context omitted.

I don't get it :(. Why could the same task be executed more than once? From my understanding, if the UPDATE is atomic, only one worker will be able to set `used = 1`. If the update statement is not successful (affected != 1), then the worker should drop the task and do another select.

With a transaction isolation level below SERIALIZABLE you can have two transactions that both read the old row (with `used = 0`) at the time they perform the update (but before they commit the transaction). In that case, both transactions will have performed an update (rows affected = 1). Why would both transactions see `used = 0`? The DB server tries to isolate transactions and actively hides effects of other transa…

This is not true in postgres. When the second transaction tries to update the row, it will wait for the first transaction to commit first and then recheck the WHERE.

https://www.postgresql.org/docs/current/transaction-iso.html...

Re: Choose Postgres queue technology

#246

Post by mkl95 »

vineyardmike wrote: post #238
mkl95 wrote: post #234

Earlier quoted context omitted.

How relevant is it to be hired by a FAANG? I have some experience with "web scale" systems, but I tend to reject FAANG recruiters because Leetcode makes me want to become an apple farmer (no pun).

> How relevant is it to be hired by a FAANG? If you want a job there, very relevant. > I tend to reject FAANG recruiters because Leetcode I understand the pain of leetcode interviews. They’re terrible. But optimizing your career based on the interview process seems… backwards? FAANG companies (for example) are very relevant if you want to make a lot of money and live in Silicon Valley without being a successful found…

I guess my career is pretty close to optimal. I get to work on interesting problems from anywhere I want and save a ton of money. If you are an EU candidate, FAANG companies want you to relocate to some city in the UK or Ireland, which would obliterate my savings rate, and are worse places to live than most mainland EU areas. I understand not everyone is as fortunate as me, which increases their motivation to grind Leetcode and the like.

Re: Choose Postgres queue technology

#247

Post by figassis »

perfmode wrote: post #193
figassis wrote: post #188

The way I implement my queues (usually as part of my monolith application) is as go routines. Each instance of the app launches with a unique id, and also a role. It can be a worker or the app itself. So when the app generates a queue item, it simply adds it to a table as pending. A worker will then, via transaction, update a set of items to add its instance id as well as an expiration for this lock. If that succeeds…

How are workers notified of new work? Or do they poll and sleep?

They poll. It's easy however to add pub/sub via redis. Queues are categorized in frequency. So for example a notification queue might process every minute or 5 minutes, a reconciliation queue might run every 12 hours, or have a fixed time, like daily at 2am and 6pm, etc. Each queue runs on its own goroutine on its onwn schedule, and every worker runs all queues. I can also adjust how many items a worker can pull each time so queues do not get backed up. Say there is some criteria an account needs to meet to be eligible for some feature. If that criteria requires some expensive db queries, I can run a daily job that pulls 100 accounts each time to check that they meet the criteria. But if the instances can process more, I can configure it to pull 1000 accounts. Or I can add 9 workers and each pulls 100 accounts.

Usually what I do is pull the records that were updated least recently (as in they should be ahead of the queue). So if a previous worker locked the oldest X records, the second worker will pull the next batch bc the condition will exclude the previously updated (locked) records. There's a lot of flexibility you can add with just these controls: schedule, frequency, batch size, number of workers.

Re: Choose Postgres queue technology

#248

Post by cpursley »

runeks wrote: post #226
MrPowerGamerBR wrote: post #118

Earlier quoted context omitted.

One reason that makes me dislike NOTIFY/LISTEN is that issues with it are hard to diagnose. Recently I had to stop using it because after a while all NOTIFY/LISTENS would stop working, and only a database restart would fix the issue https://dba.stackexchange.com/questions/325104/error-could-n...

At my previous company, they switched from using NOTIFY/LISTEN for Postgres notifications to a custom solution built on top of logical replication. As I understand it, part of the reason was reliability. I never touched that part of the code, but I believe the idea was to subscribe to logical replication updates and send out notifications based on these.

This is what we do. Even put together a library for it:

https://github.com/cpursley/walex

Re: Choose Postgres queue technology

#249

Post by mtlguitarist »

chucke wrote: post #232
phamilton wrote: post #151

Earlier quoted context omitted.

I can share our experience with RabbitMQ/SQS/Sidekiq. Our two major issues have been around the retry mechanism and resource bottlenecks. The key retry problem is "What happens when a worker crashes?". RabbitMQ solves this problem by tying "unacknowledged messages" to a tcp connection. If the connection dies, the in-flight messages are made available to other connections. This is a decent approach, but we hit a lot o…

SQS limits you further in other ways. For instance, scheduled tasks are capped to 15m (delaySconds knob), so you'll be stuck when implementing the "cancel account if not verified in 7 days" workflow. You'll either reenqueue a message every 15m until its ready (and eat your the SQS costs), or build a bespoke solution only for scheduled tasks using some other store (the database usually) and another polling loop (at a…

If you wanted to handle this scenario with the serverless AWS stack, my recommendation would be to push records to Dynamo with TTLs and then when they pop have a Lambda push them onto the queue. Would cost almost nothing to do this. If you had 10 million requests a month your Lambda cost would be ~$150 to run this (depending on duration, but just pushing to a queue should be quick). Dynamo would be another ~$50 to run, depending how big your tasks are.

Granted now you need 3 services instead of 1. I personally don't find the maintenance cost particularly high for this architecture, but it does depend on what your team is comfortable with.

Re: Choose Postgres queue technology

#250

Post by lomereiter »

maxbond wrote: post #178
surprisetalk wrote: post #112

Earlier quoted context omitted.

If you read my guide, you’ll see that I embed it in a transaction that doesn’t COMMIT until the companion code is complete :) For example, I run the above query to grab a queued email, send it using mailgun, then COMMIT. Nothing is changed in the DB unless the email is sent.

Holding a transaction open for the duration of a request to an external service makes me nervous. I've seen similar code lock up the database and bring down production. Are you using timeouts and circuit breakers to control the length of the transactions?

Yes, you absolutely need to set a reasonable idle transaction timeout to avoid a disaster (bugs in the code happen) - this can also be done globally in the database settings.
Post reply on HN