26 ms·

SQL Maxis: Why We Ditched RabbitMQ and Replaced It with a Postgres Queue

• kevsim 3y ago
  I think Segment did something similar a while back. Instead of using Kafka for the queue of events in coming in, they built a queue on MySQL [0] 0: https://segment.com/blog/introducing-centrifuge/ https://segment.com/blog/introducing-centrifuge/
• aphsalina 3y ago
  What is Maxis?
• stolsvik 3y ago
  I will argue that what they want is a "work queue", not a message queue. I've written about a somewhat similar problem in context of the Mats3 library I've made: https://mats3.io/patterns/work-queues/ https://mats3.io/patterns/work-queues/ And yes, the point is then to use a database to hold the work, dispatching from that. In the Mats3 context I describe, the reason is to pace the dispatching to a queue (!), but for them, it should be to just run the work from that table. Also, the introspection/monitoring argument presented should be relevant for them. That a message queue library fetches more messages than the one it is working on is totally normal: ActiveMQ per default uses a prefetch of 1000 for queues, and Short.MAX_VALUE-1 (!) for topics. The broker will backfill you when you've depleted half of that. This is obviously to gain speed, so that once you've finished with one message, you already have another available, not needing to go back to the broker with both network and processing latencies: https://activemq.apache.org/what-is-the-prefetch-limit-for https://activemq.apache.org/what-is-the-prefetch-limit-for In summary, I feel that the use case they have, "thousands of jobs per day", which is extremely little for a message queue, where many of these jobs are hours-long, is .. well .. not optimal use case for a MQ. It is the wrong tool for the job, and just adds complexity.
• SergeAx 3y ago
  But... That means that their workers would constantly poll Postgres server instead of push mechanism of AMQT, aren't they? Also, problem described seems like a logical error. Worker shouldn't ack the job before finishing it.
• CobaltHorizon 3y ago
  This is interesting because I’ve seen a queue that was implemented in Postgres that had performance problems before: the job which wrote new work to the queue table would have DB contention with the queue marking the rows as processed. I wonder if they have the same problem but the scale is such that it doesn’t matter or if they’re marking the rows as processed in a way that doesn’t interfere with rows being added.
  • throwaway5959 3y ago
    > To make all of this run smoothly, we enqueue and dequeue thousands of jobs every day. The scale isn't large enough for this to at all be a worry. The biggest worry here I imagine is ensuring that a job isn't processed by multiple workers, which they solve with features built into Postgres. Usually I caution against using a database as a queue, but in this case it removes a piece of the architecture that they have to manage and they're clearly more comfortable with SQL than RabbitMQ so it sounds like a good call.
    • KrugerDunnings 3y ago
      It is easy to avoid multiple workers processing the same task: `delete from task where id = (select id from task for update skip locked limit 1) returning *;`
      • throwaway5959 3y ago
        I didn't say it was difficult, I just said it was the biggest concern. That looks correct.
  • zrail 3y ago
    (not sure why this comment was dead, I vouched for it) There are a lot of ways to implement a queue in an RDBMS and a lot of those ways are naive to locking behavior. That said, with PostgreSQL specifically, there are some techniques that result in an efficient queue without locking problems. The article doesn't really talk about their implementation so we can't know what they did, but one open source example is Que[1]. Que uses a combination of advisory locking rather than row-level locks and notification channels to great effect, as you can read in the README. [1]: https://github.com/que-rb/que https://github.com/que-rb/que
• jasonlotito 3y ago
  So, this article contains a serious issue. What is the prefetch value for RabbitMQ mean? > The value defines the max number of unacknowledged deliveries that are permitted on a channel. From the Article: > Turns out each RabbitMQ consumer was prefetching the next message (job) when it picked up the current one. that's a prefetch count of 2. The first message is unacknowledged, and if you have a prefetch count of 1, you'll only get 1 message because you've set the maximum number of unacknowledged messages to 1. So, I'm curious what the actual issue is. I'm sure someone checked things, and I'm sure they saw something, but this isn't right. tl;dr: prefetch count of 1 only gets one message, it doesn't get one message, and then a second. Note: I didn't test this, so there could be some weird issue, or the documentation is wrong, but I've never seen this as an issue in all the years I've used RabbitMQ.
  • binaryBandicoot 3y ago
    Agreed ! If prefetch was the issue; they could have even used AMQP's basic.get - https://www.rabbitmq.com/amqp-0-9-1-quickref.html#basic.get https://www.rabbitmq.com/amqp-0-9-1-quickref.html#basic.get
    • jalla 3y ago
      This is most likely correct. They didn't realize that consumers always prefetch, and the minimum is 1. Answered here: https://stackoverflow.com/questions/39699727/what-is-the-difference-between-prefetch-count-vs-no-ack-in-rabbitmq https://stackoverflow.com/questions/39699727/what-is-the-dif...
  • stuff4ben 3y ago
    That's my thinking as well. Seems like they're not using the tool correctly and didn't read the documentation. Oh well, let's switch to Postgres because "reasons". And now to get the features of a queuing system, you have to build it yourself. Little bit of Not Invented Here syndrome it sounds like.
    • sseagull 3y ago
