22 ms·

Oh please stop using databases as queues. I spent a disproportionate amount of time in the last 20 years undoing that decision. It doesn’t scale at all well.

• ngrilly 5y ago
  Look at SKIP LOCKED in PostgreSQL and MySQL.
  • hughrr 5y ago
    It’s not that. It usually ends up in the same storage engine as the main business functionality which leads to IO contention. The some sick and twisted bastard does a join to it from somewhere abhorrent which means it requires some serious heavy lifting to fix it. This is unfortunately the status quo in the slightly less ideal universe than “seemed like a good idea at the time” Use SQS or RabbitMQ or something. Edit: Also if you’re using something SQL for queues you’re going to have to build out your monitoring stack yourself based on your implementation rather than suck up a metrics endpoint with queue stats or pull from cloudwatch.
    • jbverschoor 5y ago
      I dunno. If it’s usually the same problem, it should be easy to identify and fix. Unless we’re talking about a high load, there should be no problem doing this.
      • hughrr 5y ago
        It’s only the same problem until someone dumps 25,000 entries in your queue.
        • kasey_junk 5y ago
          I’ve seen all manner of hokey queue implementations in sql going back about 20 years and all of them could handle 25k enqueue bursts. That wasn’t a problem for a Sybase database on a commodity host circa 2000. I think if I were going to argue against using DBs as queues it would be around: heavy parallel write use cases, latency concerns of both reads/writes and scaling to millions of events per second. If you don’t have those concerns using a properly normalized and protected schema (which you are doing anyway right? Cause if not you are already shooting your toes off) for queues goes a very long way and removes a very big operational burden and tons of failure modes.
          • justsomeuser 5y ago
            I agree. Going from a single database to a (database + queue) means two server processes to manage, maintain, observe, test etc. I actually start with SQLite to reduce as much distributed state as possible, then move to something else once it’s proven it will not work.
          • hughrr 5y ago
            It wasn't a problem for Sybase on a commodity host circa 2000 because clearly that host wasn't doing a whole lot of other stuff. It's a big problem for our 48 core nodes with 2TiB of RAM and a metric shit ton of DAS NVMe. Ergo anecdotes don't scale either. To clarify we just moved this entire problem to SQS.
        • crazygringo 5y ago
          What database can't handle an extra 25,000 entries? That's... nothing. Databases handle billions of rows effortlessly using a b-tree index. So not really sure what point you're trying to make?
    • glogla 5y ago
      The benefit of having the queue in the same engine as the main business functionality is that you can do stuff in a single transaction. Having the queue on its own means you have to handle exceptions like "job is done but queue didn't get updated" or get into distributed transaction which is very different challenge. Also, operationally having one inherently stateful component (db) versus two (db, queue) makes things a lot simpler to manage, deploy, update, etc. Not claiming this is a perfect solution, just that the tradeoff isn't obvious.
      • hughrr 5y ago
        The point of queues is sequencing, storage and decoupling of events for the sake of scalability and durability. By putting it in the same transactional store with the same transactional boundaries you’re instantly shooting the whole point. Not only that, most queues define boundaries between different logical systems where transactional boundaries do exist. At which point your database’s transaction scope is extremely limited in capability. In the real world of messaging transactions span more than just the database engine. It’s just bad architecture. Full stop.
        • victor106 5y ago
          Having a separate queue makes sense across application boundaries. If you need queuing mechanisms in your own application you should be able to get by with a dB for 99% of use cases
        • dragonwriter 5y ago
          > The point of queues is sequencing, storage and decoupling of events for the sake of scalability and durability. The point of queues in any particular application is some subset of that; the calculus of implementation approaches that make sense depends not on the abstract point of queues in general but on the concrete point of queues in your use case.
    • flefto 5y ago
      So use a separate database. Is contention with the application your only objection? That’s pretty weak. What’s the real compelling reason not to use a database? You haven’t said.
      • hughrr 5y ago
        Contention, architectural separation, you have to build your own monitoring stack, not transactional with concerns outside the database without introducing distributed transactions and risk, no routing or distribution capability, you have to build it yourself, point in time message durability is somewhat dubious depending on your acknowledgement process which of course you had to invent yourself as well. Not to mention numerous database administrators crying into their cups of coffee. Enough? Like I said 20 years of people fucking this up is my experience.
        • flefto 5y ago
          Meh. All your objections are hand wavey and not factual. Databases actually work fine as a queue but emotionally you don’t like it. That’s fine it’s just not real strong objections. What you have not said is “it physically does not work”, and that’s because it does work fine.
          • hughrr 5y ago
            I’m not saying it doesn’t work. I’m saying you’re shooting your toes off down the line.
          • phildawes 5y ago
            I don't think he's saying 'it cannot be physically made to work (given enough effort and discipline)'. The impression I got was more like 'if you go down this route then the incremental path of least resistance leads somewhere bad'.
          • rmetzler 5y ago
            The range where databases work fine is pretty small. Sudden spikes in job creation will kill the performance of the database. Often enough the job creations come from external systems you can't control.
