4 ms·

how is writing reliably to a queue any different from writing to a database? I'd say same principles should apply - journals and two stage commits as a way to v

how is writing reliably to a queue any different from writing to a database? I'd say same principles should apply - journals and two stage commits as a way to verify writes?

scarface74 8y ago
In theory none. But in practice, a lot more can go wrong when writing to a database.
- tatersolid 8y ago
  Actually, with 20+ years working in financial services, I’d say the transactional RDBMS table as queue pattern is by far the simplest, most reliable, and almost always the best choice queueing system for business applications. Every other queue system I’ve encountered has terrible failure modes, buggy clients, weak semantics, and huge serialization overhead.
  - scarface74 8y ago
    Using a database as a queue is a well known anti pattern. http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html http://mikehadlow.blogspot.com/2012/04/database-as-queue-ant... Transactions make the problem worse with locking.
    - tatersolid 8y ago
      That’s one opinion, and ignores the reality that almost all message passing systems need to store state in a database. Read all the comments on that post. Transactions make the problem easier, not “worse”.
      - scarface74 8y ago
        If you have twenty producers and twenty consumers and transactions you are going to get all types of dead lock scenerios. I have one queue process that at its maximum scaling has 16 processes consuming a queue with 10 messages each running 10 threads - 160 messages at once. They are only doing inserts and updates - at most a row lock. But if they had to have queueing logic in the database, you would have to have locks on the table holding the messages and the statuses of the messages. Not to mention even more of a database load to delete the messages. Of course on top of that you are constantly polling the database. Now compare that to a purpose built queueing system where you just read from the queue, it automatically marks the messages as unavailable- “in flight” - and that will automatically requeue the message after a certain amount of time of it isn’t deleted. As opposed to a database system where either messages get stuck in “processing” status of a job fails or you have another process reading the queue and “fixing” the issue after a certain amount of time has passed. And finally, what happens if you need a fan out type of queue? Where you have one producer and multiple types of consumers?
        tatersolid 8y ago
        What, exactly, do you think a purpose-built queuing system does under the hood? If it’s sane, it’s doing exactly the same transactional row locking an RDBMS is highly optimized to do. I have apps with RDBMS queues doing thousands of work items per second with 16 workers. Very few applications need more than this. Introducing a whole new subsystem and API for queueing is a bad engineering decision in most cases. Fan-out is generally an anti-pattern, but if you need it, then explore other options Note that I am talking about Kafka-style linear queues in an RDBMS, not pub-sub. Triggers or app code do very fast transactional inserts into queue as part of the initial write; consumers fast-poll with “update top 1 ... where status = <unprocessed>” and back off polling exponentially when the queue is empty. This is just a few lines of code trivial to do correctly and cannot deadlock in READ COMMITTED or SERIALIZABLE modes.
        scarface74 8y ago
        How is “fan out” an anti pattern? You have an event or message that needs to be processed by multiple systems. This is completely normal. So introducing a purpose built queueing system - that people have been doing for decades instead of using a database is technically bad? A queueing system at most has to respond to a request, set a flag for “processing” and it’s done and since by definition, a queue only has to read the top most item, it is more efficient. Besides, how do you handle a message that is sitting in a “processing” state because the process that originally read it crashed and didn’t change the status? Triggers or app code do very fast transactional inserts into queue as part of the initial write; consumers fast-poll with “update top 1 ... where status = <unprocessed>” and back off polling exponentially when the queue is empty. This is just a few lines of code trivial to do correctly and cannot deadlock in READ COMMITTED or SERIALIZABLE modes. Or instead of reinventing the wheel with your own bespoke database as a queue that has all of the maintenance overhead you could just use a queueing system that is already optimized for that use case. Note that I am talking about Kafka-style linear queues in an RDBMS, not pub-sub. In general parlance, most people would call Kafka style processing stream processing. Even then, why write and maintain a pseudo streaming process that you have to maintain instead of just using Kafka where you can let it handle all of the fault tolerance, partitioning etc? It’s about like a past company that I worked for where the “architect” had his own homegrown ORM, encryption scheme, and configuration system instead of just using an off the shelf solution because he thought his system was its own special snowflake.

