Show HN: PgQueuer – Transform PostgreSQL into a Job Queue

I am building an SQS compatible queue for exactly that reason. Use with any language or framework. https://github.com/poundifdef/smoothmq

It is based on SQLite, but it’s written in a modular way. It would be easy to add Postgres as a backend (in fact, it might “just work” if I switch the ORM connection string.)

This would be so immensely useful. I’d estimate that there are so many cases where the producer is Node or Rails and the consumer is Python.

If you want a generic queue that can be consumed in any runtime, you can just build it directly into postgres via extensions like https://github.com/tembo-io/pgmq.

A common schema is one nice thing, but imho the win of these db backed queues is being able to do things, including enqueue background jobs in a single transaction. e.g. create user, enqueue welcome email - both get done, or not - with redid-based, this is ... not usually a thing; if you fail to do one, it's left half done, leading to more code etc

p.s. I maintain a ruby equivalent called QueueClassic

Qless "solves" this problem (in redis) by having all core logic written as lua and executed in redis.

You could take a similar approach for pg: define a series of procedures that provide all the required functionality, and then language bindings are all just thin wrappers (to handle language native stuff) around calls to execute a given procedure with the correct arguments.

For JavaScript and Perl there is already Minion, which relies on listen/notify + FOR UPDATE SKIP LOCKED.

https://github.com/mojolicious/minion.js

https://github.com/mojolicious/minion

River is my go to in Golang, it’s really handy to have transactional queuing with a nice little ui.

Putting low throughput queues in the same DB is great both for simplicity and for getting exactly-once-processing.

Putting high throughput queues in Postgres sucks because...

No O(1) guarantee to get latest job. Query planner can go haywire.

High update tables bloat like crazy. Needs a whole new storage engine aka ZHEAP

Write amplification as every update has to update every index

LISTEN/NOTIFY doesn't work through connection pooling

I've come to hate "Postgres is all you need." Or at least, "a single Postgres database is all you need."

Thanks for your insights!

Regarding multi-step pipelines and fan-out capabilities: It's a great suggestion, and while PgQueuer doesn't currently support this, it's something I'm considering for future updates.

As for the LISTEN/NOTIFY payload limit, PgQueuer uses these signals just to indicate changes in the queue table, avoiding the size constraint by not transmitting substantial data through this channel.

Is multi-step (fan out, etc) typically something a queue or message bus would handle?

I’ve always handled this with an orchestrator solution like (think Airflow and similar).

Or is this a matter of use case? Like for a real-time scenario where you need a series of things to happen (user registration, etc) maybe a queue handling this makes sense? Whereas with longer running tasks (ETL pipelines, etc) the orchestrator is beneficial?

At low-medium scale, this will be fine. Even at higher scale, so long as you monitor autovacuum performance on the queue table.

At some point it may become practical to bring a dedicated queue system into the stack, sure, but this can massively simplify things when you don’t need or want the additional complexity.

I mean I love postgres like the next guy. And I like simple solutions as long as they work. I just wonder if this is truly simpler than using a redis or rabbitmq queue if you need Queues. If you're already using a cloud provider sqs is quite trivial as well.

I guess if you already have postgres and don't want to use the cloud provider's solution. You can use this to avoid hosting another piece of infra.

I use it as a job queue. Yes, it has it's cons, but not dealing with another moving piece in the big picture is totally worth it.

Instead of SQS, I recently created a basic abstraction on PG that mimics the SQS apis. The intention was to use it during development and we would simply switch to SQS later.

Never did. The production code still uses PG based queue (which has been improved since) and pg just works perfectly fine. Might still need to go with a dedicated queue service at some point but it has been perfectly fine so far.

I think for me the problem with every single new PG queue is that it seems like everyone and their mother thinks they need to reinvent this specific wheel for some reason and the flavor of the day doesn’t often bring much new to the space. Probably because it's

1. Pretty easy to understand and grok the problem space

2. Scratching the programmer itch of wanting something super generic that you can reuse all over the place

3. Doable with a modest effort over a reasonable scope of time

4. Built on rock solid internals (Postgres) with specific guarantees that you can lean on

Here's 7 of them just right quick:

- https://github.com/timgit/pg-boss

- https://github.com/queueclassic/queue_classic

- https://github.com/florentx/pgqueue

- https://github.com/mbreit/pg_jobs

- https://github.com/graphile/worker

- https://github.com/pgq/pgq

- https://github.com/que-rb/que

Probably could easily find more by searching, I only spent about 5 minutes looking and grabbing the first ones I found.

