Procrastinate: PostgreSQL-Based Task Queue for Python | Hacker News Reader

Procrastinate: PostgreSQL-Based Task Queue for Python

procrastinate.readthedocs.io

123 pointsnoodlesUK38 comments

My current favourite task queue is http://p3rl.org/Minion and I mention this not in a spirit of competition but because the pg queries to be found in the source code of https://metacpan.org/dist/Minion/source/lib/Minion/Backend/P... are IMO truly beautiful and worthy of the "great artists steal" treatment.

EDIT: I was understandably being downvoted for saying that the code could be more readable. On reflection, the sql is good, and it's the Perl-ness that I had a bit of a reaction to.

I'll leave my questions here in case people would like some food for thought anyway.

- what's with the mixture of styles (q{sql}, sql with '?', sql with '$1', sql with '\$1' etc)?

- what's the use case for a right join, when left joining is the norm?

- in the $stats query, inactive_workers isn't actually inactive_workers and instead it's patched based on active workers afterwards. For me, the jumping of inconsistent column selections on each line makes it hard to see that. (If someone just copied the sql into psql to run it, it would be wrong, but not obviously)

- Maybe it's not fair, because I have sqlalchemy to improve readability, but this whole thing where the variables aren't obvious in a blob of sql could definitely be easier to read

Sorry to be that guy, but your comment is pointless, so I had to downvote it.

While the beauty is in the eye of beholder, and you may be correct, or have high standards, praise coming from the likes of @mst should be taken seriously. People may have seen code that can stand in for sh?t, so it makes them appreciate simple niceties like good formatting, good and concise logic, etc.

BTW, I have to agree with @mst on this one; even though perl is very low on my scale of pleasantness for languages, the SQL in that code is beautifully written.

I think your comment here is reasonable and you were unfairly downvoted by others missing the deleted context in my original comment.

I’ve upvoted you partly on those grounds, and partly to get you back to 1337 :)

Hah. I was bummed when I lost my leet score, especially because it was lost towards the wrong direction.

Thank you!

I see where you're coming from finding the perlish string formatting weird, I guess I barely even notice that part and was assuming incorrectly it would be everything else about the perl that would bother people.

So I guess that while you perhaps did have suboptimal judgement in your initial reply you very much weren't alone and I should remember to be more careful about that going forwards.

I agree with you on the right/left join issue. I don't think I have ever used a right join. But then again I don't use greater-than signs either so maybe it is me.

I’m reading through that file and I’m not finding anything particularly interesting — the queries all look… reasonable — though I’m also struggling to imagine what I would consider a beautiful query

Anything specific worth calling out?

I suspect as much as anything else this is a case of "any piece of code I can read through and not have a viscerally negative reaction to part of it that makes me want to rewrite it to within an inch of its life counts as beautiful to me".

Certainly, very little of my own code passes that test after I've not looked at it for a week.

The documentation is really good. I particularly like this page, which goes into detail about why they built it and how it is designed: https://procrastinate.readthedocs.io/en/stable/discussions.h...

That page is beautiful and the architecture is clearly quite similar to perl's Minion that I mentioned in a top level comment and frankly the more implementations of such a concept in different languages the better and <3

I’ve been looking for a tool that could replace Celery for my smaller projects and use postgresql as its queuing system. I was pointed this way in the recent thread about message queuing natively in postgresql [1].

I thought it seemed like a project deserving a bit more attention.

https://news.ycombinator.com/item?id=30119285

If you are just queueing outbound emails you can consider a simple SMTP buffer that uses no server at all - it just saves files to disk.

I was using Celery for sending emails - nothing else. And Celery was such a nightmare to configure and debug and such overkill for email buffering that in a fit of frustration I wrote the Arnie SMTP buffering server and ditched Celery.

https://github.com/bootrino/arniesmtpbufferserver

It's only 100 lines of code:

https://github.com/bootrino/arniesmtpbufferserver/blob/maste...

Why this and not celery?

And why Postgres and not rabbitmq or redis?

Re: why Postgres, two common answers:

