I need to write a service that reads data (a row) from a database, sends out AWS SQS messages, and finally deletes the data from the DB.
Let me explain using an example. I have a database table D, the service S, and SQS queue Q. Every minute, the service S, reads one row (R1) from D, parses it, and sends a SQS message to Q, and finally deletes the row R1.
The problem is, this is a critical service and so I need redundancy. Additionally, SQS message for every row should be sent exactly once.
Possible solutions I thought of (and problems with them):
Multiple processes: I can run the same script on multiple servers. The likelihood of all of them crashing together is low. However, this can lead to multiple SQS messages being sent for the same row. Processes P1 and P2 might read and send SQS message for the same row. If I delete the row, before sending the SQS message, and the process crashes, then I might permanently lose the row without sending any message.
Multiple processes that operate with a delay: I can run multiple processes. P1 operates as explained above. P2 operates with the check that it processes rows that are at least (e.g) 10 minutes old. P3 operates on rows that are at least (e.g.) 20 minutes old. So if P1 crashes, P2 can process the data. If P1 and P2 crash, then P3 handles the load etc. The issue with this approach is that it can cause delays in processing.
Are there any standard design/architecture patterns that I can use for such a problem?
Thank you!