• aidos 5y ago
  The solution I use now is to have the queue in the dB and then have a single process pushing to another queue better suited to dealing with workers. I drank from the “keep your queue out of the dB” koolaid once, but then you end up worrying about transaction boundaries and atomic writes to the queue and the dB. It totally depends on your workload, but in my case, I’ve found the halfway solution of the queue in the dB so you get to write to it within your transactions, with a dedicated queue for the workers to be a much better solution.
  • hughrr 5y ago
    Are you sure it works properly when something fails. That’s a wonderful situation I like to ask people because the answer is usually no. Then someone gets billed twice for something and then the guy who said this was a good idea gets shot.
    • aidos 5y ago
      Well, I only ever enqueue idempotent work, so again, in my scenario it can fail and be rerun, but I’m also unsure about what you think is a bad idea here. Honestly, I had more code managing two phase commits so I would only push to the queue when I was sure (ish) the dB commit was ok. Say you need to trigger something to send an email, but let’s say that there’s an error condition after and the transaction is rolled back. How do you handle that failure? In my scenario the queue is rolled back too, no harm to foul.
  • AtNightWeCode 5y ago
    The outbox pattern. Can in many cases be solved by using topics.
    • aidos 5y ago
      Ah ha. Yup, that’s exactly it. Thanks for the pointer. Edit: can you explain by what you mean by solving it with topics? I thought topics were a broadcast mechanism?
• skytreader 5y ago
  This please. I feel like "How to Get Away with Just PostgreSQL" and the GP comment falls squarely under being too preoccupied with whether you could, you didn't stop to think if you should. Whatever happened to use the proper data structures for the job? PostgreSQL and MySQL are, at the end of the day, b-trees with indices. Throw in relational properties and/or ACID too. Those aren't properties you need or want in a queue structure. I know I don't have a solid argument against not doing it; it's just experience (and dare I say, common sense) telling me not to. Not quite like parent but I spent the first two years of my professional career in a team that had the brilliant idea to use DBs as queues. The big task I partook in for that stint is moving them off that v2 into a v3 which used---wait for it---Redis. Everyone's quality of life improved with every migration, proportional to the size of the v2 cluster we retired.
  • zigzag312 5y ago
    > Whatever happened to use the proper data structures for the job? This so much. People too often treat databases as magical black-boxes that should handle anything. Database is most often the bottleneck and choosing the proper storage engine with appropriate data structures can be 100x more efficient that just using the defaults. 1 server vs 100 can definitely make a noticeable difference in costs and system complexity. While premature optimization is bad, choosing the right tool for the job is still somewhat important and will usually pay off in the long run.
    • mirekrusin 5y ago
      I think your "most often" is more like 0.01%, I'd say the inverse is true, that _most_ would be fine with single sqlite host or something like rqlite.
      • otoolep 5y ago
        rqlite author here. Happy to answer any questions about it. https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
• mianos 5y ago
  Skype at its peak used a postgres queue extension, pgqueue, at its core. Seemed to go OK for them. The extension did work around some limitations when running a pure sql queue in postgres. These days I would run Pulsar but if you only have a million users and already have postgres you will be fine.
  • conradfr 5y ago
    I worked at a place using pg_queue (the person who created it worked there before going to Skype IIRC) and maybe it was working well operationally but it was not a great experience to develop on it. It was hard to generate events and debug/observe them (it was height years ago so the details are a bit fuzzy). MS also moved from it afterwards when they acquired Skype but I guess it was more because of the shift to SQL Server.
    • mianos 5y ago
      Marko Kreen? He still maintains it so I guess some people still use it and the associated logical table distribution system.
