Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres LISTEN/NOTIFY does not scale

recall.ai

Post reply on HN
Print view View original

51–60 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#51

Post by sorentwo »

alberth wrote: post #44
sorentwo wrote: post #14

Postgres LISTEN/NOTIFY was a consistent pain point for Oban (background job processing framework for Elixir) for a while. The payload size limitations and connection pooler issues alone would cause subtle breakage. It was particularly ironic because Elixir has a fantastic distribution and pubsub story thanks to distributed Erlang. That’s much more commonly used in apps now compared to 5 or so years ago when 40-50% of…

I didn’t realize Oban didn’t use Mnesia (Erlang built-in).

Very very few applications use mnsesia. There’s absolutely no way I would recommend it over Postgres.

Re: Postgres LISTEN/NOTIFY does not scale

#52

Post by denysonique »

cpursley wrote: post #5

Right, plus there's character limitations (column size). This is why I prefer listening to the Postgres WAL for database changes: https://github.com/cpursley/walex?tab=readme-ov-file#walex (there's a few useful links in here)

For node.js users there is postgres.js that can listen to the Postgres WAL and emit node events that can be handled by application code.

Re: Postgres LISTEN/NOTIFY does not scale

#53

Post by kccqzy »

doc_manhat wrote: post #47

Got up to the TL;DR paragraph. This was a major red flag given the initial presentation of the discovery of a bottleneck: ''' When a NOTIFY query is issued during a transaction, it acquires a global lock on the entire database (ref) during the commit phase of the transaction, effectively serializing all commits. ''' Am I missing something - this seems like something the original authors of the system should have done…

I think it's just difficult to predict how heavy is heavy enough to make this a problem. FWIW I had worked at a startup with a much more primitive data storage system where serialized commits were actually totally fine. The startup never outgrew that bottleneck.

Re: Postgres LISTEN/NOTIFY does not scale

#54

Post by BowBun »

cpursley wrote: post #5

Right, plus there's character limitations (column size). This is why I prefer listening to the Postgres WAL for database changes: https://github.com/cpursley/walex?tab=readme-ov-file#walex (there's a few useful links in here)

Yeah until vendors butcher Postgres replication behaviors and prevent common paths of integrating these capabilities into other tools. Looking at you AWS

Re: Postgres LISTEN/NOTIFY does not scale

#55

Post by kccqzy »

0xbadcafebee wrote: post #43

RBDMS are not designed for write-heavy applications, they are designed for read-heavy analysis. Also, an RDBMS is not a message queue or an RPC transport. I feel like somebody needs to write a book on system architecture for Gen Z that's just filled with memes. A funny cat pic telling people not to use the wrong tool will probably make more of an impact than an old fogey in a comment section wagging his finger.

There are OLTP and OLAP RDBMSes. Only OLAP ones are designed for read-heavy analyses.

Re: Postgres LISTEN/NOTIFY does not scale

#56

Post by foota »

nick_ wrote: post #7
hombre_fatal wrote: post #2

Interesting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?

My thought as well. You could add notify commands to a temp table during the transaction, then run NOTIFY on each row in that temp table after the transaction commits successfully?

Wouldn't you need to then commit to remove the entries from the temp table?

Re: Postgres LISTEN/NOTIFY does not scale

#57

Post by edoceo »

cpursley wrote: post #20
leontrolski wrote: post #17

I'd be interested as to how dumb-ol' polling would compare here (the FOR UPDATE SKIP LOCKED method https://leontrolski.github.io/postgres-as-queue.html ). One day I will set up some benchmarks as this is the kind of thing people argue about a lot without much evidence either way. Wasn't aware of this AccessExclusiveLock behaviour - a reminder (and shameless plug 2) of how Postgres locks interact: https://leontrolski.…

Have you played with pgmq? It's pretty neat: https://github.com/pgmq/pgmq

Another thing for @leontrolski to add to the benchmarks - which I cannot wait to read.

Re: Postgres LISTEN/NOTIFY does not scale

#58

Post by zbentley »

nick_ wrote: post #7
hombre_fatal wrote: post #2

Interesting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?

My thought as well. You could add notify commands to a temp table during the transaction, then run NOTIFY on each row in that temp table after the transaction commits successfully?

This is roughly the “transactional outbox” pattern—and an elegant use of it, since the only service invoked during the “publish” RPC is also the database, reducing distributed reliability concerns.

…of course, you need dedup/support for duplicate messages on the notify stream if you do this, but that’s table stakes in a lot of messaging scenarios anyway.

Re: Postgres LISTEN/NOTIFY does not scale

#60

Post by zbentley »

foota wrote: post #56
nick_ wrote: post #7

Earlier quoted context omitted.

My thought as well. You could add notify commands to a temp table during the transaction, then run NOTIFY on each row in that temp table after the transaction commits successfully?

Wouldn't you need to then commit to remove the entries from the temp table?

No, so long as the rows in there are transactionally guaranteed to be present or not, a sweeper script can handle removing failed “publishes” (notifys that didn’t delete their row) later.

This does sacrifice ordering and increases the risk of duplicates in the message stream, though.

Post reply on HN