Pull important messages from DLQ queue and save them in a relational database, to be analyzed and sent back later. Is it a good idea?

Question

My project has a integration with a external system. We need to send some important informations to this system. For this, we create a micro service to connect to this external system. This micro service receive async messages from our internal systems in a dedicated Queue for that, and the micro service read the messages and try to send them to the external system. Very simple.

Before explain my doubt, I will put you guys on the context.

Context

We are not sure if this external system is reliable. Sometimes the external system will be off and we need some way to recover from this. So we implement a retry mechanism. But we also imagine that this external system could be off for a lot of time and maybe even reject some valid messages until we talk with the support team. So, we also create a durable Dead Letter Queue to receive this rejected messages.

In the worst scenario, it will be a lot of messages on the DLQ queue to analyze the cause of why they are rejected. So my team have the idea to pull the messages from the DLQ queue and persist them as a Json in a database, because we imagine that will be necessary to edit them.

The question

And my question is about this last paragraph: save messages on database. I'm not sure if is a good idea, seems to me that we are just replicating the durable feature from the Queue on a database.

The idea came from the fact that the messages on the database will be easier to analyze (maybe we will need to do that), edit them (maybe we will need, probably not) and have a more reliable way to manipulate the messages, because one mistake on the RabbitMQ Management and the message is gone. We will also will need some kind of cron job just to read this messages from the database and send them again for the main Queue.

As you see, there are a lot of maybe and a lot of extra work (database, tables, cron job, etc), and I'm not really comfortable with the solution. Of course we will be more safe if the messages could be analyzed and edited on a relational database, but I'm not sure if we will use this feature on the future.

I made some research about the use of RabbitMQ for rejected messages that could be analyzed for the team and sent back again to the main Queue, but this is a not a common scenario.

Answer 1

The proper term for the issue you are addressing is 'poison message processing'. Dead letter queues are typically used by messaging systems for undeliverable messages. A poison message is one that a client application cannot successfully process. A 'poison message queue' is a queue that is configured to receive these messages in order to prevent a message from being read and rolled-back indefinitely. This is good practice in order to prevent one bad request from causing messages to back up and exceeding queue depth which will start to cause upstream failures.

In the specific case that you are concerned with where a downstream application is offline, one easy solution is to stop processing messages on the queue. There are a couple of issues with this. The main one is that if you have a high volume of messages coming in, you could fill the queue. Another potential issue is that if that queue is not persistent, having a lot of messages sitting in it puts you at risk for data loss.

My experience with messaging solutions leads me to this general recommendation: do not rely on queues for preventing data loss. No matter what the vendor claims about 'guaranteed delivery' there are so many pitfalls and challenges in making such a system leak-proof. If you need to make sure something arrives, you need to have persistent store where you keep enough information to be able to regenerate a message in the case of loss. Often this is in a database. This is similar to what you had proposed but instead of pushing messages to a DB when they are being read off of the queue, you persist to a DB at the point of origination. In addition, I would recommend a ledger-type approach of recording (at least) the final state of each record in that store. If you need to purge data, you can do so in bulk later on.

It might seem that if you do this, queues are irrelevant but this is not the case. Queues are a great way to move and process messages in a distributed system and avoid contention. By using a DB ledger with queues, you get the best of both worlds.

Answer 2

I understand your concerns about this, but I would say that yes, you do need to extract the dead messages from the queuing system.

My sole reason for saying this is this sentance:

maybe even reject some valid messages until we talk with the support team

A dead letter queue is great for exceptions or handling a break down of a component, but the expectation is that after fixing the problem you can re-run those messages and they will flow back through correctly.

But you seem to have a requirement for manual intervention and possibly editing individual messages as part of your process. ie It is expected behaviour to have a team of people routinely go through this data, argue with the third party about their validity, possibly change some of the data and resend.

If indeed you do have this requirement, then I would say that you have to deserialise the message in to some application which handles that process. Including presumably a persistence layer and a proper UI. You can't just leave it up to people editing json and manually mucking around with queue configuration.

So I would go much further that the suggested solution of a database with json, which I agree is hardly different from the DLQ.

Perhaps you could say to them "Look, if these really are things we expect to happen and its a real requirement. Then we should be doing a full solution. If we are just add databases because we are more comfortable with them than queues, then we should be doing everything in databases'

Answer 3

For the people who have interest about what decision we make:

• The DLQ idea was rejected. Too much complex without real gain for us.
• We read the message from the main Queue and, before communicate with the unreliable external service, we save the message on a table with the status SENDING, with some important information about the message on some specific columns and a specific column to save the entire message on Json format (I will explain why later).
• If the integration with the unreliable external system occurs with success, we change the status from SENDING to SUCCESS.
• If occur some problem with the integration, we capture the exception, save the error on a second table linked with the first table, and change the status from SENDING to ERROR.
• After that, we can choose to try to trigger the integration again using some job or give this decision for some backoffice user. To not have performance problems or anything like that, when the user/job try to send again the ERROR message, we read the Json column, parse to the original Json to the original Java class (we are using Spring) and send to the main Queue again, to follow the same process.

It works very well. We apply this same strategy for more than one integration and we have no complains about it.