1. For anyone whose stack is currently a simple three-tier architecture that currently has Postgres and nothing else in the DB tier, adding anything else would incur 100+% operational complexification (e.g. now you have to figure out how to do backups for two stateful components.) Far more than 100%, even, if they get the Postgres DB "for free" as part of a virtual LAMP-stack appliance, or built into the base offering of some PaaS service, and currently don't need to do any ops work as the framework/platform handles their DB's care and feeding for them. (Ideally, such setups would do the same with a built-in MQ as well — but sadly, that's much rarer.)

2. You can be clever with a job queue that’s in your DB, by making “taking the job” and “doing the queries that comprise the job” part of the same atomic transaction, such that a ROLLBACK due to a constraint validation error in "the queries that comprise the job" will also implicitly “put back” the job immediately (even if the worker crashed in response to seeing the error.)

Generally yes, adding new tech can complicate your infrastructure, but specifically redis is one of those rare gems that, “just works”.

If you use Celery, the time saved dropping Celery will more than cover the operational costs of redis.

Really, one of the magic tools of the 21st century.

> why Postgres and not rabbitmq or redis?

I chose postrges for our mq stack over rabbitmq and redis. Since our team is small, we are trying to be efficient in our use of manpower. our architecture is as dirt simple as it gets. (loadbalancer -> api-server-nodes -> aws aurora).

adding another external dependency just adds one more thing that can potentially go wrong. Thats one more thing we need to watch and maintain accross staging and production envrionemnts and one more thingto get running on developer machines.

There may come a day where we switch over to rabbitmq or kafka but postgres has proven to be fast enough. when writes are too much of a load, we can shard the writes to its own dedicated machine and buy more time. When our traffic is sufficiently high that even that isn't enough, we'll have enough revenue to pay someone to configure kafka/rabbitmq and deal with the configuration and maintenance fulltime.

Until then, if you want to survive as a startup, KEEP IT SIMPLE. Any complexity you adopt needs to justify itself by providing a competitive advantage.

While I respect Celery, it is complicated (some amount is intrinsic to the problem space). I used to use it, but it felt heavyweight and debugging errors was so quite challenging. Have since switched to Dramatiq - which may not be web scale, but works fine for an internal service.

I love the idea of re-using infrastructure "for free".

+1 for dramatiq over celery, dramatiq was much easier to understand and extend, even if it's not as "complete". It handled most anything I threw at it for an internal task runner system with some custom scheduling logic. I'd give it a flask out of 10 compared to celery being a Django.

Not parent but in my experience celery has a number of long standing bugs / poor defaults, like prefetching tasks so they can get stuck behind long running tasks. Next project id probably try a simplistic project that’s easier to understand like arq / rq.

That prefetching is still an issue? I recall it was caused by rabbitmq though? I switched to rq over celery years ago because it was a deal breaker. We have just long running tasks and it didn’t work for us at all. Rq has been great btw.

They address that on this page: https://procrastinate.readthedocs.io/en/stable/discussions.h...

Came here to ask this as well :)

I have a production system running on Celery (on Redis). It's been fine, once I tuned it.

Time for a refactor; wondering: should I consider migration?

Quite timely to see this discussion here. I got to a point on a home project last night where I need to implement a scheduler and was thinking 'man I dont want to setup Celery for this', that can wait for tomorrow.

In the past I have actually written my own very little scheduler that borrows the app database. Really just table that is a job queue and a script on a loop that grabs the job when it appears in the table. But it did have the occasional hiccup.

I can't wait to check this out and some other suggestions in the comments.

This looks nice, I'll give it a try.

It's worth mentionning Huey too: https://huey.readthedocs.io/en/latest/ I've been using it for a year on top of sqlite.

I ran on Huey for a few years on a python/postgres ecosystem project fronted by Django, and ultimately migrated to django-postgres-queue, which is wonderful. (There has since been a fork that I have not used.) It uses the same underlying primitives as OP, and I would absolutely recommend this to anyone operating in the same ecosystem.

https://github.com/gavinwahl/django-postgres-queue

How does this compare to pq?

https://pypi.org/project/pq/

StarQueue is also written in Async Python

https://www.starqueue.org/

It's not open source though it is free.

StarQueue has a pure HTTP API so any language can talk to it.

StarQueue is designed to be a more simple implementation of the SQS way of doing things, without some of the unnecessary stuff that SQS has in its API.

Under the hood it uses Postgres although I actually wrote it to support MySQL and SQL Server also, which are equally capable of doing queueing as Postgres.

Not making a comparison, as I haven't used Procrastinate, but the author doesn't mention luigi as an alternative/competitor (originally developed at spotify, if I'm not mistaken). I thought luigi was a relatively big player in the python 'task management' scene; I have used luigi and it was incredibly powerful, and fairly intuitive to use ... was a bit surprised not to see it mentioned.

Genuine question, not a comment on OPs implementation: What is the reason that people so often use some external service for creating task queues (often backed by some kind of db), instead of just implementing it themselves in whatever language they are using for the rest of their application?

Is it: - They want persistence in case the system goes down, or the task queue service is restarted.

- They want to replicate their task queue service and want to be sure that the data is properly synchronized.

- The amount of tasks/time if simply too much for some standard library queues

- They think is is easier to add another dependency than implement it themselves

- something else

Distributed queuing, with horizontal scaling workers and retries, without ever loosing a message/job, is surprisingly difficult to get right. It's simply not worth your time in a lot of cases. Similarly, why people don't just write their own web framework.

It sounds like your question is focused on "why not just use an in memory queue". Two reasons: 1. You loose messages/jobs, and it's not scalable 2. If the task is CPU intensive, it will lock up or slow down your web server.

I can't say, I implemented my own queuing system in Python. It seemed simpler.

At my past job we have something like for newsletters and 250k/tasks per day was fine. The only design problem we have had is that if you keep past tasks on the queue table, when it grows it traches a lot cache due to index writes, so moving already finished jobs from the hot table had solved the issue.

Is there a job/message queue implemented in pure Postgres, so it doesn't depend on Python, Perl, Ruby, ...? So that it can be used from any language that can connect to Postgres?

Usually job queue libraries provide other niceties like tight language integrations. While you could probably build a job queue with a decent API in pure postgres, it wouldn't even come close to e.g. just slapping @task in front of your method like in the post.

My opinion is if you need a message queue use a message queue product, not a database.

FWIW, my opinion is that you always start with the database, especially an RDBMS, to solve your problem. Databases have some pretty advanced programming constructs, and advanced algorithms that are available to you via a language as simple as SQL.

And when it (the database) cannot serve your needs in terms of performance anymore, then you must choose a domain-specific product dedicated to solve the you challenges.

> premature optimization is the root of all evil.

Postgrastinate.

Reply on news.ycombinator.com