Here's the companion essay with more code snippets: https://taylor.town/pg-task

When I was deciding on a queuing system for the product I'm writing, after seeing the price of running a Kafka cluster on Digital Ocean and others, I opted for polling in postgresql and have been quite satisfied with its work so far (6ish months running). I have some improvements I need to make to the queue listener (right now, all the workers poll until there's work, when I could have it back off to 1); but otherwise it's plenty good enough.

I'm really surprised at the price of Kafka clusters, to be honest. Hundreds of dollars per month extra at minimum.

I tried using a PostgreSQL SKIP LOCKED queue once, turned out to be a mistake.

It was a queue for a distributed worker pool. The simpler alternatives I was used to at the time (RabbitMQ) did not support joining (i.e. run task Y when all of X1~X20 are complete) and therefore every task was stored in the database, anyway. I don't remember the exact numbers, but it was a light/moderate load--thousands, maybe tens of thousands of rows per day. It ran smoothly with an external message queue. I'd vacuum maybe every 4 months.

For one iteration, I decided to try using PostgreSQL as the queue system as well, to decrease the number of moving parts. It performed fine for a bit, then slowed to a crawl. I was sure I was doing something wrong--except every guide told me this was how to use a table as a queue. If I missed anything, it must've been PG-as-a-queue-specific.

What would you do if you wanted to pick up jobs in close to real time? Listen/notify? An extension that adds long polling? Consuming the WAL? Giving up and using a different technology?

I'm working on something similar, and these are the options I've been mulling over. They each come with pretty significant drawbacks. My current plan is to use listen/notify because it's low hanging fruit and to see how that pans out, but was wondering if anyone has been down this path before and has wisdom they're willing to share.

Another postgres queue. I'm missing a variant that allows multiple consumers to process one payload, and one where you can parallelize based off a certain key (like Kafka does).

[dead]

[flagged]

[flagged]