16 ms·

Postgres is a great pub/sub and job server (2019)

468 points by anonu 5y ago

ericls 5y ago
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.
paulryanrogers 5y ago
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.
kasey_junk 5y ago
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.
- paxys 5y ago
  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.
  - peterhunt 5y ago
    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
    - option_greek 5y ago
      Yes but then 'enterprise architects' can't put their shiny cloud certifications to use. They need pubsub regardless of the scale because that's what is recommended by big cloud. And of course the companies also don't mind because there is no immediate devops cost for it. The problems like complexity of debugging, lockin won't hit home at the time of design.
sbaildon 5y ago
Oban[1] from the Elixir ecosystem leans on Postgres for job scheduling [1] https://github.com/sorentwo/oban https://github.com/sorentwo/oban
- anonu 5y ago
  Wonder if theres a similar framework in Python
  - Dauros 5y ago
    AFAIK the only one is Dramatiq [1] with dramatiq-pg [2]: a 3rd party message broker using Postgres LISTEN/NOTIFY. [1]: https://dramatiq.io/ https://dramatiq.io/ [2]: https://gitlab.com/dalibo/dramatiq-pg/ https://gitlab.com/dalibo/dramatiq-pg/
amarshall 5y ago
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 https://github.com/que-rb/que [2]: https://brandur.org/postgres-queues https://brandur.org/postgres-queues
- bjt 5y ago
  There's something even nicer than advisory locks, as of a few years ago. https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/ https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...
  - amarshall 5y ago
    Some comments from brandur on SKIP LOCKED in https://github.com/brandur/sorg/pull/263 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!
daenz 5y ago
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-right-tool-for-a-queue-based-system.html 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.
- merb 5y ago
  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).
  - daenz 5y ago
    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.
    - merb 5y ago
      well most cloud queues do not support priorities you can only create multiple subscriptions and prefer the messages from the higher one. so in the end you would built a system on a system anyway. also these queues lock you in quite hardly (and do not work on premise) Edit: also keep in mind most queues do not like "slow consumers" i.e. if your workload is bursty with long processing times, a database might be a better fit (i.e. rabbitmq does not like it) Edit2: we implemented a queue with postgres since we need acid and having 10k inserts per second is highly unlikely since a customer upload takes longer than a second (we deal with files) we mostly have burst workloads short period of high volume followed by long pauses (i.e. nobody uploads stuff at night)
throwawaygal7 5y ago
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
vlmutolo 5y ago
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-locked-for-in-postgresql-9-5/ 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.
- seanp2k2 5y ago
  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/293-jepsen-kafka https://aphyr.com/posts/282-jepsen-postgres https://aphyr.com/posts/282-jepsen-postgres https://aphyr.com/tags/jepsen https://aphyr.com/tags/jepsen
jjeaff 5y ago
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.
petilon 5y ago
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.
- EdwardDiego 5y ago
  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.
- anotherhue 5y ago
  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 https://news.ycombinator.com/item?id=28903614#28904103
- jastr 5y ago
  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.
  - daenz 5y ago
    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.
montroser 5y ago
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.
- ramraj07 5y ago
  Why jump from Postgres to Kafka? Celery + rabbitmq or redis is a great middle ground.
  - Spivak 5y ago
    And RabbitMQ is a solid middle ground that you can scale like crazy.
- preetamjinka 5y ago
  > 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 https://github.com/ShiftLeftSecurity/sql-task-queue
- shoo 5y ago
  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
