Boring technology version of distributed, highly available and persistent message queuing

I often go for battle-tested solutions for my architectural choices. It doesn’t matter if they are considered boring technology. The primary criterion is that the job should be done with a minimal footprint. One such example is Cloudways’ queue mechanism to perform user requests on their servers.

This system has a simple stack yet has the robust features

• Highly available and Scalable The system enables both producers and consumers to operate from multiple servers simultaneously.
• The system’s persistence in the database allows for the extraction of metrics and the generation of reports, providing essential visibility. Operating it from the database grants complete control over job execution.
• Minimal footprint. Tasks are stored in your existing DB and executed in your existing app stack eliminating the need for tracing the errors and exporting the logs from outside your app stack. AWS SQS is utilized solely for managing concurrency and avoiding duplicates.

Here are the steps to implement it

• Store each job in DB. The DB will be the single source of truth for job status. You can store jobs here for an extended period. Jobs can be re-tried from here later. It can be a data source for the analytics dashboard.
• Create an SQS Queue on the AWS console. It should be set as FIFO to make sure that none of your jobs are processed twice erroneously even if consumers and producers are running from multiple instances. The queue configuration should resemble the image provided below.

A FIFO queue on AWS SQS

• Implement a producer script in your app that fetches job from DB and send them to SQS job. AWS libraries and sample codes are available in all common programming languages. You can use `sendMessageBatch` endpoint to send multiple jobs in one go for optimum performance.
• Implement a consumer script in your app that fetches task(s) from AWS SQS, executes them and marks the job as done in DB. You can run as many instances of consumer script as you want.
• As per your requirements, you can add an optional step to optimize DB performance. Implement a lock mechanism either using DB lock or Redis lock in your producer script to ensure only one of the producer instances is querying the DB at a time.

This is my design for the queue mechanism. What is your evaluation of this, and what modifications can be made to improve its efficiency while preserving its simplicity?