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 around at most once guarantees – can help to simplify the design dramatically. Depends on the use case of course, often both at least once and at most once should co-exist in one app.
Postgres LISTEN/NOTIFY does not scale
211–220 of 328 posts
Re: Postgres LISTEN/NOTIFY does not scale
#212Earlier 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…
It actually depends on the workload.
Sending webhooks, as an example, often has zero need to go back and update the database, but I've seen that exact example take down several different managed databases ( i.e., not just postgres ).
Re: Postgres LISTEN/NOTIFY does not scale
#213Earlier 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.
Nice! I'm also using queues as part of a workflow engine.
Oh really? Would love to check it out and borrow some ideas! :)
Re: Postgres LISTEN/NOTIFY does not scale
#214Earlier 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.
So what are your thoughts on constraints then? Foreign keys? Should that only be handled by the application, like Rails does (or did, haven't used in a long time).
What happens to FKs when you've to partition/shard the db? At a certain scale, they actually hinder the inserts.
Re: Postgres LISTEN/NOTIFY does not scale
#215Re: Postgres LISTEN/NOTIFY and PgQueuer, which is built on LISTEN/NOTIFY: https://news.ycombinator.com/item?id=41284703#41285614
Re: Postgres LISTEN/NOTIFY does not scale
#216Earlier quoted context omitted.
I’ve been meaning to check out NATS - I’ve tended to default to Redis for pubsub. What are the main advantages? I use clickhouse and Postgres extensively
I've been disappointed by Nats. Core Nats is good and works well, but if you need stronger delivery guarantees you need to use Jetstream which has a lot of quirks, for instance it does not integrate well with the permission system in Core Nats. Their client SDKs are very buggy and unreliable. I've used the Python, Rust and Go ones, only the Go one worked as expected. I would recommend using rabbitmq, Kafka or redpand…
Client SDKs are often a major challenge in systems like these. In my experience, building SDKs on top of asynchronous protocols is particularly tricky. It's generally much easier to make the server-side part reliable. The complexity arises because SDKs must account for a wide range of usage patterns - and you are not controlling the usage.
Asynchronous protocols frequently result in callback-based or generator-style APIs on the client side, which are hard to implement safely and intuitively. For example, consider building a real-time SDK for something like NATS. Once a message arrives, you need to invoke a user-defined callback to handle it. At that point, you're faced with a design decision: either call the callback synchronously (which risks blocking the socket reading loop), or do it asynchronously (which raises issues like backpressure handling).
Also, SDKs are often developed by different people, each with their own design philosophy and coding style, leading to inconsistency and subtle bugs.
So this isn't only about NATS. Just last week, we ran into two critical bugs in two separate Kafka SDKs at work.
Re: Postgres LISTEN/NOTIFY does not scale
#217Earlier 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)
I believe that both code and data are kings, under different realms. Code is king of the "what we're doing today" realm. Data is king of the "what's possible tomorrow" realm.
Both have their place in business.
Re: Postgres LISTEN/NOTIFY does not scale
#218I appreciate this post for two reasons: * It gives an indication of how much you need to grow before this Postgres functionality starts being a blocker. * Folks encountering this issue—and its confusing log line—in the future will be able to find this post and quickly understand the issue.
Sounds like ChatGPT appreciated the post
I'm ESL, so I often check my grammar on ChatGPT, and 99% of the time it includes em dashes in the corrected sentences, which I remove or just replace with commas or hyphens to sound more natural.
So maybe this was not entirely written but just revised by ChatGPT.
Re: Postgres LISTEN/NOTIFY does not scale
#219Earlier 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…
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
Re: Postgres LISTEN/NOTIFY does not scale
#220Earlier quoted context omitted.
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 i…
> In that snippet are links to Postgres docs and two blog posts
Yes, that's what a snippet generally is. The generated document from my very basic research prompt is over 300k in length. There are also sources from the official mailing lists, graphile, and various community discussions.
I'm not going to post the entire outout because it is completely beside the point. In my original post, I expliclity asked "What is the qualitative and quantitative nature of relevant workloads?" exactly because it's not clear from the blog post. If, for example, they only started hitting these issues with 10k simultaneous reads/writes, then it's reasonable to assume that many people who don't have such high work loads won't really care.
The ChatGPT snippet was included to show that that's what ChatGPT research told me. Nothing more. I basically typed a 2-line prompt and asked it to include the original article. Anyone who thinks that what I posted is authoritative in any way shouldn't be considering doing this type of work.