• nemothekid 3y ago
  >To make all of this run smoothly, we enqueue and dequeue thousands of jobs every day. If you your needs aren't that expensive, and you don't anticipate growing a ton, then it's probably a smart technical decision to minimize your operational stack. Assuming 10k/jobs a day, thats roughly 7 jobs per minute. Even the most unoptimized database should be able to handle this.
  • aetherson 3y ago
    And, on the other hand, people shouldn't kid themselves about the ability of Postgres to handle millions of jobs per day as a queue.
    • philipbjorge 3y ago
      We were comfortably supporting millions of jobs per day as a Postgres queue (using select for update skip locked semantics) at a previous role. Scaled much, much further than I would’ve guessed at the time when I called it a short-term solution :) — now I have much more confidence in Postgres ;)
      • fjni 3y ago
        This! Most haven't tried. It goes incredibly far.
        • jbverschoor 3y ago
          Because all popular articles are about multi million tps at bigtech scale, and everybody thinks they're big tech somehow.
          • int_19h 3y ago
            That's the original problem, but then there are the secondary effects. Some of the people who made decision on that basis write blog posts about what they did, and then those blog posts end up on StackOverflow etc, and eventually it just becomes "this is how we do it by default" orthodoxy without much conscious reasoning involved - it's just a safe bet to do what works for everybody else even if it's not optimal.
• endisneigh 3y ago
  Thousands a day? Really? Even if it were hundreds of thousands a day it would make more sense to use a managed Pub Sub service and save yourself the hassle (assuming modest throughput).
  • dymk 3y ago
    Yeah, I'd do the opposite of what they ended up doing. Start with Postgres, which will handle their thousands-per-day no sweat. If they scaled to > 100 millions/day, then start investigating a dedicated message bus / queue system if an optimized PG solution starts to hit its limits.
    • dpflan 3y ago
      Yeah, it seems like a more natural evolution into specialization and scale rather than a step "backwards" to PG which I suspect will be the subject of a future blogpost about replacing their pq queue with another solution...
• rorymalcolm 3y ago
  Were Prequel using RaabitMQ to stay cloud platform agnostic when spinning up new environments? Always wondered how companies that offer managed services on the customers cloud like this manage infrastructure in this regard. Do you maintain an environment on each cloud platform with a relatively standard configuration, or do you have a central cluster hosted in one cloud provider which the other deployments phone home to?
  • zo1 3y ago
    Low effort post on my part, but I sure won't be buying or taking any advice from this company that publicly advertises this kind of mess in such a "proud" manner. Not only that but it's like the SASS-equivalent of the recipe blog post meme. This is what you get when you hire using leet code exercises and dump all your design thinking to "Senior" developers that have 1-3 years under their belt.
• smallerfish 3y ago
  We've inadvertently "load tested" our distributed locking / queue impl on postgres in production, and so I know that it can handle hundreds of thousands of "what should I run / try to take lock on task" queries per minute, with a schema designed to avoid bloat/vacuuming, tuned indices, and reasonably beefy hardware.
• avinassh 3y ago
  > And we guarantee that jobs won’t be picked up by more than one worker through simple read/write row-level locks. The new system is actually kind of absurdly simple when you look at it. And that’s a good thing. It’s also behaved flawlessly so far. Wouldn't this lead to contention issue when a lot of multiple workers are involved?
  • prpl 3y ago
    You can do an a select statement skipping locked rows in postgres.
    • zinclozenge 3y ago
      Specifically you also want SELECT ... FOR UPDATE SKIP LOCKED;
  • CBLT 3y ago
    I believe that problem is avoided by using SKIP LOCKED[0]. [0] 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...
  • fabian2k 3y ago
    My understanding is that a naive implementation essentially serializes access to the queue table. So it works, but no matter how many requests you make in parallel, only one will be served at a time (unless you have a random component in the query). With SKIP LOCKED you can resolve this easily, as long as you know about the feature. But almost every tutorial and description of implementing a job queue in Postgres mentions this now.
