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.
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.