I'm building a simple version with horizontally scalable app servers that each use LISTEN/NOTIFY on the database. The article says this will lead to problems and you'll need PubSub services, but I was hoping LISTEN/NOTIFY would easily scale to hundreds of concurrent users. Please let me know if that won't work ;)
Some context: The use case is a digital whiteboard like Miro and the heaviest realtime functionality will be tracking all of the pointers of all the users updating 5x per second. I'm not expecting thousands/millions of users as I'm planning on running each instance of the software on-prem.
I work on the team that built a Postgres pub/sub connector at Ably (we call it LiveSync).
Our postgres connector also works on LISTEN/NOTIFY and has horizontally scalable consumers that will share the load.
There's two types of LISTEN/NOTIFY you can use. Either have the notify event carry the payload or have the notify event tell you of a change and then query for the data. If you choose the second option you'll get much better resilience under load, as the back pressure is contained in the table rather than dropped on NOTIFY.
If you do go with a poll-on-change design, you'll likely benefit from some performance tuning around denouncing the polls and how big a batch size of records to poll for.
As for the exact collaboration features, when clients are online and interacting, it's fairly easy. Lots of the hard stuff comes from knowing when a connection is dropped or when a client goes away. Detecting this and updating the other clients can be hard.
Another team at Ably worked on that problem, and called it Spaces.
Thank you! As for a bit more context, I have an tokio async Rust app which does LISTEN and forwards all messages to a (for now unbounded) tokio::sync::broadcast channel. As the Rust program handles the messages extremely quickly, it should take the load off of Postgres. If a client disconnects/starts queuing up messages then of course we might get a memory problem in the app, but the messages are only 20 bytes or so.
I assume you are using Postgres? If so i believe you will be limited by max_notify_queue_pages.
I would instead try would be to create an event table in the db, then create a publication on that table and have clients subscribe to that publication via logical replication. You would have to increase the number of replication slots though.
One thing to keep in mind is that when you LISTEN on a connection, you remove it from the connection pool until you are done with it. The straightforward approach of using one connection per connected client can quickly consume all of the connections in your connection pool and prevent you from handling additional requests.
What I’ve done for this in the past is to do all subscriptions over a single connection per server, and then have code in the server that receives all of the events and dispatches them internally based on a lookup table.
I think you'll for an app like this you'll eventually want to go with something that was designed for a high throughput (I don't think LISTEN/NOTIFY was). Yes, it's nice because you get it out of the box with Postgres, but you'll find that it's not resilient, you need to be careful with managing DB connections (in your case of hundreds/thousands users it is a significant thing), and it's lackluster compared to a specialized PubSub.
Some context: The use case is a digital whiteboard like Miro and the heaviest realtime functionality will be tracking all of the pointers of all the users updating 5x per second. I'm not expecting thousands/millions of users as I'm planning on running each instance of the software on-prem.