• cerved 5y ago
  In your experience, roughly at which point did it stop scaling and why?
  • rmetzler 5y ago
    I also have a lot of issues with people using tables and cronjobs instead of just using queues. I once was the one implementing this stupid idea. It's very easy to shoot yourself in the foot. It seems to be ok at first, but it is a stupid idea and will give nightmares to the operations team. It works as long as the table is small. It gets especially problematic when there are sudden spikes in the workload, so that the table is growing rapidly. People don't test these scenarios enough. Often it's important to do the jobs in the order they came in. This means you need to have the index set up this way or you'll end up with full table scans. You also need to remove jobs which are done, but this will also mess with the tables performance. There are tricks with databases to help scaling this, but at the end of the day it's much simpler to just use a queue.
  • hughrr 5y ago
    It stopped scaling when we put in the PO for a $150k x86-64 box to run it on and finance said WTF.
    • cerved 5y ago
      What about in terms of number of requests and requests per second?
• basilgohar 5y ago
  For the scale of most tasks, the existing RDBMS that a small app is using for its data is perfectly sufficient for the queuing needs of said app. This reduces complexity in the app and removes the need to maintain additional tools. Frameworks like Laravel make this easy because they allow you choose different backends for your DB, queue, and cache, among other things, all or some of which can be an RDBMS like Postgres or MySQL/MariaDB. When your app's need outscale what the RDBMS can rationally handle, then you can dedicate the resources to switch to a more specific tool as your growth has indicated you should. I can't say what the percentage of apps that can handle things this way, but the anecdotes mentioned in this thread are clearly the cases where growth outstripped what a traditional RDBMS like Postgres are well-suited for, and so migrating was the right thing. The probably was likely identifying the need or delaying the transition until it was too painful. I don't think there's a magic bullet here, but I also don't think that starting with a dedicated queuing store tool is also always the right thing to do.
• blowski 5y ago
  You’re dealing with survivorship bias - those companies whose products were successful enough that they needed your services. You don’t speak to the teams who went bankrupt because they spent too much time fiddling with RabbitMQ, when Postgres would have been fine. As always, there are trade offs, no silver bullets.
  • 2muchcoffeeman 5y ago
    In my experience proper abstraction is the key. If you have a clear abstraction and structure you can replace the implementation when you need to without too much disruption. If it’s too leaky you’re screwed.
    • goodoldneon 5y ago
      100%! Create a good enough solution and put it behind an abstraction that remains stable when you later create an ideal solution
    • hughrr 5y ago
      Yes. Use a queue abstraction for which there are numerous queues available off the shelf for!
    • aparsons 5y ago
      In my experience, great abstractions without a good data migration blueprint is as about as useful as no abstraction at all. In this example - great, you have a “queue” abstraction. When you switch to RabbitMQ or Postgres, how do you move your existing data without a quality of service or data loss? It can be difficult with production datastores even if the abstraction within your service is beautiful.
      • lolinder 5y ago
        Isn't migrating a queue super simple? Have a window in which you only add to the new system and you listen to both. When the old queue is empty, delete it. If you need to keep track of old data, then yes, migration is hard. But queues don't save old data—that's the point of a queue.
• baq 5y ago
  the other side of the coin is that queue servers are databases, just optimized for the use case.
• atombender 5y ago
  You're not wrong, but every solution involves compromises. More broadly, I would argue, though, that almost nobody actually needs queues. Sure, there are cases were you actually need strict ordering, but they are relatively few, in my opinion, involving things like transaction processing or other areas where the order of operations change the effect of the operations and must be strictly sequential. Ordering itself brings significant technical challenges. For example: With a queue, you can only have strict ordering as long as you only process one item as a time, with no concurrency. Ordering also complicates handling of failures and retrying, because it means every newer item has to wait until the failed item has been dealt with; if you use things like dead letter queues, order is violated. In almost all use cases -- anything from image processing to newsletter delivery -- a better system is to have an unordered list of items that anyone can take items out of, in any order. Then you throw as many workers at this system and design it so that there's minimal locking needed to prevent workers from stepping on each other's. There are queue systems that have a purpose in a stack, specifically pub/sub brokers, which can be used to schedule work along several dimensions. You use the pub/sub system to signal work to be done, but each payload just refers to some state that's stored elsewhere.
  • hughrr 5y ago
    I think you're missing something important here by applying the worker pool simplification to the idea. Order is important i that case as well. It forces both fair scheduling of the work and the metrics from the queue can easily describe the overall latency of the work being done and the capacity and utilisation. The latter can be used to scale the consumers of the queue and/or alert on when things aren't working properly. If you throw a proper messaging broker on the table like RabbitMQ then you're getting message acknowledgement as well which is important if you must do the work. A failed consumer will result in the work being rescheduled on another consumer transparently. Of course there are metrics you can see and get heads up when your consumers are not working. Ergo, don't simplify something too soon. Even reliable delivery of newsletters is rather important otherwise why would all the tracking pixels exist? (they are a form of message acknowledgement)
    • atombender 5y ago
