Postgres is a great pub/sub and job server (2019)
1–10 of 209 posts
Re: Postgres is a great pub/sub and job server (2019)
#2Agree on the pros. A few cons are increased costs due to (relatively) high IOPS, higher coupling, and need for more (costly) connections.
Overall though I'd agree it can be good as a first step or default until lower cost or higher performance is needed.
Re: Postgres is a great pub/sub and job server (2019)
#3Largely agree at the scale this article is working with.
But frankly, if 10k/s inserts is the scale you are talking about even worrying about a pub/sub solution seems odd.
Introducing something like Kafka for anything less than an order of magnitude more than that seems like an architectural blunder. By the time you are there Postgres will have obviously disqualified itself.
Re: Postgres is a great pub/sub and job server (2019)
#4Oban[1] from the Elixir ecosystem leans on Postgres for job scheduling
Re: Postgres is a great pub/sub and job server (2019)
#5It’s also possible to use advisory locks to implement a job queue in Postgres. See e.g. Que[1]. Note there are a fair number of corner cases, so studying Que is wise if trying to implement something like this, as well as some (a bit older) elaboration[2].
We implemented a similar design to Que for a specific use case in our application that has a known low volume of jobs and for a variety of reasons benefits from this design over other solutions.
[1]: https://github.com/que-rb/que [2]: https://brandur.org/postgres-queues
Re: Postgres is a great pub/sub and job server (2019)
#6Strong disagree on using a database as a message queue. This article[0] covers many of the reasons why. Summary: additional application complexity and doesn't scale well with workers.
0. https://www.cloudamqp.com/blog/why-is-a-database-not-the-rig...
EDIT>> I am not suggesting people build their own rabbitmq infrastructure. Use a cloud service. The article is informational only.
Re: Postgres is a great pub/sub and job server (2019)
#7This is a fantastic hack.
This week I watched tech demos where AWS services like kinesis were used to articulate services processing on the order of .3 to 3 jobs per second. Lol. CLOUD
Re: Postgres is a great pub/sub and job server (2019)
#8I had thought about using postgres as a job queue before, but I couldn't figure out in my head how to make sure two processes didn't both take the same job. The "FOR UPDATE" and "SKIP LOCKED" were the keys to make this work in the article. Essentially, as far as I can tell, "SELECT FOR UPDATE" locks the rows as they're selected (locks are apparently visible outside the transaction), and "SKIP LOCKED" skips over rows for the select that other transactions have locked. Cool stuff.
The below article goes over some downsides to using postgres for a job queue: namely that the data model isn't optimized to find and send new jobs to large numbers of subscribers. Actual messaging systems like Kafka/NATS are better at this.
https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...
Also, there are things that the dedicated messaging systems give you that postgres won't, such as transparent error handling and retry logic. If your worker grabs a job from postgres and fails (in some kind of transient way such that the task should be retried), you'll have to implement the retry logic yourself. A streaming platform like Kafka or NATS will notice that you haven't acknowledged the message and deliver it to someone else.
This is something you could pretty easily implement yourself with something like a process that just scans over the "processing" jobs and looks for timeouts, resetting them as it goes. But there are probably a few of these little papercuts that Kafka-like (…Kafkaesque?) systems would handle for you.
So, I guess if you already have postgres set up in a reliable way, and there's no one around whose job it is to set up new production systems like Kafka, and you don't already have something like Kafka, and you only need basic job queue requirements or you're okay with implementing whatever you need on top of postgres yourself, and your incoming job rate is fewer than maybe a thousand per second, and you have fewer than maybe tens of consumers… postgres is probably a decent job queue.
The above paragraph seems snide but it probably does describe many people's environments.
Re: Postgres is a great pub/sub and job server (2019)
#9Same goes for MySQL. I like to try new and different things, so I'm always trying to find reasons to use things like redis or other pub/sub or caching options. But I usually tend to end up sticking with tried and true relational DBs. Obviously, there is a scale at which those other options will be necessary, but I haven't hit that yet.
Many times even when I do use something like Redis, I run into limitations.
For example, I have been using it for caching calculated data that users need, but now we need to be able to query the cached data by date which puts us back to needing to store the cached data in the relational database.
Re: Postgres is a great pub/sub and job server (2019)
#10Just because something can be used to do something doesn't mean it should. Kafka is specifically designed for this purpose, it is free, and it is easy to learn and use. If "starting with Postgres and then switching out when the time comes" saves money then I can understand. Otherwise use the right tool for the right job, right from the start.