I assume that is their main database for everything, not just for pub/sub. One of the big benefits of doing it that way is that you have proper transaction handling across jobs and their related data.
I imagine their scaling problem isn't messages/day, it's probably lots of concurrent, persistent connections. And I don't think a connection pooler would work with this job queue setup.
Are you implying that given the specs, hundreds of thousands of messages per day is not good enough? I think you are, or at least that is what I was thinking myself.
People are jumping on this. Question is—do the resource requirements outlined align with usage you described, or is that combined workload? By combined workload, I mean working set plus messaging. It’s not a useful exercise to criticize a service that’s multifaceted based on a single use case. Full disclosure—-not a Postgres user, nor am I invested in the tech.
Didn't want to deal with ramifications of statement timeouts in a complex system, the failure mode mentioned (queue filling up) happened on the scale of 6 weeks, so it was very cheap operationally to set this timeout to some high value.
This is not much load at all, an iPhone running RabbitMQ could process many millions of messages per day. Even 1M messages per day is only 11 messages per second average. i.e. not taxing at all.
I've built software that can process millions of messages per second on a single thread.
I find it amusing that we happily play these AAA gaming experiences that are totally fantastical in their ability to deal with millions of things per frame and then turn around and pretend like hundreds of thousands of things per day is some kind of virtue.
I assume you use polling workers looking for the next job to grab for themselves?
Personally I do see the niceness of having a good pattern implemented using existing technology. Less deployment nonsense, less devops, less complexity, a few tables at most. I've done similar things in the past, it is nice.
For anyone who'd criticize, having complex deployments can be just about as much dev time, AND if implemented well, they can theoretically covert this whole thing to rabbitmq with minimal effort just by swapping the queueing system.
In any case, happy to see people mentioning how using existing simple tech can lead to fairly simple to manage systems, and still solve the problems you're trying to solve.
What are the options to use Postgres pub/sub with Java? Because the usual Java libraries don't seem to support the pub/sub functionality well, you have to actively poll when you want to subscribe.
I very happily use this technique and I believe I found out about it from your original blog post. Thanks for the original writeup and for the update on how it's going a few years in!
I 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.
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.
This. The article seems like "one weird trick that message queue companies HATE" as it's utilizing, as far as I understand, some SQL semantics in a very specific way to cobble together a way of achieving what other software is designed to do out of the box. It seems fine for a toy system, but I wouldn't stake the success of a real company on this approach.
One could also use DNS TXT as an RDBMS with some interesting fault tolerance and distribution schemes. That doesn't mean it's a good idea or the best way to solve a problem.
It's strange to see you suggesting this can't work for a "real" company. That's demeaning to the OPs company, which seems quite real. It's also odd that you're proposing replacing this with a distributed system and citing the Jepsen articles as support for it, when they prove the opposite. Distributed systems are hard. If you can avoid them and stay in the happy ACID town with Postrgres, indeed why not?
If you want to carry messages across the internet SQS et al is fine. But within the same system, e.g. in the same computer, or cluster, it makes much more sense to use something like this rather than something like SQS. Different tool, different job.
If a job fails, the connection to the database will timeout. Postgres will rollback the transaction, which releases row locks, freeing a job to be retried.
Of course, the database client and server together form a distributed system. The client might continue processing a job under the mistaken impression that it still holds the lock. Jobs still need to be idempotent, as with the streaming platforms.
> If a job fails, the connection to the database will timeout. Postgres will rollback the transaction, which releases row locks, freeing a job to be retried.
Only if you begin a transaction when your job starts that isn’t committed until you job finishes. As I understand it, this is not a good idea for long-running jobs, since you’ll have a long-running Postgres transaction. Am I missing something? The linked article doesn’t seem to use this approach.
Does the “FOR UPDATE” decrease the lock to just a single row, thus making it unproblematic?
I think the key concept here is atomicity. If some API is responsible for creating a job, storing it in the database AND publishing it can never be an atomic operation. Both the database and pub/sub servers are separate network connections from the application server. For example, if you save the record first and then publish, It's quite possibe that you save the record in the database and then lose the connection to the pub/sub server when publishing. If this happens, you can never know if the pub/sub server received the request and published it. In systems where it's critical to guarantee that a record was saved and guarantee that it was published as well, the only way to do that is by using a single external connection - in this case to a Postgres DB. We've used the same setup on an AWS RDS t2.medium machine to process over 600 records/second.
>If some API is responsible for creating a job, storing it in the database AND publishing it can never be an atomic operation.
We use transactional outbox for that - we insert a record about the event into the event table in the same transaction as the rest of the operation (providing atomicity), and then a special goroutine reads this table and pushes new events to the message broker on a different server. In our design, there are multiple services which might want to subscribe to the event, and the rule is that they shouldn't share their DB's (for proper scaling) so we can't handle event dispatch in some single central app instance. Of course we could implement our own pub/sub server in Go over a DB like Postgres if we wanted, but what's the point of reinventing the wheel if there's already existing battle-tested tools for that, considering you have to reimplement: queues, exchanges, topics, delivery guarantee, proper error handling, monitoring etc.
Distributed transactions are hard. And if queue data is the source of truth for something then they must be durable. All that said, when traffic volume, payload size, or throughout demands something specialized then it makes sense to do it.
If I'm building one shed then maybe I only need 5 tools, while a shed factory might use 100. Context matters.
Postgres is really just great for being able to build just about anything to get that first viable product built. It's basically the swiss army knife for anything data in my opinion. You got sql, nosql, job queues, full text indexing. It's great.
I use it as a sql database and full text search for little personal project I work on off and on and it works great. I haven't touched it except to check every few weeks for security updates for months since I got a promotion and it, the golang app server and python scripts have had no issue just churning along keeping a 30 day archive of links found via reddit and twitter. Postgres is great.
This seems to come up on HN at least once a year. Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expensive. Also, mitigation strategies like PgBouncer cannot be used with this approach (nor can scale out solutions like CitusDB I don't think).
Of course, if scalability is not a concern (or the connection limitations are eventually fixed in postgres - this has improved in 14), this would be a very viable approach.
Supabase's Realtime [1] is one of the solutions that can help with that. Although it doesn't exactly let you LISTEN at scale, but it allows the applications to be notified on changes in the database.
> Listen to changes in a PostgreSQL Database and broadcasts them over WebSockets
I like Supabase's approach over pub/sub. One of the big advantages is they listen to the Postgres WAL which overcomes the 8000 bytes limitation[1] of the notify approach.
And Elixir is especially well suited for this type of workload. I actually extracted out much of the Supabase Realtime library so that I could work with the data directly in Elixir[2]
> Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expensive.
Scalability is always limited, no matter which solution you choose. This article argues that the scalability limit for this particular solution is acceptable for most people to begin with:
> It's rarely a mistake to start with Postgres and then switch out the most performance critical parts of your system when the time comes.
Wait, what?? I can't keep doing things with my connection after I issue a LISTEN? That doesn't seem right! I would assume it would isomorphic to how unix-y bg programs will occasionally write to the console (although I see how this might be hard to deal with at the driver level). Now I will have to go check.
You (the application) can certainly go on to issue other database commands. See the docs [1].
> With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received.
It’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.
Some comments from brandur on SKIP LOCKED in https://github.com/brandur/sorg/pull/263. I haven’t looked into it too much since what we have works without any issues; but good to know, thanks!
This is fantastic for relatively low volume queues with intensive work to be done by a small number of workers. For these types of use cases, I'll take this approach over RabbitMQ or Kafka all day long.
But once you get even just up to say, 40 messages per second with 100 worker processes, you're now up to 4000 updates per second just to see which worker got to claim which job, and up from there becomes untenable.
> This is fantastic for relatively low volume queues with intensive work to be done by a small number of workers. For these types of use cases, I'll take this approach over RabbitMQ or Kafka all day long.
That’s what our workload is like for our SaaS code analysis platform. We create a few tasks (~10 max) for every customer submission (usually triggered by a code push). We replaced Kafka with a PostgreSQL table a couple of years ago.
We made the schema, functions, and Grafana dashboard open source [0]. I think it’s slightly out-of-date but mostly the same as what we have now in production, and has been running perfectly.
a few years back i worked on an enterprisey project that used postgres as a database, along with rabbitmq and celery for job processing.
of _course_ the system architecture had to have a job queue and it had to be highly available (implemented with a rabbitmq cluster)
what we learned after a few months in production was the only time the rabbitmq cluster had outages was when it got confused* and thought (incorrectly) there was a network partition, and flipped into partition recovery mode, causing a partial outage until production support could manually recover the cluster
the funny thing about this is that our job throughput was incredibly low, and we would have had better availability if we had avoided adding the temperamental rabbitmq cluster and instead implemented the job queue in our non-HA postgres instance that was already in the design --- if our postgres server went down then the whole app was stuffed anyway!
* this was a rabbitmq defect when TLS encryption was enabled and very large messages were jammed through the queue -- rabbitmq would be so busy encrypting / decrypting large messages that it'd forget to heartbeat, then it'd timeout and panic that it hadn't got any heartbeats, then assume that no heartbeats implied a network partition, and cause an outage, needing manual recovery. i think rabbitmq fixed that a few years back
I actually wrote StarQueue for MySQL, Microsoft SQL server and also Postgres - they all work just fine as a message queue back end, because they all support SKIP LOCKED.
I started out including Oracle as a supported database but found that I loathed Oracle's complexity.
Strong 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.
I'm increasingly of the opinion that relational databases are absolutely the right way to build queue systems for most projects.
One of the biggest advantages comes when you start thinking about them in terms of transactions. Transactional guarantees are really useful here: guarantee that a message will be written to the queue if the transaction commits successfully, and guarantee that a message will NOT be written to the queue otherwise.
The transaction feature seems nice but how often is your application dropping queue messages because something happened between tx.commit() and queue.send(msg)? My experience has been that this is not an issue.
The scalability problem is way overblown. Setting the correct isolation level and locking mode isn't that hard and a modern cloud-hosted PG/MySQL can push 10s of thousands of inserts/s no problem.
IMO, the downsides of hosting a queue inside your primary relational DB are very much outweighed by the downsides of 1) having to run a new piece of infra like rabbit and 2) having to coordinate consistency between your message queue and your relational DB(s)
In the end, engineering is just whether the thing works with minimal maintenance. And ultimately, I've had great experiences using DBaaQ for low volume data movement. It works as a persistent queue with easy to do retries etc.
For high throughput (we had ad tech servers with 1E7 hits/s) we used a home-built low-latency queue that supported real time and persisted data. But for low throughput stuff, the DBaaQ worked fine.
well rabbitmq is really really hard to setup correctly and stuff like priority, time based scheduling are not that much easier than rabbitmq.
in fact a queue adds more complexity and it is not necessary until you outscale your database. not saying that rabbitmq might be a better fit, it's just not a good fit to start with.
if you have a small team < 8 it's better to stay with as few things as possible and especially with things you know (well).
I wouldn't recommend setting up your own message queue infrastructure either. The cloudamqp link was more about the content than the product. All cloud providers come with extremely simple, scalable, and inexpensive message queue services with bindings to most languages.
A message queue is one of those things that is easy enough and worth the effort to do "right" early on, because it is not something you want to rip out and rewrite when you hit your scaling bottlenecks, given how critical it is and how many things it will end up touching.
It seems notable that this is a blog post from the perspective of rabbitmq authors, or at least the author of a book about it. It talks very vaguely about one potential implementation of a queue on postgres.
It proves again, that in 90% of the cases Postgres is totally enough. If you don’t expect a high load (and most applications don’t), then just go with Postgres, simplicity wins!
For fire and forget type jobs you can use lists instead of pub/sub: save a job to a list by a producer, pop it on the other end by a consumer and execute it. It's also very easy to scale, just start more producers and consumers.
We're currently using this technique, to process ~2M jobs per day, and we're just getting started. Redis needs very little memory for this, just a few mb.
Beware of the scale up challenges with Redis. Redis can only utilize a single core. If you do anything sophisticated that needs to be atomic then you can't scale out to multiple servers, and you can't scale up to multiple cores.
At least with Postgres you can scale up trivially. Postgres will efficiently take advantage of as many cores as you give it. For scale out you will need to move to a purpose built queuing solution.
The problem is that you can't atomically write to your other database and also put a message on a redis queue. So you'll either end up with db changes not conveyed to redis, or you'll have messages on redis not reflected by changes to the db.
I just spent two months unrolling an unruly pg_boss implementation and that experience has soured me on using postgres for pubsub, jobs, or messaging. For my money, Github Actions is fine for infrequent cron jobs, or hell even a tiny lambda with a CloudWatch rule is super cheap, with infra as code to make it easy (relatively so) for anyone to deploy. After all, I'd rather spend the majority of my time on writing code than being a DevOps. If I need pub/sub, I'd much rather use SNS quick and dirty, or SNS+SQS for the heavy lifts. Separation of concerns and separation of tiers is still important to me, and I have no desire to maintain a server moving forward.
I have mostly great results using pg_boss. There was one upgrade which had a major bug, but the response was quick and the work around was easy.
The benefit of having a distributed cron that is version controlled and type checked in my repo has been amazing. I am also confident that my pg_boss implementation has higher uptime than GitHub actions.
Why couldn't the code run from a GitHub Action or within a Lambda be type checked and in version control? All of mine are. As for uptime, it completely depends on where you host your pg instance. But I'd wager you know that already.
Anybody using graphile-worker[1] in production/heavy load? It looks awesome, and I coded up some simple prototype tasks (email, sms, etc), but question how it truly scales. They claim horizontal scaling is trivial.
> graphile-worker is horizontally scalable. Each instance has a customisable worker pool, this pool defaults to size 1 (only one job at a time on this worker) but depending on the nature of your tasks (i.e. assuming they're not compute-heavy) you will likely want to set this higher to benefit from Node.js' concurrency.
Graphile Worker maintainer here; keep in mind that postgres is not the ideal location for a job queue, so you’re going to be limited ultimately by postgres’ capabilities. I’ve seen Worker max out at around 10k jobs/second but very much YMMV - you should benchmark it for your expected use case. Personally I’d move to a dedicated job queue if I started having an average of more than 1-2k jobs per second. The majority of systems never hit anywhere near this (we very much cater to the “long tail” of job queue needs).
Regarding the horizontal scalability; that relates to if you have heavy tasks (tasks that take a second or more to execute) - you can use more instances to get higher throughput.
Largely 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.
What does scale have to do with it? Pub/sub as an architectural pattern could be equally relevant for your use case whether there are a hundred users in your system or a billion.
And Kafka isn't the only solution for it. There are many lightweight pub/sub and queuing systems which also don't involve needlessly adding abstraction layers and application code into an RDBMS.
I think massive scale is the only reason you'd really want to adopt something like Kafka. If you're 10k inserts/s or less then there's no reason not to do everything in a single big relational DB where you get the warm fuzzy feeling of transactions, point in time backups, scalable read replication, etc
If you're already using Postgres, you can avoid increasing operational complexity by introducing another database. Less operational complexity means better availability.
You can atomically modify jobs and the rest of your database. For example, you can atomically create a row and create a job to do processing on it.
But one of the golden rules of databases is to not use them as queues/integration.
Granted I didn't even read the main article because it seems like such a casual headline.
Edit post-read: yeah, using it as a CI jobs database. He lists the alternatives, but seriously, Kafka? Kafka is for linear scaling pub/sub. This guy has a couple CI jobs infrequently run.
Sure this works if the entire thing is throwaway for a non critical pub/sub system.
"It's possible to scale Postgres to storing a billion 1KB rows entirely in memory - This means you could quickly run queries against the full name of everyone on the planet on commodity hardware and with little fine-tuning."
Yeah just because it can does not mean it is suited for this purpose.
Don't do this for any integration at even medium scale.
Avoiding increasing operational complexity is really important, but for pub/sub we are using Redis. While this does add complexity, it is very little, because it is incredibly easy to install and maintain.
Obviously you're in a better position to evaluate the trade-offs for your application than I am, so I'm not saying your decision is wrong, but this can potentially decrease availability if your application depends on both PostgreSQL AND Redis to be available to function.
Rolling your own pub/sub server implementation is often riddled with subtle concurrency bugs. For example, in our first iteration, when finding new events to dispatch, the implementation simply used the "last dispatched event ID" from the previous run. It sounded logical, because ID's are guaranteed to grow monotonically. But in MySQL, this approach is problematic under high load. There's a race condition: first, the server reserves a new autoincrement ID, and only then (in a different, non-atomic step) it inserts an actual row. So sometimes, the goroutine would retrieve a newer row skipping an older row, if two sessions were concurrently reserving autoincrement IDs (and you could get a wrong order of events, with ORDER BY id). We fixed that, but a month later another arcane concurrency bug was found due to wrong transaction model assumptions (I don't already remember the details). So if I was to choose between rolling your own implementation, and using an existing well-tested tool, now I'd choose the latter.
This 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
You are correct that things have improved further.
You can now efficiently partition your job queue, just like you would do with Kafka to get higher scalability. You then "prepare" your "dequeue" query in Postgres and the planner will only look at the relevant partition, pruning all of the others. It's like having one logical queue to insert into and hundreds of actual physical queues, transparently partitioned, to pull from. You then assign your workers to specific partitions and plow through your jobs.
In PG 14, you can reasonably have a thousand partitions on a single job queue, each virtually independent performance-wise. As a bonus, you can have two-level partitions gated on the task's timestamp. Older partitions/tasks can then be detached and dropped without using DELETE, which makes it a fast operation in Postgres. Any index or table bloat from the older partitions disappears immediately. Pretty sweet.
Obviously, this takes more work to set up and there's ongoing operational stuff (cron job), but you retain all of the transactional guarantees of Postgres in the process and the performance is quite good. I like the overall operational simplicity of having everything in a single, inexpensive and rock-solid RDBMS. I like getting transaction-safe jobs "for free."
It's so cheap, too. People on this thread talk about using SQS. I spend ~$1600/month on SQS to process 20M messages per day, going on three years now. I can do far more than that on our Postgres instance, a $5K machine bought two years ago, sitting in our co-lo in downtown LA with a cheap 10Gb Cogent connection that also runs the rest of the business ($2300/month for the entire rack of machines + Internet).
But I'm forced to pay for that damn SQS queue because that's how a business partner gets us data. Such a waste of money for something so cheap and easy to do reliably with Postgres. I've now spent over $50K on something I can do more or less for free on a 2012 Dell server. Such is business.
Just 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.
This is advice that seems reasonable but is actually pretty harmful.
Take a startup with a few users. The senior engineer decides they need pub/sub to ship a new feature. With Kafka, the team goes to learn about Kafka best practices, choose client libraries, and learn the Kafka quirks. They also need to spin up Kafka instances. They ship it in a month.
With postgres, they’ve got an MVP in a day, and shipped within a week.
I can set up an application to use AWS SQS or GCP PubSub in a day and it will scale without a second thought. I don't think it's productive to compare the worst case of scenario A and the best case of scenario B.
> With postgres, they’ve got an MVP in a day, and shipped within a week.
And the next week they realize they want reader processes to block until there is work to do. Oops that's not supported. Now you have to code that feature yourself... and soon you're reinventing Kafka.
I've been working with Kafka since 0.8, I mildly beg to differ on "easy to learn and use", just based on the fact that to use it well, you have to design your applications for its semantics, and that tuning it requires a lot of indepth understanding of its mechanics.
And I've seen a looot of bad designs and misconfigurations.
All that said, I'm a massive fan of Kafka, I'm the first to admit it's a complex tool, but it needs to be for the problem space it targets.
This needs to be sung from the rooftops every time Kafka is mentioned. It's an amazing tool but it is the wrong wrong wrong tool if you need a queue. It will bite you in the ass and you'll be left with someone breathing down your neck wondering why jobs are processing so slowly and why you can't just spin up more workers.
Exactly. If you do want something very scalable that fixes these problems but shares a lot of architectural similarity with Kafka then you should check out Apache Pulsar.
If anyone is looking to use postgres as a job server with node.js clients, I can highly recommend the pg-boss library (https://github.com/timgit/pg-boss). Looks like it just added pub/sub too (yesterday), so I guess you could use it for that too.
Webapp.io people: I really don't get how your service works, exactly. I see the broad strokes, but I don't know how it will fit my own use cases, because I cannot see how it would integrate with my architecture or workflows. Please create some more diagrams with actual examples of all the use cases you hint at on your website but don't actually show examples of (other than your config file examples, which do not answer any of my questions). Even if I could use your thing at work, I won't even try now, because I can't see how it would apply, and I don't want to spend an hour digging into your tech docs to figure out if it would apply.
The benchmark shows that the time to send notifications grows
quadratically with the number of notifications per transaction without
the patch while it grows linearly with the patch applied and
notification collapsing disabled.
I actually implemented this after seeing this post back in the days, and it worked great, but writes are somewhat slow after it passes ~1m messages per day in my small VPS, and then I switched to redis.
Same 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.
There is a simpler method. Rabbitmq for accepting new jobs. In the function start a job via spring batch. Basically you get the same effect but using existing tools.
Author here! A few updates since this was published two years ago:
- The service mentioned (now called https://news.ycombinator.com/item?id=21484215
Happy to answer any questions!
> doing hundreds of thousands of messages per day
> The postgres instance now runs on 32 cores and 128gb of memory and has scaled well.
Am I the only one?
I should've clarified, the database handles more than just the "regular" pub/sub, some of the tables have over a billion rows.
1 reply →
I assume that is their main database for everything, not just for pub/sub. One of the big benefits of doing it that way is that you have proper transaction handling across jobs and their related data.
5 replies →
Such a server is 400$/mo, a backend developer that can confidently maintain kafka in production is significantly more expensive!
35 replies →
I imagine their scaling problem isn't messages/day, it's probably lots of concurrent, persistent connections. And I don't think a connection pooler would work with this job queue setup.
Yeah, every home IoT hub processes more messages than that with less thsn raspberri pi worth of compute
4 replies →
Are you implying that given the specs, hundreds of thousands of messages per day is not good enough? I think you are, or at least that is what I was thinking myself.
4 replies →
People are jumping on this. Question is—do the resource requirements outlined align with usage you described, or is that combined workload? By combined workload, I mean working set plus messaging. It’s not a useful exercise to criticize a service that’s multifaceted based on a single use case. Full disclosure—-not a Postgres user, nor am I invested in the tech.
It’s such a low throughput requirement I think even bitcoin could support it.
1 reply →
To cherry pick two details of the post and insinuate something about it?
No.
Thanks for the great blog post - still relevant after a few years!
> statement_timeout=(a few days)
wouldnt you want this to be a few seconds or minutes? Maybe I miss the point of setting this to days...
Didn't want to deal with ramifications of statement timeouts in a complex system, the failure mode mentioned (queue filling up) happened on the scale of 6 weeks, so it was very cheap operationally to set this timeout to some high value.
2 replies →
"hundreds of thousands of messages per day"
This is not much load at all, an iPhone running RabbitMQ could process many millions of messages per day. Even 1M messages per day is only 11 messages per second average. i.e. not taxing at all.
I've built software that can process millions of messages per second on a single thread.
I find it amusing that we happily play these AAA gaming experiences that are totally fantastical in their ability to deal with millions of things per frame and then turn around and pretend like hundreds of thousands of things per day is some kind of virtue.
I assume you use polling workers looking for the next job to grab for themselves?
Personally I do see the niceness of having a good pattern implemented using existing technology. Less deployment nonsense, less devops, less complexity, a few tables at most. I've done similar things in the past, it is nice.
For anyone who'd criticize, having complex deployments can be just about as much dev time, AND if implemented well, they can theoretically covert this whole thing to rabbitmq with minimal effort just by swapping the queueing system.
In any case, happy to see people mentioning how using existing simple tech can lead to fairly simple to manage systems, and still solve the problems you're trying to solve.
I've always been curious, what kind of latency do you see between an insert, and when the notify goes out over the channel?
10ms or so
I'd be curious to know what the drawbacks of using PG for a pub/sub server are.
What are the options to use Postgres pub/sub with Java? Because the usual Java libraries don't seem to support the pub/sub functionality well, you have to actively poll when you want to subscribe.
https://impossibl.github.io/pgjdbc-ng/docs/0.8.9/user-guide/...
or
https://github.com/pgjdbc/r2dbc-postgresql#listennotify
This may depend on the JDBC driver support Listen/Notify. Though if queue traffic is relatively steady then maybe polling isn't so bad?
I very happily use this technique and I believe I found out about it from your original blog post. Thanks for the original writeup and for the update on how it's going a few years in!
Silly question but how does this compare to SQS? More cost friendly I assume?
SQS vs "Postgres Queue", I think mainly:
- Closed/lock-in vs. Open/lock-free
- Rigid data access pattern vs. Very flexible SQL access
-Managed by AWS vs. Managed by you/your team (although you could use one of those managed Postgres services to reduce ops burden)
- Integrates well with other AWS services (e.g. Lambda, SNS, DynamoDB, etc) vs. No integrations with AWS ecossystem out of the box
What was the isolation level used when that incident occurred?
The default postgres one, serializable (if I remember correctly?)
1 reply →
I 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.
This. The article seems like "one weird trick that message queue companies HATE" as it's utilizing, as far as I understand, some SQL semantics in a very specific way to cobble together a way of achieving what other software is designed to do out of the box. It seems fine for a toy system, but I wouldn't stake the success of a real company on this approach.
One could also use DNS TXT as an RDBMS with some interesting fault tolerance and distribution schemes. That doesn't mean it's a good idea or the best way to solve a problem.
If you haven't seen them already, the Jepsen analyses are really worth a read: https://aphyr.com/posts/293-jepsen-kafka https://aphyr.com/posts/282-jepsen-postgres https://aphyr.com/tags/jepsen
It's strange to see you suggesting this can't work for a "real" company. That's demeaning to the OPs company, which seems quite real. It's also odd that you're proposing replacing this with a distributed system and citing the Jepsen articles as support for it, when they prove the opposite. Distributed systems are hard. If you can avoid them and stay in the happy ACID town with Postrgres, indeed why not?
If you want to carry messages across the internet SQS et al is fine. But within the same system, e.g. in the same computer, or cluster, it makes much more sense to use something like this rather than something like SQS. Different tool, different job.
Thank you.
If a job fails, the connection to the database will timeout. Postgres will rollback the transaction, which releases row locks, freeing a job to be retried.
Of course, the database client and server together form a distributed system. The client might continue processing a job under the mistaken impression that it still holds the lock. Jobs still need to be idempotent, as with the streaming platforms.
> If a job fails, the connection to the database will timeout. Postgres will rollback the transaction, which releases row locks, freeing a job to be retried.
Only if you begin a transaction when your job starts that isn’t committed until you job finishes. As I understand it, this is not a good idea for long-running jobs, since you’ll have a long-running Postgres transaction. Am I missing something? The linked article doesn’t seem to use this approach.
Does the “FOR UPDATE” decrease the lock to just a single row, thus making it unproblematic?
This assumes that you're creating a transaction per message, which I think is not advisable.
6 replies →
I think the key concept here is atomicity. If some API is responsible for creating a job, storing it in the database AND publishing it can never be an atomic operation. Both the database and pub/sub servers are separate network connections from the application server. For example, if you save the record first and then publish, It's quite possibe that you save the record in the database and then lose the connection to the pub/sub server when publishing. If this happens, you can never know if the pub/sub server received the request and published it. In systems where it's critical to guarantee that a record was saved and guarantee that it was published as well, the only way to do that is by using a single external connection - in this case to a Postgres DB. We've used the same setup on an AWS RDS t2.medium machine to process over 600 records/second.
>If some API is responsible for creating a job, storing it in the database AND publishing it can never be an atomic operation.
We use transactional outbox for that - we insert a record about the event into the event table in the same transaction as the rest of the operation (providing atomicity), and then a special goroutine reads this table and pushes new events to the message broker on a different server. In our design, there are multiple services which might want to subscribe to the event, and the rule is that they shouldn't share their DB's (for proper scaling) so we can't handle event dispatch in some single central app instance. Of course we could implement our own pub/sub server in Go over a DB like Postgres if we wanted, but what's the point of reinventing the wheel if there's already existing battle-tested tools for that, considering you have to reimplement: queues, exchanges, topics, delivery guarantee, proper error handling, monitoring etc.
Distributed transactions are hard. And if queue data is the source of truth for something then they must be durable. All that said, when traffic volume, payload size, or throughout demands something specialized then it makes sense to do it.
If I'm building one shed then maybe I only need 5 tools, while a shed factory might use 100. Context matters.
You can use transactional outbox pattern to solve this.
Postgres is really just great for being able to build just about anything to get that first viable product built. It's basically the swiss army knife for anything data in my opinion. You got sql, nosql, job queues, full text indexing. It's great.
I use it as a sql database and full text search for little personal project I work on off and on and it works great. I haven't touched it except to check every few weeks for security updates for months since I got a promotion and it, the golang app server and python scripts have had no issue just churning along keeping a 30 day archive of links found via reddit and twitter. Postgres is great.
This seems to come up on HN at least once a year. Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expensive. Also, mitigation strategies like PgBouncer cannot be used with this approach (nor can scale out solutions like CitusDB I don't think).
Of course, if scalability is not a concern (or the connection limitations are eventually fixed in postgres - this has improved in 14), this would be a very viable approach.
Supabase's Realtime [1] is one of the solutions that can help with that. Although it doesn't exactly let you LISTEN at scale, but it allows the applications to be notified on changes in the database.
> Listen to changes in a PostgreSQL Database and broadcasts them over WebSockets
[1]: https://github.com/supabase/realtime
Disclosure: I'm a Supabase employee.
I like Supabase's approach over pub/sub. One of the big advantages is they listen to the Postgres WAL which overcomes the 8000 bytes limitation[1] of the notify approach.
And Elixir is especially well suited for this type of workload. I actually extracted out much of the Supabase Realtime library so that I could work with the data directly in Elixir[2]
[1]: https://github.com/supabase/realtime#why-not-just-use-postgr...
[2]: https://github.com/cpursley/walex
Does Supabase have HA or failover yet? Asked a few months ago and got no answer
How many connected users does this scale to roughly?
1 reply →
> Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expensive.
Scalability is always limited, no matter which solution you choose. This article argues that the scalability limit for this particular solution is acceptable for most people to begin with:
> It's rarely a mistake to start with Postgres and then switch out the most performance critical parts of your system when the time comes.
>LISTEN ties up a connection
Wait, what?? I can't keep doing things with my connection after I issue a LISTEN? That doesn't seem right! I would assume it would isomorphic to how unix-y bg programs will occasionally write to the console (although I see how this might be hard to deal with at the driver level). Now I will have to go check.
You (the application) can certainly go on to issue other database commands. See the docs [1].
> With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received.
[1]: https://www.postgresql.org/docs/current/sql-listen.html
It’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
There's something even nicer than advisory locks, as of a few years ago. https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...
Some comments from brandur on SKIP LOCKED in https://github.com/brandur/sorg/pull/263. I haven’t looked into it too much since what we have works without any issues; but good to know, thanks!
This is fantastic for relatively low volume queues with intensive work to be done by a small number of workers. For these types of use cases, I'll take this approach over RabbitMQ or Kafka all day long.
But once you get even just up to say, 40 messages per second with 100 worker processes, you're now up to 4000 updates per second just to see which worker got to claim which job, and up from there becomes untenable.
> This is fantastic for relatively low volume queues with intensive work to be done by a small number of workers. For these types of use cases, I'll take this approach over RabbitMQ or Kafka all day long.
That’s what our workload is like for our SaaS code analysis platform. We create a few tasks (~10 max) for every customer submission (usually triggered by a code push). We replaced Kafka with a PostgreSQL table a couple of years ago.
We made the schema, functions, and Grafana dashboard open source [0]. I think it’s slightly out-of-date but mostly the same as what we have now in production, and has been running perfectly.
[0] https://github.com/ShiftLeftSecurity/sql-task-queue
a few years back i worked on an enterprisey project that used postgres as a database, along with rabbitmq and celery for job processing.
of _course_ the system architecture had to have a job queue and it had to be highly available (implemented with a rabbitmq cluster)
what we learned after a few months in production was the only time the rabbitmq cluster had outages was when it got confused* and thought (incorrectly) there was a network partition, and flipped into partition recovery mode, causing a partial outage until production support could manually recover the cluster
the funny thing about this is that our job throughput was incredibly low, and we would have had better availability if we had avoided adding the temperamental rabbitmq cluster and instead implemented the job queue in our non-HA postgres instance that was already in the design --- if our postgres server went down then the whole app was stuffed anyway!
* this was a rabbitmq defect when TLS encryption was enabled and very large messages were jammed through the queue -- rabbitmq would be so busy encrypting / decrypting large messages that it'd forget to heartbeat, then it'd timeout and panic that it hadn't got any heartbeats, then assume that no heartbeats implied a network partition, and cause an outage, needing manual recovery. i think rabbitmq fixed that a few years back
Why jump from Postgres to Kafka? Celery + rabbitmq or redis is a great middle ground.
And RabbitMQ is a solid middle ground that you can scale like crazy.
I wrote StarQueue https://www.starqueue.org which uses Postgres as a back end.
I actually wrote StarQueue for MySQL, Microsoft SQL server and also Postgres - they all work just fine as a message queue back end, because they all support SKIP LOCKED.
I started out including Oracle as a supported database but found that I loathed Oracle's complexity.
Oban[1] from the Elixir ecosystem leans on Postgres for job scheduling
[1] https://github.com/sorentwo/oban
Wonder if theres a similar framework in Python
AFAIK the only one is Dramatiq [1] with dramatiq-pg [2]: a 3rd party message broker using Postgres LISTEN/NOTIFY.
[1]: https://dramatiq.io/
[2]: https://gitlab.com/dalibo/dramatiq-pg/
Strong 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.
I'm increasingly of the opinion that relational databases are absolutely the right way to build queue systems for most projects.
One of the biggest advantages comes when you start thinking about them in terms of transactions. Transactional guarantees are really useful here: guarantee that a message will be written to the queue if the transaction commits successfully, and guarantee that a message will NOT be written to the queue otherwise.
https://brandur.org/job-drain describes a great pattern for achieving that using PostgreSQL transactions.
The transaction feature seems nice but how often is your application dropping queue messages because something happened between tx.commit() and queue.send(msg)? My experience has been that this is not an issue.
10 replies →
The scalability problem is way overblown. Setting the correct isolation level and locking mode isn't that hard and a modern cloud-hosted PG/MySQL can push 10s of thousands of inserts/s no problem.
IMO, the downsides of hosting a queue inside your primary relational DB are very much outweighed by the downsides of 1) having to run a new piece of infra like rabbit and 2) having to coordinate consistency between your message queue and your relational DB(s)
In the end, engineering is just whether the thing works with minimal maintenance. And ultimately, I've had great experiences using DBaaQ for low volume data movement. It works as a persistent queue with easy to do retries etc.
For high throughput (we had ad tech servers with 1E7 hits/s) we used a home-built low-latency queue that supported real time and persisted data. But for low throughput stuff, the DBaaQ worked fine.
And ultimately, maybe it was a lack of imagination on our part since Segment was successful with a mid-throughput DBaaQ https://segment.com/blog/introducing-centrifuge/
well rabbitmq is really really hard to setup correctly and stuff like priority, time based scheduling are not that much easier than rabbitmq. in fact a queue adds more complexity and it is not necessary until you outscale your database. not saying that rabbitmq might be a better fit, it's just not a good fit to start with. if you have a small team < 8 it's better to stay with as few things as possible and especially with things you know (well).
I wouldn't recommend setting up your own message queue infrastructure either. The cloudamqp link was more about the content than the product. All cloud providers come with extremely simple, scalable, and inexpensive message queue services with bindings to most languages.
A message queue is one of those things that is easy enough and worth the effort to do "right" early on, because it is not something you want to rip out and rewrite when you hit your scaling bottlenecks, given how critical it is and how many things it will end up touching.
3 replies →
It seems notable that this is a blog post from the perspective of rabbitmq authors, or at least the author of a book about it. It talks very vaguely about one potential implementation of a queue on postgres.
Basically every piece of this article's criticism is wrong as applied to the source / link above.
- No need to poll the database table
- No table-level locks and manual handling: row locks used for handling the work in progress
- "Manual cleanup" -- uhhh
Etc.
Basically all of those reasons are solved by using LISTEN/NOTIFY and FOR UPDATE SKIP LOCKED, which every queue built on pg will use.
>all of those reasons are solved
How does it solve the additional code complexity problem?
7 replies →
It proves again, that in 90% of the cases Postgres is totally enough. If you don’t expect a high load (and most applications don’t), then just go with Postgres, simplicity wins!
A single core small Redis server can do wonders.
For fire and forget type jobs you can use lists instead of pub/sub: save a job to a list by a producer, pop it on the other end by a consumer and execute it. It's also very easy to scale, just start more producers and consumers.
We're currently using this technique, to process ~2M jobs per day, and we're just getting started. Redis needs very little memory for this, just a few mb.
Redis also supports acid style transactions.
Beware of the scale up challenges with Redis. Redis can only utilize a single core. If you do anything sophisticated that needs to be atomic then you can't scale out to multiple servers, and you can't scale up to multiple cores.
At least with Postgres you can scale up trivially. Postgres will efficiently take advantage of as many cores as you give it. For scale out you will need to move to a purpose built queuing solution.
Good point. My assumption is that the first hit would be memory usage, way before core usage.
There are many options for scaling:
- vertically scale by adding more memory
- start redis instance on another port (takes 1mb) if decided to add more cores on the same vm
- separate data into another vm
- sharding comes out of the box, but that would be my last resort
The problem is that you can't atomically write to your other database and also put a message on a redis queue. So you'll either end up with db changes not conveyed to redis, or you'll have messages on redis not reflected by changes to the db.
I just spent two months unrolling an unruly pg_boss implementation and that experience has soured me on using postgres for pubsub, jobs, or messaging. For my money, Github Actions is fine for infrequent cron jobs, or hell even a tiny lambda with a CloudWatch rule is super cheap, with infra as code to make it easy (relatively so) for anyone to deploy. After all, I'd rather spend the majority of my time on writing code than being a DevOps. If I need pub/sub, I'd much rather use SNS quick and dirty, or SNS+SQS for the heavy lifts. Separation of concerns and separation of tiers is still important to me, and I have no desire to maintain a server moving forward.
I have mostly great results using pg_boss. There was one upgrade which had a major bug, but the response was quick and the work around was easy.
The benefit of having a distributed cron that is version controlled and type checked in my repo has been amazing. I am also confident that my pg_boss implementation has higher uptime than GitHub actions.
Why couldn't the code run from a GitHub Action or within a Lambda be type checked and in version control? All of mine are. As for uptime, it completely depends on where you host your pg instance. But I'd wager you know that already.
Anybody using graphile-worker[1] in production/heavy load? It looks awesome, and I coded up some simple prototype tasks (email, sms, etc), but question how it truly scales. They claim horizontal scaling is trivial.
> graphile-worker is horizontally scalable. Each instance has a customisable worker pool, this pool defaults to size 1 (only one job at a time on this worker) but depending on the nature of your tasks (i.e. assuming they're not compute-heavy) you will likely want to set this higher to benefit from Node.js' concurrency.
[1] https://github.com/graphile/worker
Graphile Worker maintainer here; keep in mind that postgres is not the ideal location for a job queue, so you’re going to be limited ultimately by postgres’ capabilities. I’ve seen Worker max out at around 10k jobs/second but very much YMMV - you should benchmark it for your expected use case. Personally I’d move to a dedicated job queue if I started having an average of more than 1-2k jobs per second. The majority of systems never hit anywhere near this (we very much cater to the “long tail” of job queue needs).
Regarding the horizontal scalability; that relates to if you have heavy tasks (tasks that take a second or more to execute) - you can use more instances to get higher throughput.
Hope this helps!
Largely 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.
What does scale have to do with it? Pub/sub as an architectural pattern could be equally relevant for your use case whether there are a hundred users in your system or a billion.
And Kafka isn't the only solution for it. There are many lightweight pub/sub and queuing systems which also don't involve needlessly adding abstraction layers and application code into an RDBMS.
I think massive scale is the only reason you'd really want to adopt something like Kafka. If you're 10k inserts/s or less then there's no reason not to do everything in a single big relational DB where you get the warm fuzzy feeling of transactions, point in time backups, scalable read replication, etc
1 reply →
To spell out good reasons for doing this:
If you're already using Postgres, you can avoid increasing operational complexity by introducing another database. Less operational complexity means better availability.
You can atomically modify jobs and the rest of your database. For example, you can atomically create a row and create a job to do processing on it.
But one of the golden rules of databases is to not use them as queues/integration.
Granted I didn't even read the main article because it seems like such a casual headline.
Edit post-read: yeah, using it as a CI jobs database. He lists the alternatives, but seriously, Kafka? Kafka is for linear scaling pub/sub. This guy has a couple CI jobs infrequently run.
Sure this works if the entire thing is throwaway for a non critical pub/sub system.
"It's possible to scale Postgres to storing a billion 1KB rows entirely in memory - This means you could quickly run queries against the full name of everyone on the planet on commodity hardware and with little fine-tuning."
Yeah just because it can does not mean it is suited for this purpose.
Don't do this for any integration at even medium scale.
Avoiding increasing operational complexity is really important, but for pub/sub we are using Redis. While this does add complexity, it is very little, because it is incredibly easy to install and maintain.
Obviously you're in a better position to evaluate the trade-offs for your application than I am, so I'm not saying your decision is wrong, but this can potentially decrease availability if your application depends on both PostgreSQL AND Redis to be available to function.
Agree 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.
If you are interested how to implement this in Python, check out this Gist: https://gist.github.com/kissgyorgy/beccba1291de962702ea9c237...
It's really simple. I used this snippet for a real-time application which can react to any kind of change in the database instantly.
Rolling your own pub/sub server implementation is often riddled with subtle concurrency bugs. For example, in our first iteration, when finding new events to dispatch, the implementation simply used the "last dispatched event ID" from the previous run. It sounded logical, because ID's are guaranteed to grow monotonically. But in MySQL, this approach is problematic under high load. There's a race condition: first, the server reserves a new autoincrement ID, and only then (in a different, non-atomic step) it inserts an actual row. So sometimes, the goroutine would retrieve a newer row skipping an older row, if two sessions were concurrently reserving autoincrement IDs (and you could get a wrong order of events, with ORDER BY id). We fixed that, but a month later another arcane concurrency bug was found due to wrong transaction model assumptions (I don't already remember the details). So if I was to choose between rolling your own implementation, and using an existing well-tested tool, now I'd choose the latter.
This 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
So SKIP LOCKED is a pretty well worn optimization at this point. People have been doing this for a while.
What kind of TPS are people seeing on queues based on psql?
edit: https://gist.github.com/chanks/7585810
10k/s here, but that was on a postgres from years ago - there have been like 4 or 5 major versions since then I think.
That's a good amount and I'm betting you can push it forward. Further, a queue is trivially sharded since messages are entirely isolated.
That said, Kafka can do hundreds of thousands if not millions of messages per second, so clearly there's room for optimizations.
You are correct that things have improved further.
You can now efficiently partition your job queue, just like you would do with Kafka to get higher scalability. You then "prepare" your "dequeue" query in Postgres and the planner will only look at the relevant partition, pruning all of the others. It's like having one logical queue to insert into and hundreds of actual physical queues, transparently partitioned, to pull from. You then assign your workers to specific partitions and plow through your jobs.
In PG 14, you can reasonably have a thousand partitions on a single job queue, each virtually independent performance-wise. As a bonus, you can have two-level partitions gated on the task's timestamp. Older partitions/tasks can then be detached and dropped without using DELETE, which makes it a fast operation in Postgres. Any index or table bloat from the older partitions disappears immediately. Pretty sweet.
Obviously, this takes more work to set up and there's ongoing operational stuff (cron job), but you retain all of the transactional guarantees of Postgres in the process and the performance is quite good. I like the overall operational simplicity of having everything in a single, inexpensive and rock-solid RDBMS. I like getting transaction-safe jobs "for free."
It's so cheap, too. People on this thread talk about using SQS. I spend ~$1600/month on SQS to process 20M messages per day, going on three years now. I can do far more than that on our Postgres instance, a $5K machine bought two years ago, sitting in our co-lo in downtown LA with a cheap 10Gb Cogent connection that also runs the rest of the business ($2300/month for the entire rack of machines + Internet).
But I'm forced to pay for that damn SQS queue because that's how a business partner gets us data. Such a waste of money for something so cheap and easy to do reliably with Postgres. I've now spent over $50K on something I can do more or less for free on a 2012 Dell server. Such is business.
Just 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.
I've been on stage at KafkaConf demo-ing my Kafka SRE chops, and I would avoid Kafka until I am sure it is necessary.
'easy to learn and use' is a downright lie.
edit: link to this same topic being discussed a few weeks ago: https://news.ycombinator.com/item?id=28903614#28904103
This is advice that seems reasonable but is actually pretty harmful.
Take a startup with a few users. The senior engineer decides they need pub/sub to ship a new feature. With Kafka, the team goes to learn about Kafka best practices, choose client libraries, and learn the Kafka quirks. They also need to spin up Kafka instances. They ship it in a month.
With postgres, they’ve got an MVP in a day, and shipped within a week.
I can set up an application to use AWS SQS or GCP PubSub in a day and it will scale without a second thought. I don't think it's productive to compare the worst case of scenario A and the best case of scenario B.
How does any of this equally not apply to PostgreSQL ?
Is this some magical database where you don't need to worry about access patterns, best practices or how it is deployed.
2 replies →
> With postgres, they’ve got an MVP in a day, and shipped within a week.
And the next week they realize they want reader processes to block until there is work to do. Oops that's not supported. Now you have to code that feature yourself... and soon you're reinventing Kafka.
2 replies →
I've been working with Kafka since 0.8, I mildly beg to differ on "easy to learn and use", just based on the fact that to use it well, you have to design your applications for its semantics, and that tuning it requires a lot of indepth understanding of its mechanics.
And I've seen a looot of bad designs and misconfigurations.
All that said, I'm a massive fan of Kafka, I'm the first to admit it's a complex tool, but it needs to be for the problem space it targets.
Kafka is not a queue. Kafka's parallelism is limited by the number of partitions you allocate, and you have to be sure to avoid head of line blocking.
Not the case with a queue.
This needs to be sung from the rooftops every time Kafka is mentioned. It's an amazing tool but it is the wrong wrong wrong tool if you need a queue. It will bite you in the ass and you'll be left with someone breathing down your neck wondering why jobs are processing so slowly and why you can't just spin up more workers.
Exactly. If you do want something very scalable that fixes these problems but shares a lot of architectural similarity with Kafka then you should check out Apache Pulsar.
what is "head of line" blocking?
1 reply →
> Kafka is specifically designed for this purpose, it is free, and it is easy to learn and use
I think Kafka is great, but it is absolutely not “easy to learn and use”.
If anyone is looking to use postgres as a job server with node.js clients, I can highly recommend the pg-boss library (https://github.com/timgit/pg-boss). Looks like it just added pub/sub too (yesterday), so I guess you could use it for that too.
Webapp.io people: I really don't get how your service works, exactly. I see the broad strokes, but I don't know how it will fit my own use cases, because I cannot see how it would integrate with my architecture or workflows. Please create some more diagrams with actual examples of all the use cases you hint at on your website but don't actually show examples of (other than your config file examples, which do not answer any of my questions). Even if I could use your thing at work, I won't even try now, because I can't see how it would apply, and I don't want to spend an hour digging into your tech docs to figure out if it would apply.
I know there used to be some O(n^2) logic associated with notify. Anyone know if that ever got fixed? Cursory search only turns up https://postgrespro.com/list/thread-id/2407396
I actually implemented this after seeing this post back in the days, and it worked great, but writes are somewhat slow after it passes ~1m messages per day in my small VPS, and then I switched to redis.
Related previous discussion: https://news.ycombinator.com/item?id=28903614#28904103
You don't understand! Our small business with a few hundred customers needs to scale
Doesn't LISTEN connection holds transaction and therefore prevent vacuuming of old rows?
I don’t know
Same 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.
How would this Postgres feature work in a Postgres cluster? Does it work at all? If so, what are the requirements on the setup?
Could this be used with Postgrest?
So much mentions of Kafka or RMQ. What are peoples thoughts on Active MQ?
There is a simpler method. Rabbitmq for accepting new jobs. In the function start a job via spring batch. Basically you get the same effect but using existing tools.
this is the equivalent of solving Advent of Code with Postgress