I'm the GoodJob author. Here's the class that is responsible for implementing Postgres's LISTEN/NOTIFY functionality in GoodJob:

https://github.com/bensheldon/good_job/blob/10e9d9b714a668dc...

That's heavily inspired by Rail's Action Cable (websockets) Adapter for Postgres, which is a bit simpler and easier to understand:

https://github.com/rails/rails/blob/be287ac0d5000e667510faba...

Briefly, it spins up a background thread with a dedicated database connection and makes a blocking Postgres LISTEN query until results are returned, and then it forwards the result to other subscribing objects.

I can't speak for how they do it, but when your worker polls the table and finds no rows, you will sleep. While sleeping, you can also LISTEN on a channel (and if you get a message, you abort your sleep).

Then, whenever you write a new job to the queue, you also do a NOTIFY on the same channel.

This lets you keep latency low while still polling relatively infrequently.

NOTIFY is actually transactional which makes this approach even better (the LISTENer won't be notified until the NOTIFY transaction commits)

These are somewhat obscure PostgreSQL SQL commands:

https://www.postgresql.org/docs/15/sql-listen.html

https://www.postgresql.org/docs/15/sql-notify.html

I think they have been around for ages, but handling the LISTEN responses may need special client library support.