• borplk 3y ago
  In many scenarios a DB/SQL-backed queue is far superior to the fancy queue solutions such as RabbitMQ because it gives you instantaneous granular control over 'your queue' (since it is the result set of your query to reserve the next job). Historically people like to point out the common locking issues etc... with SQL but in modern datbases you have a good number of tools to deal with that ("select for update nowait"). If you think about it a queue is just a performance optimisation (it helps you get the 'next' item in a cheap way, that's it). So you can get away with "just a db" for a long time and just query the DB to get the next job (with some 'reservations' to avoid duplicate processing). At some point you may overload the DB if you have too many workers asking the DB for the next job. At that point you can add a queue to relieve that pressure. This way you can keep a super dynamic process by periodically selecting 'next 50 things to do' and injecting those job IDs in the queue. This gives you the best of both worlds because you can maintain granular control of the process by not having large queues (you drip feed from DB to queue in small batches) and the DB is not overly burdened.
  • dangets 3y ago
    +1 to this. I'm just as wary to recommend using a DB as a queue as the next person, but it is a very common pattern at my company. DB queues allow easy prioritization, blocking, canceling, and other dynamic queued job controls that basic FIFO queues do not. These are all things that add contention to the queue operations. Keep your queues as dumb as you possibly can and go with FIFO if you can get away with it, but DB queues aren't the worst design choice.
• sevenf0ur 3y ago
  Sounds like a poorly written AMQP client of which there are many. Either you go bare bones and write wrappers to implement basic functionality or find a fully fleshed out opinionated client. If you can get away with using PostgreSQL go for it.
  • kitd 3y ago
    As a matter of interest, what are good, reliable, actively developed AMQP clients? Asking because I've had problems with several.
    • datadeft 3y ago
      So far I have not had any issues with the Elixir one. https://github.com/pma/amqp https://github.com/pma/amqp
• simonw 3y ago
  The best thing about using PostgreSQL for a queue is that you can benefit from transactions: only queue a job if the related data is 100% guaranteed to have been written to the database, in such a way that it's not possible for the queue entry not to be written. Brandur wrote a great piece about a related pattern here: https://brandur.org/job-drain https://brandur.org/job-drain He recommends using a transactional "staging" queue in your database which is then written out to your actual queue by a separate process.
  • tlarkworthy 3y ago
    It's got a better name called a transactional outbox https://microservices.io/patterns/data/transactional-outbox.html https://microservices.io/patterns/data/transactional-outbox....
    • simonw 3y ago
      Yeah, that's a better name for it - good clear explanation too.
    • breischl 3y ago
      On a quick read this seems like another name for Change Data Capture. In general the pattern works better if you can integrate it with the database's transaction log, so then you can't accidentally forget to publish something. https://en.wikipedia.org/wiki/Change_data_capture https://en.wikipedia.org/wiki/Change_data_capture
      • stickperson 3y ago
        CDC is slightly different; it depends what type of events you are interested in. This is a pretty good read: https://debezium.io/blog/2020/02/10/event-sourcing-vs-cdc https://debezium.io/blog/2020/02/10/event-sourcing-vs-cdc.
      • orthoxerox 3y ago
        CDC is one of the mechanisms you can use to implement this if the volume of message is high, but the idea is to decouple your business transactions from sending out notifications and do the latter asynchronously.
• chime 3y ago
  If you don't want to roll your own, look into https://github.com/timgit/pg-boss https://github.com/timgit/pg-boss
• bstempi 3y ago
  I've done something like this and opted to use advisory locks instead of row locks thinking that I'd increase performance by avoiding an actual lock. I'm curious to hear what the team thinks the pros/cons of a row vs advisory lock are and if there really are any performance implications. I'm also curious what they do with job/task records once they're complete (e.g., do they leave them in that table? Is there some table where they get archived? Do they just get deleted?)
  • brasetvik 3y ago
    Advisory locks are purely in-memory locks, while row locks might ultimately hit disk. The memory space reserved for locks is finite, so if you were to have workers claim too many queue items simultaneously, you might get "out of memory for locks" errors all over the place. > Both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken not to exhaust this memory or the server will be unable to grant any locks at all. This imposes an upper limit on the number of advisory locks grantable by the server, typically in the tens to hundreds of thousands depending on how the server is configured. – https://www.postgresql.org/docs/current/explicit-locking.html https://www.postgresql.org/docs/current/explicit-locking.htm...
  • wswope 3y ago
    What did you do to avoid implicit locking, and what sort of isolation level were you using? Without more information about your setup, the advisory locking sounds like dead weight.
    • bstempi 3y ago
      > What did you do to avoid implicit locking, and what sort of isolation level were you using? I avoided implicit locking by manually handling transactions. The query that acquired the lock was a separate transaction from the query that figured out which jobs were eligible. > Without more information about your setup, the advisory locking sounds like dead weight. Can you expand on this? Implementation-wise, my understanding is that both solutions require a query to acquire the lock or fast-fail, so the advisory lock acquisition query is almost identical SQL to the row-lock solution. I'm not sure where the dead weight is.
• autospeaker22 3y ago
  We do just about everything with one or more Postgres databases. We have workers that query the db for tasks, do the work, and update the db. Portals that are the read-only view of the work being performed, and it's pretty amazing how far we've gotten with just Postgres and no real tuning on our end. There's been a couple scenarios where query time was excessive and we solved by learning a bit more about how Postgres worked and how to redefine our data model. It seems to be the swiss army knife that allows you to excel at most general cases, and if you need to do something very specific, well at that point you probably need a different type of database.
• twawaaay 3y ago
  As much as I detest MongoDB immaturity in many respects, I found a lot of features that are actually making life easier when you design pretty large scale applications (mine was typically doing 2GB/s of data out of the database, I like to think it is pretty large). One feature I like is change event stream which you can subscribe to. It is pretty fast and reliable and for good reason -- the same mechanism is used to replicate MongoDB nodes. I found you can use it as a handy notification / queueing mechanism (more like Kafka topics than RabbitMQ). I would not recommend it as any kind of interface between components but within an application, for its internal workings, I think it is pretty viable option.
  • necovek 3y ago
    That's quite interesting: I wonder if someone has done something similar with Postgres WAL log streaming?
    • twawaaay 3y ago
      MongoDB's change stream is accidentally very simple to use. You just call the database and get continuous stream of documents that you are interested in from the database. If you need to restart, you can restart processing from the chosen point. It is not a global WAL or anything like that, it is just a stream of documents with some metadata.
      • spmurrayzzz 3y ago
        > If you need to restart, you can restart processing from the chosen point One caveat to this is that you can only start from wherever the beginning of your oplog window is. So for large deployments and/or situations where your oplog ondisk size simply isn't tuned properly, you're SOL unless you build a separate mechanism for catching up.
        • twawaaay 3y ago
          Which is fine, queueing systems can't store infinity of messages either. In the end messages are stored somewhere so there is always some limit.
• mark242 3y ago
  In summary -- their RabbitMQ consumer library and config is broken in that their consumers are fetching additional messages when they shouldn't. I've never seen this in years of dealing with RabbitMQ. This caused a cascading failure in that consumers were unable to grab messages, rightfully, when only one of the messages was manually ack'ed. Fixing this one fetch issue with their consumer would have fixed the entire problem. Switching to pg probably caused them to rewrite their message fetching code, which probably fixed the underlying issue. It ultimately doesn't matter because of the low volume they're dealing with, but gang, "just slap a queue on it" gets you the same results as "just slap a cache on it" if you don't understand the tool you're working with. If they knew that some jobs would take hours and some jobs would take seconds, why would you not immediately spin up four queues. Two for the short jobs (one acting as a DLQ), and two for the long jobs (again, one acting as a DLQ). Your DLQ queues have a low TTL, and on expiration those messages get placed back onto the tail of the original queues. Any failure by your consumer, and that message gets dropped onto the DLQ and your overall throughput is determined by the number * velocity of your consumers, and not on your queue architecture. This pg queue will last a very long time for them. Great! They're willing to give up the easy fanout architecture for simplicity, which again at their volume, sure, that's a valid trade. At higher volumes, they should go back to the drawing board.
  • stolsvik 3y ago
    Their solution does not preclude fanout. Fetching work items from such a work queue by multiple nodes/servers should be no problem. One solution that also would be good for monitoring would be to have a state column, and a "handled by node" column. So, in a transaction, find a row that is not taken, take it by setting the state to PROCESSING, and set the handled_by_node to the nodename. Shove in a timestamp for good measure. When it is done, set the state to DONE, or delete the row. Monitor by having some health check evaluating that no row stays in PROCESSING for too long, and that no row stays NOT_STARTED for too long, etc. Introspect by making a nice little HTML screen that shows this work queue and its states. As I wrote in another comment, this is somewhat similar to a "work queue pattern" I've described here: https://mats3.io/patterns/work-queues/ https://mats3.io/patterns/work-queues/
  • datavirtue 3y ago
• gorjusborg 3y ago
  I'm all for simplifying stacks by removing stuff that isn't needed. I've also used in-database queuing, and it worked well enough for some use cases. However, most importantly: calling yourself a maxi multiple times is cringey and you should stop immediately :)
  • Zopieux 3y ago
    What even is a maxi, please?
    • gorjusborg 3y ago
      maximalist
• say_it_as_it_is 3y ago
  I love postgresql. It's a great database. However, this blog post is by people who are not quite experienced enough with message processing systems to understand that the problem wasn't RabbitMQ but how they used it.
• jrib 3y ago
  > One of our team members has gotten into the habit of pointing out that “you can do this in Postgres” whenever we do some kind of system design or talk about implementing a new feature. So much so that it’s kind of become a meme at the company. love it
  • mordae 3y ago
    Did this recently with a GIS query. But everyone here loves PG already. :-)
