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

251–260 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#251

Post by imtringued »

dathinab wrote: post #235
mike_hearn wrote: post #177

Earlier quoted context omitted.

Using the database for queues is more than fine, it's often essential to correctness. In many use cases for queues you need to atomically update the database with respect to popping from the queue, and if they're separate systems you end up needing either XA or brittle and unreliable custom idempotency logic. I've seen this go wrong before and it's not nice, the common outcome is business-visible data corruption that…

if you need transaction across a queue into a normal SQL DB or similar I believe you are doing something very wrong. Sure you need transaction about processing things in a queue (mark as "taken out", but not yet remove then remove or "place back in (or into a failed messages inbox)" on timeout or similar can be _very_ important for queue systems. But the moment the "fail save if something dies while processing a mess…

>But the moment the "fail save if something dies while processing a message" becomes a directly coupled with DB transactions you have created something very brittle and cumbersome.

The standard workflow for processing something from a queue is to keep track of all the messages you have already processed in the transactional database and simply request the remaining unprocessed messages. Often this is as simple as storing the last successfully processed message ID in the database and updating it in the same transaction that has processed the message. If an error occurs you roll the transaction back, which also rolls back the last message ID. The consumer will automatically re-request the failed message on the next attempt, giving you out of the box idempotency for at least once messaging.

Re: Postgres LISTEN/NOTIFY does not scale

#252

Post by mattxxx »

The article is good, but maybe a bit negative on the postgres feature. I think the article reads much better with the slant:

  "LISTEN/NOTIFY got us to this level of concurrency; here's how we diagnosed the performance cliff, and here's what we're doing now."
Which is like... cool, you were able to scale pretty far and create a lot of value before you needed to find a new solution.

Re: Postgres LISTEN/NOTIFY does not scale

#253

Post by cryptonector »

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

Instead of LISTEN/NOTIFY you could listen to the wal / logical replication stream.

Or you could have a worker whose only job is to listen to the wal / logical replication stream and then NOTIFY. Being the only one to do so would not burden other transactions.

Or you could have a worker whose only job is to listen to the wal / logical replication stream and then publish on some non-PG pubsub system.

Re: Postgres LISTEN/NOTIFY does not scale

#254

Post by cryptonector »

williamdclt wrote: post #27
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)

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…

`pg_logical_emit_message()` is great and better than `NOTIFY` in terms of how it works, but...

`pg_logical_emit_message()` perpetuates/continues the lack of authz around `NOTIFY`.

Re: Postgres LISTEN/NOTIFY does not scale

#255

Post by williamdclt »

cryptonector wrote: post #254
williamdclt wrote: post #27

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

`pg_logical_emit_message()` is great and better than `NOTIFY` in terms of how it works, but... `pg_logical_emit_message()` perpetuates/continues the lack of authz around `NOTIFY`.

What do you mean by this? What authz would you expect/like?

Re: Postgres LISTEN/NOTIFY does not scale

#256

Post by cryptonector »

TrackerFF wrote: post #185
jjgreen wrote: post #175

Earlier quoted context omitted.

Just for the em-dashes? Some humans also use them.

A decent way to classify human vs bot when it comes to dashes, is that all bots use ‘em-dashes(—), while almost none use regular dashes (-) in writing. While plenty of humans will use regular dashes, because they won’t bother to look for ‘em-dashes on the keyboard, or phone. Of course, you have the people that correctly use em-dashes, too.

On iPhones the input methods turn -- into —. If you see me using em-dashes it's cause I wrote on an iPhone. But I prefer -- to —.

Re: Postgres LISTEN/NOTIFY does not scale

#257

Post by cryptonector »

calderwoodra wrote: post #205
bjornsing wrote: post #156

If I’m not mistaken LISTEN/NOTIFY doesn’t work with connection poolers, and you can’t have tens of thousands of connections to a Postgres database. Not sure you need a more elaborate analysis than that to reach the same conclusion.

Why doesn't LISTEN/NOTIFY work with connection poolers?

Because if you have N connections in your pool you're going to have to execute LISTEN on all N, or else the connection pool needs to be LISTEN-aware so it can process async notifies by calling some registered callback.

I.e., the connection pool API has to be designed with this in mind.

For that matter connection pools also need to be designed with the ability to run code upon connecting to create TEMP schema elements because PG lacks GLOBAL TEMP.

Re: Postgres LISTEN/NOTIFY does not scale

#258

Post by cryptonector »

FZambia wrote: post #211

For real-time notifications, I believe Nats ( https://nats.io ) or Centrifugo ( https://centrifugal.dev ) are worth checking out these days. Messages may be delivered to those systems from PostgreSQL over replication protocol through Kafka as an intermediary buffer. Reliable real-time messaging comes with lots of complexities though, like late message delivery, duplicate message delivery. If the system can be built a…

And Debezium.

Re: Postgres LISTEN/NOTIFY does not scale

#259

Post by closeparen »

jelder wrote: post #221
osigurdson wrote: post #110

I like this article. Lots of comments are stating that they are "using it wrong" and I'm sure they are. However, it does help to contrast the much more common, "use Postgres for everything" type sentiment. It is pretty hard to use Postgres wrong for relational things in the sense that everyone knows about indexes and so on. But using something like L/N comes with a separate learning curve anyway - evidenced in this c…

"use Postgres for everything" is certainly wrong, eventually. It's still the second-best choice for every new project, and most products will never see the traffic levels that justify using something more specialized. Obviously, recall.ai hit the level of traffic where Postgres was no longer ideal. I bet they don't regret it for the other parts of their product.

They aren't even questioning its use as a database, just as an event bus.

Re: Postgres LISTEN/NOTIFY does not scale

#260

Post by Matthias247 »

Clarification question:

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

It only serializes commits where NOTIFY was issued as part of the transaction, right? Transactions which did not call NOTIFY should not be affected?

Post reply on HN