how is writing reliably to a queue any different from writing to a database? I'd say same principles should apply - journals and two stage commits as a way to verify writes?

scarface74 8y ago

In theory none. But in practice, a lot more can go wrong when writing to a database.

tatersolid 8y ago
Actually, with 20+ years working in financial services, I’d say the transactional RDBMS table as queue pattern is by far the simplest, most reliable, and almost always the best choice queueing system for business applications. Every other queue system I’ve encountered has terrible failure modes, buggy clients, weak semantics, and huge serialization overhead.
- scarface74 8y ago
  Using a database as a queue is a well known anti pattern. http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html http://mikehadlow.blogspot.com/2012/04/database-as-queue-ant... Transactions make the problem worse with locking.
  - tatersolid 8y ago
    That’s one opinion, and ignores the reality that almost all message passing systems need to store state in a database. Read all the comments on that post. Transactions make the problem easier, not “worse”.
    - scarface74 8y ago
      If you have twenty producers and twenty consumers and transactions you are going to get all types of dead lock scenerios. I have one queue process that at its maximum scaling has 16 processes consuming a queue with 10 messages each running 10 threads - 160 messages at once. They are only doing inserts and updates - at most a row lock. But if they had to have queueing logic in the database, you would have to have locks on the table holding the messages and the statuses of the messages. Not to mention even more of a database load to delete the messages. Of course on top of that you are constantly polling the database. Now compare that to a purpose built queueing system where you just read from the queue, it automatically marks the messages as unavailable- “in flight” - and that will automatically requeue the message after a certain amount of time of it isn’t deleted. As opposed to a database system where either messages get stuck in “processing” status of a job fails or you have another process reading the queue and “fixing” the issue after a certain amount of time has passed. And finally, what happens if you need a fan out type of queue? Where you have one producer and multiple types of consumers?
      - tatersolid 8y ago
        What, exactly, do you think a purpose-built queuing system does under the hood? If it’s sane, it’s doing exactly the same transactional row locking an RDBMS is highly optimized to do. I have apps with RDBMS queues doing thousands of work items per second with 16 workers. Very few applications need more than this. Introducing a whole new subsystem and API for queueing is a bad engineering decision in most cases. Fan-out is generally an anti-pattern, but if you need it, then explore other options Note that I am talking about Kafka-style linear queues in an RDBMS, not pub-sub. Triggers or app code do very fast transactional inserts into queue as part of the initial write; consumers fast-poll with “update top 1 ... where status = <unprocessed>” and back off polling exponentially when the queue is empty. This is just a few lines of code trivial to do correctly and cannot deadlock in READ COMMITTED or SERIALIZABLE modes.
        scarface74 8y ago
        How is “fan out” an anti pattern? You have an event or message that needs to be processed by multiple systems. This is completely normal. So introducing a purpose built queueing system - that people have been doing for decades instead of using a database is technically bad? A queueing system at most has to respond to a request, set a flag for “processing” and it’s done and since by definition, a queue only has to read the top most item, it is more efficient. Besides, how do you handle a message that is sitting in a “processing” state because the process that originally read it crashed and didn’t change the status? Triggers or app code do very fast transactional inserts into queue as part of the initial write; consumers fast-poll with “update top 1 ... where status = <unprocessed>” and back off polling exponentially when the queue is empty. This is just a few lines of code trivial to do correctly and cannot deadlock in READ COMMITTED or SERIALIZABLE modes. Or instead of reinventing the wheel with your own bespoke database as a queue that has all of the maintenance overhead you could just use a queueing system that is already optimized for that use case. Note that I am talking about Kafka-style linear queues in an RDBMS, not pub-sub. In general parlance, most people would call Kafka style processing stream processing. Even then, why write and maintain a pseudo streaming process that you have to maintain instead of just using Kafka where you can let it handle all of the fault tolerance, partitioning etc? It’s about like a past company that I worked for where the “architect” had his own homegrown ORM, encryption scheme, and configuration system instead of just using an off the shelf solution because he thought his system was its own special snowflake.