• draw_down 5y ago
  I bet a bunch of them love the phrase "use the right tool for the job" too. Just shows how hollow/meaningless that phrase can often be
• vincentmarle 5y ago
  Exactly, GP does not sound like someone who has worked on anything with decent scale. Because if you did, you would know that it is a really, really bad idea.
  • lolinder 5y ago
    Or maybe GP is someone who has worked on projects from their infancy. If your queue will be handling less than a request per second for the foreseeable future, why not just use the technologies already in your stack and plan a migration path now? What do I gain from adding Redis (and thereby making my system even more distributed, with all the hazards that carries) if my project isn't even guaranteed to make it to 10 rps before going bankrupt?
  • manigandham 5y ago
    Very few things have "decent scale". 99% of apps can run on a single server with modern hardware.
• crazygringo 5y ago
  Can you explain exactly what doesn't scale well? Databases scale well in general, it's one of the things they're built for. A single database server with an indexed table (b-tree) and an SSD is extremely performant. (And yes there are other indexes that could be even more performant, but b-trees are already extremely fast.) But the huge advantage of putting a queue in your database is being able to create transactions across both the queue and data in other tables that needs to change at the same time. Not to mention one less extra tool to maintain, existing database tools for backups replication etc. automatically include the queue as well, etc. As well as the fact that it's incredibly easy to make your queue behave according to whatever kind of custom business logic you might need. (Which is where additional indexes and b-trees can turn out to be necessary, and which is precisely what databases are great at.) So to the contrary -- unless you're at Facebook-level scale, using modern databases for queues generally seems like an excellent choice. (And in the special cases where it's not, that's usually quite obvious.)
  • Rapzid 5y ago
    I believe most people simply have no concept of how performant modern RDBMS are. "Joins are slow" and all that.
  • sh87 5y ago
    I very strongly agree to the idea of using the tools you already have and know to solve the problem at hand and release it. Then observe where it could use help, then seek options on specific products solving those problems and only then can we get to real good questions that can pierce the veil of marketing and the comfort of herd mentality.
• onlyrealcuzzo 5y ago
  There are A LOT of use cases for a queue that aren't particularly computer intensive and you aren't worried about ever needing to scale - especially larger than your Postgres database. So while this might be good advice if you will need to scale, it's certainly not blanket advice. In the first case, I would much rather just have Postgres and not a separate queue to manage.
• maxrev17 5y ago
  Lean startup begs to differ :p
• sonthonax 5y ago
  It can absolutely scale well if you design the database properly. However, it’ll probably be fairly high latency compared to a Redis or Kafka stream. The things that often make databases appear slow is that they’re typically configured for large random reads rather than small uniform writes. And they have insane initial response times because of all the IPC they typically do when starting a session. But aside from that, if you use a heap table without an index you’ll can probably get similar throughout to Kafka. And if you turn Fsync off you might even get up to REDIS speeds.
  • oneplane 5y ago
    It's possible at scale, but the engineers that can do that are usually 1:20 outnumbered within most organisations. (when scoping out FAANG and co) Asking a developer to design a database is not even normal process today, unless the ORM does it for you, very little actually is done with the database. It might as well be a flat file...
    • api 5y ago
      That’s sad. I learned relational DB design before I even really got into systems programming and it was really a valuable thing to learn. You can be so much more efficient and less error prone with normalized data structures. There’s a reason SQL is still around after all these years. It’s basically math and while the syntax is a little moldy the principles are eternal.
• holoduke 5y ago
  One of my businesses is running a software solution build in 2012. Ever since it is using queuing via a database. It's simple, easy to maintain. And scales really well. We are talking about 10k queues per second. Yes it disattracts developers like you. They usually prefer the latest fancy trending tool. luckily those types are often responsible for overengineering things and overly complex solutions.