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

291–300 of 369 posts

Re: Choose Postgres queue technology

#291

Post by acaloiar »

sgarland wrote: post #257

I just want to commend OP - if they’re here - for choosing an int64 for job IDs, and MD5 for hashing the payload in Neoq, the job library linked [0] from the article. Especially given the emphasis on YAGNI, you don’t need a UUID primary key, and all of its problems they bring for B+trees (that thing RDBMS is built on), nor do you need the collision resistance of SHA256 - the odds of you creating a dupe job hash with…

Thanks! I'd like to believe that those decisions were incredibly thoughtful, but they're largely Bob Rossian happy accidents.

Re: Choose Postgres queue technology

#292

Post by runeks »

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.

If your first point holds, then all app components should be “scalable” from the beginning, because you may not have time to make it so later.

And that’s terrible advice, of course. You very likely will have time to scale things up (customer count almost never increase dramatically from one day to the next), and even if you don’t you’ll most likely never deliver a useable product if all components need to be “scalable” from the beginning.

Re: Choose Postgres queue technology

#293

Post by JohnBooty »

acaloiar wrote: post #280
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? I googled "SQLALchemy notify listen postgres" and I find a few unanswered stackoverflow questions and a github gist that has some code but no context. Author here. I would say that my post is less targeted at someone like you (application developer, presumably) and more targeted at library developers. I don't think it's ideal for everyone to be implementing bespoke, Postgres-bac…

Thank you for writing this.

I love the article's point, and I tend to feel that the "chasing the cargo cult of 'scale'" is maybe the biggest problem I see in development teams today. It is certainly the biggest problem that I rarely hear anybody talking about.

    Author here. I would say that my post is less 
    targeted at someone like you (application developer, 
    presumably) and more targeted at library developers.
I think the article might benefit from clarification on this point.

Reading the HN comments, I see that I'm not the only person who came away with a misunderstanding there.

Again, I 100% love the overall point.

Re: Choose Postgres queue technology

#294

Post by ComodoHacker »

>Postgres queue tech is a thing of beauty, but far from mainstream.

A small nitpick. FOR UPDATE SKIP LOCKED was mainstream (in a sense) way before it was copied from Oracle to Postgres. It's used under the hood in most non-trivial Oracle deployments. Like for replication or refreshing materialized views in parallel.

Re: Choose Postgres queue technology

#295

Post by evantbyrne »

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.

Hard disagree. Some things are difficult to change later on, others not so much, and you can't do everything for v1. The product has to launch at some point. Your choice of queue is one of the things you'll be able to change. Don't complicate things unless you've run the numbers and know you'll need to. A lot of very large companies do just fine with using relational databases as queues.

Re: Choose Postgres queue technology

#296

Post by bunderbunder »

ekidd wrote: post #268

I've built three distributed job systems at this point. A handy rule of thumb which I have promoted for years is "build for 10x your current scale." If you need to handle 70 requests/second, design for 700. If you need to handle 20 servers running batch jobs, design for 200 servers. If you're in a startup that grows 100% per year, you'll be at 8x scale in 3 years. So you'll have time to rewrite as you grow! Out of th…

Similar experience here. Multiple times, I've pushed an SQL-based queue a couple orders of magnitude past the scale where others say SQL craps out and a distributed solution is an absolute requirement. And the SQL solution is typically simpler, requires fewer compute resources, and easier to support in production.

But, to make it work, you've got to know the database well enough to know that things like SELECT FOR UPDATE SKIP LOCKED exist in the first place. Which is a kind of knowledge that's getting quite rare these days, because more and more engineers grow up never having known a world where they aren't walled off from their DBMS's true capabilities by a heavyweight ORM.

Re: Choose Postgres queue technology

#297

Post by lysecret »

latchkey wrote: post #184
lysecret wrote: post #182

Earlier quoted context omitted.

I love Task Queues. We are using them extensively. Also, they give you deduplication for free and a lot of other nice features like delayed tasks storing tasks for up to 30 days extremely detailed rate limits etc.

GCP is really under rated in this regard. Are there any open source implementations of Task Queues? It feels like something that has been missing for years.

Yea, this is the only thing I don't like about them, that I can't test them locally.

More generally, is there something like a "on prem cloud" which just replicates say Cloud Tasks (but also other Cloud Apis) using local compute as well as say a local db. For testing / development this would be very cool.

Re: Choose Postgres queue technology

#298

Post by runeks »

andrewstuart wrote: post #105
MattIPv4 wrote: post #81

We process around 1 million events a day using a queue like this in Postgres, and have processed over 400 million events since the system this is used in went live. Only issue we've had was slow queries due to the table size, as we keep an archive of all the events processed, but some scheduled vacuums every so often kept that under control.

Partial indexes might help.

Also: an ordered index that matches the ordering clause in your job-grabbing query. This is useful if you have lots of pending jobs.

Re: Choose Postgres queue technology

#299

Post by _jal »

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.

Building things you don't need in hopes that you'll need them because things you aren't spending time on will grow to demand them is like hiring an investment manager when you're in debt.

Re: Choose Postgres queue technology

#300

Post by phamilton »

mtlguitarist wrote: post #249
chucke wrote: post #232

Earlier quoted context omitted.

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 ru…

I've explored this space pretty thoroughly, including the Dynamo approach you've described. Dynamo does not have a strict guarantee on when items get deleted:

  TTL typically deletes expired items within a few days. Depending on the size and activity level of a table, the actual delete operation of an expired item can vary. Because TTL is meant to be a background process, the nature of the capacity used to expire and delete items via TTL is variable (but free of charge). [0]
Because of that limitation, I would not use that approach. Instead I would do Scheduled Lambdas to check for items every 15 minutes in a Serverless Aurora and then add them to SQS with delays.

I've had my eye on this problem for a few years and keep thinking that a simple SaaS that does one-shot scheduled actions would probably be a worthy side project. Not enough to build a company around, but maintenance would be low and there's probably some pricing that would attract enough customers to be sustainable.

[0] https://docs.aws.amazon.com/amazondynamodb/latest/developerg...

Post reply on HN