I'm all for doing this kind of thing as an academic exercise, because it's a great way to learn about this problem space. But at this point if you're reinventing the Postgres job queue wheel and sharing it to this technical audience you need to probably also include why your wheel is particularly interesting if you want to grab my attention.

It's simple and atomic

and almost certainly incorrect, gotta read at least https://www.pgcon.org/2016/schedule/attachments/414_queues-p... which discusses FOR UPDATE SKIP LOCKED

those systems offer good affordances for testing and running locally in an operationally simple way

Define "operationally simple", most if not all of them need persistent anyway, on top of the queue itself. This eliminates the queue and uses a persistent you likely already have.

Some names

- Celery (massive and heavy)

- Dramatiq

- APScheduler

- Huey

Today, Redis queues, unless stricly a single process, seem to be most pain free for small scale use.

In my experience, it's easy to test locally with PG: we have unit tests which re-create DB for each test... It works.

Also DB transactions are absolutely the best way to provide ACID guarantee

There’s also the new built in SolidQueue.

https://github.com/rails/solid_queue/

Wanted to post this, glad it's already here. This is PgQueuer for Rails but also with some history under its belt.

Thanks for mentioning this gem (literally).

I moved my projects to GoodJob and it has been smooth sailing.

I think graphile worker is Node only. This project is for Python.

I haven’t used Graphile Worker since I’m not familiar with JavaScript. PgQueuer is tailored for Python with PostgreSQL environments. I’d be interested to hear about Graphile Worker’s features and how they might inspire improvements to PgQueuer.

It’s a simple query to write. You don’t need a library or framework.

Updlock rowlock readpast should do the trick

Hangfire with PostgreSQL driver.

Also wanted to say I thought this problem has already been solved by River.

Although seems like OP references a Python library rather than standalone server, so would probably be useful to Python devs.

I’ve been using river for some low volume stuff. I love that I can add a job to the queue in the same db transaction that handle the synchronous changes.

Glancing at it briefly, I like the Workflows feature. I'm a long time Sidekiq user (Ruby), and while you can construct workflows pretty easily (especially using nested batches and callbacks in the Pro version), there really isn't a dedicated UI for visualizing them.

I think the usage of listen/notify is just a mechanism to save you from querying the database every X seconds looking for new tasks (polling). That has some drawbacks, because if the timeout is too small, you are making too much queries that usually may not return any new tasks, and if it's too big, then you may start processing the task long after it was submitted. This way, it just notifies you that new tasks are ready so you can query the database.

LISTEN/NOTIFY and SELECT FOR UPDATE SKIP LOCKED serve different purposes in PgQueuer. LISTEN/NOTIFY notifies consumers about changes in the queue table, prompting them to check for new jobs. This method doesn’t inherently lose messages if a process crashes, because it simply triggers a check rather than transmitting data. The actual job handling and locking are managed by SELECT FOR UPDATE SKIP LOCKED, which safely processes each job even when multiple workers are involved.

There are two things:

1. Signaling

2. Messaging

In some systems, those are, effectively, the same. A consumer listens, and the signal is the message. If the consumer process crashes, the message returns to the queue and gets processed when the consumer comes back online.

If the signal and messaging are separated, as in Postgres, where LISTEN/NOTIFY is the signal, and the skip locked query is the message pull, the consumer process would need to do some combination of polling and listening.

In the consumer, that could essentially be a loop that’s just doing the skip locked query on startup, then dropping into a LISTEN query only once there are no messages present in the queue. Then the LISTEN/NOTIFY is just signaling to tell the consumer to check for new messages.

At first glans i i see two thins that PgQueuer can. It has native async support, it you can also have sync functions, they will be offloaded to threads via anyio (https://github.com/janbjorge/PgQueuer/blob/99c82c2d661b2ddfc...)

It has a global rate limit, synced via pg notify.

I’m using Procrastinate in several projects. Would definitely like to see a comparison.

What I personally love about Procrastinate is async, locks, delayed and scheduled jobs, queue specific workers (allowing to factor the backend in various ways). All this with a simple codebase and schema.

it should be the opposite of procastination, but good naming anyway.

Already done. See: PostgREST. Want to use PostgreSQL (or most other RDBMSs) as the backend for an actively developed, multiprotocol, multiplatform, open source, battle proven message broker that also provides a messaging REST API of its own? Use ActiveMQ (either flavor) and configure a JDBC backend. Done.

But can you make a decent job queue with anything that stores data? Not easily. E.g. you need atomicity if multiple consumers can take jobs, and I think you need CAS for that, not just any storage will do, right?

You probably need ACI and also D if you want your jobs to persist.

thanks for sharing, added to my to watch list.