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

341–350 of 369 posts

Re: Choose Postgres queue technology

#341

Post by nsonha »

sarchertech wrote: post #265
nsonha wrote: post #227

Earlier quoted context omitted.

There is clearly a conceptual difference between a set of things from which you pull things out randomly, and a queue. A queue always has intrinsic criteria to select the next item to be pulled out.

There are many times when the start order doesn’t really matter, and the additional sorting overhead isn’t worth it. In those cases people will still tend to refer to the entity holding the jobs to be processed as a queue despite the fact that it doesn’t strictly follow FIFO order. If they are being technically precise, queue isn’t the correct term, but language changes with context and time. Either way the implement…

you're confusing between "i don't care about order", and "there is no order". Name ONE queue implementation that doesn't have order.

Re: Choose Postgres queue technology

#342

Post by nijave »

vb-8448 wrote: post #289
nijave wrote: post #288

If you're on a cloud provider, I'd say just use their offering. For small/medium amounts of messages (single digit millions a day) the cost will be trivial. A lot of frameworks already have queue/job libraries with adapters (so you're not really locked in) and cloud providers are highly scalable and fault tolerant. It seems silly to try to build into Postgres something that is already cheap and readily available unle…

> If you're on a cloud provider, I'd say just use their offering. For small/medium amounts of messages (single digit millions a day) the cost will be trivial. It's a good compromise but not suitable for every use case. The thing I really don't like is that you need to be connected to the cloud even for local development and test.

AWS SDK can stub responses. In general, I usually recommend creating local/in memory implementations of infrastructure so you don't end up running a gigantic stack locally. You can write tests against the real integration that selectively run against a real environment and write contract tests that cover the real implementation and your local stubs (or maybe even interfaces is strongly typed languages are sufficient).

Some languages/frameworks have this built in (a lot of ORMs do this out of the box). A lot of frameworks also have facilities for conditionally wiring in different implementations based on runtime config.

Re: Choose Postgres queue technology

#343

Post by zacksiri »

mixmastamyk wrote: post #333
zacksiri wrote: post #148

During my tenure as CTO at a fintech company I built a banking engine using postgres backed queue system using Elixir / Phoenix. It's still in use today. The company processed large volumes of transactions and we were able to do things in real-time in terms of payments. Our system reached a point where I realized that we can scale almost infinitely just using a 2 tier architecture (Elixir / Phoenix / Oban and Postgre…

Nice. How did you split the data and queue records? Tables, dbs, partitions, etc?

The job queue had it's own table. It's basically whatever was the default of the job queue library we were using.

Re: Choose Postgres queue technology

#344

Post by mlyle »

xpe wrote: post #318
mlyle wrote: post #198

Earlier quoted context omitted.

Disimbue is also a word with an appropriate meaning-- even if it's archaic (and no longer appears in most dictionaries) and pretentious.

No, I won't believe it until I see evidence. Did you find it in a credible source? Which one(s)? I've found nothing credible in Merriam Webster, Etymology Online, nor _any_ other I've searched. There is at least one low-quality ad-serving site that credits ChatGPT with a definition. I'm happy to be enlightened.

e.g. Fallow's 1898 dictionary of synonyms and antonyms: https://imgur.com/a/65yLkw7

https://archive.org/download/completedictiona00falluoft

There's many uses in British literature of the 1800's, and a whole lot of uses in academic literature of the 70's to 80's. https://i.imgur.com/BhMv2nF.png "Disabuse" would fit into many of these slots, but not all.

Only common use now is RPG jargon; imbuing something with an attribute is something role playing nerds talk about, and it really needs an antonym.

Re: Choose Postgres queue technology

#345

Post by worthless-trash »

nsonha wrote: post #341
sarchertech wrote: post #265

Earlier quoted context omitted.

There are many times when the start order doesn’t really matter, and the additional sorting overhead isn’t worth it. In those cases people will still tend to refer to the entity holding the jobs to be processed as a queue despite the fact that it doesn’t strictly follow FIFO order. If they are being technically precise, queue isn’t the correct term, but language changes with context and time. Either way the implement…

you're confusing between "i don't care about order", and "there is no order". Name ONE queue implementation that doesn't have order.

Having and caring about are different things.

I care about money, I dont have money.

Re: Choose Postgres queue technology

#346

Post by natmaka »

runeks wrote: post #292
natmaka wrote: post #230

Earlier quoted context omitted.

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 “scalabl…

This is all a matter of balancing constraints. I wrote "you know that when you will need it you probably won't have time to invest into migrating this component.". I didn't wrote "always go for the more scalable".

For a starter "the most scalable component is always the most difficult to integrate and use" isn't true, and "whatever your team knows or don't know, the challenges tied to integrating then exploiting a given component are always the same". There are many parameters. In some contexts taking into account the team's subjective preferences is crucial.

There is no universal rule, à la "always go for the most scalable, neglecting any other consideration" or "the minimal immediate effort is always the best option".

Re: Choose Postgres queue technology

#347

Post by runeks »

bunderbunder wrote: post #296
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 UP…

> 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.

What about availability, though? The distributed solution is also useful to avoid downtime in case of single node failure.

Is there an off-the-shelf solution that lets me do that with Postgres? I know the newest version (16) just added active-active replication, but I wouldn’t know how to use that to achieve resilience.

Re: Choose Postgres queue technology

#348

Post by hiyer »

physicles wrote: post #174
what-no-tests wrote: post #101

Earlier quoted context omitted.

No time at the moment to break it all down, but here's a previous discussion thread on a similar topic. https://news.ycombinator.com/item?id=27483003

Good counter-point. I see these main points over there: 1. It doesn't scale (there it is again) 2. Queuing with Postgres is super fiddly to get right 3. You're hacking a queue on top of something that isn't a queue 4. Running redis or rabbit isn't all that complicated #1 as TFA argues, premature concern about scaling is the root of so much needless complexity. You should make scaling decisions like this: 1) assume bo…

#3 applies to Redis as well - it's an in-memory KV-store and not a queue. But as TFA mentioned, it's one of the top tools used to implement queues.

Re: Choose Postgres queue technology

#349

Post by runeks »

ekidd wrote: post #319
merb wrote: post #316

Earlier quoted context omitted.

slower to run, but when you keep the postgres connection open you will know that the job is still running, while with for update skip locked you would need to have a status and a job_timeout basically. so pg_try_advisory_lock/pg_advisory_unlock can lock over transactions while for update skip locked can't, thus you would either need to keep a transaction open or use status+job_timeout (and in postgres you should not…

Here's my current favorite recipe for building complex job systems on PostgreSQL. I'm not thinking about "send an email"-type jobs, but bigger jobs that do complex tasks. The usual trick I use is to have a `jobs.state` field containing "pending", "running", "done", or "error" (or whatever that job system needs). I only hold SELECT FOR UPDATE SKIPPED LOCKED long enough to: 1. Transition from "pending" to "running". Or…

Isn’t this just https://news.ycombinator.com/item?id=29599132 with the addition of a custom worker ID (pod name)?
Post reply on HN