• FooBarWidget 3y ago
  I've also used PostgreSQL as a queue but I worry about operational implications. Ideally you want clients to dequeue an item, but put it back in the queue (rollback transaction) if they crash while prpcessing the item. But processing is a long-running task, which means that you need to keep the database connection open while processing. Which means that your number of database connections must scale along with the number of queue workers. And I've understood that scaling database connections can be problematic. Another problem is that INSERT followed by SELECT FOR UPDATE followed by UPDATE and DELETE results in a lot of garbage pages that need to be vacuumed. And managing vacuuming well is also an annoying issue...
  • benlivengood 3y ago
    I've generally seen short(ish) leases as the solution to this problem. The queue has an owner and expiration column and workers update the lease and NOW()+N when getting work, and when selecting for work get anything that has expired or has no lease. This assumes the processing is idempotent in the rest of the system and is only committed transactionally when it's done. Some workers might do wasted work, but you can tune the expiration future time for throughput or latency.
• omneity 3y ago
  Postgres is super cool and comes with batteries for almost any situation you can throw at it. Low throughput scenarios are a great match. In high throughput cases, you might find yourself not needing all the extra guarantees that Postgres gives you, and at the same time you might need other capabilities that Postgres was not designed to handle, or at least not without a performance hit. Like everything else in life, it's always a tradeoff. Know your workload, the tradeoffs your tools are making, and make sure to mix and match appropriately. In the case of Prequel, it seems they possibly have a low throughput situation at hands, i.e. in the case of periodic syncs the time spent queuing the instruction <<< the time needed to execute it. Postgres is great in this case.
• semiquaver 3y ago
  When your workload is trivially tiny, most any technology can be made to work.
  • fabian2k 3y ago
    There's a pretty large area between "trivially tiny" and "so large that a single Postgres instance on reasonable hardware can't handle it anymore".
    • semiquaver 3y ago
      Agreed! Lots of people overengineer. I’d venture to guess that the median RabbitMQ-using app in production could not easily be replaced with postgres though. The main reasons this one could are very low volume and not really using any of RMQ’s features. I love postgres! But RMQ and co fulfill a different need.
    • baq 3y ago
      and the high end of unreasonable hardware can get you reaaaaaallly far, which in the age of cloud computing is something people forget about and try to scale horizontally when not strictly necessary
      • threeseed 3y ago
        Most people should at least be thinking about horizontal scalability. Because the chances of a cloud instance randomly going down is not insignificant.
    • orthecreedence 3y ago
      Not when a queue is involved. IME trying to replicate something like beanstalkd (https://beanstalkd.github.io/ https://beanstalkd.github.io/) in postgres is asking for trouble for anything but trivial workloads. If you're measuring throughput in jobs/s, use a real work queue.
      • fabian2k 3y ago
• exabrial 3y ago
  We use ActiveMQ (classic) because of the incredible console. Combine that with hawt.io and you get some extra functionality not included in the normal console. I'm always surprised, even with the older version of ActiveMQ, what kind of throughput you can get, on modest hardware. A 1gb kvm with 1 cpu easily pushes 5000 msgs/second across a couple hundred topics and queues. Quite impressive and more than we need for our use case. ActiveMQ Artemis is supposed to scale even farther out.
  • therealdrag0 3y ago
    Same. Ran a perf test recently. With two 1 core brokers I got 2000 rps with persistence and peaked at 12000 rps with non-persistence. We’ve also had similar issues as OP, except fixing it just came down to configuring the Java client to have 0 prefetch so that long jobs don’t block other msgs from being processed by other clients. Also using separate queues wide different workloads.
    • knallfrosch 3y ago
      It seems that OP's company doesn't really know anything about the job workload beforehand, as the jobs are created by their customers. Having different queues for short/long workloads might be impossible.
• u89012 3y ago
  Would be nice if a little more detail were added in order to give anyone looking to do the same more heads-up to watch out for potential trouble spots. I take it the workers are polling to fetch the next job which requires a row lock which in turn requires a transaction yeah? How tight is this loop? What's the sleep time per thread/goroutine? At what point does Postgres go, sorry not doing that? Or is there an alternative to polling and if so, what? :)
  • adamckay 3y ago
    > Or is there an alternative to polling and if so, what? :) LISTEN and NOTIFY are the common approaches to avoiding polling, but I've not used them myself (yet). https://www.postgresql.org/docs/current/sql-listen.html https://www.postgresql.org/docs/current/sql-listen.html https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html
