42 ms·

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.

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.