Potential LISTEN/NOTIFY scaling issues

[fcortes]

The people at recall.ai wrote an article (HN) about scaling issues they had when using LISTEN/NOTIFY. They give this TL;DR

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. Under many concurrent writers, this results in immense load and major downtime. Don’t use LISTEN/NOTIFY if you want your database to scale to many writers.

They talk about having "tens of thousands of simultaneous writers" and a single use of NOTIFY on some critical path. Weirdly this combination of factors make the database stall while it is waiting for the notifications locks to clear up.

The authors say they were able to replicate it but did not share the code. It may be a good idea to try to replicate it with river bench (or some variation of it) to check if this stalling happens when using pgx (which as I understand is the only driver that uses LISTEN/NOTIFY)

Has anyone experienced an issue like that?

[bgentry]

@fcortes as of today, RIver uses LISTEN/NOTIFY in a debounced setup where the notifications are triggered conditionally by application code IF there hasn't been one triggered on that same client within the debounce interval. This change was made pretty early on as we encountered bottlenecks in our benchmarks from executing many thousands of jobs per second. Now, each client only emits an insert notification up to once per FetchCooldown per queue: https://github.com/riverqueue/river/blob/bec698298d0de6d1dcbb4ae5da0a6ff91bb5ac5d/internal/notifylimiter/limiter.go

While this is still prone to the global locking issue described in that article, it should be far less impactful if only happening for a small fraction of inserts. You can choose to run in PollOnly mode if you want, but that does come with downsides for i.e. instant queue pausing and other infrequent events notifications are used for.

We're also still thinking about alternatives because certain database drivers (database/sql) don't support LISTEN/NOTIFY, but I'm not sure how soon it will get prioritized in the scheme of other things on our list.

[fcortes]

That's awesome, I didn't know about the PollOnly option. I don't think is too critical of an issue but I wanted to let you know in case someone has this sort of problem at some point. It's great that you've already thought about it.

Thanks for the response!