• stereosteve 3y ago
  Another good library for this is Graphile Worker. Uses both listen notify and advisory locks so it is using all the right features. And you can enqueue a job from sql and plpgsql triggers. Nice! Worker is in Node js. https://github.com/graphile/worker https://github.com/graphile/worker
• haarts 3y ago
  I didn't even know Postgres had a queue last year. I used it just for fun and it is GREAT. People using Kafka are kidding themselves.
• code-e 3y ago
  As the maintainer of a rabbitmq client library (not the golang one mentioned in the article) the bit about dealing with reconnections really range true. Something about the AMQP protocol seems to make library authors just... avoid dealing with it, forcing the work onto users, or wrapper libraries. It's a real frustration across languages, golang, python, JS, etc. Retry/reconnect is built in to HTTP libraries, and database drivers. Why don't more authors consider this a core component of a RabbitMQ client?
  • initialed85 3y ago
    Lol, your comment resonates deeply with me. We've had RabbitMQ as part of our stack at my day job since time began, I think it's great software overall but boy are the client libraries a challenge. We've built a generalised abstraction around first Pika and then pyamqp (because Pika had some odd issues, I forget the details of which) and while pyamqp seems better, it's still not without its odd warts. We ended up needing to develop a watchdog to wrap invocation of amqp.Connection.drain_events(timeout: int) because despite using the timeout, that call would very occasionally inexplicably block forever (with the only way to break it free being to call amqp.Connector.collect()). My other data point was a time I built something to slice off a copy of production data for testing purposes (from instances of the system above) using Benthos (pretty cool software tbh, Go underneath), but it would inexplicably just stop consuming messages and I had no idea why (so I just went back to our gross but proven Python abstraction to achieve the same).
  • klabb3 3y ago
    Nats has all of these features built in, and is a small standalone binary with optional persistence. I still don’t understand why it’s not more popular.
  • deleted 3y ago
    [deleted]
  • skrimp 3y ago
    This also occurs when dealing with channel-level protocol exceptions, so this behavior is doubly important to get right. I think one of the hard parts here is that the client needs to be aware of these events in order to ensure that application level consistency requirements are being kept. The other part is that most of the client libraries I have seen are very imperative. It's much easier to handle retries at the library level when the client has specified what structures need to be declared again during a reconnect/channel-recreation.
• eckesicle 3y ago
  Postgres is probably the best solution for every type of data store for 95-99% of projects. The operational complexity of maintaining other attached resources far exceed the benefit they realise over just using Postgres. You don’t need a queue, a database, a blob store, and a cache. You just need Postgres for all of these use cases. Once your project scales past what Postgres can handle along one of these dimensions, replace it (but most of the time this will never happen) It also does wonders for your uptime and SLO.
  • fullstop 3y ago
    We collect messages from tens of thousands of devices and use RabbitMQ specifically because it is uncoupled from the Postgres databases. If the shit hits the fan and a database needs to be taken offline the messages can pool up in RabbitMQ until we are in a state where things can be processed again.
    • hn_throwaway_99 3y ago
      Still trivial to get that benefit with just a separate postgres instance for your queue, then you have the (very large IMO) benefit of simplifying your overall tech stack and having fewer separate components you have to have knowledge for, keep track of version updates for, etc.
    • wvenable 3y ago
      You may well be the 1-5% of projects that need it.
      • Spivak 3y ago
        Everyone should use this pattern unless there's a good reason not too though. Turning what would otherwise be outages into queue backups is a godsend. It becomes impossible to ever lose in-flight data. The moment you persist to your queue you can ack back to the client.
        • mordae 3y ago
• dapearce 3y ago
  Love to see it. We (CoreDB) recently released PGMQ, a message queue extension for Postgres: https://github.com/CoreDB-io/coredb/tree/main/extensions/pgmq https://github.com/CoreDB-io/coredb/tree/main/extensions/pgm...
  • fullstop 3y ago
    Perhaps you mean https://github.com/CoreDB-io/coredb/tree/main/extensions/pgmq https://github.com/CoreDB-io/coredb/tree/main/extensions/pgm... Your link results in a 404.
    • dapearce 3y ago
      Yes, copy/paste error just fixed!
• 0xbadcafebee 3y ago
  It's important not to gloss over what your actual use-case is. Don't just pick some tech because "it seems simpler". Who gives a crap about simplicity if it doesn't meet your needs? List your exact needs and how each solution is going to meet them, and then pick the simplest solution that meets your needs. If you ever get into a case where "we don't think we're using it right", then you didn't understand it when you implemented it. That is a much bigger problem to understand and prevent in the future than the problem of picking a tool.
• gamedna 3y ago
  Side note, the amount of times that this article redundantly mentioned "Ditched RabbitMQ And Replaced It With A Postgres Queue" made me kinda sick.
• j3th9n 3y ago
  Sooner or later they will have to deal with deadlocks.
• northisup 3y ago
  Reticulating Splines?
  • orthecreedence 3y ago
    Zzzzt
