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

271–280 of 369 posts

Re: Choose Postgres queue technology

#271

Post by n_time »

Could it be people are choosing over-engineered solutions because AWS has bad documentation and that's what the solutions architects tell them to do?

Could it be because microservices and so-called "server-less" have been sold as cost-saving measures that increase the business' flexibility and decrease capital investment?

When... in reality a single deployed Docker container is way more manageable than a distributed system constructed with "lambdas" and requires fewer engineers in the long run?

What I'm trying to say is that FAANG cargo-culting is only part of why developers choose to build solutions that scale larger than they need. Another large part is the cloud development ecosystem writ-large and the consulting culture that has built up around it.

Re: Choose Postgres queue technology

#272

Post by vb-8448 »

n_time wrote: post #271

Could it be people are choosing over-engineered solutions because AWS has bad documentation and that's what the solutions architects tell them to do? Could it be because microservices and so-called "server-less" have been sold as cost-saving measures that increase the business' flexibility and decrease capital investment? When... in reality a single deployed Docker container is way more manageable than a distributed…

Did you ever hear about "no one got fired because he bought IBM/Oracle/Microsoft" ?

It's the same exact thing: most of the people do what is considered a "best practice" just to avoid any type of annoyance when something will break.

Re: Choose Postgres queue technology

#273

Post by sgarland »

maxbond wrote: post #168
valzam wrote: post #85

Earlier quoted context omitted.

Considering that you have a native "offset" (auto incrementing id) and the ability to partition by date I would say postgres is a great candidate for a simple Kafka replacement. It will also be significantly simpler to set up consumers if you don't really need to whole consumer group, partition etc. functionality.

Unfortunately `serial` is not sufficient on it's own for that use case. If you observe the values 1, 2, and 4, you can't actually conclude whether 3 exists or not. That transaction may have failed after incrementing the serial, meaning 3 does not exist, or it may be an ongoing transaction which hasn't yet committed, meaning 3 exists but is not yet visible to you. So if you update your offset to 4 before the transacti…

SERIAL/SEQUENCE/IDENTITY increment immediately, not at commit. They’re just reading from a generator. You can also get the current key from it if you’d like.

Advisory locks also exist, if you want to implement logic in the application to inform you of various row conditions without having the DB care about it.

But for the example given, you could do many things:

* Add some boolean columns for ack and complete. Performance due to cardinality of these will eventually start to suck if they’re being indexed, but by that point (millions of rows) you can have thought of another solution.

* Add ctime and atime columns, each of which can be handled by Postgres natively to update when created / written, respectively. This has the advantage of lending itself nicely to partitioning by date range, if that becomes necessary.

* Have three tables - available, in_progress, and completed. Use triggers or application logic to move entries in an atomic manner.

None of this is necessarily normalized, but if you’re receiving JSON payloads for the job, 1NF went out the window anyway.

Re: Choose Postgres queue technology

#274

Post by sgarland »

zacksiri wrote: post #200
ezekiel68 wrote: post #196

Earlier quoted context omitted.

>> I simply conducted a small experiment and PG analyze clearly showed a missing index in one of the key tables. Based on this sentence, I interpreted that part as representing that the engineers did not believe the missing index was causing the problem (until the experiment was run).

Yes, one of the theory was that the index wasn't the problem because there was already a multi column index on that particular column. However the PG analyze tool showed some particular query didn't utilize the index, so there needed to be a separate index just for that particular column.

The number of reasons why an RDBMS - especially Postgres - can choose to not use an index is wide. Sometimes it’s your fault, sometimes it’s the table statistics fault.

Good on you for actually empirically determining reality.

Re: Choose Postgres queue technology

#275

Post by CyberDildonics »

worthless-trash wrote: post #141
nsonha wrote: post #122

Earlier quoted context omitted.

job should be attempted inthe same order/priority they are enqueued, that's the meaning of the word "queue". That they take varrying amounts of time is another matter.

Queue can clearly mean "work that needs to be completed" not necessarily 'work completed in order'. Your definition is much stricter than it needs to be for most use cases.

not necessarily 'work completed in order'

That's exactly what a queue means, not just in every day life, but specifically in computer science.

Re: Choose Postgres queue technology

#276

Post by nerpderp82 »

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…

Design (and test) for 10x your current scale, build for what you need now. And the system has to be able to handle peak loads, and if you don't know what those are build in a safety margin or a way to shed or defer work if you need to.

Everything is a tradeoff, optimize for the things that need optimizing, and determining what those are is the hallmark of a good engineer.

Re: Choose Postgres queue technology

#277

Post by nerpderp82 »

Karrot_Kream wrote: post #209
mlyle wrote: post #195

Earlier quoted context omitted.

> and are confident you'll be working at tens of tasks per second forever. It's more like a few thousand per second, and enqueues win, not dequeues like you say... on very small hardware without tuning. If you're at tens of tasks per second, you have a whole lot of breathing room: don't build for 100x current requirements. https://chbussler.medium.com/implementing-queues-in-postgres... > eventually your dequeue queri…

> https://chbussler.medium.com/implementing-queues-in-postgres... This link is simply raw enqueue/dequeue performance. Factor in workers that perform work or execute remote calls and the numbers change. Also, I find when your jobs have high variance in times, performance degrades significantly. > This doesn't really make sense to me. To me, the main problem seems to be that you end up with having a lot of snapshots a…

What happens when you get 500x the traffic or 50x?

How does the system behave when the traffic rate is higher for which it was designed for or can currently handle? Because that number will always be there, even in a "scalable" system. One won't be able to add capacity at the same rate that work will increase.

Re: Choose Postgres queue technology

#278

Post by welder »

jarofgreen wrote: post #5

> I’d love to see more neoq-like libraries for languages other than Go. Python has Celery, but maybe the author is looking for more choice between brokers. https://docs.celeryq.dev/en/stable/index.html

Celery is crap. Full of bugs. https://wakatime.com/blog/56-building-a-background-task-queu...

Re: Choose Postgres queue technology

#279

Post by welder »

jasongi wrote: post #158

I’m not against using Postgres for this. But I am against the rolling your own distributed task queue. It always seems like a simple task but snowballs in complexity. Any gains you get simplifying your stack will be wiped out by the fact that things like Celery (for example) don’t support using Postgres as a broker so now you have to do your own DIY Celery instead of say, just using Celery with the SQS broker (which……

> I’m not against using Postgres for this. But I am against the rolling your own distributed task queue.

Good thing I didn't listen to your advice... my DIY background task queue saved my website when Celery couldn't scale. Why are you against rolling your own task queue besides it seeming complicated?

https://wakatime.com/blog/56-building-a-background-task-queu...

Re: Choose Postgres queue technology

#280

Post by acaloiar »

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-backend (or any other queue for that matter) background job workers in their applications. There's a lot of nuance and implementation details to get wrong with background jobs, and for that reason I think background work should generally be done by more comprehensive, dedicated libraries or frameworks.

If every Rails application didn't have Sidekiq/Active Jobs and instead had bespoke background worker implementations, Rails applications would likely have a much less rosy reputation on account of their unreliability.

Post reply on HN