colinchartier 5y ago
Author here! A few updates since this was published two years ago: - The service mentioned (now called https://webapp.io https://webapp.io ) eventually made it into YC (S20) and still uses postgres as its pub/sub implementation, doing hundreds of thousands of messages per day. The postgres instance now runs on 32 cores and 128gb of memory and has scaled well. - We bolstered Postgres's PUBLISH with Redis pub/sub for high traffic code paths, but it's been nice having ACID guarantees as the default for less popular paths (e.g., webhook handling) - This pattern only ever caused one operational incident, where a transaction held a lock which caused the notification queue to start growing, and eventually (silently) stop sending messages, starting postgres with statement_timeout=(a few days) was enough to solve this Previous discussion: https://news.ycombinator.com/item?id=21484215 https://news.ycombinator.com/item?id=21484215 Happy to answer any questions!
- anonu 5y ago
  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...
  - colinchartier 5y ago
    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.
    - runeks 5y ago
      So, just to make sure I understand correctly: notifications are delivered while the notification queue size is increasing (due to the transaction holding a lock), and it doesn’t become a problem until the queue size reaches its maximum, at which point it causes dropped notifications? But the queue grows precisely because some notifications aren’t getting delivered, right?
anotherhue 5y ago
Related previous discussion: https://news.ycombinator.com/item?id=28903614#28904103 https://news.ycombinator.com/item?id=28903614#28904103
nicoburns 5y ago
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 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.
andrewstuart 5y ago
I wrote StarQueue https://www.starqueue.org 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.
staticassertion 5y ago
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 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.
- deleted 5y ago
  [deleted]
- erichocean 5y ago
  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.
osigurdson 5y ago
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.
- javajosh 5y ago
  >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.
  - gurjeet 5y ago
    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 https://www.postgresql.org/docs/current/sql-listen.html
- gurjeet 5y ago
  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 https://github.com/supabase/realtime Disclosure: I'm a Supabase employee.
  - seanwilson 5y ago
    How many connected users does this scale to roughly?
andrew_ 5y ago
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.
- hermanradtke 5y ago
  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.
  - andrew_ 5y ago
    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.
andix 5y ago
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!
LAC-Tech 5y ago
You don't understand! Our small business with a few hundred customers needs to scale
fouadf 5y ago
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.
- k1w1 5y ago
  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.
  - fouadf 5y ago
    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
- joshlemer 5y ago
  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.
jrussbowman 5y ago
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.
- deleted 5y ago
  [deleted]
therealdrag0 5y ago
So much mentions of Kafka or RMQ. What are peoples thoughts on Active MQ?
nodesocket 5y ago
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 https://github.com/graphile/worker
- BenjieGillam 5y ago
  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!
throwaway984393 5y ago
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.
MaxMoney 5y ago
this is the equivalent of solving Advent of Code with Postgress
aneeshd16 5y ago
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.
- kgeist 5y ago
  >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.
  - paulryanrogers 5y ago
    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.
- 5y ago
tzahifadida 5y ago
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.
kissgyorgy 5y ago
If you are interested how to implement this in Python, check out this Gist: https://gist.github.com/kissgyorgy/beccba1291de962702ea9c237a900c79 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.
sgt 5y ago
Could this be used with Postgrest?
kgeist 5y ago
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.
rossmohax 5y ago
Doesn't LISTEN connection holds transaction and therefore prevent vacuuming of old rows?
- chuckSu 5y ago
  I don’t know
kpmah 5y ago
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.
- svdr 5y ago
  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.
  - kpmah 5y ago
    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.
- AtlasBarfed 5y ago
  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.
polskibus 5y ago
How would this Postgres feature work in a Postgres cluster? Does it work at all? If so, what are the requirements on the setup?
gregors 5y ago
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 https://postgrespro.com/list/thread-id/2407396 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.

468 points by anonu 5y ago

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.

paulryanrogers 5y ago

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.

kasey_junk 5y ago

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.

paxys 5y ago
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.
- peterhunt 5y ago
  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
  - option_greek 5y ago
    Yes but then 'enterprise architects' can't put their shiny cloud certifications to use. They need pubsub regardless of the scale because that's what is recommended by big cloud. And of course the companies also don't mind because there is no immediate devops cost for it. The problems like complexity of debugging, lockin won't hit home at the time of design.

sbaildon 5y ago

Oban[1] from the Elixir ecosystem leans on Postgres for job scheduling [1] https://github.com/sorentwo/oban https://github.com/sorentwo/oban

anonu 5y ago
Wonder if theres a similar framework in Python
- Dauros 5y ago
  AFAIK the only one is Dramatiq [1] with dramatiq-pg [2]: a 3rd party message broker using Postgres LISTEN/NOTIFY. [1]: https://dramatiq.io/ https://dramatiq.io/ [2]: https://gitlab.com/dalibo/dramatiq-pg/ https://gitlab.com/dalibo/dramatiq-pg/

amarshall 5y ago

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 https://github.com/que-rb/que [2]: https://brandur.org/postgres-queues https://brandur.org/postgres-queues

bjt 5y ago
There's something even nicer than advisory locks, as of a few years ago. https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/ https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...
- amarshall 5y ago
  Some comments from brandur on SKIP LOCKED in https://github.com/brandur/sorg/pull/263 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!

daenz 5y ago

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-right-tool-for-a-queue-based-system.html 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.

merb 5y ago
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).
- daenz 5y ago
  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.
  - merb 5y ago
    well most cloud queues do not support priorities you can only create multiple subscriptions and prefer the messages from the higher one. so in the end you would built a system on a system anyway. also these queues lock you in quite hardly (and do not work on premise) Edit: also keep in mind most queues do not like "slow consumers" i.e. if your workload is bursty with long processing times, a database might be a better fit (i.e. rabbitmq does not like it) Edit2: we implemented a queue with postgres since we need acid and having 10k inserts per second is highly unlikely since a customer upload takes longer than a second (we deal with files) we mostly have burst workloads short period of high volume followed by long pauses (i.e. nobody uploads stuff at night)