• pnathan 3y ago
  I've had a very good experience with pg queuing. I didn't even know `skip locked` was a pg clause. That would have... made the experience even better! I am afraid I've moved to a default three-way architecture: - backend autoscaling stateless server - postgres database for small data - blobstore for large data it's not that other systems are bad. its just that those 3 components get you off the ground flying, and if you're struggling to scale past that, you're already doing enormous volume or have some really interesting data patterns (geospatial or timeseries, perhaps).
  • allan_s 3y ago
    For geospatial you actually have postgis extension which is a battle tested solution
    • pnathan 3y ago
      Quite correct. Made an error. Carryover from a prior job where we mucked with weather data... I was thinking more along the lines of raster geo datasets like sat images, etc. Each pixel represents one geo location, with a carrying load of metadata, then a timeseries of that snapshot & md, so timeseries-raster-geomapped data basically. I don't remember anymore what that general kind of datatype is called, sadly.
• tonymet 3y ago
  whenever i see RDBMS queues i think : why would you implement a queue or stack in a b-tree ? always go back to fundamentals. the rdbms is giving you replication , queries , locking but at what cost ?
• macspoofing 3y ago
  How do you handle stale 'processing' jobs (i.e. jobs that were picked-up by a consumer but never finished - maybe because the consumer died)?
  • bstempi 3y ago
    Not the author, but I've used PG like this in the past. My criteria for selecting a job was (1) the job was not locked and (2) was not in a terminal state. If a job was in the "processing" state and the worker died, that lock would be free and that job would be eligible to get picked up since its not in a terminal state (e.g., done or failed). This can be misleading at times because a job will be marked as processing even though its not.
  • severino 3y ago
    In the very few occasions that I've seen a queue backed by a Postgres table, when a job was taken, its row in the database was locked for the entire processing. If the job was finished, a status column was updated so the job won't be taken again in the future. If it wasn't, maybe because the consumer died, the transaction would eventually be rolled back, leaving the row unlocked for another consumer to take it. But the author may have implemented this differently.
    • throwaway74567 3y ago
      That's a good approach if the worker is connected to the database. If an external process is responsible for marking a job as done, you could add a timestamp column that will act as a timeout. The column will be updated before the job is given to the worker. SELECT ... WHERE ts > NOW() UPDATE ... SET ts = NOW() + INTERVAL '1 HOUR'
• animex 3y ago
  Interestingly, we've always started with an SQL custom queue and thought one day we'll "upgrade to RabbitMQ".
• SergeAx 3y ago
  > One of our team members has gotten into the habit of pointing out that “you can do this in Postgres” Actually, using Postgres stored procedures they can do anything in Postgres. I am quite sure they can rewrite their entire product using only stored procedures. Doesn't mean they really want to do that, of course.
• sa46 3y ago
  Here are a couple of tips if you want to use postgres queues: - You probably want FOR NO KEY UPDATE instead of FOR UPDATE so you don't block inserts into tables that have a foreign key relationship with the job table. [1] - If you need to process messages in order, you don't want SKIP LOCKED. Also, make sure you have an ORDER BY clause. My main use-case for queues is syncing resources in our database to QuickBooks. The overall structure looks like: BEGIN; -- start a transaction SELECT job.job_id, rm.data FROM qbo.transmit_job job JOIN resource_mutation rm USING (tenant_id, resource_mutation_id) WHERE job.state = 'pending' ORDER BY job.create_time LIMIT 1 FOR NO KEY UPDATE OF job NOWAIT; -- External API call to QuickBooks. -- If successsful: UPDATE qbo.transmit_job SET state = 'transmitted' WHERE job_id = $1; COMMIT; This code will serialize access to the transmit_job table. A more clever approach would be to serialize access by tenant_id. I haven't figured out how to do that yet (probably lock on a tenant ID first, then lock on the job ID). Somewhat annoyingly, Postgres will log an error if another worker holds the row lock (since we're not using SKIP LOCKED). It won't block because of NOWAIT. CrunchyData also has a good overview of Postgres queues: [2] [1]: https://www.migops.com/blog/2021/10/05/select-for-update-and-its-behavior-with-foreign-keys-in-postgresql/ https://www.migops.com/blog/2021/10/05/select-for-update-and... [2]: https://blog.crunchydata.com/blog/message-queuing-using-native-postgresql https://blog.crunchydata.com/blog/message-queuing-using-nati...
  • dilyevsky 3y ago
    Not doing SKIP LOCKED will make it basically single threaded, no? I’m of the opinion that you should just use Temporal if you don’t need inter-job order guarantees
    • AlfeG 3y ago
      Afree. Skip locked main utility is to provide work queue capability to PG. Even documentation of PG is referring to skip locked as a main driver for work queues
    • sa46 3y ago
      I explicitly want single-threaded execution within a tenant. After writing the original post, I figured out how to parallelize across tenants. The trick is to limit the query to only look at the next pending job for each tenant, which then allows for using SKIP LOCKED to process tenants in parallel: SELECT job.job_id, rm.data FROM qbo.transmit_job job JOIN resource_mutation rm USING (tenant_id, resource_mutation_id) WHERE job.state = 'pending' AND job.job_id = ( SELECT qj2.job_id FROM qbo.transmit_job qj2 WHERE job.tenant_id = qj2.tenant_id AND qj2.state = 'pending' ORDER BY qj2.create_time LIMIT 1 ) ORDER BY job.create_time LIMIT 1 FOR NO KEY UPDATE OF job SKIP LOCKED > you should just use Temporal Do you mean the Airflow-esque DAG runner? I prefer Postgres because it's one less moving part, I like transactional guarantees, my volume is tiny, and I can tweak the queue logic like above with a simple predicate change.
• deleted 3y ago
  [deleted]
