Design a messaging queue table in MySQL

Background

Implement a messaging queue table in MySQL, including

• table name, columns and index

• thoughts behind the design

Hit

• Use one table for each message queue, or put all messages in one table and add a "queue name" field.

Thoughts

• Different queues/jobs have different requirements. From a scalability perspective, using separate tables is a better solution.

• From a priority perspective, we can use the message-id (auto-increment primary key) or store a specific priority weight in the database with each message.

• For high availability, we can use a master-slave database pattern.

• Have a success consumption log table and an error log table for each queue. The success consumption log table is for auditing purposes and the error log table is for retrying mechanism.

• Have a clean-up schedule job to delete any data older than N days (based on the business requirement).

MySQL table

Queue message table

# table name, eg for queue foofoo_queue
# columnid 				  auto-increment primary keyname			  queue name (varchar)status			queue message status (enum)message			(varchar)priority		(enum)created_at	(datetime)

Success consumption log table

# table name, eg for queue foofoo_consumption_event
# columnid 				  auto-increment primary keyname			  queue name (varchar)status			queue message status (enum)message			(varchar)priority		(enum)created_at	(datetime)

Error log table

# table name, eg for queue foofoo_failed_event
# columnid 					  auto-increment primary keyname				  queue name (varchar)status				queue message status (enum)message				(varchar)priority			(enum)error_message (varchar)error_code		(varchar)created_at		(datetime)