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

301–310 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#301

Post by cryptonector »

maxbond wrote: post #285
cryptonector wrote: post #250

Earlier quoted context omitted.

That's pretty cool. IMO LISTEN/NOTIFY is badly designed as an interface to begin with because there is no way to enforce access controls (who can notify; who can listen) nor is there any way to enforce payload content type (e.g., JSON). It's very unlike SQL to not have a `CREATE CHANNEL` and `GRANT` commands for dealing with authorization to listen/notify. If you have authz then the lack of payload content type const…

> there is no way to enforce access controls (I thought this was a fun puzzle, so don't take this as advice or as disagreement with your point.) There is the option to use functions with SECURITY DEFINER to hack around this, but the cleanest way to do it (in the current API) would be to encrypt your messages on the application side using an authenticated system (eg AES-GCM). You can then apply access control to the k…

Yes, I've thought about this too, but it's annoying to have to resort to that, no?

Re: Postgres LISTEN/NOTIFY does not scale

#302

Post by maxbond »

cryptonector wrote: post #301
maxbond wrote: post #285

Earlier quoted context omitted.

> there is no way to enforce access controls (I thought this was a fun puzzle, so don't take this as advice or as disagreement with your point.) There is the option to use functions with SECURITY DEFINER to hack around this, but the cleanest way to do it (in the current API) would be to encrypt your messages on the application side using an authenticated system (eg AES-GCM). You can then apply access control to the k…

Yes, I've thought about this too, but it's annoying to have to resort to that, no?

Absolutely, Postgres is fantastic but LISTEN/NOTIFY is it's weakest feature. It's convenient, it has the potential to open up compelling use cases, it very nearly works, but has all these nasty limitations and rough edges that cause people to steer clear. I think a lot of people don't know it exists, you almost never hear it mentioned in discussions about async job queues in Postgres (which would seem like and obvious use case). I don't think it's ever been mentioned on the Postgres.FM podcast (I'm sure they're aware of it but it speaks to the lack of usage). I'd love to see it get some love in future releases, and I agree that access control is necessary for it to really work.

Re: Postgres LISTEN/NOTIFY does not scale

#303

Post by fmajid »

Listen/Notify is potentially lossy and should not be used. At one of my previous companies we used it for cache invalidation (a trigger on tables would sent notify messages to invalidation Redis keys for potentially affected cache entries). We ended up ripping it out and replacing it with NSQ.io as a reliable transport.

Re: Postgres LISTEN/NOTIFY does not scale

#304

Post by mike_hearn »

osigurdson wrote: post #268
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…

>> but they should just migrate to an Oracle Database No big tech companies or unicorn type startups are using Oracle. Is your claim that they are all wrong? >> Some startup builds on Postgres then spends half their eng budget at the most critical growth time firefighting around its limits instead of scaling their business This is why I suggest starting with some kind of normal queue / stream mechanism and columnar D…

Big tech companies do use it. Apple was advertising for Oracle DBA roles just last month. And consider that Amazon had to staff a massive multi-year project to migrate off it to their own in-house DB, which they only did because they had become a competitor.

W.R.T. unicorn type startups; yes, my argument is that they are all wrong and should be using a different database. There's competitive advantage to be had there.

Re: Postgres LISTEN/NOTIFY does not scale

#305

Post by williamdclt »

cryptonector wrote: post #276
williamdclt wrote: post #255

Earlier quoted context omitted.

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

I'd like to say that only some roles can NOTIFY to some channels. Similarly for alternatives to LISTEN/NOTIFY.

Right. It’s not something I’ve had to handle, I’ve always worked in environments where db clients are well behaved and under my control, what’s your use case out of interest?

Re: Postgres LISTEN/NOTIFY does not scale

#306

Post by gunnarmorling »

brightball wrote: post #244
gunnarmorling wrote: post #151

Earlier quoted context omitted.

pg_logical_emit_message() is how I recommend users on Postgres to implement the outbox pattern [1]. No table overhead as you say, no need for housekeeping, etc. It has some other cool applications, e.g. providing application-specific metadata for CDC streams or transactional logging, wrote about it at [2] a while ago. Another one is making sure replication slots can advance also if there's no traffic in the database…

You know, this would be a great talk at the 2026 Carolina Code Conference...

Ha, that's interesting :) Do you have any more details to that one?

Re: Postgres LISTEN/NOTIFY does not scale

#307

Post by saltcured »

valenterry wrote: post #120
saltcured wrote: post #65

Earlier quoted context omitted.

For reliability, you can make the recipient poll the table(s) of record for relevant state and use the out-of-band notification channel as a latency-reducer. So, the poller is eventually consistent at some configured polling interval, but opportunistically can respond much sooner when told to check again ahead of the next scheduled poll time. In my experience, this means you make sure the polling solution is complete…

> you can make the recipient poll the table(s) of record for relevant state That is tricky due to transactions and visibility. How do you write the poller to not miss events that were written by a long/blocked transaction? You'd have to set the poller scan to a long time (e.g. "process events that were written since now minus 5minutes") and then make sure transactions are cancelled hard before those 5minutes.

I'd say that the most reliable way is to use some mutable lifecycle metadata other than times to identify work. An indexed query will find the "new and unclaimed" work items and process them, regardless of their potentially backdated temporal metadata.

Updates of the lifecycle properties can also help coordinate multiple pollers so that they never work on the same item, but they can have overlapping query terms so that each poller is capable of picking up a particular item in the absence of others getting there first.

You also need some kind of lease/timeout policy to recognize orphaned items. I.e. claimed in the DB but not making progress. Workers can and should have exception handling and compensating updates to report failures and put items "back in the queue", but worst case this update may be missing. Some process, or even some human operator, needs to eventually compensate on behalf of the AWOL worker.

In my view, you always need this kind of table-scanning logic, even if using something like AMQP for work dispatch. You get in trouble when you fool yourself into imagining "exactly once" semantics actually exists. The message-passing layer could opportunistically scale out the workload, but a relational backstop can make sure that the real system of record is coherent and reflecting the business goals. Sometimes, you can just run this relational layer as the main work scheduler and skip the whole message-passing build-out.

Re: Postgres LISTEN/NOTIFY does not scale

#308

Post by jelder »

lytedev wrote: post #291
jelder wrote: post #221

Earlier quoted context omitted.

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

What is the first-best choice for a new project? SQLite?

That’s my point, there is no best-first choice for everything. There will always be trade-offs. But Postgres makes the right trade-offs to be good enough in almost every scenario.

Re: Postgres LISTEN/NOTIFY does not scale

#309

Post by infogulch »

JoelJacobson wrote: post #196

Hey folks, I ran into similar scalability issues and ended up building a benchmark tool to analyze exactly how LISTEN/NOTIFY behaves as you scale up the number of listeners. Turns out that all Postgres versions from 9.6 through current master scale linearly with the number of idle listeners — about 13 μs extra latency per connection. That adds up fast: with 1,000 idle listeners, a NOTIFY round-trip goes from ~0.4 ms…

Cool! This article and thread has already been referenced on the mailing list, maybe its worth mentioning this benchmark and experiment.

https://www.postgresql.org/message-id/flat/CAM527d_s8coiXDA4...

https://www.postgresql.org/message-id/flat/175222328116.3157...

Re: Postgres LISTEN/NOTIFY does not scale

#310

Post by cryptonector »

williamdclt wrote: post #305
cryptonector wrote: post #276

Earlier quoted context omitted.

I'd like to say that only some roles can NOTIFY to some channels. Similarly for alternatives to LISTEN/NOTIFY.

Right. It’s not something I’ve had to handle, I’ve always worked in environments where db clients are well behaved and under my control, what’s your use case out of interest?

Security in depth. If I have to give someone login access, I should be able to control what they do.
Post reply on HN