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

151–160 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#151

Post by gunnarmorling »

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 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 they monitor [3].

[1] https://speakerdeck.com/gunnarmorling/ins-and-outs-of-the-ou...

[2] https://www.infoq.com/articles/wonders-of-postgres-logical-d...

[3] https://www.morling.dev/blog/mastering-postgres-replication-...

Re: Postgres LISTEN/NOTIFY does not scale

#152

Post by gunnarmorling »

williamdclt wrote: post #48
cyberax wrote: post #42

Earlier quoted context omitted.

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

Debezium handles logical decoding messages OOTB. There's also an SMT (single message transform) for decoding the binary payload: https://debezium.io/documentation/reference/stable/transform....

Re: Postgres LISTEN/NOTIFY does not scale

#153

Post by jl6 »

bevr1337 wrote: post #126
fathomdeez wrote: post #125

Earlier quoted context omitted.

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.

> 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 think an argument can be made that relations, schemas and constraints encode a kind of business logic that is intrinsic to the definition and integrity of the data, while other types of business logic represent processes that may hinge on data but aren’t as tightly coupled to it. Similar to the difference between a primitive type and a function.

I guess some will argue that their business logic is special and really is so tightly coupled to the data definition that it belongs in the database, and I’m not going to claim those use cases don’t exist, but I’ve seen over-coupling far more often than under-coupling.

This is why I say: Applications come and go, but data is forever.

Re: Postgres LISTEN/NOTIFY does not scale

#154

Post by perlgeek »

chatmasta wrote: post #148
fathomdeez wrote: post #125

Earlier quoted context omitted.

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.

The first thing I did when I saw this article was to check the Postgres docs, because I thought "heh, surely they just didn't read the fine print," but the LISTEN/NOTIFY page has zero mentions of "lock" in the entire content.

I really hope somebody reading this article (or HN thread) writes a doc patch to mention that.

I'm unlikely to get it myself today, and by tomorrow I've probably already forgotten it :-(

Re: Postgres LISTEN/NOTIFY does not scale

#155

Post by tsimionescu »

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.

The way you model data and store it in your database is fundamentally a part of your business logic. The same data can be modeled in many different ways, with different trade-offs for different use cases. Especially if you have a large amount of data, you can't just work with it as is, you need to know how you will use it and model it in a way that makes the common operations fast enough. As your application evolves, this may change, and even require data migrations.

None of this means you have to or even should use stored procedures, triggers, or listen/notify. I'm just making the point that there is no clean separation between "data" and "business logic".

Re: Postgres LISTEN/NOTIFY does not scale

#156

Post by bjornsing »

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.

Re: Postgres LISTEN/NOTIFY does not scale

#157

Post by Someone »

doc_manhat wrote: post #47

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

If “doing due diligence” involves reading the source code of a database server to verify a design, I doubt many people writing such systems do due diligence.

The documentation doesn’t mention any caveats in this direction, and they had 3 periods of downtime in 4 days, so I don’t think it’s a given that testing would have hit this problem.

Re: Postgres LISTEN/NOTIFY does not scale

#158

Post by ilitirit »

> The structured data gets written to our Postgres database by tens of thousands of simultaneous writers. Each of these writers is a “meeting bot”, which joins a video call and captures the data in real-time.

Maybe I missed it in some folded up embedded content, or some graph (or maybe I'm probably just blind...), but is it mentioned at which point they started running into issues? The quoted bit about "10s of thousands of simultaneous writers" is all I can find.

What is the qualitative and quantitative nature of relevant workloads? Depending on the answers, some people may not care.

I asked ChatGPT to research it and this is the executive summary:

  For PostgreSQL’s LISTEN/NOTIFY, a realistic safe throughput is:

  Up to ~100–500 notifications/sec: Handles well on most systems with minimal tuning. Low risk of contention.

  ~500–2,000 notifications/sec: Reasonable with good tuning (short transactions, fast listeners, few concurrent writers). May start to see lock contention.

  ~2,000–5,000 notifications/sec: Pushing the upper bounds. Requires careful batching, dedicated listeners, possibly separate Postgres instances for pub/sub.

  >5,000 notifications/sec: Not recommended for sustained load. You’ll likely hit serialization bottlenecks due to the global commit lock held during NOTIFY.

Re: Postgres LISTEN/NOTIFY does not scale

#159

Post by cap11235 »

ilitirit wrote: post #158

> The structured data gets written to our Postgres database by tens of thousands of simultaneous writers. Each of these writers is a “meeting bot”, which joins a video call and captures the data in real-time. Maybe I missed it in some folded up embedded content, or some graph (or maybe I'm probably just blind...), but is it mentioned at which point they started running into issues? The quoted bit about "10s of thousa…

[flagged]

Re: Postgres LISTEN/NOTIFY does not scale

#160

Post by DumBthInker007 »

My understanding: i think as postgres takes an exclusive lock to enqueue the notifications into a shared queue in PreCommit_Notify(), as the actual commit happens after notification was enqueued into the queue,as other transactions also try to notify but wait becacause of the lock ,so does the commit waits.
Post reply on HN