Show HN: PgQueuer – Transform PostgreSQL into a Job Queue

Does the celery SQLAlchemy broker support PostgreSQL's LISTEN/NOTIFY features?

Similar support in SQLite would simplify testing applications built with celery.

How to add table event messages to SQLite so that the SQLite broker has the same features as AMQP? Could a vtable facade send messages on tablet events?

Are there sqlite Triggers?

Celery > Backends and Brokers: https://docs.celeryq.dev/en/stable/getting-started/backends-...

/? sqlalchemy listen notify: https://www.google.com/search?q=sqlalchemy+listen+notify :

asyncpg.Connection.add_listener

sqlalchemy.event.listen, @listen_for

psychopg2 conn.poll(), while connection.notifies

psychopg2 > docs > advanced > Advanced notifications: https://www.psycopg.org/docs/advanced.html#asynchronous-noti...

PgQueuer.db, PgQueuer.listeners.add_listener; asyncpg add_listener: https://github.com/janbjorge/PgQueuer/blob/main/src/PgQueuer...

asyncpg/tests/test_listeners.py: https://github.com/MagicStack/asyncpg/blob/master/tests/test...

/? sqlite LISTEN NOTIFY: https://www.google.com/search?q=sqlite+listen+notify

sqlite3 update_hook: https://www.sqlite.org/c3ref/update_hook.html

We use listen notify extensively and it is great. The things it lacks most for us is guaranteed single recipient. All subscribers get all notifications which leads to problems in determining who should act on the message n our case.

How does LISTEN/NOTIFY compare to using select for update skip locked? I thought listen/notify can lose queue items when the process crashes? Is that true? Do you need to code for those cases in some manner?

I am going to go the other direction on this... to anyone reading this, please consider using a backend-generic queueing system for your Python project.

Why? Mainly because those systems offer good affordances for testing and running locally in an operationally simple way. They also tend to have decent default answers for various futzy questions around disconnects at various parts of the workflow.

We all know Celery is a buggy pain in the butt, but rolling your own job queue likely ends up with you just writing a similary-buggy pain in the butt. We've already done "Celery but simpler", it's stuff like Dramatiq!

If you have backend-specific needs, you won't listen to this advice. But think deeply how important your needs are. Computers are fast, and you can deal with a lot of events with most systems.

Meanwhile if you use a backend-generic system... well you could write a backend using PgQueuer!

I've been referring to this post about issues with Celery: https://docs.hatchet.run/blog/problems-with-celery

Does PgQueuer address any of them?

You could even layer in PostgREST for a nice HTTP API that is available from any language!

Any suggestions for something like this for dotnet?

You can make anything that stores data into a job queue.

It looks like PgQueuer integrates well with Postgres RPC calls, triggers, and cronjobs (via pg_cron). Interesting, will check it out.

Cool, congrats on releasing. Have you seen graphile worker? Wondering how this compares or if you're building for different use-cases.

Broadcaster is one we use in production for PUB/SUB stuff with OPA/OPAL. https://pypi.org/project/broadcaster/

How does this compare to the popular Graphile Worker library?

https://github.com/graphile/worker

Although I am more of a MySQL guy, I have been exploring PostgreSQL from sometime. Seems it has lot of features out of box.

This is very interesting tool.

Nice! Seems to be pretty well-crafted.

The name is perhaps slightly fraught with risk... I missed the second 'u', at first, so I misread it.

for typescript there is pg-boss, works great for us

Why so much code for Avery simple concept

One table.

Producer writes Co sumer reads

A very good idea

I’ve been thinking about the potential for PostgreSQL-backed job queue libraries to share a common schema. For instance, I’m a big fan of Oban in Elixir: https://github.com/sorentwo/oban

Given that there are many Sidekiq-compatible libraries across various languages, it might be beneficial to have a similar approach for PostgreSQL-based job queues. This could allow for job processing in different languages while maintaining compatibility.

Alternatively, we could consider developing a core job queue library in Rust, with language-specific bindings. This would provide a robust, cross-language solution while leveraging the performance and safety benefits of Rust.

This looks like a great task queue, I'm a massive proponent of "Postgres is all you need" [0] and doubling down on it with my project that takes it to the extreme.

What I would love is a Postgres task queue that does multi-step pipelines, with fan out and accumulation. In my view a structured relational database is a particularly good backend for that as it inherently can model the structure. Is that something you have considered exploring?

The one thing with listen/notify that I find lacking is the max payload size of 8k, it somewhat limits its capability without having to start saving stuff to tables. What I would really like is a streaming table, with a schema and all the rich type support... maybe one day.

0: https://www.amazingcto.com/postgres-for-everything/

BTW: Good PostgresFM episode on implementing queues in Postgres, various caveats etc: https://www.youtube.com/watch?v=mW5z5NYpGeA .

The Symfony framework (PHP) provides a similar feature, which also relies on LISTEN/NOTIFY and FOR UPDATE SKIP LOCKED: https://symfony.com/doc/current/messenger.html#doctrine-tran...

It also supports many other backends including AMQP, Beanstalkd, Redis and various cloud services.

This component, called Messenger, can be installed as a standalone library in any PHP project.

(Disclaimer: I’m the author of the PostgreSQL transport for Symfony Messenger).

You might also want to look at River (https://github.com/riverqueue/river) for inspiration as they support scheduled jobs, etc.

From an end-user perspective, they also have a UI which is nice to have for debugging.

There is also Procrastinate: https://procrastinate.readthedocs.io/en/stable/index.html

Procrastinate also uses PostgreSQL's LISTEN/NOTIFY (but can optionally be turned off and use polling). It also supports many features (and more are planned), like sync and async jobs (it uses asyncio under the hood), periodic tasks, retries, task locks, priorities, job cancellation/aborting, Django integration (optional).

DISCLAIMER: I am a co-maintainer of Procrastinate.

Good Job does the same for Rails

https://github.com/bensheldon/good_job

I really like the emergence of simple queuing tools for robust database management systems. Keep things simple and remove infrastructure complexity. Definitely a +1 from me!

For handling straightforward asynchronous tasks like sending opt-in emails, we've developed a similar library at All Quiet for C# and MongoDB: https://allquiet.app/open-source/mongo-queueing

In this context:

    LISTEN/NOTIFY in PostgreSQL is comparable to MongoDB's change streams.
    SELECT FOR UPDATE SKIP LOCKED in PostgreSQL can be likened to MongoDB's atomic read/update operations.

Most of the small python alternatives I've seen use Redis as backend:

- https://github.com/rq/rq - https://github.com/coleifer/huey - https://github.com/resque/resque

there seems to be a big hype to adapt pg into any infra. I love PG but this seems not be right thing.

The most simple job queue in MySQL:

    update job_table set key=value where ... limit 1

It's simple and atomic. Unfortunately PG doesn't allow `update ... limit` syntax