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

191–200 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#191

Post by the_duke »

v5v3 wrote: post #173
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…

Isn't Kafka the Postgresql of pub/sub I.e. use Kafka unless you have a explicit reason not to? So why Nats?

Kafka is far from trivial to operate, for one thing, even post zookeeper.

Re: Postgres LISTEN/NOTIFY does not scale

#192

Post by ako »

Jailbird wrote: post #127
bevr1337 wrote: post #126

Earlier quoted context omitted.

> the data does not get to decide what happens next based on itself. Then why bother with a relational database? Relations and schemas are business logic, and I'll take all the data integrity I can get.

I've seen both of these philosophies. I liken them to religions, the believers are devout. Code is King vs the DB is King. I'm personally Code is King, and I have my reasons (like everyone else)

It’s really not about code is better or database it better, it’s mostly about locality: if you want to update thousands of records, you can’t pull those records into a separate process, update them there and then write back. So you put your code next to the data in the database. Stored procedures are just code deployed to a database container…

Re: Postgres LISTEN/NOTIFY does not scale

#193

Post by ants_a »

nine_k wrote: post #73
shivasaxena wrote: post #21

Earlier quoted context omitted.

Yeah, I'm going to remove triggers in next deploy of a POS system since they are adding 10-50ms to each insert. Becomes a problem if you are inserting 40 items to order_items table.

Hmm, imho, triggers do scale, they are just slow. But as you add more connections, partitionss, and CPUs, the slowness per operation remains constant.

Triggers are not even particularly slow. They just hide the extra work that is being done and thus sometimes come back to bite programmers by adding a ton of work to statements that look like they should be quick.

Re: Postgres LISTEN/NOTIFY does not scale

#195

Post by ants_a »

ilitirit wrote: post #181
cap11235 wrote: post #159

Earlier quoted context omitted.

[flagged]

What is wrong with you? Why would you even bother posting a comment like this? Maybe you also don't know what ChatGPT Research is (the Enterprise version, if you really need to know), or what Executive Summary implies, but here's a snippet of the 28 sources used: https://imgur.com/a/eMdkjAh

In that snippet are links to Postgres docs and two blog posts, one being the blog post under discussion. None of those contain the information needed to make the presented claims about throughput.

To make those claims it's necessary to know what work is being done while the lock is held. This includes a bunch of various resource cleanup, which should be cheap, and RecordTransactionCommit() which will grab a lock to insert a WAL record, wait for it to get flushed to disk and potentially also for it to get acknowledged by a synchronous replica. So the expected throughput is somewhere between hundreds and tens of thousands of notifies per second. But as far as I can tell this conclusion is only available from PostgreSQL source code and some assumptions about typical storage and network performance.

Re: Postgres LISTEN/NOTIFY does not scale

#196

Post by JoelJacobson »

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 to ~14 ms.

To better understand the bottlenecks, I wrote both a benchmark tool and a proof-of-concept patch that replaces the O(N) backend scan with a shared hash table for the single-listener case — and it brings latency down to near-O(1), even with thousands of listeners.

Full benchmark, source, and analysis here: https://github.com/joelonsql/pg-bench-listen-notify

No proposals yet on what to do upstream, just trying to gather interest and surface the performance cliff. Feedback welcome.

Re: Postgres LISTEN/NOTIFY does not scale

#197

Post by eatonphil »

qianli_cs wrote: post #89
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.…

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?

Re: Postgres LISTEN/NOTIFY does not scale

#198

Post by KronisLV »

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…

> Postgres/ClickHouse/NATS

Maybe throw in a dedicated key-value store like Redis or Valkey.

Oh and maybe something S3 compatible like MinIO, Garage or SeaweedFS for storing bunches of binary data.

With all of that, honestly it should cover most of the common workloads out there! Of course, depends on how specialized vs generic you like your software to be.

Re: Postgres LISTEN/NOTIFY does not scale

#199

Post by KronisLV »

fathomdeez wrote: post #125
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…

This kind of issue always comes up when people put business logic inside the database. Databases are for data. The data goes in and the data goes out, but the data does not get to decide what happens next based on itself. That's what application code is for.

> That's what application code is for.

I've seen people who disagree with that statement and say that having a separate back end component often leads to overfetching and in-database processing is better. I've worked on some systems where the back end is essentially just passing data to and from stored procedures.

It was blazing fast, but working with it absolutely sucked - though for whatever reason the people who believe that seem to hold those views quite strongly.

Re: Postgres LISTEN/NOTIFY does not scale

#200

Post by whaleofatw2022 »

KronisLV wrote: post #198
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…

> Postgres/ClickHouse/NATS Maybe throw in a dedicated key-value store like Redis or Valkey. Oh and maybe something S3 compatible like MinIO, Garage or SeaweedFS for storing bunches of binary data. With all of that, honestly it should cover most of the common workloads out there! Of course, depends on how specialized vs generic you like your software to be.

NATS does KV pretty well now (didn't have expiration till earlier this year)
Post reply on HN