• jabl 3y ago
  Seems like a slamdunk example of choosing boring technology. https://boringtechnology.club/ https://boringtechnology.club/
• andrewstuart 3y ago
  I wrote a message queue in Python called StarQueue. It’s meant to be a simpler reimagining of Amazon SQS. It has an HTTP API and behaves mostly like SQS. I wrote it to support Postgres, Microsoft’s SQL server and also MySQL because they all support SKIP LOCKED. At some point I turned it into a hosted service and only maintained the Postgres implementation though the MySQL and SQL server code is still in there. It’s not an active project but the code is at https://github.com/starqueue/starqueue/ https://github.com/starqueue/starqueue/ After that I wanted to write the worlds fastest message queue so I implemented an HTTP message queue in Rust. It maxed out the disk at about 50,000 messages a second I vaguely recall, so I switched to purely memory only and in the biggest EC2 instance I could run it on it did about 7 million messages a second. That was just a crappy prototype so I never released the code. After that I wanted to make the simplest possible message queue so I discovered that Linux atomic moves are the basis of a perfectly acceptable message queue that is simply file system based. I didn’t put it into a message queue, but close enough to be the same I wrote an SMTP buffer called Arnie. It’s only about 100 lines of Python. https://github.com/bootrino/arniesmtpbufferserver https://github.com/bootrino/arniesmtpbufferserver
• anecdotal1 3y ago
  Postgres job queue in Elixir: Oban "a million jobs a minute" https://getoban.pro/articles/one-million-jobs-a-minute-with-oban https://getoban.pro/articles/one-million-jobs-a-minute-with-...
• tantalor 3y ago
  What does "maxi/maxis" mean in this context? Google search for [sql maxis] just returns this article.
• VincentEvans 3y ago
  One thing worth pointing out - that the approach described in TFA changes PUSH architecture to PULL. So now you have to deal with deciding how tight your polling loop is, and with reads that are happening regardless of whether you have messages waiting to be processed or not, expending both CPU and requests, which may matter if you are billed accordingly. Not in any way knocking it, just pointing out some trade-offs.
• user3939382 3y ago
  Another middle ground is AWS Batch. If you don’t need like complicated/rules based on the outcome of the run etc it’s simpler, especially if you’re already used to doing ECS tasks.
• coding123 3y ago
  We have a mix of agenda jobs and rabbitmq. I know there are more complex use-cases, like fan out. but in reality the rabbit stack keeps disconnecting silently in the stack we're using (js). Someone has to go in and restart pods (k8s). All the stuff on Agenda works perfectly all the time. (which is basically using mongo's find and update)
• TexanFeller 3y ago
  Using a DB as an event queue opens up many options not easily possible with traditional queues. You can dedupe your events by upserting. You can easily implement dynamic priority adjustment to adjust processing order. Dedupe and priority adjustment feels like an operational superpower.
  • throwaway74567 3y ago
    You can get dedup with some queues, like SQS
• phlakaton 3y ago
  I don't doubt that a switch from a custom backfill management system written on RabbitMQ could be rewritten in certain cases on Postgres for equivalent if not better results. My question would be why you're in the business of writing a job manager in the first place.
  • marcosdumay 3y ago
    > My question would be why you're in the business of writing a job manager in the first place. Most often, because it's easier than configuring a ready made one.
• habibur 3y ago
  Something I didn't get : SQL server won't callback clients [or application-server] informing that new data is available. So how do you poll it? A query in periodic loop? Some other way? How much that scales, or how much load does that create on the server?
  • lukaszkorecki 3y ago
    Postgres has a NOTIFY feature: https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html that can be used for this if you don't want to poll.
• yarg 3y ago
  > We maintain things like queue ordering by adding an ORDER BY clause in the query that consumers use to read from it (groundbreaking, we know). The dude's being a bit too self-deprecating with that (sarcastic quip). But there's valuable something buried there - if it is possible to efficiently solve a problem without introducing novel mechanisms and concepts, it is highly desirable to do so. Don't reinvent the wheel unless you need to. > You could set the prefetch count to 1, which meant every worker will prefetch at most 1 message. Or you could set it to 0, which meant they will each prefetch an infinite number of messages. O what the actual fuck? I'm really hoping he's right about holding it wrong, because otherwise ???
• municha 3y ago
  Take a look at Apache pulsar which is a messaging and streaming platform https://streamnative.io/blog/comparison-of-messaging-platforms-apache-pulsar-vs-rabbitmq-vs-nats-jetstream https://streamnative.io/blog/comparison-of-messaging-platfor...
• rbut 3y ago
  Also had a similar experience using RabbitMQ with Django+Celery. Extremely complicated and workers/queues would just stop for no reason. Moved to Python-RQ [1] + Redis and been rock solid for years now. Redis is also great for locking to ensure only one instance of a job/task can run at a time. [1] https://python-rq.org/ https://python-rq.org/
• dmtroyer 3y ago
  > took half a day to implement + test anyone else have trouble completely disregarding the whole of the article when they see things like this?
• crooked-v 3y ago
  A lot of companies would be better off if they had just used a single big database instance with some read replicas instead of all the distributed cloud blahblahblah that 99.9% of even tech companies will never need.
• est 3y ago
  in my experience, implementing a queue in pg is more troublesome than mysql. In Mysql you can directly call Update queue set working_id=xxxx where working_id='' limit 1 to atomically pop one task from queue.
• sidcool 3y ago
  What are the workers that the post mentions? Are these cron jobs?
