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

261–270 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#261

Post by qianli_cs »

eatonphil wrote: post #197
qianli_cs wrote: post #89

Earlier quoted context omitted.

My colleague did some internal benchmarking and found that LISTEN/NOTIFY performs well under low to moderate load, but doesn't scale well with a large number of listeners. Our findings were pretty consistent with this blog post. (Shameless plug [1]) I'm working on DBOS, where we implemented durable workflows and queues on top of Postgres. For queues, we use FOR UPDATE SKIP LOCKED for task dispatch, combined with expo…

Why not read the WAL?

We considered using WAL for change tracking in DBOS, but it requires careful setup and maintenance of replication slots, which may lead to unbounded disk growth if misconfigured. Since DBOS is designed to bolt onto users' existing Postgres instances (we don't manage their data), we chose a simpler, less intrusive approach that doesn't require a replication setup.

Plus, for queues, it's so much easier to leverage database constraints and transactions to implement global concurrency limit, rate limit, and deduplication.

Re: Postgres LISTEN/NOTIFY does not scale

#262

Post by jumski »

pbronez wrote: post #219
jumski wrote: post #204

Earlier quoted context omitted.

Using queues in atomic, transactional way was a core principle for building https://pgflow.dev - having whole workflow state transactionally updated alongside the work on the in db queue really simplifies a lot of things: debugging is easier, audit log is easy, reporting, stats etc are one SQL query away.

Looks interesting- but why the Supabase dependency? That’s a much tighter requirement than a vanilla PostgreSQL extension or something like PostgREST

Valid point!

So pgflow is really agnostic and Postgres is it's fundamental dependency. All components are modular and ready to be adapted to other runtimes.

It's just that Supabase is what I use and I figured out this will be my first platform, but the abstraction to port to others is there!

Re: Postgres LISTEN/NOTIFY does not scale

#264

Post by gwbas1c »

parthdesai wrote: post #41
hombre_fatal wrote: post #2

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

... And working outside of the guarantee is harder, especially if you're in a "move fast and break things because we can fix it later" mode.

Anyway, the article indicates that the fix was very simple and primarily in the application layer. Makes me wonder if someone was getting "creative" when they used LISTEN/NOTIFY.

Re: Postgres LISTEN/NOTIFY does not scale

#265

Post by LgWoodenBadger »

j16sdiz wrote: post #94
kristianc wrote: post #84

Earlier quoted context omitted.

Sounds like a deliberate attempt to avoid spinning up Redis, Kafka, or an outbox system early on.. and then underestimated how quickly their scale would make it blow up. Story as old as time.

Kafka head of line blocking sucks.

Isn't this one of the things partitioning is meant to ameliorate? Either through partitions themselves, or through an appropriate partitioning strategy?

Re: Postgres LISTEN/NOTIFY does not scale

#266

Post by riku_iki »

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…

> However, it does help to contrast the much more common, "use Postgres for everything" type sentiment.

I think sentiment is to use "for everything in 99% business cases", which involves few 100GB of data with some thousands QPS, and could be handled by PG very well.

Re: Postgres LISTEN/NOTIFY does not scale

#267

Post by shivasaxena »

const_cast wrote: post #98
candiddevmike wrote: post #80

Earlier quoted context omitted.

I don't follow how you would do that in a stored procedure outside of a trigger.

I think instead of performing an INSERT you call a stored proc that does the insert and some extra stuff.

Yes, we already have all of our business logic in postgres functions(create_order, create_partial_payment etc).

Doing the extra work in stored procedures is noticeably faster than relying on triggers.

Re: Postgres LISTEN/NOTIFY does not scale

#268

Post by osigurdson »

mike_hearn wrote: post #177
ownagefool wrote: post #166

Earlier quoted context omitted.

Largely agree. Functionality wise if you don't have many jobs, using the database as the queue is fine. However, I've been in several situations where scaling the queue brings down the database, and therefore the app, and am thus of the opinion you probably shouldn't couple these systems too tightly. There are pros and cons, of course.

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 DB if needed. It isn't even harder than using one DB, particularly if you are using niche features.

Re: Postgres LISTEN/NOTIFY does not scale

#269

Post by bhollis »

The pattern I've always used for this, which I suspect is what they landed on, is to have an optimistic notification method in a separate message queue that says "something changed that's relevant to you". Then you can dedupe that, etc. Then structure the data to easily sync what's new, and let the client respond to that notification by calling the sync API. That even lets you use multiple notification methods for notification. None of that involves having to have the database coordinate notifications in the middle of a transaction.
Post reply on HN