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

191–200 of 369 posts

Re: Choose Postgres queue technology

#191

Post by deredede »

refibrillator wrote: post #87
matsemann wrote: post #3

Earlier quoted context omitted.

I've done even simpler without locks (as no transaction logic), where I select a row, and then try to update a field about it being taken. If 1 row is affected, it's mine. If 0, someone else did it before me and I select a new row. I've used this for tasks at big organizations without issue. No need for any special deployments or new infra. Just spin up a few worker threads in your app. Perhaps a thread to reset aban…

PSA: This is a read-modify-write pattern, thus it is not safe under concurrency unless a transaction isolation level of SERIALIZABLE is specified, or some locking mechanism is used (select for update etc).

The part about checking the number of affected rows hints at using `UPDATE ... WHERE ...` which should act as an atomic CAS regardless of isolation level.

Edit: To clarify, I mean `SELECT id WHERE used = 0` followed by `UPDATE ... SET used = 1 WHERE id = ... AND used = 0`

Re: Choose Postgres queue technology

#192

Post by zacksiri »

ansc wrote: post #180
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…

>There was a lot of back and forth between engineers that discussed whether we should add the index. Jeez. What was the idea behind not adding? Disk space I presume?

There was a fear that having to create an index on a table that large would take a long time, and I think some of it was also ego "I intentionally didn't add it in, because so and so reason". This was why I dug in and did my thing, debunk all the fear / opinions / rationalization. Sometimes you just gotta be able to tell people they're wrong supported with empirical evidence. That's how the team will grow. There is just no need to dance around facts. I remember having to tell the team, "taking a long time to run an index is no reason to avoid creating the index".

Re: Choose Postgres queue technology

#193

Post by perfmode »

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?

Re: Choose Postgres queue technology

#194

Post by zacksiri »

SanderNL wrote: post #160
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…

Interesting how the immediate reaction is “postgres does not scale” when there is a single table lacking an index. This also tells how important competence and knowledge of the system is. People that came in new and didn’t know the system like you do probably lacked the confidence/skills to just “get in” like that.

Yeah, I think though what happened in this scenario probably happens a lot everywhere else also. In my entire career, this type of scenario is very typical. Lots of Meetings / discussions, standups and talking uselessly without jumping in face to face with the actual problem. Things get in the way of the science and facts. Which is why it's important to remove fear, think from first principles and break things down and get your hands dirty.

Re: Choose Postgres queue technology

#195

Post by mlyle »

Karrot_Kream wrote: post #183
aduffy wrote: post #2

For several projects I’ve opted for the even dumber approach, that works out of the box with every ORM/Query DSL framework in every language: using a normal table with SELECT FOR UPDATE SKIP LOCKED https://www.pgcasts.com/episodes/the-skip-locked-feature-in-... It’s not “web scale” but it easily extends to several thousand background jobs in my experience

In my experience, a queue system is the worst thing to find out isn't scaling properly because once you find out your queue system can't architecturally scale, there's no easy fix to avoid data loss. You talk about "several thousand background jobs" but generally, queues are measured in terms of Little's Law [1] for which you need to be talking about rates; according to Little's Law namely average task enqueue rate p…

> 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 queries will throttle each other's locks a

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

Re: Choose Postgres queue technology

#196

Post by ezekiel68 »

ansc wrote: post #180
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…

>There was a lot of back and forth between engineers that discussed whether we should add the index. Jeez. What was the idea behind not adding? Disk space I presume?

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

Re: Choose Postgres queue technology

#197

Post by mlyle »

s17n wrote: post #185

I don't usually downvote posts, but this article is just garbage - a rant about "the cargo cult of scale" but no actual arguments as to why Postgres is better than redis or sqs or anything else. The main reason that people don't use postgres for this kind of thing isn't some kind of misguided obsession with scalability, it's because postgres is way more complicated to deploy / manage and harder to use for application…

The argument is stated-- you shouldn't adopt too many technologies. The author merely says that if you're running postgres already, you should seriously consider it for your queue. They also point out that many of the arguments against psql-as-queue are flawed.

Re: Choose Postgres queue technology

#198

Post by mlyle »

xpe wrote: post #147

> I hope to disimbue anyone of the notion that Postgres is an inferior queue technology. I offer this correction to the author: it is "disabuse" not "disimbue".

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

Re: Choose Postgres queue technology

#199

Post by matsemann »

deredede wrote: post #191
refibrillator wrote: post #87

Earlier quoted context omitted.

PSA: This is a read-modify-write pattern, thus it is not safe under concurrency unless a transaction isolation level of SERIALIZABLE is specified, or some locking mechanism is used (select for update etc).

The part about checking the number of affected rows hints at using `UPDATE ... WHERE ...` which should act as an atomic CAS regardless of isolation level. Edit: To clarify, I mean `SELECT id WHERE used = 0` followed by `UPDATE ... SET used = 1 WHERE id = ... AND used = 0`

This is spot on! We let the db provide the atomics.

Re: Choose Postgres queue technology

#200

Post by zacksiri »

ezekiel68 wrote: post #196
ansc wrote: post #180

Earlier quoted context omitted.

>There was a lot of back and forth between engineers that discussed whether we should add the index. Jeez. What was the idea behind not adding? Disk space I presume?

>> 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.
Post reply on HN