Suggestion: Use WAL instead of LISTEN/NOTIFY

[psteinroe]

Feature description

This is a big ask, but LISTEN/NOTIFY can become a bottleneck for Graphile Worker because it acquires a global lock.

• https://www.recall.ai/blog/postgres-listen-notify-does-not-scale
• https://x.com/samokhvalov/status/1959468091035009245

Maybe it could be replaced by subscribing to the WAL stream of the jobs table? This could increase the rate at which jobs are inserted. Especially with local queues, INSERT could become the new bottleneck.

Motivating example

Not seeing issues myself yet, but better be safe than sorry.

Breaking changes

not on the API layer, but this requires a significant rework internally.

Supporting development

I [tick all that apply]:

• am interested in building this feature myself
• am interested in collaborating on building this feature
• am willing to help testing this feature before it's released
• am willing to write a test-driven test suite for this feature (before it exists)
• am a Graphile sponsor ❤️
• have an active support or consultancy contract with Graphile

[benjie]

I think you might be underestimating the complexity and overhead of a WAL-based flow; but that said, this is one of the ways I was considering making exporting to other queues as fast as possible and we already have the logical decoding server that we used for PostGraphile V4 that we could re-use for this, so it's worth exploring.

Another option would be to just disable listen/notify and rely on polling - with the new rearchitecture in v0.17 this should actually be quite viable. No more 2ms notification periods, but sub-second should still be viable and at much greater throughput.