Interesting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?
You lose transactional guarantees if you notify outside of the transaction though
Postgres LISTEN/NOTIFY does not scale
41–50 of 328 posts
Re: Postgres LISTEN/NOTIFY does not scale
#42Right, 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)
I found recently that you can write directly to the WAL with transactional guarantees, without writing to an actual table. This sounds like it would be amazing for queue/outbox purposes, as the normal approaches of actually inserting data in a table cause a lot of resource usage (autovacuum is a major concern for these use cases). Can’t find the function that does that, and I’ve not seen it used in the wild yet, idk…
One annoying thing is that there is no counterpart for an operation to wait and read data from WAL. You can poll it using pg_logical_slot_get_binary_changes, but it returns immediately.
It'd be nice to have a method that would block for N seconds waiting for a new entry.
You can also use a streaming replication connection, but it often is not enabled by default.
Re: Postgres LISTEN/NOTIFY does not scale
#43RBDMS 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.
Re: Postgres LISTEN/NOTIFY does not scale
#44Postgres 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).
Re: Postgres LISTEN/NOTIFY does not scale
#45Interesting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?
You lose transactional guarantees if you notify outside of the transaction though
Yeah, but pub/sub systems already need to be robust to missed messages. And, sending the notify after the transaction succeeds usually accomplishes everything you really care about (no false positives).
Re: Postgres LISTEN/NOTIFY does not scale
#46There’s lots of ways to invoke NOTIFY without doing it from with the transaction doing the work. The post author is too focused on using NOTIFY in only one way. This post fails to explain WHY they are sending a NOTIFY. Not much use telling us what doesn’t work without telling us the actual business goal. It’s crazy to send a notify for every transaction, they should be debounced/grouped. The point of a NOTIFY is to l…
Yeah, the way I've always used LISTEN/NOTIFY is just to tell some pool of workers that they should wake up and check some transactional outbox for new work. False positives are basically harmless and therefore don't need to be transactional. If you're sending sophisticated messages with NOTIFY (which is a reasonable thing to think you can do) you're probably headed for pain at some point.
Re: Postgres LISTEN/NOTIFY does not scale
#47Got 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 due diligence on before implementing a write heavy work load.
Re: Postgres LISTEN/NOTIFY does not scale
#48Earlier quoted context omitted.
I found recently that you can write directly to the WAL with transactional guarantees, without writing to an actual table. This sounds like it would be amazing for queue/outbox purposes, as the normal approaches of actually inserting data in a table cause a lot of resource usage (autovacuum is a major concern for these use cases). Can’t find the function that does that, and I’ve not seen it used in the wild yet, idk…
One annoying thing is that there is no counterpart for an operation to wait and read data from WAL. You can poll it using pg_logical_slot_get_binary_changes, but it returns immediately. It'd be nice to have a method that would block for N seconds waiting for a new entry. You can also use a streaming replication connection, but it often is not enabled by default.
I think replication is the way to go, it’s kinda what it’s for.
Might be a bit tricky to get debezium to decode the logical event, not sure
Re: Postgres LISTEN/NOTIFY does not scale
#49RBDMS 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.
But those rules of thumb aren't true. People use Postgres for job queues and write-heavy applications.
You'd have to at least accompany your memes with empirics. What is write-heavy? A number you might hit if your startup succeeds with thousands of concurrent users on your v1 naive implementation?
Else you just get another repeat of everyone cargo-culting Mongo because they heard that Postgres wasn't web scale for their app with 0 users.
Re: Postgres LISTEN/NOTIFY does not scale
#50wow thanks for the heads up! no idea this was a thing.
It’s not a thing.