• sontek 3y ago
  Another article I saw on HN awhile back where someone did this: https://webapp.io/blog/postgres-is-the-answer/ https://webapp.io/blog/postgres-is-the-answer/ I think its a reasonable option and the webapp.io people scaled it out pretty high. At Zapier we utilize RabbitMQ heavily and I cannot imagine scaling the amount of tasks we handle each day on postgres. This article mostly is lesson learns and misconfigurations of RabbitMQ though. Which is probably a good reason to simplify if you don't need its power. No reason to have to learn how to configure it if postgres is good enough.
• concerned_ 3y ago
  RabbitMQ may have been overkill for the need, but it's also clear that there was an implementation bug which was missed. Db queues are simple to implement and so given the volume it's one way to approach working around an mq client issue. Personally, and I mean personally, I have found messaging platforms to be full of complexity, fluff, and non-standard "standards", it's just alot of baggage and in the case of messaging alot of bugs. I have seen Kafka deployed and ripped out a year later, and countless bugs in client implementations due to developer misunderstanding, poor documentation, and unnecessary complexity. For this reason, I refer to event driven systems as "expert systems" to be avoided. But in your life "there will be queues"
• DevKoala 3y ago
  I’ve been doing this for a long time. Back then I thought I was just being lazy, not wanting to maintain another component for a low volume of events, but over time, I saw the elegance of reducing the number of components in the architecture. Today, a quarter of a billion dollar/yr business runs on top of that queue, which just works.
• mads_quist 3y ago
  While some argue that RabbitMQ was misconfigured, I agree with the argument that reducing tech stack complexity is beneficial if a technology does not offer advantages that cannot be achieved with the basic tech stack. When programming my side hustle I also had the requirement of a SIMPLE queue. I didn't want to introduce AWS SQS or RabbitMQ, so I wrote a few C# classes which where dequeueing from a MongoDB collection. It works pretty well. It basically leverages the atomic MongoDB operation "findAndModify", so you can ensure that dequeueing will find only messages in status "enqueued" and in the same operation sets the status to "processing" so you can ensure that only one reader processes the message. (https://www.mongodb.com/docs/manual/reference/method/db.collection.findAndModify https://www.mongodb.com/docs/manual/reference/method/db.coll...) I created a small NuGet package, which you can find here: https://allquiet.app/open-source/mongo-queueing https://allquiet.app/open-source/mongo-queueing.
• yakorevivan 3y ago
  [dead]
• sass_muffin 3y ago
  I find it funny how sometimes there are two sides to the same coin, and articles like these rarely talk about engineering tradeoffs. Just one solution good, other solution bad. I think it is a mistake for a technical discussion to not talk in terms of tradeoffs. Obviously it makes sense to not use complex tech when simple tech works, especially at companies with a lower traffic volume. That is just practical engineering. The inverse, however, can also be true. At super high volumes you run into issues really quickly. Just got off a 3 hour site-wide outage due to the database unable to keep up with the unprecedented queue load, and the db system basically ground to a halt. The proposed solution is actually to move off of a dedicated db queue for SQS. This was a system running that has run well for about 10 years. Granted there was an unprecedented queue volume for this system, but sometimes a scaling ceiling is hit, and it is hit faster than you might expect from all these comments saying to always use a db always, even with all the proper indexing and optimizations.
• rawoke083600 3y ago
  By holding on to msg for a long time,and then ack ? You basically trying to 'store state' in your message-pipline ?
• jwmoz 3y ago
  For small stuff, rabbitmq and celery are hideously heavy to use. I had issues with celery-for bg tasks that are scheduled you could not execute further async requests in the tasks which was mind blowingly useless. This was years ago. Nowadays for small stuff I just create a simple script that uses asyncio, I can async request stuff and run_forever etc and handle it all as a docker service.
• yawboakye 3y ago
  from what i gather, it looks like they saw the 'q' in rabbitmq and thought 'ha, a queue we could use.' totally ignoring the 'm' part. the blog post doesn't say much but it's obvious they were not sending 'messages'. a message is a specific thing, it contains information that is meaningful to the recipient[0]. or perhaps rabbitmq has allowed itself to be drawn into all sorts of use cases (as a result of competition with kafka)? a message should be very small, immediately ack-ed or rejected (i.e. before any processing begins). that's why rabbitmq assumes it can run entirely in memory, because messages are not expected to stay in queues for long (i.e. during processing by recipients). [0]: https://en.wikipedia.org/wiki/Information_theory https://en.wikipedia.org/wiki/Information_theory
• akamaozu 3y ago
  re: Prefetch and Reconnection Issues - clear case of misconfigured instance (prefetch) and a bug somewhere in the stack (reconnection issue) - prefetch behavior sounds like they receive a message, ack it then process it - i wouldn't recommend ack before processing, because you become responsible for tracking and verifying if the worker ran to completion or not. - work then ack is the way. the other way around ignores key job processing benefits like rabbitmq automagically requeueing messages when a worker crashes and failure related logic like deadletter queues. - the trick i've started leaning on with rabbitmq is giving each worker their own instance queue (i call it their mailbox). - when a worker starts a job, it writes the job id, start time and the worker's mailbox to a db. any system can now look up the "running job" in the database, know how long it has been running and can even talk to the worker using its mailbox to inquire if it is running and if that job state in the db is accurate. - happy the writer and team found what works for them. ultimately, what you understand best would serve you better, so they made a good choice to lean on their strengths (postgre).
  • janwillemb 3y ago
    Other comments point out that your proposed solution is probably their problem and they should do a quick ack instead.
    • akamaozu 3y ago
      I don't agree with their assessment, but also not really interested in putting effort into debunking it. Just occurred to me there's a different possibility: The extra connection bug mentioned could be from a rogue thread/process/code-path connecting to the queue and fetching an item, but somehow not doing the work. Verifying this likely requires access to the codebase, so treat this as little more than idle speculation.