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

251–260 of 369 posts

Re: Choose Postgres queue technology

#251

Post by lomereiter »

w23j wrote: post #231
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.

Long running transactions can lead to an accumulation of dead tuples: https://brandur.org/postgres-queues

This article was written in 2015, a year before idle_in_transaction_session_timeout parameter was added (in Postgres 9.6) - which is unfortunately still disabled by default, but that's the easiest way to make sure no transaction sits idle for too long.

Re: Choose Postgres queue technology

#252

Post by valenterry »

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…

Scalability comes at a price. Unless you need it, it makes you less flexible. And that is exactly what you don't want to be as a startup.

For instance, if you use postgres with a low load, it is almost trivial to migrate schemas, add new constraints, do analytics etc.

If you use SQS, Cassandra, whatever, then you now get scalability/availability but it becomes much more time-consuming to change things if you figure out that your original design doesn't work. Say the business comes and says "please add constraint X. All users of type foo must never combined value bar at the same time."

It is possible to implemented that without postgres, but it is not easy or simple, especially if you need to make changes.

Therefore, my take is that you either use postgres to stay flexible or you use both postgres and something else on top of it when you know that you won't have to change things. Of course this means additional infrastructure/maintenance overhead.

In the end it's always a trade-off, you just need to know when to trade which thing off against what.

Re: Choose Postgres queue technology

#253

Post by bavell »

w23j wrote: post #231
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.

Long running transactions can lead to an accumulation of dead tuples: https://brandur.org/postgres-queues

This is from 2015, does it still hold true in 2023?

Re: Choose Postgres queue technology

#254

Post by Lutger »

valenterry wrote: post #252
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…

Scalability comes at a price. Unless you need it, it makes you less flexible. And that is exactly what you don't want to be as a startup. For instance, if you use postgres with a low load, it is almost trivial to migrate schemas, add new constraints, do analytics etc. If you use SQS, Cassandra, whatever, then you now get scalability/availability but it becomes much more time-consuming to change things if you figure o…

This is all true, important and often misunderstood, but beside the point made to which you reply.

There's a (sort of) objective trade-off to be made, but another dimension is how familiar you are with the solution and/or how quick can you implement it using documentation and examples.

If you happen to know exactly how to create a horizontally scalable microservice based hairball with nodejs, then maybe you are quicker with that than with some traditional django monolith using a nicely normalized sql database (or whatever).

In a startup, you are often always squeezed for time, so making the objectively right tradeoff for your context is usually secondary to the simple question of 'when can you ship?' If the scalable-yet-inflexible is what stack overflow abundantly recommends and documents, maybe this is quicker to get done now, whatever the consequences are on the longer run.

Re: Choose Postgres queue technology

#255

Post by samus »

A1kmm wrote: post #228
samus wrote: post #62

Earlier quoted context omitted.

I recently got introduced to this system at work, and also built a new job using it. It works fine, but since I had to implement work stealing to deal with abandoned jobs in a timely manner, I wouldn't dare to use it for actions that absolutely must not happen twice.

Exactly-once is only meaningfully possible if you have a rollback for tasks of unknown completion state - for example if the task involves manipulating the same database as the one controlling the task execution. Otherwise, it becomes the (impossible to solve) two-generals problem between updating the task status and performing the task.

Full agree here.

There is actually another possibility: there must be a way to check whether the receiving system has received the message. But this only works if there are no "rogue" senders.

Re: Choose Postgres queue technology

#256

Post by gazpacho »

valenterry wrote: post #252
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…

Scalability comes at a price. Unless you need it, it makes you less flexible. And that is exactly what you don't want to be as a startup. For instance, if you use postgres with a low load, it is almost trivial to migrate schemas, add new constraints, do analytics etc. If you use SQS, Cassandra, whatever, then you now get scalability/availability but it becomes much more time-consuming to change things if you figure o…

> Scalability comes at a price. Unless you need it, it makes you less flexible. And that is exactly what you don't want to be as a startup.

This is a valid comment. I’ve chosen Postgres in the past for the features, not the performance. For example guaranteed at most once delivery (via row locks) and filtering of jobs based on attributes (it’s a database after all).

Re: Choose Postgres queue technology

#257

Post by sgarland »

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 MD5 are vanishingly small.

As to the actual topic, it’s fine IFF you carefully monitor for accumulating dead tuples, and adjust auto-vacuum for that table as necessary. While not something you’d run into at the start, at a modest scale you may start to see issues. May. You may also opt to switch to Redis or something else before that point anyway.

EDIT: if you choose ULID, UUIDv7, or some other k-sortable key, the problem isn’t nearly as bad, but you still don’t need it in this situation. Save yourself 8 bytes per key.

[0]: https://github.com/acaloiaro/neoq

Re: Choose Postgres queue technology

#258

Post by randomdata »

ritzaco wrote: post #241
zozbot234 wrote: post #236

Earlier quoted context omitted.

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

If SQLAlchemy’s documentation doesn’t explain its use with LISTEN/NOTIFY, perhaps it’s the wrong tool for the job? You are presumably not going to use it with Redis or SQS queues, so why are you so hung up on it here?

Re: Choose Postgres queue technology

#259

Post by sgarland »

ritzaco wrote: post #241
zozbot234 wrote: post #236

Earlier quoted context omitted.

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

IMHO - and this probably why I’ll never launch a product - you should understand each piece of your infra. Not necessarily to the metal on each, but I don’t think it’s unreasonable to be able to explain why each piece is necessary, what it’s doing, and how to troubleshoot it when it breaks.

With your mentioned list, three of them are Python, so that significantly reduces the breadth.

Re: Choose Postgres queue technology

#260

Post by edanm »

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…

That's a great point, that often people misunderstand.

It's even worse than you say though. As someone who has used neither Postgres or Redis for queueing, how am I supposed to know what is the "simple" solution here and if it really solves my problem?

Almost everyone uses solution X. A few people are saying "no, just use solution Y, it's obviously enough and far simpler". Even if it is far simpler, how am I supposed to know whether there are some hidden gotchas here?

Much safer to bet on technology that is proven to work, given that large amounts of people are using it in production for this purpose.

Post reply on HN