throwawaygal7 5y ago

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

vlmutolo 5y ago

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-locked-for-in-postgresql-9-5/ 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.

seanp2k2 5y ago
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/293-jepsen-kafka https://aphyr.com/posts/282-jepsen-postgres https://aphyr.com/posts/282-jepsen-postgres https://aphyr.com/tags/jepsen https://aphyr.com/tags/jepsen

jjeaff 5y ago

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.

petilon 5y ago

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.

EdwardDiego 5y ago
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.
anotherhue 5y ago
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 https://news.ycombinator.com/item?id=28903614#28904103
jastr 5y ago
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.
- daenz 5y ago
  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.

montroser 5y ago

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.

ramraj07 5y ago
Why jump from Postgres to Kafka? Celery + rabbitmq or redis is a great middle ground.
- Spivak 5y ago
  And RabbitMQ is a solid middle ground that you can scale like crazy.
preetamjinka 5y ago
> 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 https://github.com/ShiftLeftSecurity/sql-task-queue
shoo 5y ago
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

colinchartier 5y ago

Author here! A few updates since this was published two years ago: - The service mentioned (now called https://webapp.io https://webapp.io ) eventually made it into YC (S20) and still uses postgres as its pub/sub implementation, doing hundreds of thousands of messages per day. The postgres instance now runs on 32 cores and 128gb of memory and has scaled well. - We bolstered Postgres's PUBLISH with Redis pub/sub for high traffic code paths, but it's been nice having ACID guarantees as the default for less popular paths (e.g., webhook handling) - This pattern only ever caused one operational incident, where a transaction held a lock which caused the notification queue to start growing, and eventually (silently) stop sending messages, starting postgres with statement_timeout=(a few days) was enough to solve this Previous discussion: https://news.ycombinator.com/item?id=21484215 https://news.ycombinator.com/item?id=21484215 Happy to answer any questions!

anonu 5y ago
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...
- colinchartier 5y ago
  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.
  - runeks 5y ago
    So, just to make sure I understand correctly: notifications are delivered while the notification queue size is increasing (due to the transaction holding a lock), and it doesn’t become a problem until the queue size reaches its maximum, at which point it causes dropped notifications? But the queue grows precisely because some notifications aren’t getting delivered, right?

anotherhue 5y ago

Related previous discussion: https://news.ycombinator.com/item?id=28903614#28904103 https://news.ycombinator.com/item?id=28903614#28904103

nicoburns 5y ago

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 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.

andrewstuart 5y ago

I wrote StarQueue https://www.starqueue.org 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.

staticassertion 5y ago

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 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.

deleted 5y ago
[deleted]
erichocean 5y ago
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.

osigurdson 5y ago

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.

javajosh 5y ago
>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.
- gurjeet 5y ago
  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 https://www.postgresql.org/docs/current/sql-listen.html
gurjeet 5y ago
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 https://github.com/supabase/realtime Disclosure: I'm a Supabase employee.
- seanwilson 5y ago
  How many connected users does this scale to roughly?

andrew_ 5y ago

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.

hermanradtke 5y ago
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.
- andrew_ 5y ago
  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.

andix 5y ago

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!

LAC-Tech 5y ago

You don't understand! Our small business with a few hundred customers needs to scale

fouadf 5y ago

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.

k1w1 5y ago
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.
- fouadf 5y ago
  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
joshlemer 5y ago
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.

jrussbowman 5y ago

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.

deleted 5y ago
[deleted]

therealdrag0 5y ago

So much mentions of Kafka or RMQ. What are peoples thoughts on Active MQ?

nodesocket 5y ago

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 https://github.com/graphile/worker

BenjieGillam 5y ago
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!

throwaway984393 5y ago

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.

MaxMoney 5y ago

this is the equivalent of solving Advent of Code with Postgress

aneeshd16 5y ago

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.

kgeist 5y ago
>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.
- paulryanrogers 5y ago
  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.
5y ago

tzahifadida 5y ago

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.

kissgyorgy 5y ago

If you are interested how to implement this in Python, check out this Gist: https://gist.github.com/kissgyorgy/beccba1291de962702ea9c237a900c79 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.

sgt 5y ago

Could this be used with Postgrest?

kgeist 5y ago

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.

rossmohax 5y ago

Doesn't LISTEN connection holds transaction and therefore prevent vacuuming of old rows?

chuckSu 5y ago
I don’t know

kpmah 5y ago

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.

svdr 5y ago
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.
- kpmah 5y ago
  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.
AtlasBarfed 5y ago
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.

polskibus 5y ago

How would this Postgres feature work in a Postgres cluster? Does it work at all? If so, what are the requirements on the setup?

gregors 5y ago

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 https://postgrespro.com/list/thread-id/2407396 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.